Tracking Sales

When I was traditionally published, I didn’t track my sales. I received statements from my publishers, glanced at them and filed them. The information was not only slow to arrive in my hands, but there wasn’t as much I could do to influence sales. Now that I’m indie, I’m my publisher, so I do track sales. The information is available to me in close to real time and I use it to make choices about marketing and about new projects.

I don’t track free downloads, although you certainly could. When I set up my system, I was more interested in what people would pay to read. Tracking free downloads would allow me to compare how well my funnel book is working, or how effectively I’m pulling people into my funnel.

When I have books in subscription programs, like Kindle Unlimited, I track page reads in much the same way outlined below. That tracking showed me that KU wasn’t a good choice for me.

There are services that you can use to track your sales and probably software programs, too. I’m a bit old school – I set up my own spreadsheet in Excel. Every month, I input the raw sales data from the statements from the portals – yes, I type it in again – and then the formulae set up in the spreadsheet do the rest. It’s not a lot of fun inputting the sales data, but having my sales information all in one place like this is invaluable. You can use another spreadsheet program, but I’ll be talking about Excel. The same functionality will be in any other spreadsheet program – you just might have to poke around a bit to find it.

1. Create a new workbook and save it. Name it for the year: 2018 Sales.

2. On the first sheet, widen the first column. List your books in the first column, in alphabetical order, starting in cell 4A. List the months of the year across the top, starting with January in column B.  Below the last book title – you might want to leave a blank line – type Total. I like to outline the cells in this row and put it in bold. I also underline the months. Your result should look like this:

Tracking Book Sales #1 by Deborah Cooke

3. Add the formulae for the totals: click on the first total square, in column B – in my example, it’s B11. In the formula bar, create a formula to sum all book unit sales from this column in this territory. The formula here is that B11 = sum(B4:B9) You could also click on each cell individually B11 = B4+B5+B6 etc. but as your list of books gets longer, this quickly becomes unwieldy.

Tracking Book Sales #3 by Deborah CookeCopy and paste this formula across the total line, so there’s a total for each month. The little green triangle in the top left corner of those cells means its content will be calculated with a formula.

Tracking Book Sales #3 by Deborah Cooke

4. Copy this sheet once for each portal you track plus one more. If you track sales from Amazon, iBooks, Nook, KOBO, Draft2Digital and Smashwords, you’ll need six sheets plus one, or seven copies in all. Change their names on the tabs at the bottom, like this:

Tracking Book Sales #4 by Deborah Cooke

5. Update the information on each sheet by putting the portal name at the top, like this:

Tracking Book Sales #5 by Deborah Cooke

6. For iBooks and Amazon, you may want to include more detail. Amazon supplies sales information broken down for its fourteen geographic portals. iBooks supplies sales information for fifty-two territories. I track Amazon territories individually, even though I don’t sell much in some of them, and break iBooks down into regions. Generally speaking, if any territory shows strong sales, I break it out separately. Similarly, at an aggregator like Smashwords or Draft2Digital, if one portal shows strong results, I’ll break it out separately to have a closer look at the data.

When you want to do this, copy and paste this block as many times down the spreadsheet as you need. For my Amazon sheet, I have fifteen copies of my title and total block. Change the names of each block to be for each territory Amazon reports – UK, DE, FR, etc. It will look like this:

Tracking Sales Results #6 by Deborah Cooke

The last block on the sheet will be for Total Amazon Sales. Build formulae in this block to total the unit sales from all of the portals listed above. On this one, I click on the cells to add:

Tracking Sales Results #7 by Deborah Cooke

Repeat for each book title, then as before, copy and paste the column of formulae for January across all the months of the year. Repeat this on every sheet where you’re tracking data from more than one portal or territory. Each page will have either one block of data, or a total block of data at the bottom of the page.

These detail pages are the only place you will ever enter sales information. Everything else will be calculated. Let’s set that up now.

7. Go to the Monthly Sales sheet. Build your formulae for January on this sheet, by summing sales from each sheet: the cell for January of A Book equals Amazon January sales for A Book plus iBooks January sales for A Book. etc. It’s going to look something like this:

I always mess this one up a couple of times. Remember that you need to click in the formula field at the top after clicking on each cell, and add a + sign there before going to the next sheet to add the next cell.

Once you’ve created formulae for all of the books in January, copy and paste the January column to the other eleven months of the year. Your spreadsheet will be looking like this:

Tracking Book Sales #9 by Deborah Cooke

9. Add another sheet to the front of the workbook and call it Annual Sales. Copy and paste your book list and put the year at the top of column B. This time, the formula for each book in the column equals the sum of all twelve months of sales on the Monthly Sales sheet. (Note that my formula only lists to G4, because my sample spreadsheet has just six months. Yours will go to M4.)

Tracking Book Sales #10 by Deborah Cooke

Once again, you’ll create formulae for all of your books. Create the sum in the total line, too. Your result will look like this:

Tracking Book Sales #11 by Deborah Cooke

Author Lacey Silks suggested an extra step here to check the formulae: she put a 1 in all the cells on the detail pages, then checked her summary pages to make sure the formulae were correct. That’s a great idea. Thanks Lacey!

10. Now, you’ve created a means of tracking your ebook sales. You can use the same system to track audiobook sales, subscription service page reads, library sales, print book sales, or whatever. The idea is that the data is pulled from the raw data and compiled into a summary that’s useful to you.

11. You can add sheets to look for patterns in your sales. When you have only a few books, it’s easy to eyeball sales trends, but that becomes harder the more books you have for sale. The first summaries I added were by sub-genre. The most useful one to me right now is the sales by series. Let’s build one of those sheets.

Add a sheet and call it Series Sales.

List your books by series. Mine looks like this:

Tracking Book Sales #12 by Deborah Cooke

Add the formulae. This sheet will pull data from the Annual Sales summary. So, the formula for each book on this sheet will equal the cell for that book on the Annual Sales page. (Yes, you could track by month, but I don’t. The beauty of this system is that you can customize it to summarize what you most want to see.)

Tracking Book Sales #13 by Deborah Cooke

You know how to finish filling this in and set the totals.

As soon as you enter sales data on the detail pages, the magic happens and the summary sheets begin to populate. Add additional tracking sheets as you see fit. You can compare sales by geographic territory or by sub-genre, for example.

13. If you publish a new book, it’s easy to add that title into your reporting. Insert a row where that title belongs (maintaining alphabetical order) on each of the spreadsheets. Add the title to your tracking sheets and add the formulae to have the sales units sum there.

14. At the end of the year, once all your reporting has been entered and saved, copy your spreadsheet. At the end of 2018, for example, you’ll copy the 2018 Sales spreadsheet and rename the copy 2019 Sales. Don’t touch the data!

First, go to your Annual Sales summary and label Column C for the previous year (in this case, 2018).

Tracking Book Sales #14 by Deborah Cooke

I’ve added some unit sales data to the detail spreadsheets and you can see that it’s been compiled into column B. Column B is where the formulae are, and you don’t want to touch them. Re-type all the numbers in Column B into Column C. These are NOT derived from formulae: they’re just typed in. Don’t copy and paste! Retype them! Add a total and it will look like this:

Tracking Book Sales #15 by Deborah Cooke

The two columns look the same but they aren’t the same. The one on the left is getting its data using formulae. The one on the right isn’t.

Now, go to your detail pages and clear the content of the cells. You can highlight all of the data – do NOT touch any cells with formulae! – and choose Clear Contents from the Edit menu. Poof. Your unit sales information is gone and the Annual Sales sheet will look like this:

Tracking Book Sales #16 by Deborah Cooke

The formulae are intact and the workbook will tabulate your new data right alongside the old data, so you can compare sales from year to year. You probably noticed that I renamed the column for the new year.

In subsequent years, you’ll need to add a new Column C so that your data stays in order.

This tracks sales by month and by year, but when I do a promotion, I also track sales by day and by portal to assess the results. Of course, I do it with a spreadsheet. I’ll add another page explaining that one soon.