Monitoring Ebook Sales

On my 10th April, 2012 blog, I mentioned I was looking at developing a tool (program) to undertake the repetitive tasks in compiling my eBook sales reports into one database for ease of comparing sales at different internet retail sites.

I receive reports from Smashwords and Amazon, and I plan on uploading my POD files this year, so will receive reports from CreateSpace.

I set up an Access database to store the common data from the different reports.

I worked out I need the following data for my sales reviews (subject to review later).

Distributor

Country of Sale

ISBN

Title

Author

ListPrice

Qty

Royalty

CountryCurrency

SaleDate

Month

Year

Some notes on the data fields.

I am only using one author name at this stage, but may add other author names in the future so included the field. If I use other author names, I will also add a search criteria for author as well as the ones I already set up.

ISBN is included but the field can include AISN numbers from Amazon and other identification numbers from new start-up distributors/retailers.

I broke the sales date into Month and Year, because these fields are easy to compute and store, and work better for reports.

I created a second database table to store the filename of each report processed, so I don’t accidentally process it a second time, thereby distorting the figures.

The rules I set up for the import comma delimited files are:

Smashwords .csv files must start with “Sma”

Smashwords .csv files before 2012 must have an added column between “amountFinal (USD)” and “date” called “Notes” because Smashwords added the column in 2012

Amazon .csv files must start with “kdp”

Remove all payment rows from Smashwords .csv files

I have one small problem with downloading the 2012 Smashwords reports. The ISBN column displays 9.78E+12 in every row instead of the ISBN allocated with each title.

The 2010 and 2011 reports downloaded correctly. Not sure why the 2012 download did not bring in the correct ISBN. If you know why this is happening, please let me know in the comments.

I am looking at creating a third table to link the many ISBN with an author and title, but at this stage I only have the single ISBN for each title, supplied by Smashwords, and the single AISN supplied by Amazon. The extra ISBNs will begin when I start using Createspace for my POD books.

Next, I created a form in Visual Basic 6. The form has three areas of interest.

Importing the .csv spreadsheets files from the distributors/retailers

SelectFileToImport

Selecting the search criteria

SearchCriteria

and visual displays of selected data.

DisplayingData

Since then, I have been testing importing each sales report.

I am ready to import 2012 report from Smashwords, so will list the steps.

1] Open the Smashwords report in Excel.

2] Save the report using the .csv extensions and the same naming format as previous.

Eg: SmashwordsSalesReport-2012.csv

3] Start the eBook Royalties program.

4] Select the Path and folder then the newly created .csv file to import. Notice I can confirm it has not already been imported by checking the list on the right. Also, I added code to stop processing if the file name is already included in the Imported Files list.

RoyaltiesMainForm

5] Click the Import button. The curser displays the hourglass and when the import is completed, a message box appears advising the data has been imported to the database.

6] Click Display Results button and the imported data should display in the tab called Data or select search criteria to show in the Data tab.

DisplayedData

7] Click the Totals tab to show the totals of the search criteria as the data.

SearchTotals

8] Click Graph tab and the Totals data is displayed as a graph.

SearchGraph

I am still testing the import part of the program and I do realise every time the retailers change the format of the reports, I will have to update the import part of the program. However, it will be quicker to run the program then manually copy each line of data to the database.

The search section and display section are not really necessary, because there are many spreadsheet programs available that can import the data to manipulate into graphs, etc to view, but I will look at developing the other areas further once the import section is working correctly.

The graph area is a simple graph showing the totals data, and the pivot table is not working yet, but once the rest is working correctly, I will play around with linking the Totals data to the pivot table area.

So, what do you think? A good way to merge the data?  Or, have you discovered other programs that do the same thing?

Is it worthwhile working on the search function and display area, or a waste of my time?

If you have any other suggestions please comment. I would be happy to hear them and see if I can incorporate them in the program.

I am having fun writing and creating useful tools for both the writing and publishing side of this business. I hope you are having fun, too
.

Advertisements

4 comments so far

  1. Angela on

    Amazing stuff. Keep up the great info, I appreciate your sharing.

    • D J Mills on

      Thanks. I love writing programs. And writing stories. 🙂

  2. Joshua Lisec on

    Reblogged this on Joshua Lisec.

    • D J Mills on

      Welcome Joshua. Thanks for reblogging this article. 🙂 All suggestions to improve the program are welcome here.


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: