Did you get a whole
new slew of Apple iTunes Connect reports yesterday in your email inbox?
I did. They're pretty useless. Each one simply reminds me to go download the full report from iTunes Connect. And I get a separate one for each currency in which I've sold books. It would be nice if I just got a single email, perhaps detailing which currencies.
So, then you head over to iTunes Connect. I always go first to Sales and Trends, only to remember, a few glacial seconds later, that I have to download the reports from
Payments and Financial Reports. Click the Earnings Tab and you'll see a long list of documents to download, one for each currency and for each month. Ugh.
As you can see, my earnings on the iBookstore are paltry. Good thing I sell books elsewhere! This is certainly due to a combination of my books being very niche categories (extreme EPUB and Catalonia!) but I'm certainly willing to lay some blame at Apple's door. I wish they would beef up the iBookstore and make it a place that was friendly to book buyers. They do a number of things right: being available in the largest number of countries, creating the best ereader software (iBooks), and more, but the store just isn't up to snuff. It needs search desperately. It needs reviews, and ways to share, and comment. Anyways.
When you click those download buttons in the right column, you get
gzipped files (who knows why!?) for each individual period. Before you proceed, count up the ones in the list and count the ones you downloaded just to make sure you haven't missed any. (I always miss a couple.) Then, double-click each one to unzip. I wish, oh I wish, that I could choose the period myself! Maybe I want a single period, maybe I want all the data for a year.
When you open the files up in a text editor, you'll see that they are partly a
tab delimited collection of sales data, but with totals at the end.
That first line contains the field titles, and the second line is a sales record. If there were more sales records, there would be additional lines. Each field is separated with a tab (shown here by a triangle). Those sums at the end will mess up my database,
so I eliminate them.
But this data on its own is not that interesting. What I want is to be able to
compile the sales data from each currency and indeed each month into a single report. This is where FileMaker comes in.
I created a database with
FileMaker into which I can import Apple's files, and then generate reports that tell me how many books I've sold, and which months and countries I've sold them in. If you're a
subscriber, you'll have received a free download link.
Before importing, remember to make a backup of the FileMaker database, and to have removed the total lines from the Apple reports, as described above. If your fields have accented characters, I also recommend saving them with a
Mac Roman character encoding. It's a hassle, but it will ensure that those characters are properly read into FileMaker.
Then choose File > Import Records > File in FileMaker. Choose an Apple report and click Open.
In the Import Field Mapping box, first make sure that each of the Field Titles matches the corresponding Field in the database. Click Add new records, and also Don't import first record (contains field names), since that is precisely the case with the Apple records. Then click Import.
You will see that a record is created for each title that has been sold in that currency,during that period.
Import all of the new Apple sales record files. The hardest part of this step is not importing the same file twice! (I've tried saying the name of the file out loud—paying attention to the two-letter key at the end of the code—and also looking at the just-imported file in FileMaker just before choosing the new one. Maybe this is easier for some people?)
Once you've got all the data in my FileMaker database, the rest is easy. I've got automated scripts for creating lists, as shown here.
Here are some samples. Note that they're very simple, and they don't have titles. This is because there are only three layouts that serve the eight lists. But they work.
For example, here's what the list "By Currency, Title, Country, Period" looks like. This first page corresponds to first page of the sales in Australian Dollars, but has summaries for each period, for each country, and for each title. With currencies that are used in multiple countries, like the euro, the country section is more interesting.
And here is the report created with the Script "By Title, Period, Currency":
This page corresponds to the sales of a single title, with summaries for each period, and for each currency. Notice that only the estimated conversion to dollars is subtotaled, since it wouldn't make sense to sum different currency amounts.
And so on. I'm not a FileMaker developer, so I'm sure if you are, you could do this more beautifully. But it works, and indeed gives a much clearer and more complete view of your Apple iBookstore sales.
I'm happy to share the database (without my data, obviously) to all my
subscribers for free. Or you can buy it separately for $5. Use at your own risk!
Apple's new monthly and yearly reports
As so often happens, just as I was writing this blog post, I got an email from iTunes Connect letting me know that the Sales and Trends module of iTunes Connect had been improved. And that now I could
download sales data for the whole year. I was very excited, but alas, it has a major flaw: if you download a yearly report, it doesn't give you the date for each sale; for example, all of the 2012 sales are are marked "December 31, 2012". So if you want a very summarized view of your sales, it might be helpful, but it's not sufficient for my purposes.
The one thing that is in those monthly reports through the Sales and Trends module that you won't find in the Payments and Financial Reports section is data about how many
free books you're giving away. That is essential information if you're using free books as a hook to get people to look at your books for sale. I don't have a database set up for that yet (mostly because it would require downloading each monthly report from the Sales module), but I hope to offer that to you in the future.
How do you find those reports? It took me a while, and I even called the iTunes Connect hotline to ask for help (1-877-206-2092 in the US, and there are numbers in other countries as well). They answered almost immediately, but unfortunately said that they didn't know how to do it either! After I rummaged around a bit more, I figured it out:
Start in the Sales and Trends module. Click the Sales tab in the upper-right corner of the window.
Next, click the date and choose monthly, yearly, and then the actual month or year you're interested it.
Then click Download. And you get one of those marvelous g-zipped files, that when unzipped is a tab-delimited set of data (distinct from those downloaded from Payments and Financial Reports.
What could Apple do better?
What I want is a single place where I can
choose the period (from all my sales, to monthly, yearly or whatever I want), and then
export all the data at once,
in a single file. And I want all the data: which book, how many copies, which country, which currency, and on which date. It's not hard. Lots of other distributors offer such tools.
