It’s always interesting to see how well (or poorly) your app is performing in App Store and iTunes Connect has since the latest update provided much nicer interface to track this with graphs. The iTC Mobile app also provide the same graphs/information on the go. However, iTunes Connect falls short because daily statistics are only available for  14 days, and weekly statistics doesn’t stretch further than 13 weeks. Apple has a ton of apps (400.000+) on the App Store and downloads has already passed the one billion mark, I guess they just find it too expensive to store (or at least provide access) to that amount of data. But why o why can’t they just provide an API where we could easily download and store the statistics ourselves then? My guess is that they have an API for the iTC Mobile app that does this already!

AppDailySales to the rescue!

AppDailySales is a great open source python project that with very little effort logs in and downloads the statistics files from iTunes Connect for you. For the statistics to be really useful in my opinion you want them imported to some kind of database and if you’re a company focused on selling apps you might also want the statistics in some sort of OLAP-cube.

Loading statistics into MySQL


There are a few ways to get the downloaded files into MySQL but rather than modifying AppDailySales or relying on some other app/script to parse and load the data into the database I wrote a bash script that use the CSV-import functionality (load data) that already exist in MySQL. Below is a step by step guide on how to achieve this and you can download the script at the end of the post as usual.

1. Create the MySQL database

Assuming you already have MySQL installed you first need to create a database and a login for this database. This can be done using phpMyAdmin or using the command line. phpMyAdmin is so simple that it requires no guide and there are plenty of resources on how to do this using the command line, if you are too lazy to find one yourself here’s an example.

2. Create the table to store statistics

The following SQL-command is used to create the table you need. Note that I am using a swedish collate, you might want to change this depending on what characters your file contains. You will notice if you need to change this when you import data.

CREATE TABLE `sales` (
  `Provider` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `ProviderCountry` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `SKU` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `Developer` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `Title` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `Version` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `ProductTypeIdentifier` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `Units` int(11) NOT NULL,
  `DeveloperProceeds` float NOT NULL,
  `BeginDate` date NOT NULL,
  `EndDate` date NOT NULL,
  `CustomerCurrency` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `CountryCode` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `CurrencyOfProceeds` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `AppleIdentifier` varchar(255) character set utf8 collate utf8_swedish_ci NOT NULL,
  `CustomerPrice` float NOT NULL,
  `PromoCode` varchar(255) character set utf8 collate utf8_swedish_ci default NULL,
  `ParentIdentifier` varchar(255) character set utf8 collate utf8_swedish_ci default NULL,
  `Subscription` varchar(255) character set utf8 collate utf8_swedish_ci default NULL,
  `Period` varchar(255) character set utf8 collate utf8_swedish_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Also notice that this table doesn’t have any keys or indexes set. If you want to set a primary key for this table it will have to be a clustered key and because I only have access to free applications that lack a number of information I don’t want to set this up because it could make the import functionality break. If you’re not importing a huge amount of data there is really no need to set a primary key, you will notice if you queries are running really slow and can optimize this later.

3. Configure AppDailySales

Download AppDailySales to an appropriate location, lets say: /home/username/itunesconnect. You’re going to want to download the report files to a directory, so create a directory for this, lets say: /home/username/itunesconnect/reportfiles. Now open the appdailysales.py file using your favorite editor.

  1. You need to set your appleid and password, this is what you use to log into iTunes Connect.
  2. Set the “outputDirectory” to the directory you created (/home/username/itunesconnect/reportfiles).
  3. Set ” unzipFile” to True
  4. Set “daysToDownload” to 7

The rest can be left as is. I’m downloading 7 days every day to make up for any fails, say that statistics aren’t available a given day (this happens sometimes), then the script will make up for this the coming day. Now run appdailysales.py to make sure it’s working and that report files are placed in your output directory.

4. Loading your app store sales statistics into MySQL

Download the script file the same directory where you placed appdailysales.py. This is what the script looks like:

#!/bin/bash
################################################
#### SETTINGS
# Path to directory where appdailysales downloads reports
FILES="/home/username/itunesconnect/reportfiles"
 
# Path to directory where imported files will be moved after import
ARCHIVE="/home/username/itunesconnect/reportfilearchive"
 
# Path to appdailysales script
APPDAILYSALES="/home/username/itunesconnect/appdailysales.py"
 
# Username for mysql authentication
MYSQLUSER="mysqluser"
 
# Password for mysql authentication
MYSQLPASSWORD="mysqluserpassword"
 
# Database where report data is to be stored
MYSQLDATABASE="itunesconnect"
 
################################################
 
$APPDAILYSALES || { echo 'Appdailysales failed downloading data!' ; exit 1; }
 
# Create archive dir if it doesn't exist
if [ ! -d "$ARCHIVE" ]; then
	mkdir $ARCHIVE
fi
 
shopt -s nullglob
for f in $FILES/S_*
do
        filename=`basename $f | cut -d "_" -f -4`
 
	fileexist=$(ls ${ARCHIVE}/* | grep "/$filename" | wc -l)
        if [ "$fileexist" != "0" ]
        then
                # This date was already imported, we can just remove the file and ignore it
                rm $f
        else
                mysql --user=$MYSQLUSER --password=$MYSQLPASSWORD --database=$MYSQLDATABASE -e "load data local infile '${f}' into table sales fields terminated by '\t' lines terminated by '\n' ignore 1 lines (Provider,ProviderCountry,SKU,Developer,Title,Version,ProductTypeIdentifier,Units,DeveloperProceeds,@BeginDate,@EndDate,CustomerCurrency,CountryCode,CurrencyOfProceeds,AppleIdentifier,CustomerPrice,PromoCode,ParentIdentifier,Subscription,Period) SET BeginDate=str_to_date(@BeginDate, '%m/%d/%Y'), EndDate=str_to_date(@EndDate, '%m/%d/%Y')"
 
                if [ $? -eq 0 ]
                then
                       mv $f $ARCHIVE
                       echo "Imported: $f"
                else
                       echo "Import failed for: $f"
                fi
        fi
done

Basically what it does is to run appdailysales.py and iterate through all the downloaded report files and check against an archive directory to see if the given date has already been loaded into MySQL. If not, it uses the built in “load data” functionality in MySQL to directly load the information into our sales table. After a successful import the report file is moved into the archive. As you can see in the beginning of the script there are a few things to configure. You will need create a directory where imported files is to be stored after successful import.

Edit the script using your favorite editor and run it! Your sales information should now be stored in the sales database. Run a query to verify that the sales table contain the information as you’d expect.

5. Schedule using crontab

With everything working you want this to happen automatically every day. Crontab is the easiest way to automate tasks on a linux platform. In your shell, type: “contab -e” to open crontab. This is my crontab which runs the script ten past 6 pm every day:
10 18 * * * /home/username/itunesconnect/importsales.sh

Depending on your timezone etc you might want to run it at a different time. This is from iTunes Connect FAQ:

Reports are generated after the close of business in the last time zone (11:59:59 PM). As such, reports are generally available by 10:00 AM PST for the prior day or week. Earlier access to reporting for other time zones (where the close of business is earlier) is not available.

Wrap up

This solution is straight forward and will work even with updated versions of AppDailySales. I’ve been importing statistics for Mitt Saldo for about 2 weeks now. What sucks is that I started doing this too late so all the early statistics has been lost. What I’ve done to make up for this is to sum up the available statistics agains the total number of downloads (which is still available in iTC Mobile as it shows statistics since 22 weeks) and created rows that make up for the missing statistics. Here’s a screenshot of my simple visualization of Mitt Saldo download statistics using the MySQL database, php and Flot.

Appdailysales, MySQL and Flot

Downloads

  1. Download AppDailySales
  2. SQL command to create required database
  3. Bash script to import statistics

Related posts