Did you know that you can also remove all imported app reporting data from Google Sheets with a single click? Here i how it works:
(If the video doesn’t load click here)
Please note that this function will delete all previously imported app reporting data permanently. This step can’t be revoked.
Did you know that you can automate your app reporting with AppStoreMetrix by using the schedule imports option?
This is how it works:
To get started, just follow the steps below to set up scheduled imports for iTunes Connect and Google Play Store app reporting data:
Navigate to Add-ons in Google Sheets top navigation
Click AppStoreMetrix and then Schedule Import instead of launching the sidebar
Choose the platform you want to import data from
Make your choice at report type and schedule import options
Today we want to show you how to retrieve the right credentials from iTunes Connect and Google Play to connect AppStoreMetrix.
(If the video doesn’t load, click here)
To generate a new iTunes Connect Access Token follow the four steps below:
Your personal access token will be displayed in the appearing window. You are now able to copy and paste the existing code into the AppStoreMetrix side bar or even generate a new access token before.
(If the video doesn’t load, click here)
The Google Play bucket id is displayed in your Google Play Console:
To extract the bucket id just paste the copied Url into the AppStoreMetrix side bar and erase gs:// at the beginning and /sales or /financial at the end of the URL.
Import your iTunes Connect and Google Play app reporting data into Google spreadsheets with just a few clicks:
*Available for iTunes Connect data only.
As most app publishers can tell comparing iTunes Connect and Google Play app reporting data is quite hard. Although the key metrics are more or less the same the app reports will be delivered in a different format what requires some workarounds to match the data properly to visualize. To ease the process we created a workflow using AppStoreMetrix to import the app reporting data from iTunes and Google Play to Google Spreadsheets and connected the sources with Google Data Studio to visualize the key metrics in a single dashboard.
The reference customer is an app publisher that is running a mobile app with In-App Sales at Google Play Store and Apple App Store. With more than 150,000 monthly transactions on both platforms the need for an automatic reporting solution is quite reasonable, not just to give further insights on app analytics by providing proper data visualizations. The main goal was to create a stable workflow that eases the preparation of big data sets and minimizes the amount of work.
As most app publishers can tell an automatic comparison of Google Play and iTunes Connect reporting data is quite tough to set up. Using online based solutions such as the Google Sheets add-on AppStoreMetrix and Google Data Studio it is possible to create a stable workflow that includes all necessary key metrics:
The dashboard below provides insightful visualizations and turned out to be time-saving by minimizing the amount of manual work from half a day to about half an hour per month.
To connect the app reporting data of both app stores with Google Spreadsheets we set up a scheduled data import of both platforms using AppStoreMetrix. This Google sheets add-on provides an interface that connects reporting data from both platforms with Google Spreadsheets with a few clicks. Based on the iTunes Connect access token and the Google Play bucket ID the add-on lets you pull data automatically into Google sheets on a daily, weekly, monthly or yearly basis.
!!! To set up the connection just have your iTunes Connect access token and Google Play bucket id ready and follow the instructions described in the AppStoreMetrix help section: https://appstoremetrix.com/help/ or watch the latest video tutorial on YouTube: https://www.youtube.com/watch?v=Za-7J-Au0D4 !!!
The comparison of Google Play and iTunes Connect reporting data is challenging due to the differently structured report files. While monthly sales reports of Google Play show pure sales data for each date with one row for each in-app transaction, the iTunes Connect Summary Sales Report contains all transactions such as free and paid downloads, in-app sales or subscriptions for the whole period aggregated by the interval you select (e.g. daily, weekly, monthly).
To merge data within the dashboard it is necessary to set up four harmonization sheets:
Although Google Data Studio eases the process by providing various calculation and filter functions it is not yet possible to merge several sheets within a single chart.
To provide an overview for all in-app sales on a daily basis the first step is to set up a daily scheduled import for iTunes Connect data with AppStoreMetrix to guarantee that the transactions will be assigned to a specific date. For further harmonization we’ve created the sheet “Items Sold” that basically sums all iTunes and Google data from the import sheets by date by using the SUM, COUNT and SUMIFS functions.
As the iTunes Summary Sales Report not just contains in-app purchases but also app download stats we had to split it by the product type identifier. As Google Spreadsheet function SUMIFS doesn’t allow to look for more than one criteria in a single column we had to enter a clustered formula defining all product type identifiers that label in-app purchases:
=(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IA1”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IA1-M”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IA9”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IA9-M”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IAC”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IAC-M”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IAY”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“IAY-M”))+(SUMIFS(‘Import Data Sales Summary daily’!H:H,‘Import Data Sales Summary daily’!J:J,A2,‘Import Data Sales Summary daily’!G:G,“FI1”))
As Google Play Data is structured differently we can use an easier common function here:
=COUNTIF(‘Google Play – salesreport’!B:B,C2)
Before comparing iTunes and Google sales amounts per currency we need to add the sheet iTunes Pricing. This is necessary as iTunes app reporting data doesn’t contain the total sales amount per currency and it is not possible to combine SUMIFS functions with further calculations in data studio. Basically this sheet is directly connected to the sheet Import Data Sales Summary daily calculating the total price (unit*customer price) and connects it to other data for further usage, e.g. units, currency, product type identifier and device type.
To display the total sales amount in EUR (or another currency) for iTunes and Google Play data we created the sheet “Sales per Currency” that contains a list of all currencies, exchange rates, sales amount per currency and the sales amount in Euro. To automate the conversion from one currency to another we added the current exchange rate in column B by using the google finance function:
!!! Please note that this modified function is currently not officially documented in the Google Spreadsheets function list. The first three letters describe the currency you wish to convert from. The last three letters describe the currency you wish to convert to. !!!
Column C and E are providing the total sales per currency for iTunes Connect and Google Play using the sumif function:
=SUMIF(‘iTunes Pricing’!C:C,A2,‘iTunes Pricing’!B:B)
=SUMIF(‘Google Play – salesreport’!J:J,A2,‘Google Play – salesreport’!M:M)
Column D and F provide the converted amount of each currency in EUR while column G shows the total sales for each currency.
To compare the Sold Items per Country for Google Play and iTunes Connect we created the sheet “Items per Country”. Similar to the sheet “Sales per Currency” we first listed the country codes and then connected data by using the SUMIFS function for iTunes Connect and the COUNTIF function for Google Play data that have been already used in chapter 3.1.
!!! Once in a while Apple is updating the product type identifiers. If you are looking for a complete list of all product type identifiers please follow the link below. Stated with December 2017 all current available identifiers have been considered in the formulas above. http://help.apple.com/itc/appssalesandtrends/?lang=en#/itc2c006e6ff !!!
Before you can start creating your personal app reporting dashboard with iTunes Connect and Google Play Store data you have to setup the connection to the data source first.
To connect Google Spreadsheets with data studio just follow the three steps below and start creating your own dashboard:
!!! Please note that each sheet of the document should be connected as a separate data source. !!!
If you have any questions regarding this case study or functions and features of AppStoreMetrix please contact us.
Great news! AppStoreMetrix has been sucessfully launched and published to the Google add-on store for DE and UK users on November 28th. After some reviews and tests the add-on will be published to US and all other countries soon.
Here is what we changed in this version:
We are still in private beta. Just finishing up some tasks – public release should happen soon, please subscribe to our newsletter in the sidebar, be the first to know and receive our special launch offer.
Here is the install link for registered beta users once again:
You need to be signed in to your Google account, that you used to sign-up for our beta, otherwise it will show a 404 page. If you want to receive an invitation, you can still register in the sidebar.
Great news! We fixed the oAuth issues (“This app is not verified by Google”) and got approval from Google, again. That means the AppStoreMetrix Add-On is now available again to registered beta users.
The install link is the same, here it is once more.
Sidenote: If you can just see the menu item “help” and nothing else you need to delete and re-install the add-on.
We are now preparing the public release, which should happen in the coming days as well.
If you dont want to wait and are not a registered beta user yet, just sign up with your e-mail in the sidebar.
p.s. By the way, we also removed mail as a neccessary property permission while logging in with your Google Account.