Connect GA4 Data to Squadbase via BigQuery. How to Build a Dashboard in 30 Minutes

Introduction

Many of you working with GA data analysis are likely using GA's native dashboards or Looker integrations for your analysis.

However, the data available through GA's API has limitations.

For example, the following types of data cannot be retrieved via the API:

• Tracking individual user behavior using raw IDs (user_pseudo_id)
• Retroactive analysis of unregistered custom parameters

In particular, tracking user behavior by ID is something many people want to do but find themselves unable to — a common frustration.

In this article, we'll show you how to transfer GA data to BigQuery and easily perform user behavior analysis without writing any SQL.

Transferring GA Data to BigQuery

GCP-Side Configuration

First, prepare a container (project) to receive the data.

1. Go to the Google Cloud Console.

2. Create a project: Select "New Project" from the project selection menu at the top of the screen (you can also use an existing one if available).

3. Enable the BigQuery API: From the menu, go to "APIs & Services" > "Library," search for "BigQuery API," and click "Enable."

   

   

4. Verify permissions:

   • Make sure that the user performing the setup (you) has "Owner" or "BigQuery Admin" permissions for the project.

Linking Configuration in the GA4 Admin Panel

Next, tell GA4 to "send data to this project."

1. Open GA4 "Admin": Click the gear icon in the bottom left, and select the BigQuery link under Product Links.

2. Create a link: Click the [Link] button and select the Google Cloud project you created (or selected) in Step 1.

3. Set the data location: Choose where to store the data.

4. Select export settings:

   • Frequency:
     • Daily: Data from the previous day is transferred once per day (free tier allows up to 1 million events per day).
     • Streaming: Near real-time transfer within minutes. Note: Minor data transfer costs apply.
   • Data type: Check "Events."

   

5. Submit: Review the details and click "Submit" to complete the setup.

Retrieve BigQuery connection information

Required Information

• Google Cloud Service Account JSON A file containing authentication credentials associated with a Google Cloud service account. It acts as an "ID card" when external applications access BigQuery, and contains sensitive information such as private keys.
• Google Cloud Project ID A globally unique identifier for resources created on Google Cloud. It is essential for specifying which project's data and computing resources to use when performing BigQuery operations.

How to Obtain Them

Google Cloud Service Account JSON

Issue a Google Service Account for integration.

In any Project on Google Cloud Platform, select "Service Accounts" from the menu.

After clicking on Service Accounts, click + Create service account at the top of the screen.

Enter the information required to create a service account:

• Service account name: Give it a descriptive name so you can identify its purpose.
• Service account ID: This is auto-populated based on the service account name.
• Service account description: Optionally add a description for this service account.

Click "Create and continue" to create the service account.

Next, grant Permissions.

The permissions differ depending on whether you want to query data or also edit it:

• To query data (read-only): BigQuery Job User + BigQuery Viewer
• To edit data (write/update): BigQuery Job User + BigQuery Editor

Once added, click "Done."

This completes the service account creation.

Next, generate a key.

From the service account list, click the email address of the service account you created.

Select the "Keys" tab, choose Add key > JSON, and click the "Create" button.

A JSON file will be downloaded to your PC. Please upload this JSON file in a later step.

Google Cloud Project ID

Click the project name to the right of the Google Cloud logo in the upper left of the console screen.

In the dialog that opens, you'll see a list with names and IDs displayed side by side.

The alphanumeric string in the ID column (e.g., my-awesome-project-12345) is the Project ID needed for integration. Make sure to select the Project ID of the project where the BigQuery you want to connect is located.

Performing Data Analysis with Squadbase

Using the JSON and Project ID obtained in the steps above, you can leverage AI-powered analysis in Squadbase.

Click the "Create Project" button in Squadbase and select BigQuery.

Answer the questions about the kind of dashboard you want to build with this data, then click the "Next" button.

The editor screen will launch, displaying input fields for the Google Cloud Service Account JSON and Google Cloud Project ID. Enter the respective information in each field.

Squadbase's AI will then automatically analyze the BigQuery data and build a dashboard based on the instructions you provided during project creation.

If a dashboard like the one shown below appears, the connection was successful.

Of course, you can freely add metrics to the generated dashboard and customize it further.

By transferring data from BigQuery, you can also visualize user behavior — such as which pages users navigated from and to.