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.
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.
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.
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);
@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.
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…
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?
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.
perfect….now it’s ok….many many thanks
Glad you got it to work.
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 ?
Hi Karl,
can your php get ranking data? Thanks.
No, I’m afraid not. I know there are services like AppAnnie (http://www.appannie.com/) that do something along those lines though,
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!
My host does not allow exec(). Is there another way to handle this or?
You could rewrite the scripts to use the gzuncompress (http://us3.php.net/manual/en/function.gzuncompress.php) functions if that’s compiled into the version of PHP on your host.
Thank you. It’s working now 🙂 Nice tool!
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
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. 🙂
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.
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 75Could 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.
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 🙂
I got it working! Thank you for this nice script 🙂
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.
I got it! Some helpful links for anyone else:
http://www.montrealcommunity.com/Thread-Enable-deflate-on-WAMP
http://sourceforge.net/projects/gnuwin32/ <– I put this in a folder included in PATH and as well in the folder of the .gz files
Also had to do what Selphira said, and change from gunzip to gzip -d.
Great script Karl, thanks very much.
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 :/
excellent. thank you very much!
Thanks for the script. Had to update it after 2013-10-09, because Apple added an additional field ‘category’.
Is there a way to have it download all iTunes reports?
Are you looking for the yearly reports? If you look at the documentation for the API (http://www.apple.com/itunesnews/docs/AppStoreReportingInstructions.pdf), you’ll see there’s a few options that can be given to the DATETYPE parameter. This is on line 43 of ingest.php, so if you changed it to yearly and changed the date parameter to just YYYY you should get all the reports.
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
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.
Thanks, please let me know if you find it, I assume is mandatory ?
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