Books and Python

On various sites I post my books.

PaulKater.com has the 'decent' ones, in English and some in Dutch.

Zjuzdme.org has the naturist fiction ones. All in English.

It's fun to know how many books are sold or downloaded (not all books have a price tag, many of them are free on paulkater.com, go check them out if you want).

First attempt to keep track was stuffing the numbers into a few MySQL tables. By hand. That gets boring fast.

Then I wrote a PHP shell around it. Still, everything was done by hand. Boring.

Then I decided to throw that overboard and put everything in a spreadsheet. Using vlookups and other funny bits, I sort of automated that, but after some time I decided there was too much information in it, and still too much work for the hardly ever looking at it.

So I did away with the whole lot.

Until a few weeks ago, when I felt I should do something more about it.

So I looked at two friends.

Python, as my programming language of choice. Python can do anything, and does it fast.

Okay, it can't make coffee yet (I haven't tried that).

I tried to figure something out with ASCII files (plain text), but that got confusing, so that is where friend 2 came in.

MySQL. It's great, fast, and I know how to play with databases.

And off we went.

I decided on a simple, two-table structure. 

Table One: stores the title, the number of total sales/downloads, and an auto-incrementing pointer.

Table Two: stores all the identifiers for the books, e.g. ISBNs, ASINs for Amazon, GGKEYs for Google, and each identifier has the pointer which leads back to the book in table One. That way it's easy to see the name of the book, the total of sales etc, and all the ISBNs etc (identifiers). That also makes it easy to import the store reports, as each report has their own identifier. So when the loader finds an identifier, it goes to table Two, looks for the identifier and, if it's found, nice. If it's not found, the program prints a message that a new identifier was found.

This also happens when a new book is discovered. In table One I store the title twice. Once for real, once with only letters and digits, in uppercase. The loader will search for a book that way. If found, it has the pointer for table Two, and goes hunting for the identifier.

Enough with that for now.

At this moment, the program can:

1. Add a new book (manual action).
2. Add a new identifier (manual action).
3. Import and transform files from Amazon (XLSX) and Google (for some reason sent as UTF-16??).
4. Load the reported information into the tables. (It won't load anything until all requirements of new books and new identifiers are satisfied.)
5. Counts the sales per shop separately, where the Draft2Digital information is also split out over the 4 mail shops (Apple, Kobo, B&N, Smashwords, or 'other').
6. Counts the turnover per book per country (in a separate table, and for this I also set up a country code table).
7. Show stats about the number of books, best selling, worst selling, number of identifiers, and number of countries where books ended up.
8. Display all relevant information about a specific book.
9. Generate a report with all turnover in HTML (default) or CSV (command line switch).
10. Search for a specific title (substring based, if more than 1 is found, I get to choose the right one or quit).
11. Show help information.

I'm pretty happy with what I've created. It's still (and will probably remain) a work in progress, but it's stable so far.