Monitoring my Sales

It was easy to monitor my sales when I was only using Smashwords.

I opened the yearly report in Excel and ran pivot tables and graphs to get a sense of what was selling well, what was selling slowly and what was not selling at all.

If you followed this blog from the beginning, you may remember my announcement in June 2010 to test the waters of the new Indie publishing movement by uploading one free short story to Smashwords. I also announced I would not do marketing just to see if readers would find my story among the thousands of other stories, because I realised my time was better spent editing existing stories and writing new stories so I could build a backlist.

Jumping forward almost 2 years, I now have 9 stories on Smashwords with another one almost ready, while writing the fifth in a series.

I also now have my stories on Amazon, and am receiving Amazon spreadsheet reports, but as all Indie authors are finding, different formats make it more difficult to study the overall sales trends.

Luckily, I am only publishing under one author name, not like others who write and publish under many names.

On top of that, I am working towards selling on my own author blog. This will include another list of data, whether from the shopping cart or entered into a spreadsheet by me. And yet another report when I upload to CreateSpace for POD books to reach the 80% of readers who do not read ebooks.

So, before I get too far behind, I decided to merge the spreadsheets into one large file. This will allow me to open in Excel and study the pivot tables and graphs over all distributor sites.

It has been over 5 years since I worked as a programmer, so I am relearning a lot of the coding issues that have arisen from this project.

Once I started programming, I ignored other distractions like eating, sleeping, or writing more scenes in my current story, to get the basic program logic down.

The more I looked into the solution that would be best, the more I understand there are many difficulties.

Firstly, where to store the merged data. I looked at yet another Excel spreadsheet. I looked at creating an XML file, or a simple database, such as Dbase or Access.

In the end, I decided on creating an Access database. The reasons behind this are ease of queries, ability to extract to Excel spreadsheets and the ability to display graphs.

I looked at what identifiers marked each sale as unique. Surprisingly, I sold one title with the same ISBN on the same site at the same time, but listed as separate sales. Another distributor combines sales of the same title and ISBN into one line of data.

I concluded I need to rely on my own ability to only import each report once, because the program (at this stage) can not control multiple entries of single sales.

In the end, I just wrote the code for Smashwords, after create the database to collect the data I needed.

I got it working, with a lookup table to stop re-importing the same file. It will work if I maintain a consistent file name structure, and it will do at this stage.

Today I am testing the code to import the KDP spreadsheets. Of course, they are monthly spreadsheets, and the actual date/time field is missing, so another area where I can’t do unique checks.

If I can get the data merged for these two distributors, I can then continue to prepare my stories for CreateSpace and add CreateSpace reporting spreadsheet info later on.

I know Dean Wesley Smith wants a program written that will do the same thing, but he uses Apple Mac computers and software. Others can write a program for him to purchase.

Other decisions I made were to keep the programming relevant to FAT32 file structure and Microsoft Operating System. I do not want to update my operating system every time Microsoft decide to include new tools, security, etc. so I am writing the code in Visual Basic for applications (VBA). This way, I can copy and paste the code into Excel, or another MS program and run as macros in a template, if, way down the line I want to either sell or give away the code to other Indie authors.

What do you use for reporting? Excel for individual spreadsheets? Another program?

I know some of you use Omnilit, or upload to individual distributors instead of using Smashwords. How do you handle monitoring sales data?

Are you thinking of creating or purchasing a program to merge your Indie Sales reports? Please let me know if you purchased one suitable for MS operating systems.

Now, back to coding for the day. And tomorrow, writing.


2 comments so far

  1. curiocat on

    I haven’t started tracking anything like this but I had thought to use Excel when I did. When I tried to learn Access, years ago, it was very difficult to learn. Of course I was learning on the run so that may have been part of it. Has is gotten more user friendly since then or do you recommend taking a course for something like this?

    • D J Mills on

      No. I plan on using Excel, importing the merged data from the Access Database, and running pivot tables and graphs in Excel. Using the Access table makes it easy to export to any other programs. So far, I imported Smashwords 2010 and 2011, and Amazon 2011, and ran a pivot table. It worked. 🙂

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: