Google Play Console to BigQuery Integration: 3 Easy Methods

• July 14th, 2022

google play console to bigquery: FI

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.

Table of Contents

Prerequisites

App management on Google Play Console

What is Google Play Console?

google play console to bigquery: google play console logo
Image Source

Google Play Console is a tool released by Google in May 2017 which allows developers to manage their App Store and Google Play publishing from one central location. It’s the official management and reporting platform for Android apps listed on Google Play Store. 

There are two primary targets: app developers and app marketers. App developers focus on developing, publishing, and monitoring their apps’ technical performance. App marketers, on the other hand, are looking for ways to improve conversion rates, optimize their app store presence, and monitor app performance on the Play Store. 

Google Play Console has comprehensive, first-party data that companies can use to analyze their app performance and find improvement areas. The optimizations will impact organic rankings in the Play Store, and influence paid user acquisition through Google app campaigns.

Key Features of Google Play Console

Google Play Console offers crash reporting, analytics, and customer support features. Some key features of Google Play Console include:

  • Submit crash reports to help improve app reliability and fix errors.
  • Track user engagement with their apps through detailed insights into behavior patterns across devices and platforms.
  • Admin panel: For managing your device’s configurations (emulators, builds, signing certificates).
  • Quickly introduce changes to your app’s metadata or screenshots.
  • Issuing new versions of your app with automated testing and promotion support.
  • Understanding and analyzing user reviews, ratings, and comments from the Google Play Store.

What is Google BigQuery?

google play console to bigquery: google bigquery logo
Image Source

Google BigQuery is a cloud-native, enterprise-grade Data Warehouse. It is a highly scalable serverless, fully-featured, fully manageable Data Warehouse that enables scalable analysis over petabytes of data. It is developed by Google and launched on 19th May 2010. It is designed such that it uses the processing power of Google’s infrastructure that makes a single SQL query to analyze petabytes of data in seconds.

BigQuery has evolved into a fully managed, economical data warehouse that can run interactive queries on petabyte-scale datasets. It can also integrate with Google Cloud Platform (GCP) and third-party tools. Since BigQuery is serverless or data warehouse as a service, users don’t have to install or manage any servers or database software. BigQuery manages the underlying software and infrastructure, including scalability and high availability. The pricing model is flexible and quite simple; for every 1 TB of data processed, users have to pay $5.

BigQuery is also called SQL-based Data Warehouse as a Service (DWaaS) with zero infrastructure management. It is a serverless warehouse that does not require any upfront hardware provisioning or management.  BigQuery runs SQL Queries and all requests are to be authenticated. Google provides a complete package to their users with Big Data loading features on Google Cloud Storage and connectivity with various Google apps like Apps Script.

BigQuery uses a Columnar Storage format that is optimized for analytical queries to store data. BigQuery displays data in tables, rows, and columns, with full database transaction semantics support (ACID). To ensure high availability, BigQuery storage is automatically replicated across multiple locations. You can use external tables or federated queries to query data stored in BigQuery or run queries on data stored elsewhere, such as Cloud Storage, Bigtable, Spanner, or Google Sheets in Google Drive.

Key Features of Google BigQuery

Some key features of BigQuery are:

  • Manageability: Google BigQuery is fully managed, everything from patching, storage management, compute allocation, and more.
  • Scalability: BigQuery relies on MPP or massively parallel computing to offer a highly scalable, secure, and consistent performance data warehouse system. 
  • Storage: Users can easily load data in various data formats like JSON, CSV, AVRO, and more. BigQuery automatically converts all data into columnar storage, with various benefits, including quickly scanning data and optimal storage utilization.
  • Data Ingestion: Google BigQuery supports both batch data and streaming ingestion methods. There are no charges for batch data ingestion, while there is a fee for streaming data ingestion. Users can stream millions of rows of data every minute without the complexity of installing and managing infrastructure. 
  • Pricing: BigQuery offers flexible pricing: flat-rate and on-demand. You can decide on a pricing model by considering the scale of your operation. There is a segregation between compute and storage resource pricing — medium-sized companies with irregular query demands only pay for the resources utilized for query processing. In contrast, large enterprises and corporations can pay for dedicated resources. 
  • Security: Google BigQuery supports various authentication models. For example, users or service accounts can be granted access to specific Google BigQuery resources at various levels. Any tables or views under the dataset with authentication automatically inherit the permissions from the dataset. 

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.

Reliably integrate data with Hevo’s Fully Automated No Code Data Pipeline

If yours anything like the 1000+ data-driven companies that use Hevo, more than 70% of the business apps you use are SaaS applications Integrating the data from these sources in a timely way is crucial to fuel analytics and the decisions that are taken from it. But given how fast API endpoints etc can change, creating and managing these pipelines can be a soul-sucking exercise.

Hevo’s no-code data pipeline platform lets you connect over 150+ sources in a matter of minutes to deliver data in near real-time to your warehouse. What’s more, the in-built transformation capabilities and the intuitive UI means even non-engineers can set up pipelines and achieve analytics-ready data in minutes. 

All of this combined with transparent pricing and 24×7 support makes us the most loved data pipeline software in terms of user reviews.

Take our 14-day free trial to experience a better way to manage data pipelines.

Get started for Free with Hevo!

Connecting Google Play Console to BigQuery

Method 1: Using Hevo to Set Up Google Play Console to BigQuery

google play console to bigquery: Hevo Logo
Image Source

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.
google play console to bigquery: configure google play account
Image Source
  • 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.
google play console to bigquery: configure google play as source
Image Source
  • 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.
google play console to bigquery: configure google bigquery account
Image Source
  • 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.
google play console to bigquery: hevo access
Image Source
  • 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.
    • Enable Streaming Inserts: Enable this option to load data via a job according to a defined Pipeline schedule rather than streaming it to your BigQuery Destination as it comes in from the Source. To learn more, go to Near Real-time Data Loading Using Streaming. The setting cannot be changed later.
    • 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.
google play console to bigquery: configure bigquery as destination
Image Source
  • 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.

Deliver Smarter, Faster Insights with your Unified Data

Using manual scripts and custom code to move data into the warehouse is cumbersome. Changing API endpoints and limits, ad-hoc data preparation, and inconsistent schema makes maintaining such a system a nightmare. Hevo’s reliable no-code data pipeline platform enables you to set up zero-maintenance data pipelines that just work.

  • Wide Range of Connectors: Instantly connect and read data from 150+ sources including SaaS apps and databases, and precisely control pipeline schedules down to the minute.
  • In-built Transformations: Format your data on the fly with Hevo’s preload transformations using either the drag-and-drop interface or our nifty python interface. Generate analysis-ready data in your warehouse using Hevo’s Postload Transformation 
  • Near Real-Time Replication: Get access to near real-time replication for all database sources with log-based replication. For SaaS applications, near real-time replication is subject to API limits.   
  • Auto-Schema Management: Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with destination warehouse so that you don’t face the pain of schema errors.
  • Transparent Pricing: Say goodbye to complex and hidden pricing models. Hevo’s Transparent Pricing brings complete visibility to your ELT spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in the data flow.
  • 24×7 Customer Support: With Hevo you get more than just a platform, you get a partner for your pipelines. Discover peace with round-the-clock “Live Chat” within the platform. What’s more, you get 24×7 support even during the 14-day free trial.
  • Security: Discover peace with end-to-end encryption and compliance with all major security certifications including HIPAA, GDPR, and SOC-2.
Get started for Free with Hevo!

Get Started for Free with Hevo’s 14-day Free Trial.

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.
google play console to bigquery: download reports from google play console
Image Source
  • 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.
google play console to bigquery: upload reports to bigquery step 1
Image Source
  • Step 2: Select the file and file format. Enter the project name and dataset name under ‘Destination.’ 
google play console to bigquery: upload reports to bigquery step 2
Image Source
  • 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

google play console to bigquery: bigquery data transfer service
Image Source: Self

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

google play console to bigquery: bigquery page in google cloud console
Image Source: Self

Step 5: Click on Create Transfer Under Data Transfer

google play console to bigquery: transfer under data transfer
Image Source: Self

Step 6: Set IP the Transfer

  • Google Play for Source
google play console to bigquery: set IP transfer
Image Source
  • Schedule 

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

google play console to bigquery: schedule options
Image Source
  • Destination Settings

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

google play console to bigquery: destination settings
Image Source

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. 

Visit our Website to Explore Hevo

Hevo offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Marketing, Customer Management, etc.

This platform allows you to transfer data from 150+ sources (including 40+ Free Sources) such as Google Play Console and Cloud-based Data Warehouses like Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

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.

No-Code Data Pipeline for Google BigQuery