I just saw an author that makes $40,000+ a month and is exclusive to Amazon talk about how hard it is for them to differentiate sales between their two pen names.  And I thought, really?

Then I remembered that there was a point in time when I was in college and I didn’t have a clue how to use pivot tables.  Turns out they’re very straightforward and very powerful, so I thought I’d share how I use them when looking at the Amazon sales reports.

First, you have to download the report from Amazon.  (Note, this is current as of June 17, 2015…)

Log in and go to the Reports sections.

In the third drop down or in the date range drop downs, select the dates you want to use.  (I generally use the month-to-date option.)  Click on the yellow Update Report button on the right.

Scroll down and click on the Generate Report button below the line graph.  You’ll have the option to either open or save the file.  If I’m just curious for a quick look, I open the file.  If I want to keep my calculations, I save it.

Whatever you do, open the Excel file.  You’ll have two tabs.  One is called Royalty Report.  The other is called Orders Report.

The Royalty Report is what you want to use to look at your sales.  The Orders Report lists a number for sales, too, but the number doesn’t include returns, so I tend to ignore it and just use the Orders Report for my borrows.

Okay.  Time to use pivot tables.

Take the Royalty Report and select all of the cells in the table that have values.

Choose Insert-Pivot Table from the top of the Excel spreadsheet.

It should pop-up with a box that lists the cells you just selected and offers to save the table to a new worksheet.  Say Okay.

Now you’ll see a little table on the left-hand side of the page with the options to Drop Row Fields Here, Drop Value Fields Here, Drop Column Fields Here, and Drop Report Filter Fields Here.  On the right you’ll see the names of all of the columns you selected.

Let’s say you want to just look at sales by different pen names.

Click and drag the Author Name column from the right and drop it into the Drop Row Fields Here section.  You can also drag it down to the same option in the bottom right corner.

Now, click and drag Net Units Sold and Royalty to the Drop Value Fields Here section of the table.

Look in the bottom right corner and make sure that it shows SUM for both of these.  (Sometimes it defaults to Count.  If it does that, in the bottom right corner, you’ll want to click on each one and choose to change the value field settings and then choose sum from the list of options.)

Now, something to note here.  This approach treats all sales in all currencies the same.  If you just want sales by USD, for example, then you can click and drag Currency into the filters section and in the drop-down choose just USD.  (I choose Select Multiple Items, click on All (which unclicks everything), and then click on USD.)

If you just want a rough estimate, though, this isn’t necessary.  Currency rates are close enough these days that you’ll be in the ballpark if you treat them the same.  (Or on the low end.)

If it really matters to you, you could also add another column into the original spreadsheet and use the conversion rate for each currency to derive a value in the currency that matters to you and then use that column instead of Royalty.

Okay.  One other thing you need to do.  If you have freebies, you want to filter those out of your units sold number.

To do this, drag Transaction Type to the Filters section and then remove the Free transaction types from your options.  (Select Multiple Items and then uncheck both of the free choices.)

You should now have your net units sold by author and how much you made on those sales.  NOTE:  This is SALES only.  Not borrows.

You can also do this by Title.  Instead of dragging author name to the rows section, drag the Title field instead.

You can also have both there.  It’ll break it down with the titles listed for each author and a subtotal by author name.

So that’s sales.  You do something very similar for borrows.

Highlight all the cells in the Orders Report tab.

Create Pivot Table.

Drag Title and/or Author to the Rows section.

Drag KU/KOLL Units under the Sum Values section.  (Make sure it sums them not counts them.)

That should give you a table that shows number of borrows per author or title.  Now, if you’re me, you want to multiply that number by how much you think you’ll earn per borrow because the money is what really matters.  I’ve used $1.25 each month and it’s been a conservative but close amount.

When I had Excel 2003 on my computer I could just use the existing table and in a cell next to it write a formula that said =1.25*(CELL) and copy that formula down the length of the table.  That doesn’t seem to work in 2013 (which I just got).  Now I have to select the cells in the table, copy, paste special-values (so I basically create a second table that’s no longer a pivot table), and then do the formula in a column next to that new table.  It gives me borrows by title and amount earned per title.

That’s the basics.  You can get fancy and sort by any of the columns in the table.  (I like to sort highest to lowest by amount earned).  Or add more filters or even exclude specific titles if you want.

Hope that all made sense.  It sounds complicated, but if you do it a few times,  you’ll see that it’s pretty quick and easy to do.

Also, it should work the same with the new KU changes to borrows.  Jut change the multiplication factor. (I’m thinking I’ll use .001, so a tenth of a cent until we see what kind of payouts they’re going to do based on page counts.  I’ll adjust from there.  I’d always rather earn more than I thought I would than less.)