Case Study – How to visualize app reporting data easily with AppStoreMetrix & Google Data Studio

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.

1 Overview

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:

  1. total in-app sales units on a daily basis
  2. total sales by currency
  3. transactions per country in total and percentage
  4. integrate a detailed report for iTunes Connect data

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.

2 Connect App Store Reporting Data With Google Sheets

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 !!!

 

3 Harmonize iTunes Connect With Google Play Data

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:

  1. Items Sold
  2. iTunes Pricing
  3. Sales per Currency
  4. Sales per Country

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.

3.1 Total Items Sold

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)  

3.2 iTunes Pricing | Sales per Currency

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:

=GOOGLEFINANCE(“currency:AEDEUR”)

!!! 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.

3.3 Items per Country

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 !!!

 

4 How to Create a Dashboard With Google Data Studio

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.

4.1 Connect Google Spreadsheets With Data Studio

To connect Google Spreadsheets with data studio just follow the three steps below and start creating your own dashboard:

    • Navigate to Google Data Studio and login with your Google credentials:

https://datastudio.google.com/navigation/reporting

    • Create a new Dashboard and name it:
    • Connect the data sources by clicking on “CREATE NEW DATA SOURCE” in the right corner.

!!! Please note that each sheet of the document should be connected as a separate data source. !!!

4.1.1 Google Spreadsheet data source to connect with Data Studio

5 THE RESULT: Google Data Studio Dashboard with App Reporting Data of iTunes Connect & Google Play

If you have any questions regarding this case study or functions and features of AppStoreMetrix please contact us.

Click Here to Leave a Comment Below

←Previous post Next post→