SQL queries to Appstore
We are opening up SQL access to our core database, for those who needs RDBMS for data analysis.
Queries can be like this:
SELECT COUNT(*) FROM apps WHERE device_type_MacDesktop ; /* How many MacOS apps in the store?*/
SELECT COUNT(DISTINCT application_id) from apps; /* How many apps total? */
SELECT COUNT(DISTINCT (IF (seller_name='',artist_name,seller_name))) from apps WHERE language_code = '';
/* How many publishers in the store? */
$ mysql -h athena.statsios.com -udemo -pdemo statsios
Brief Appstore stats:
select COUNT(DISTINCT application_id) from apps;
About 1.2 million apps total.
select COUNT(DISTINCT application_id) from apps WHERE export_date >= 20131210 ;
About 1.08 million apps are active.
select COUNT(DISTINCT application_id) from apps WHERE language_code='' AND price_USA != '0.00 USD'
Half ( ~510.000) is paid apps.
SELECT a.c AS how_many_apps, COUNT(*) as how_many_sellers FROM ( select seller_name, count(*) as c FROM apps WHERE seller_name != '' GROUP BY seller_name ) a GROUP BY c ORDER BY how_many_apps ASC;
Briefly, half of all sellers (~150.000) have only one app.
We are also planning to move our database of changes to RDBMS too.
Apple allowed App transfers. What changed?
As we know, on Jun 11, 2013 Apple officially allowed App transfers from one seller to another. For someone who bough/sold app that would mean that existing app would be seamlessly transferred from one account to another, preserving app id, user base, reviews, ranking positions etc.
Before that significant change, to transition apps developers had such options:
1) Delete App on one account, and re-create it on another account. This would not preserve any rankings or userbase.
2) Transfer whole ItunesConnect developer’s account. That was viable for accounts who had only one single app. Sometimes developers specifically created new ItunesConnnect accounts if they planned to sell the app in the first place. I’m not sure if this was legitimate in point of view of Apple.
3) Some developers reported that it was possible to transfer Apps if they applied to Apple’s support engineers. Not to mention that process was complicated and frustrating, with no guarantee of success.
3*) There is another reason why some Apps change their Seller’s Name: in case if private developer (such as john Doe for example) decides to register a business name and associate his account with that business name (such as JohnDoeApps LTD). In that case all developer’s apps change their Seller’s Name in one single moment once its approved.
With new “App transfer” feature transferring Apps became hustle free, easy and legitimate, especially with a rise of such Apps marketplaces as http://apptopia.com and mobile apps Industry still growing overall.
I was curious, how many app transfers really happen in Appstore? So I’ve built a simple chart based on Statsios API to visualise seller changes.
Count of seller changes breakdown by day.
Only unique Seller name changes were considered. In other words, if some seller changed his account name from private name to business name, and it resulted in change of all his apps (say, ten), its counted as one, not ten.
What we are seeing here prior to June 11 is publishers changing their names to business names (that’s average 20-25 per day). After June 11, sellers started transferring apps, and average grew up to 100 changes per day.
It’s hard to tell for each case whether Seller name change is real app transfer or just private-to-business name change. Each particular case requires investigation.
For anyone interested to dig deeper - Statsios.com provides API calls to fetch all Appstore changes for any date starting from March 1st. It is possible to tell who, when and what was transferred.
Finally I’ve created this blog to post some stuff on our progress developing StatsiOS.com
Stats iOs is a startup that focuses on extended statistics of Apple’s App Store. It was born as a passion of two software engineers for Big Data. We try to give you additional value in insights of “What actually happend?” during certain period of time, which impacted statistics of the app.
We surf and agregate 15-30 Gigabytes of data relevant to Apps and AppStore daily, and to tell you the truth, StatsiOs is a mashup of other cool technologies and services in the core. We are standing on the shoulders of giants.
We have open API for anyone to use and integrate, whether it be internal needs or another applications on top of our data. Request API here http://statsios.com/?page=api and currently available API calls are here http://statsios.com/?page=apihelp
Check out our PDF with use case examples: