A Sunday afternoon project: Providing App Store stats via JSON

Last month Apple announced their auto-ingest tool for fetching the download and update statistics for apps.  The tool downloads the stats for a particular day or week as a gzipped CSV file that you can then process yourself.  This is far nicer than having to try and scrape the numbers from the iTunes Connect page although the daily numbers are still restricted by a two week limit and the weekly by 13 weeks.  Hopefully in the future you’ll be able to retrieve further into the past (assuming the data still exists).

I’d a bit of free time this weekend and decided to have a poke at this and see if I could  grab the data and present it in a easily consumed format (i.e. JSON).  Another reason for looking at this is because I have two iPhone Developer accounts and it’s be nice to see the data from both as one graph rather than having to log in and out.

To that end I wrote a couple of lumps of PHP to ingest and process the stats which is available on GitHub (https://github.com/kmonaghan/itunes-connect-auto-ingest).  There’s a couple of small steps to get it all to work.

First, create a database (or just use an existing one) and create the table in schema.sql.

Next, edit boot.php.  Here, you need to put in your database details and the details for each iTunes Connect account you have.  To do this you need to put in an array with the following key pairs:

array('username' => 'iTunes Connect username',
'password' => 'iTunes Connect password',
'vndnumber' => 'VND number',
),

The username and password are the details you use to log into iTunes Connect.  You’ll find the VND number if you log into iTunes Connect and go into ‘Sales and Trends’.  It’s the number beside your name at the top left of the screen.

Update: From the comments, this is how you find the Vendor ID (VND):

Choose Sales and Reports.

Choose “Reports” from the top left menu (Where default is “Top Content”).
From the form that will be shown, choose monthly report and download.
The downloaded file will be of format S_M_(VendorID)_TIMEPERIOD.TXT

You can have as many of these accounts as you want and the ingest script will check each one.

Now all the details have been set up, you can ingest the data from Apple.  This is done by simply running ingest.php.   If it’s the first time the script has been run, it will look back 14 days.  Otherwise, it will look in the database for the last successful day there was an import and attempt to import all days since then.  After your first import, you should create a cronjob to run this script once a day to capture the latest stats.  If the downloaded tar files are 0 bytes, check your login details and VND number.

Once all the data is in the db, we can view it via daily.php which outputs the results as JSON.  This will by default output the total number of units downloaded for all apps in the last month.  It can take four parameters:

  • apple_identifier: A particular app identifier.
  • product_type_identifier: This can be 1 (default) which is downloads or 7 which is updates.
  • from: The date to get the stats from.  Expected as dd/mm/yyyy.  Defaults to 32 days ago.
  • to: The date to get the stats to.  Expected as dd/mm/yyyy.  Defaults to yesterday.

And that’s that!  There’s some Javascript in stats.html which consumes the JSON and uses the Google Chart Tools to display the results.  An example of some live data using my own apps is available here.

This is very much a works-for-me lump of code, so it may not do exactly what you want but it should be easily extendable to provide exactly what you want.  It will certainly not cover every error condition.  I’ll probably add bits and pieces to this as I think of a stat I want to visualise or I come across a bug unexpected feature.

Something I might consider looking at in the future is writing a clone of the iTC Mobile app that utilises the saved data.  It’d be a nice little project to try out Core Plot.

33 thoughts on “A Sunday afternoon project: Providing App Store stats via JSON”

  1. The script looks great when I look at your output. Unfortunately, I can’t get it to work. The database has been setup, but as soon as I run ingest.php, files like “20111122.gz” appear in the app folder, but they are 0 bytes.

    1. Have you edited boot.php and put in your iTunes Connect details? The username is an email address. You’ll find the VND number if you log into iTunes Connect and go into ‘Sales and Trends’. It’s the number beside your name at the top left of the screen.

  2. I have the same problem…
    I have simplified the php just to test and checked all the parameters but the file is always 0 bytes..
    (obviously with my real mail, password and vndnumber)
    Please..¿do you find something wrong in next script?..thanks
    Alejandro

    $url=’https://reportingitc.apple.com/autoingestion.tft’;
    $string=”USERNAME=mymail&PASSWORD=mypassword&VNDNUMBER=myvnd&TYPEOFREPORT=Sales&DATETYPE=Weekly&REPORTTYPE=Summary&REPORTDATE=20120108″;
    $ch = curl_init();
    $fp = fopen(“somefile.txt.gz”, “w”);
    curl_setopt($ch,CURLOPT_URL, $url);
    curl_setopt($ch,CURLOPT_FILE, $fp);
    curl_setopt($ch,CURLOPT_POST, 1);
    curl_setopt($ch,CURLOPT_POSTFIELDS,$string);
    curl_exec($ch);
    curl_close($ch);
    fclose($fp);

    1. @Alejandro Have you tried an earlier date? Sometimes there is a lag of more than 24 hours (especially over weekends) before the report is actually ready for download. I’d try 20120107 or 20120106 and see if you get anything then.

  3. Yes ..i have tried different dates and always the same 0 bytes files…
    With the java class Autoingest from Apple i have no problem but from php with the same data i can’t get the file…

    1. I just tried out your script with my details and the only issue I had was that it couldn’t write to the local directory. Once I fixed that it downloaded the file file fine. Are you running the script via the command line?

    2. You most likely need to set this:

      curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

      When connecting to remote resources under https you either need to make further configurations to your server or set the option as above.

          1. Hello,
            I ran your code and got certificate errors when downloading the files, reading the web and the answers here I added:

            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);

            but I got another ssl error ( 54 )

            Any ideas ?

      1. Thanks a lot. AppAnnie is great. Report is clean and professional, and most important, displaying ranking data is free too. Your tip really helps me!

        1. Hi @Dinsen, can you post your updated code? Have the same exec()-Problem and would love to see it running with your workaround …
          Thanks
          Tobias

  4. Looks like it’s not working for me…
    Don’t know if you will find my comment or if I’m too late, but I’m getting these errors when I try to launch ingest.php :

    Warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\wamp\www\appsite\itunesingest\ingest.php on line 92

    I see gz files being created and having data inside, but I can’t seem to read them properly afterwards to do the inserts into the database…

    Could you please help me? 🙁 (I’m using Wamp, but I don’t think this is the cause of the issue)

    Thanks for this article and nice this script anyway, looks like it is going to save me a lot of time, when I’ll get it working. 🙂

    1. Is anything getting into your DB? It could be a change in the format of the files downloaded. I’d output the line read in before it tries to insert and see if it has the right number of columns (there should be one for each ? in the prepared statement).

      I’ve updated the repo with the latest I’m using myself just in case that helps.

  5. Thank you for your quick answer, and for the Github update.

    Actually no, nothing gets written in my database, I think that is simply because my WAMP can’t unzip the gz files properly…

    I have all the latest versions of the files now, and yet I still only see gz files in my folder… (plus, they are remaining, after execution) According to this, I have a new kind of error messages whenever I launch the ingest script (one error for each file) :

    Warning: fopen(somedate-someID) [function.fopen]: failed to open stream: No such file or directory in C:\wamp\www\appsite\itunesingest\ingest.php on line 75
    Could not open somedate-someID for reading

    I think this is only because WAMP can’t gunzip the files, so they don’t exist without a .gz extension, and therefore are not readable.

    Right now I’m trying to find a way to unzip .gz files properly in a local WAMP server, but I couldn’t find a solution for now.

  6. Okay, now I’ve managed to install gzip properly on my local server. I can now gzip and gzip -d (what you called gunzip, I guess!) but now the gz files generated by the script have a 0 size… Before, they had the right size and proper data inside (I tried to extract some on my computer with Winrar, and checked).

    Now that I can gzip and gunzip, I can only download one or two files (the script stops right after then) and I have the message “File is of size 0” every time…

    I can’t figure out why. But I know I’m getting closer! Gonna try to get it working, once again 🙂

    1. Dear Selphira, could you share some details how you got things working? I am also using WAMP and run into the same problem. Trying to install gzip but as of yet no data is entered into the database.

      Help or pointers would be much appreciated.

        1. Actually yeah, I realized that gzip was not properly installed on the WAMP I was working with, so I fixed this, and switched as you said for the Linux-like command (gzip -d means ‘deflate’, I guess :D) and it’s working alright now. Sorry for the late answer :/

  7. Hi, great code ! I’m having trouble finding the vnd number, is not on the top left corner anyone, I can only see my name.

    Any clues ?

    Thanks

    JAZ

    1. I’ve had a search around myself and I can’t see where it’s displayed anymore. It’s a real head scratcher. I’ll continue to look and let you know if I find it.

  8. For VND number:

    Choose Sales and Reports.
    Choose “Reports” from the top left menu (Where default is “Top Content”).
    From the form that will be shown, choose monthly report and download.
    The downloaded file will be of format S_M_(VendorID)_TIMEPERIOD.TXT

Leave a Reply

Your email address will not be published. Required fields are marked *