Getting Started with the GA4 BigQuery Export connector in Analytics Canvas
This article goes through the steps of connecting to your GA4 BigQuery Event export and importing one or more queries from that dataset into Analytics Canvas using the GA4 BigQuery connector.
Demo Dataset for GA4 available for SQL generation
Whether you are a subscriber of Analytics Canvas or in a trial, the quickest way to see how the query builder works is to use the GA4 Sample dataset available in any Canvas. This is a dataset provided by Google for the Google Store and is replicated across all BigQuery data locations. There is 1 complete month of data available for preview, as made available by Google.
Connecting to your own GA4 BigQuery dataset
🛑 To connect to your GA4 BigQuery dataset, first permit the Analytics Canvas Service Account for your subscription to access the BigQuery project with your GA4 data in it.
🔗 Learn more in this article from our knowledge base.
Once you have permitted access (following the steps in the article linked above), find the location of your BigQuery dataset within the Google BigQuery console by clicking on the dataset and reviewing the Dataset info tab.
Next, create or edit a Canvas in the same region as the Data Location of your Dataset in BigQuery.
If you create a Canvas and make a query in any other Data Location, your Dataset will not be shown and you will not be able to query against it.
To add a data location to your subscription, go to Admin > BigQuery Quota and Location
On your canvas, you can now select from two methods of querying the GA4 tables in your BigQuery project.
- GA4 BigQuery SQL Connector (visual UI to generate the SQL query)
- BigQuery SQL Connector (for writing your own custom queries)
The Google Analytics connector generates the SQL based on user selections of dimensions and metrics. The BigQuery SQL connector allows you to write your own SQL, or customize SQL produced by the Google Analytics connector.
To connect to the GA4 BigQuery Export and use the SQL Builder, follow the steps outlined below. To write your own SQL, go to the next section.
Accessing GA4 BigQuery SQL Connector with Analytics Canvas Online
- Select "New GA4 BigQuery Table" after dragging and dropping the Google Analytics data source onto the main canvas.
All users have access to the Google Demo Property. You can use this property to explore the connector, making as many queries as you'd like.
- Once you're ready to view your own data, click "+ Grant Access to GA4 BQ"
** In order to connect to your data in BigQuery, you must first permit the Service Account for your subscription to access the BigQuery project with your GA4 data in it. Learn more in this article from our knowledge base. **
- If you haven't already authorized a Google account with access to your GA4 property, click "Add a new Google Analytics 4 credential." The list should contain at least one account that has at least "Viewer" access to the GA4 Property in Google Analytics that is linked to BigQuery.
- Next, the Service Account for your Analytics Canvas subscription will need permission to read your GA4 Property's data in BigQuery. Follow these instructions on our knowledge-base if you haven't already completed this step. If you've already permitted the Service Account, click "Scan". It may take up to 10 minutes before permissions propagate. Continue to rescan periodically until the property is shown.
** The scanning step might take a few minutes if you have a number of Properties, event params and user property values in your account. **
Select your GA4 Project in BigQuery and click "Next Step: Edit Query".
In the query editor, you can select from the available dimensions and metrics and the SQL will be generated for you.
- You can click Run Query, then select the Preview tab to see a preview of the results. Canvas will preview over the past 5 days.
- You can validate the query by clicking "Validate without running" to see if your query is valid and how many bytes it will process to return the result.
When you've completed your query, click "Next Step:Date Range"
Under Table Name, give your table a name. You can find the table under this name in the Tables menu, and in the list of available tables in your Table library for GA4, shown each time you add a new GA4 input to the Canvas.
Select the date range for your query. By default, Canvas will perform a historic load, then update the table on an incremental basis based on the number of days you enter for the refresh period.
When you've completed your query, click "Next Step:Submit". If you included a date dimension, a date-partitioned table will now be added to your Canvas.
With the data on your Canvas, you can now preview it, profile it, prepare it for reporting, and publish to your preferred data destination!
Adding another GA4 BigQuery Property
Agencies and companies who are working with multiple GA4 properties may need to add a second and subsequent GA4 property. Simply go back to the main GA4 BigQuery editor and click "Refresh Properties".
Note you will have to have a Google Account authorized that has access to the property via the API, and you will have to permit the service account for your subscription to view that BigQuery dataset following the same instructions as above.
Refreshing your GA4 BigQuery Property to fetch Custom Dims and Metrics
If you have made changes to your property by adding custom dimensions and metrics and you do not see them within the BigQuery connector, click the +Refresh Properties button.
You should then see them in the editor under Custom Dimensions, Event Parameters, or User Properties.
Writing your own SQL using the BigQuery SQL Connector
Using the BigQuery SQL Connector in Analytics Canvas, you can write your own queries. Start by dragging the BigQuery SQL connector onto the main canvas.
Having already setup the account as discussed above and in this article, you can now write your SQL in the query window and land your data onto the Canvas. To use the Date Parameters in your query, follow the directions in this article.
If you run into any issues at all, please contact email@example.com.