Category Archives for news

How to delete all imported data from Google Sheets

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:

Delete Sheets permanently with AppStoreMetrix

(If the video doesn’t load click here)

Instructions

  1. Navigate to “Add-ons” on the top navigation of Google Sheets
  2. Choose AppStoreMetrix
  3. Click on “Delete All Imported Sheets”

Please note that this function will delete all previously imported app reporting data permanently. This step can’t be revoked.

How to schedule iTunes Connect and Google Play imports to Google Sheets

Did you know that you can automate your app reporting with AppStoreMetrix by using the schedule imports option?

This is how it works:

Schedule iTunes Connect imports to Google Sheets with AppStoreMetrix

Schedule Google Play imports to Google Sheets with AppStoreMetrix

(If the videos don’t load, click here for iTunes and Google Play)

Instructions

To get started, just follow the steps below to set up scheduled imports for iTunes Connect and Google Play Store app reporting data:

  1. Navigate to Add-ons in Google Sheets top navigation

  2. Click AppStoreMetrix and then Schedule Import instead of launching the sidebar

  3. Choose the platform you want to import data from

  4. Make your choice at report type and schedule import options

For further instructions just have a look at our help section or contact us.

How to find the iTunes Connect Access Token and Google Play Bucket ID

Today we want to show you how to retrieve the right credentials from iTunes Connect and Google Play to connect AppStoreMetrix.

Generate iTunes Connect Access Token

(If the video doesn’t load, click here)

To generate a new iTunes Connect Access Token follow the four steps below:

  1. Choose Sales and Trends
  2. Switch from Sales to Reports in the top left corner
  3. Click on the questionmark beside About Reports in the top right corner
  4. Click Generate Access Token

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.

Where to find the Google Play Bucket ID

(If the video doesn’t load, click here)

The Google Play bucket id is displayed in your Google Play Console:

  1. Navigate to Google Play Console
  2. Choose Download Reports in the left navigation
  3. Click Financial
  4. Scroll down and copy one of the direct report URls

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.

Start importing your app reporting data with AppStoreMetrix

Import your iTunes Connect and Google Play app reporting data into Google spreadsheets with just a few clicks:

  1. Install AppStoreMetrix
  2. Have your iTunes Connect access token and Google Play bucket id ready.
  3. Choose the platform you want to import data from.
  4. Make your choice at report type, report subtype*, date type* or choose a single day.

For more information please have a look at our help section or contact us.

*Available for iTunes Connect data only.

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.

AppStoreMetrix – v2.6 released

Here is what we changed in this version:

  • Notify user if no data for iTunes Dashboard is available
  • Get Premium button shows for every user
  • Added infos to checkout screens
  • Changed premium licensing packages
  • Fixed some bugs 🙂

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:

https://chrome.google.com/webstore/detail/appstoremetrix/eonflbgifkcopaagegpajbafilncjief

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.

 

Beta available again. Now version 2.3. Verification problems fixed. :)

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.

https://chrome.google.com/webstore/detail/appstoremetrix/eonflbgifkcopaagegpajbafilncjief

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.

AppStoreMetrix v1.13

AppStoreMetrix version 1.13 now offers the import of various subtypes for Google Play Store reports. For a better performance the import by product ID was removed and deferred to a later release date. Check out our latest features and sign up for beta testing!

1 2