As you’ve seen on my Tracking Sales page, I track my monthly sales results. Sometimes, though, I want to take a closer look at the data. When I run a sale or a promotion, for example, I like to look at daily results to better identify what’s working and where it’s working best.

In my world, this calls for another spreadsheet. 🙂

1. Create a new spreadsheet and save it under a name that makes sense to you. Book1Sale2018, for example.

2. Widen Column A, then list the applicable dates in row 1, starting in B1. Leave a blank line, then put your featured title in A3. List the portals you want to track below, and create a total line for each day. Create a column for totals at the right, as well. Your spreadsheet will look like this:

A couple of tips here. I always start the spreadsheet the day before my promotion launches. In this case, I’ll put the month-to-date numbers in August 26. This gives me a point of comparison. Also, I pick a time to take down the numbers for the day, usually the following morning. I believe that all of the portals do some batch processing at night, even when they display numbers that appear to be real-time. GooglePlay doesn’t offer daily reporting right now, so I add that information after I get the monthly sales report.

On Amazon, I find the month-to-date sales summary to be more accurate than searching on individual titles on the units-ordered display. This is probably because units-ordered doesn’t take returns into account, while the month-to-date actually shows them. Month-to-date is kind of real-time, though, so to try to keep the data as close to the results of each day as possible, I update my spreadsheet for the previous day first thing in the morning. On Kobo (KWL) I do search by individual title, because then I can isolate sales by day for that title. Often by the time I look in the morning, there have been unit sales allocated to the day (possibly from the UK) and I can ensure they’re tracked on the right day this way.

2. Build your formulae for totaling the sales. The daily totals across the bottom are the sum of unit sales by portal. Once you’ve done the first one, you can copy and paste it across the row.

The far right column totals the sales per portal over the course of the promotion. Leave out the first column, the one that will include your month-to-date sales, because they aren’t the result of the promotion.

Again, once you’ve done the first one, you can copy and paste down the column.

3. At this point, I fill in the promotions. That’s why line 2 was left blank. Typically, I use this spreadsheet when I have a BookBub featured ad. So, in this case, August 26 would be MTD (month to date). On the 27th, I’d send out my newsletter (NL) and share the news of the sale on social media. I like to get units moving before the big ad and sometimes start more than a day or two in advance. The 28th is the day of the BookBub ad (BB) and then later in the week, I have other promotions book to extend the halo of the sale. I try to isolate the BB ad from other promotions, but when other authors are good enough to share your sale in their newsletter, that doesn’t always work out. Having one event per day helps me to isolate what works and what doesn’t.

4. Copy and paste the entire block for your “halo” book or books. Halo books are where you make your money, especially when you promote a title for free. So, if book #1 in the series is being offered free for the promotion, it’s the featured title and the first block on the spreadsheet will track downloads. The revenue, though, will come from sales of the other books in the series. Copy the spreadsheet once for every potential title in the halo.

If you’ve never done a sale like this before, you might want to take a snapshot of your month-to-date reports at all of the portals so you can compare before the promotion and afterward. Which books are identified as halo titles by readers might be different from your expectation. I find, for example, that with a free promotion, some readers will seek out my other free titles, despite the fact that they’re in different series.

5. Time to sum up. Create a block at the bottom of your spreadsheet to total the units moved during the promotion for each title you’ve listed.

Add the formulae. The total units for the each title is equal to the total units sold of that title. My spreadsheet is a bit too big for a screenshot but the Units of the Featured Title is equal to this cell – it’s the one at the far right of the block totaling results for that title:

Add the formulae for your halo books and create a sum in the Total line.

6. Add formulae for the revenue. If your featured title is free, of course, there’s no revenue from it. If it’s 99 cents, the revenue will equal the units sold times .35 – because you get a 35% royalty at that price point. If the halo book is $4.99, then the revenue will equal the units sold times 4.99 times .7, because you (usually) get a 70% royalty on sales at that price point. If your sales skew to portals or territories that pay less than 70%, you can adjust that percentage to be a more accurate reflection of results.

Create a formula in the Total cell to add your results together.

You’ll also want to select that column and format the cells to “currency”. That will give you the decimal point and the dollar sign.

7. Sum up your promotion. Create another little box at the bottom of the spreadsheet that looks like this:

Gross sales is equal to the total revenue in the summary spreadsheet above.

You can see that I haven’t completed my formulae in the summary, but skipped ahead. (My bad.)

Total your advertising costs in the Cost of ads cell. You might want to do another summary spreadsheet if that’s more complicated.

Your net profit is equal to Gross Sales minus the Cost of ads. As you fill in your unit sales information each day, this spreadsheet will populate and update, so you can quickly see when you reach the breakeven point.

If you save the spreadsheet like this, you can duplicate it to track a specific promotion pretty easily. You could also save it as a template. Be sure to rename it when you save the version with the new sales data.