Download - New ScanPower Unity Data Report
©2014 MyPromote Books. All Rights Reserved. Page 1 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
The new ScanPower Unity program provides many more benefits than its previous version – but not all of them are obvious. This Step-by-
Step™ will show you where to find the new data report that comes free with ScanPower List and how to use it. I have asked ScanPower to put
this report under its own tab at the top of the page and received feedback that this was a good idea. Depending on when you are reading this,
you may click on a “Reports” tab to get to this report. In this Step-by-Step I will also cover how to sort the report in Excel and use it to gather
data for your bookkeeping, CPA, etc. To get started, go to: https://unity.scanpower.com and sign in to your account.
You will see a mostly blank screen with tabs at the top. Choose “Settings” and “List”
If you are interested in knowing more about the other settings on this page, see my previous Step-by-Step on the new Unity HERE. For this
Step-by-Step we are focusing on the data report.
With the settings open, scroll down to “Inventory.” The picture is on the next page. First, make sure your inventory has been recently synched
with Amazon. Click on “Sync Now.” Next, click on “View My Inventory.” See next page for image.
©2014 MyPromote Books. All Rights Reserved. Page 2 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
All your inventory will show up – active and inactive – along with all your notes, prices, MSKU, FNSKU and much more. This raw data is
unsorted. You will need to download it to sort it. We’ll cover that next. I have over 11,000 MSKUs right now but less than 4,000 are active.
©2014 MyPromote Books. All Rights Reserved. Page 3 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
As of the date of this report (Jan 2014), there is no way to sort the data onscreen. That may change in the future. In the meantime, you can
download the report and use Excel or some other spreadsheet program to open it. Go to the very bottom of the page of your report. On the far
right-hand side you will see a tiny link that says “Download.” Click on it.
You will now see a pop up screen and be given the option to open the file or save it. Excel is the default for my computer. If you don’t have
Excel, you may see something different or no “Open with” option at all. You can choose to save the file and then open it later from inside your
chosen spreadsheet program. The file is a “.CSV” file which means it can be opened by pretty much any spreadsheet program. The data is
separated by commas (CSV=”comma separated values”). See the picture below:
©2014 MyPromote Books. All Rights Reserved. Page 4 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
Save the file right now in a place that you’ll be able to find again in the future. Once your file is open in your spreadsheet, you will see a lot of
data squished together. The first thing I do is sort the data so it is easier to read.
©2014 MyPromote Books. All Rights Reserved. Page 5 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
Click on the upper far left corner to select all the data. Your screen will change color to indicate that the data is selected. Next, choose “Sort &
Filter” on the upper right-hand corner of your screen. You want to select “Custom Sort.”
©2014 MyPromote Books. All Rights Reserved. Page 6 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
On this pop-up screen, you have many options for how to sort your data. First, select the “My data has headers” box. Next, choose the column
by which you want to search first – “total-qty.” Next, choose “largest to smallest”:
©2014 MyPromote Books. All Rights Reserved. Page 7 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
Next, click on “Add Level” to add an additional search to your first one. It will be performed sequentially. In my case, I chose to also search by
MSKU. You can add more levels to your search if you want. For me this is all I need for now. Click “OK.”
The data will be sorted but still hard to read at this point. I select all the data again (upper left-hand corner) and choose “format” and then
“auto-fit column width.” This will make it much easier to see the data (see picture on next page). At this point, you may wish to delete columns
you don’t need right now like your notes, the URL for the page, the FNSKU, the UPC and Updated. It is totally optional, but it makes it easier to
view your spreadsheet without needing to scroll from side-to-side to view information. Select the column from the top and then “right-click”
with your mouse. You will get another pop-up screen (see below). After you finish sorting, resizing your columns and (optionally), deleting
columns, save your spreadsheet with a different file name (a “save as”). This will make sure you always have the original data and the modified
data in case you need it in the future.
©2014 MyPromote Books. All Rights Reserved. Page 8 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
:
Adjust the width of all your columns Delete unneeded columns and/or adjust individual columns
On the next page, you will see my cleaned up spreadsheet. Before we go further, a short glossary of terms:
MSKU – “Merchant Stock Keeping Unit.” This the name you give your individual items of merchandise.
ASIN – “Amazon’s Stock Indentification Number”. Each item on the Amazon catalog has its own unique web page and matching ASIN.
©2014 MyPromote Books. All Rights Reserved. Page 9 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
Total Quantity – The total number of units you sent in to Amazon over time.
Cost – What you paid for an item. If you’ve been typing in your cost as you’ve priced items with ScanPower, you will see a number here.
Price - This is the price you’ve listed for the item on Amazon.com
Last Quantity – This is the amount you sent in the last time.
If you look at my “100% Natural Microwave” product, you will see that I’ve sent in 104 of them. The last shipment I sent in had 11 units. The cost
and price look weird, right? That is because when the data transferred, it did not include the decimal point. Looking at the same product and
adding in the decimal point, you can see that I spent $3.89 and I priced it at $19.98 most recently. I’m assuming I’m matching someone since I’m
normally a “.99” kind of gal.
This decimal point problem is a known issue and it may be fixed by the time you go to pull down your data. If not – and if you really want to see
those decimal points – here is the workaround. If you don’t care or the problem is fixed, skip down to where we start using the information to
make decisions:
©2014 MyPromote Books. All Rights Reserved. Page 10 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
Decimal Point Workaround:
1. Insert new columns after “cost” and “price.” Select the column to the right of the desired column. Right click and choose “insert.”
2. Select the top square of the new column after “price.”
3. Type in equal sign and the word “sum” and an open parenthesis =SUM(
©2014 MyPromote Books. All Rights Reserved. Page 11 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
4. Click on the first square under “price.” Then add the asterisk sign “*” and “.01.” Next, close the parenthesis. Your formula will look like
this: =SUM(H2*.01) Hit the “enter” button on your keyboard. H2 = column H, row 2. Depending on the columns you deleted, your
formula may start with a different letter.
5. You should see the correct number in the square now. To extend this fix to the rest of your inventory, grab the lower right-hand corner
of that correct square and pull your cursor down to the end of your spreadsheet. When you stop scrolling down, the corrected numbers
should show up all the way down the column.
6. Repeat this process for the “cost” column.
7. Now you have two columns each that say “cost” and two that say “price.” To hide the ones with the non-fixed numbers, highlight the
column, right-click and then choose “hide.” This will hide the columns you don’t want to see, but keep your formulas functioning. See
the picture on the next page.
©2014 MyPromote Books. All Rights Reserved. Page 12 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
I know what you are thinking…this is a pain! And it is. I’m hopeful that ScanPower will fix this problem soon so advanced Excel skills are not
necessary to review the data!
Reviewing the Data:
Now for the fun part – using the data. One of the benefits of this report is that it will keep track of your inventory costs for you. You can enter
your cost while pricing with ScanPower List and then later use that data for reporting to your CPA or determining how you are doing. For
example, since it is real-time data, you can see your current prices vs. your cost. How much did you have to discount? What is your margin? Did
you make your money off a particular item? This report will help you determine that. Since I HAD been entering my cost data all year, the first
thing I did was create a report for my CPA of all the inventory I had up at the Amazon warehouse on 12/31 and what it was worth. I combined
©2014 MyPromote Books. All Rights Reserved. Page 13 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
the data from the Amazon reports I recommend you print off at the end of each year with this report and could tell her to the penny what my
inventory was worth. Wow!
If you only have 1 of each item up at the warehouse, you can simply total up your “Cost” column. Go to the bottom of your spreadsheet and for
the empty box under “cost,” select it and key in the following formula – with modifications for your actual numbers - =SUM(G1:G3603). Your
formula will be different as “G” is my cost column and yours might be a different letter. Also, “3603” is how many active items I have at the
warehouse. It is the end of my spreadsheet. Yours will be different, depending on how many active items are at the warehouse.
Now the sum will fill in for you. Obviously I have multiples and more advanced Excel spreadsheet work was required before my accurate total,
but I’m trying to keep this simple and not write the Step-by-Step guide to Excel. It works the same way as fixing the decimal points, but you are
now multiplying the cost by the number of units at the warehouse and then adding up the sum of that.
In addition, besides not taking into account my multiples, this report – done for example purposes – does not include leftover inventory from
2012 and 2011 that has no cost entered yet. Since ScanPower didn’t have that feature back then, I didn’t record it that way. I’m currently adding
older inventory costs from last year’s spreadsheet so I can provide my CPA with a final report by the end of the month.
You will notice several other things about this report. For example, many items do not have a title – just my newer items. I don’t know why that
is, but I assume it has something to do with the transition to Unity in 2013.
©2014 MyPromote Books. All Rights Reserved. Page 14 http://www.fbastepbystep.com
ScanPower Unity Data Report – Step-by-Step™
If you have inactive MSKUs as I do, you will also notice they are all at the end of the spreadsheet. This is why we sorted by “total-qty” first.
Anything without a quantity is stuck at the end. In my case, I simply deleted those nearly 8,000 inactive listings and focused on the active ones.
This report also tells me which items require my attention the most. If I have 104 microwave bags to sell, then I’m going to spend more of my
time watching them than a textbook where I only have one copy. I currently have 185 items with more than one unit at the warehouse. It is
easier to spend my time repricing 185 items rather than 3,603. This is not to say I never reprice my books but rather that I spend more time on
my multiples.
If I had wanted to, I could have sorted by condition, price, MSKU…anything.
If you don’t delete the column with the URL, you can click directly from your spreadsheet to the Amazon page and look at who else is selling
that product and for what prices.
Because the data opens up into a spreadsheet, it is easy for me to combine with data from Amazon.com. I can also pull data into my
Quickbooks (most bookkeeping programs have the ability to pull in data from a spreadsheet) if I want to.
Enjoy your new report!
Please be sure to send praise, suggestions or questions relating to the software to ScanPower at: [email protected].