Last week at the Dean & Kris workshops, the topic of dealing with ebook data came up from a number of the participants. The various epub sites will dump data into Excel for accounting and tracking purposes, but once a writer starts getting hundreds of entries a month (or hundreds of thousands in Amanda Hocking’s case), just scrolling through the data isn’t going to cut it anymore.
Having a fair amount of experience dealing with data, I offered my help. So after a quick review of the data formats of Smashwords and Amazon, I put together a brief tutorial on how to use pivot tables in Excel to manage sales. I’m going to keep this first one simple and if there’s continued interest, I’ll show other ways to utilize Excel for book sales data mining.
How to Use Excel Pivot Tables to Wrangle Your Data:
1. Download the file (I used Smashwords in this case)
2. Open in Excel
3. Click the upper left hand box between columns and rows (this highlights the whole data set)
4. Select Data –> Pivot Table
5. Select Next –> Next –> Finished
6. The pivot table is now available to be formatted
7. Drag the word title from the “Pivot Table Field List”
8. Drop it in the leftmost box on the pivot table which reads: Drop Field Rows Here
9. Drag and drop “Amount Final (USD)” into the: Drop Data Items Here
10. Change the Count to Sum by right clicking on “Count of amount Final USD” and select Field Settings (this gives you the total value rather than the counting of the sales)
11. Select Sum in the drop down box
This methods gives you a very simple look at the data from Smashwords. For twenty-five entries this isn’t an issue, but when you start recieving 100’s or 1000’s, then a pivot table is very handy.
If you have questions or requests for future examples, please put them in the comment section
