Google Play Console to BigQuery Integration: 3 Easy Methods

Google Play Console and BigQuery are two powerful tools that organizations use together to achieve great results. Connecting Google Play Console to BigQuery allows you to centralize your app data for in-depth insights across teams. This will enable teams within organizations to work together and make necessary changes based on the issues identified from the application data. Since both Google Play Console and BigQuery are offered by Alphabet, it is seamless to integrate the platforms.

In this blog, the steps needed to integrate Google Play Console to BigQuery. It also gives a brief introduction to Google Play Console and BigQuery.

Why Connect Google Play Console to BigQuery?

You can get a lot of useful information from Google Play Console for both marketers and developers. When businesses release their apps in numerous nations, it becomes complex because different data silos are created for teams.
Therefore, it becomes crucial for businesses to incorporate this Google Play data into a scalable data warehouse like BigQuery along with data produced from other apps and tools like customer support platforms, websites, inventory management, payment gateways, and CRMs. To better serve customers and enhance their experiences, integrate your Google Play Console data with BigQuery and other tools and applications in your company.

Connecting Google Play Console to BigQuery

• Method 1: Using Hevo to Set Up Google Play Console to BigQuery
• Method 2: Using Custom Code to Move Data from Google Play Console to BigQuery
• Method 3: Using BigQuery Data Transfer Service to Connect Google Play Console to BigQuery

Method 1: Using an Automated Data Pipeline to Set Up Google Play Console to BigQuery

Hevo provides an Automated No-code Data Pipeline that helps you move your Google Play Console to BigQuery. Hevo is fully-managed and completely automates the process of not only loading data from your 150+ data sources(including 40+ free sources)but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Using Hevo, you can connect Google Play Console to BigQuery in the following 2 steps

• Step 1: Configure Google Play Console as the Source in your Pipeline by following these steps:
  • Step 1.1: In the Asset Palette, select PIPELINES.
  • Step 1.2: In the Pipelines List View, click + CREATE.
  • Step 1.3: Select “Google Play Console” on the Select Source Type page.
  • Step 1.4: Click + ADD GOOGLE PLAY ACCOUNT on the Configure your Google Play Account page.

• Step 1.5: Choose the Google Play account to sign in with from the Sign in with Google dialogue box.
• Step 1.6: To grant Hevo access to your Google account and read the report data for the associated applications, click Allow.
• Step 1.7: Enter the following information on the Configure your Google Play Source page:

• Pipeline Name: A distinct, 255-character-maximum name for the Pipeline.
• Bucket ID: ID of the bucket that holds the reports, which you retrieved from Google Play.
• Reports: Modify the selection, if needed. By default, all reports are selected.

• Step 1.5: Simply press TEST & CONTINUE.
• Step 1.6: Configure the data ingestion and establish the destination after that.
• Step 2: To set up Google BigQuery as a destination in Hevo, follow these steps:
  • Step 2.1: In the Asset Palette, select DESTINATIONS.
  • Step 2.2: In the Destinations List View, click + CREATE.
  • Step 2.3: Select Google BigQuery from the Add Destination page.
  • Step 2.4: Choose the BigQuery connection authentication method on the Configure your Google BigQuery Account page.

• Step 2.5: Choose one of these:
  • Using a Service Account to connect:
    • Service Account Key file, please attach.
    • Note that Hevo only accepts key files in JSON format.
    • Go to CONFIGURE GOOGLE BIGQUERY ACCOUNT and click it.
  • Using a user account to connect:
    • To add a Google BigQuery account, click +.
    • Become a user with BigQuery Admin and Storage Admin permissions by logging in.
    • To grant Hevo access to your data, click Allow.

• Step 2.6: Set the following parameters on the Configure your Google BigQuery page:
  • Destination Name: A unique name for your Destination.
  • Project ID: The BigQuery Project ID that you were able to retrieve in Step 2 above and for which you had permitted the previous steps.
  • Dataset ID: Name of the dataset that you want to sync your data to, as retrieved in Step 3 above.
  • GCS Bucket: To upload files to BigQuery, they must first be staged in the cloud storage bucket that was retrieved in Step 4 above.
  • Sanitize Table/Column Names: Activate this option to replace the spaces and non-alphanumeric characters in between the table and column names with underscores ( ). Name Sanitization is written.

• Step 2.5: Click Test Connection to test connectivity with the Amazon Redshift warehouse.
• Step 2.6: Once the test is successful, click SAVE DESTINATION.

Method 2: Using Custom Code to Move Data from Google Play Console to BigQuery

Connecting your Google Play Console to BigQuery accounts has various benefits, including improved data analysis and insights. You can access Google Play reporting data in BigQuery by linking your Google Play Console to BigQuery. With Google Play Console to BigQuery integration, you can easily analyze reporting data across devices and identify trends.

The data analysis techniques in BigQuery will help users to improve app release management by better analyzing the changes and allowing companies to release updates and improvements as soon as they catch a bug. You can also see increased collaboration with team members since companies can easily share data and resources between team members, including developers, testers, and analytics experts.

To manually upload your Google Play Console reports to your BigQuery, follow these steps:

You can export two types of reports: Detailed reports and Aggregated reports.

Download Reports from Google Play Console

• Step 1: Open Google Play Console.

• Step 2: Click Download Reports, and select from Statistics, Financial, or Reviews.
• Step 3: Under “Select an application,” find your app’s name.
• Step 4: Select the year and month of the report you want to download.

Upload Reports to BigQuery

To import Play Console reports into BigQuery, you need to convert the CSV files from UTF-16 to UTF-8. Batch loading jobs are the best option when uploading local files to BigQuery, especially if it supports your file format. This method supports the following file formats:

• Avro
• Comma-separated values (CSV)
• JSON (newline-delimited)
• ORC
• Parquet
• Firestore exports stored in Cloud Storage.

Follow these steps to import Play Console data to BigQuery:

• Step 1: Go to the BigQuery home page and select upload under the Create Table section.

• Step 2: Select the file and file format. Enter the project name and dataset name under ‘Destination.’ 

• Step 3: BigQuery will automatically determine the table structure. 

Method 3: Using BigQuery Data Transfer Service to Connect Google Play Console to BigQuery

The BigQuery Data Transfer Service helps to automate data movement into BigQuery on a scheduled basis without writing a single line of code. You can access BigQuery Data Transfer Service from the command-line tool, Google Cloud Console, and BigQuery Data Transfer Service API. BigQuery Data Transfer Service supports loading data from Google Play and other Google SaaS apps, external sources, data warehouses, and third-party apps. 

Pricing: There is a monthly charge of $25 per unique Package Name in the Installs_country table for automating Google Play Console to BigQuery migration. In addition, standard BigQuery storage and query pricing applies after data is transferred to BigQuery. 

For Google Play, BigQuery Data Transfer Service supports reviews and financial reports under detailed reports and statistics and user acquisition under aggregated reports. 

Step 1: Enable the BigQuery Data Transfer Service

You will have to create a project and enable BigQuery API. 

• Go to the project selector page in Google Cloud Console.
• Select or create a Google Cloud project.
• Enable billing for all transfers. 
• BigQuery API will be automatically enabled.  

Step 2: Grant bigquery.admin IAM Role Access

• Open the IAM page in the Google Cloud console.
• Select and open the project you created in the previous steps.
• Click Add to add members, provide access, and set permissions. 

Step 3:  Create a BigQuery Dataset to Store the Google Play Data

You can create datasets in various ways like Google Cloud Console, SQL query, bq command-line tool, client libraries, etc. In this step, you’ll see how to create datasets in BigQuery with Google Cloud Console.

• Open BigQuery in the Google Cloud console.
• Go to the Explorer panel and select the project you created in 1st step.
• Expand Actions and click Create Dataset.
• Enter Dataset ID, Data location, and default table expiration values on Create dataset page.
• Click on Create dataset.

Step 4: Click on the Transfers in the BigQuery Page in the Google Cloud Console

Step 5: Click on Create Transfer Under Data Transfer

Step 6: Set IP the Transfer

• Google Play for Source

• Schedule 

Click on Start at a set time, or leave the default value (start now) under the Schedule tab.

• Destination Settings

Choose the dataset created in previous steps as the destination for Google Play data. 

Step 7: Click Save

The BigQuery Data Transfer Service will start to automatically move data from your Google Play Console to BigQuery at your scheduled time. 

Conclusion

Google Play Console and BigQuery are two of the most powerful tools that Google offers developers. Using these tools together, you can streamline the process of managing your app store app data and making better decisions about app development. In this blog, we have outlined the steps necessary to connect Google Play Console with BigQuery. You can also use a platform like Hevo to automate the Google Play Console to BigQuery integration. 

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready. 

Visit our Website to Explore Hevo

Want to take Hevo for a spin? 

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.