How to Automate GA4 Reports in BigQuery with Scheduled Queries

If you have your Google Analytics 4 (GA4) data in BigQuery and want to automate running your queries daily, weekly, or hourly, this tutorial will guide you through the entire process. Scheduling queries in BigQuery can save you time and ensure your reports and dashboards stay up-to-date without manual intervention.

For a detailed video walkthrough, feel free to watch the tutorial here:

Step 1: Understanding Total Users vs Active Users in GA4 Data

Before diving into scheduling queries, it’s important to clarify the difference between total users and active users in GA4 data. When querying user data:

• Total users count distinct user IDs in your dataset. This includes everyone who opened your website or app, regardless of engagement.
• Active users are filtered based on engagement events, such as the user_engagement event. This ensures you only count users who actively interacted with your site.

This distinction matters because total users might include those who simply opened your site and left immediately, whereas active users represent a more engaged audience. When writing your SQL query, you can implement this by counting distinct user_pseudo_id for total users, and filtering to only those with user engagement events for active users.

Step 2: Preparing Your BigQuery Dataset for Scheduled Queries

Since raw GA4 export tables in BigQuery are not optimized for direct reporting or visualization, it’s best practice to create a separate dataset where you store processed tables tailored for tools like Looker Studio.

In this tutorial, I created a dataset named ga4_daily_reports to hold the results of my queries. This dataset will contain tables that are easier to analyze and connect to visualization platforms.

Creating a dedicated dataset helps keep your workspace organized and makes it easier to manage scheduled queries and data refreshes.

Step 3: Writing and Testing Your Query for GA4 Data

Once your dataset is ready, write your SQL query to extract the necessary data. For example, you might want a table with daily user statistics broken down by source, medium, and other dimensions.

Make sure your query returns the expected results, including total users and active users if relevant. You can test your query by running it manually in BigQuery and reviewing the output.

After confirming the query works as intended, you’re ready to schedule it for automatic execution.

Step 4: Naming Your Scheduled Query Strategically

When setting up your scheduled query, choose a clear and consistent naming convention. I recommend including the project name, data source, and granularity in the query name to keep things organized, especially if you manage multiple queries.

For example, you might name your query ga4_mywebsite_daily_reports to indicate it processes GA4 data for your website on a daily basis.

Step 5: Setting the Frequency and Start Time for the Scheduled Query

Decide how often you want your query to run. Since GA4 data in BigQuery usually updates once per day, scheduling your query to run daily is generally sufficient.

Choose a time that makes sense for your workflow. I usually schedule mine to run at 4 AM UTC, which is a quiet time when I’m not actively working, ensuring fresh data is ready at the start of the day.

You can also choose to start the schedule immediately or set a future start time.

Step 6: Configuring the Destination Table Settings

Next, configure where the query results will be stored. Select your dataset (ga4_daily_reports in this case) and specify a table name that matches your query name or is easy to identify.

For small datasets, overwriting the table every time the query runs is fine. However, if you have a large dataset or want to keep historical data, consider appending new rows instead of overwriting.

Appending is useful if you modify your query to only process new data (e.g., yesterday’s data), which reduces costs and speeds up processing.

Step 7: Choosing Location, Credentials, and Notifications

For location, automatic selection usually works well unless your data requires a specific region.

Regarding credentials, you can use your user credentials or set up a service account. For beginners, using your own credentials is simpler and sufficient.

Enable email notifications for scheduled query failures so you can quickly respond if something goes wrong. This is a handy feature to keep your data pipeline reliable.

Step 8: Authorizing and Creating the Scheduled Query

Before the schedule can run, you need to authorize BigQuery to execute the query on your behalf. This step requires granting permissions through a Google authorization prompt.

Once authorized, create the scheduled query. You can then find it listed under “Scheduled Queries” in the BigQuery console, where you can monitor its status, run history, and configuration.

Step 9: Monitoring and Managing Your Scheduled Queries

After creation, you can:

• Check run history to see if the query executed successfully.
• View configuration details.
• Edit, disable, or delete scheduled queries as needed.
• Manually trigger a run outside the schedule.

Queries that run successfully will show a green status. If there are errors, you will see red status alerts with details to help debug.

For best practices, try to limit the query to only new data instead of rewriting entire tables daily, especially for large datasets.

Step 10: Connecting Your Scheduled Query Output to Looker Studio

Once your scheduled query outputs the processed data into a BigQuery table, you can connect this table to Looker Studio for reporting and visualization.

This setup allows your dashboards to update automatically as the scheduled query refreshes the data daily.

Wrapping Up Your BigQuery GA4 Users Sessions Tutorial

Scheduling queries in BigQuery to automate GA4 data processing is a powerful way to maintain fresh reports without manual effort. By following this step-by-step guide, you can:

• Understand the difference between total and active users in GA4 data.
• Create a dedicated dataset for processed tables.
• Write and test queries that prepare your data for visualization.
• Schedule queries with clear naming, appropriate frequency, and proper destination settings.
• Monitor scheduled queries, handle failures, and optimize query performance.
• Connect your processed data to Looker Studio to build automated dashboards.

If you want to deepen your knowledge about querying GA4 data in BigQuery, I recommend checking out these related tutorials:

• How to Query GA4 Top Pages in BigQuery by Event: A Step-by-Step Tutorial
• BigQuery GA4 Tutorial: Distinguish New and Returning Users Easily
• How to Filter GA4 Users by Device in BigQuery: Step-by-Step

Feel free to leave comments or questions if you want to share your experience or need help with scheduling queries in BigQuery. Happy querying!