Query Your Google Play Data in BigQuery

I follow the Google advertisers group from time to time to help answer some of the community questions. There was one question that popped up, which I thought I would answer with a longer post.

The Question…

The Answer…

The BigQuery team has been incubating a feature called “BigQuery Data Transfer Service”, which allows one to automatically schedule and manage recurring load jobs for various data sources. As of January 2018, the following data sources can be integrated:

The BigQuery Data Transfer Service automatically loads: Crashes & ANRs, Reviews, Financial reports, Statistics, and User Acquisition data for your Google Play reports every 24 hours from the time of creating your Transfer Config (source). At the time of writing this post, the BQDTS for Google Play Store Data is still in Alpha-mode, which means there is currently no charge for the transfers, however you have to pay for normal BigQuery storage once the data is in BigQuery (pricing subject to change).

Set-up your admin & billing

1. To schedule a BigQuery Data Transfer Service for Google Play, you must have access to the Google Play Developer Console account as admin. So ask your webmaster to grant you admin access first.
2. Select or create a Cloud Platform project against which you want to create this transfer: GO TO THE PROJECTS PAGE
3. Enable billing for your project: ENABLE BILLING
4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, ENABLE THE API
5. The user creating your transfers should be a project EDITOR or OWNER for the transfers to succeed. You can confirm project-level permissions in the IAM & ADMIN console.

Enroll for the transfer service

Alpha customers need to apply for access, agree to applicable terms, and have their projects whitelisted. Copy the Project Number(s) from step 2 above, navigate to this form and enroll by selecting the “Google Play Store (alpha)”-option. BigQuery will email you if you have successfully been enrolled.

Add your transfer configurations

Ensure that you are signed in as the right user (Google BigQuery doesn’t support signing in with multiple accounts). Navigate to your BigQuery Web UI.

Notice that a “Transfer”-tab has been added to your sidebar after your enrollment. This is essentially where the magic happens. Before we can start the transfer, please create a BigQuery “dataset”. This dataset will store the Google Play Store data.

To create a data transfer for Google Play:

1. Click “Transfers”.
2. If prompted, click Enable to grant permissions to the BigQuery Data Transfer Service. Note: The first time you enable transfers in a project, you are required to grant permissions to the Data Transfer Service.
3. Click Add Transfer.
4. On the New Transfer page:

• For Source, choose Google Play Reports.
• For Destination, choose the appropriate dataset (that you created above).
• For Display Name, enter a name for the transfer such as My Transfer. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.
• For GCS Bucket, enter the name of the Cloud Storage bucket that stores your Data Transfer files. When you enter the bucket name, do not include gs://. Setting up a BigQuery data transfer for Google Play requires your Google Play-provided Google Cloud Storage bucket ID. Please note that the Play transfer will load all available reports, for all available apps under your bucket ID. Your Google Cloud Storage bucket ID is listed near the bottom of your Reports pages in the Google Play Developer Console. Your bucket ID begins with “pubsite_prod_rev”, for example: pubsite_prod_rev_01234567890987654321.
• The Table Suffix, has to be unique for all data sources loading into the same dataset. Table names are of the format [report_name]_[suffix].

5. Finally, Click Add.

“Once the transfer is added, you can edit the transfer, delete the transfer, update the credentials, schedule a backfill, or view the transfer properties such as the next scheduled transfer in the BigQuery web UI. As your scheduled transfers are run, the BigQuery web UI displays the run history, including successful transfers and transfers that fail. Scheduled runs more than 90 days old are automatically deleted from the run history” (source).

NB! The Google Play BigQuery Transfer service will be a paid service from August 2018 onwards at $25 per unique Package Name in you BigQuery Installs_country table. Currently, the Google Play BigQuery transfer service is set up in such a way that you not only transfer one specific app within your Google Play account, but data of all your apps in your account. So, it will probably be cheaper to set-up one scheduled query for only one app, and thereafter query other apps data from this one data-set.

Visualize your Play Console Data (with other data)

No longer are you bound to only keep your Google Play Store Data siloed from other parts of your business, but can visualize it with other business critical data, such as:

• iTunes Connect Data
• Firebase Event Data (Android + iOS)
• Relational Data (MySQL db)
• Adwords Data

To do this, we can integrate BigQuery with services such as Redash or Google Data Studio.

Redash.io

Thanks for reading!

Who in this world does not like feedback?

• ⚡Comments: Is there something I could improve or perhaps address in a next post? Let your fingers do the talking and please drop me a comment below.
• ⚡Followers: If you loved this post, please consider following me on Medium and Twitter for future posts on various indie tasks, workflows, and open source tech.
• ⚡Readers: If you found this article helpful, please feel free to also scan over some of my other workflows