Studying the Sales of my Ebooks

While reading Dean Wesley Smith’s blog on 7th June where he asks for help in building an ebook tracking system based on the excel reports received from the distributors, I realised I should probably monitor when and where my ebooks sold to find patterns.

Then I asked myself why? To see how my ebooks are doing on the different sites.

Would I change the genre I write if my books don’t sell? Well, no.

The reason I spend so much time studying writing, and practicing to improve my understanding of the craft, is because no publishers are printing the books I love to read (other than L E Modesitt, Jr.), so I am writing the books I love to read. I am also putting the ebooks on Smashwords, in case there are other readers who are willing to pay to read my stories.

So, back to tracking ebook sales.

What should I watch for in the sales reports?

Dean’s list includes tracking each title, for each author (I am not a publishing business, so there is only one author, me), tracking sales each month, and over time, total sales per month, or year, and a comparison of sales monthly.

The comments on Dean’s site are interesting and a few are technical, discussing how to import the various spreadsheets into tables in a database, then accessing the data in report form, or spreadsheet form for each listed request.

The accountant knowledge I have thinks a program to monitor ebooks is a good thing.

The programming knowledge I have tells me I could have the basics up and running in a month, to a level others could purchase it, but, I would have to maintain it every time another site springs up to distribute ebooks, and if I sold it, I would have to roll out the newer version of the program.

All this is fun to do, but, it takes time away from my first love, writing stories I want to read, then laying on my bed for the day, or night, and reading them from start to finish without interruptions from anyone. Bliss!

So, until the program is written, tested and available for us Indie Publishers, I have a simple solution. Create a Pivot Table, then select each field you want to study.

Step 1: Open the Excel Spreadsheet from Smashwords or one of the other sites.

First, I located the date field (s2), and inserted two more columns next to it. I called the first Month and added the code in the first line of data “=month(s2)”, and the second I called Year and added the code in the first line of data “=year(s2)”. Then I selected the cells with the code (t2, u2), and copied the code into every line of data. The code changed to correspond to each line of data.
Step 2: Click the Data drop down menu item, and select PivotTable and Pivotchart Wizard

The PivotTable and PivotChart Wizard window opens.

Make sure the radio button PivotTable is selected and click the Next> button.

The next wizard window automatically shows the range of the spreadsheet you are using. Click the Next> button.

Make sure New worksheet is selected and click the Finish button.

You should now have a new sheet like the following.

Now the fun starts.

I dragged the Year and Month from the PivotTable Field List and dropped them in the Drop Row Fields Here area. I dragged the Title from the PivotTable Field List and dropped it into the Drop Page Fields Here area. I dragged the Channel from the PivotTable Field List and dropped them into the Drop Column Fields Here area.

Lastly, I dragged the qty and amountFinal (USD) to the Drop Data Items Here area.

Once you get the hang of displaying the data, you may want to add location or other fields.
To work the Pivot table you select one of the fields and select only the data you wish to see.

First, select the year field and click the arrow to open the listed years. Click on Show All to deselect all, then select 2010, and click OK. Next, click on the Month drop down arrow and select one month. I selected the first month I put the first book up. Then I select the Title dropdown arrow and selected the first book I put on Smashwords.

Now I can see that I sold 2 copies of the ebook Broken Faith the first month I put it on Smashwords.

As you change the month, or years, you will see the changes in the spreadsheet.

You can look at one channel at a time, or select all channels and study the patterns of sales of your ebooks.

But the best is still to come. Now you can create a chart to get an idea of the growth in sales, and which sites are selling more of your ebooks than other sites.

Step 3: Create a chart to study the results.

Click the Insert drop down menu item and select Chart.

A chart is created in a new sheet, usually called Chart1. I recommend saving your workbook now.

Play around with each of the fields, either selecting all, or a few, or look at the chart one title at a time.

From the chart, I can see that for the short story Fire Starter, August 2010 was the best month and the best distributor was Barnes and Noble.

From this chart, I can see that for my first novel, Broken Faith, the best month was December 2010 and the most sales were at Apple.

So, what do you think? Easy to create the pivot table and chart? Easy to get a feel of where the sales are best and where you need to advertise by commenting on other’s blogs to get your book noticed on the different sites?


7 comments so far

  1. Angela/Curiocat on

    This is great. You are so cool, Diane. Thanks for sharing.

    • djmills on

      You are welcome. 🙂 I don’t understand why more don’t use it because if you own Excel you already have the ability to set it up.

  2. imotherofpearl on

    Pivot tables are a great way to analyse data. Do you have any idea why some months were better than others for particular suppliers? Maybe someone reviewed your book online and provided a link to that particular supplier…

    Congratulations on so many downloads for Firestarter!

    • djmills on

      Thanks. 🙂
      No idea about good months, other than lots of authors commented on a spike in Sept, and again over Dec/Jan period.

  3. Texanne on

    I’ve never made friends with excel, and have no idea what a pivot table is. Something to do with airline seats? 🙂

    What got me most from this post was the image of you, lying on your bed, and luxuriating in reading your own story. Priceless! Inspiring–and it goes right to the heart of a question I’ve had lately, which is how to rekindle my passion for the WIP I was working on when Life Got Difficult.


    Also reminded me that Firestarter is available. I’ve been wanting to give that a go. :)TX

    • djmills on

      lol! The pretty graphs at the end of the article that display the months and number of sales depending which distributor is selected is the end result of pivot tables.

    • cat on

      It’s such a lovely story…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: