BigQuery Scheduled Queries: Automate GA4 Reporting Tables Daily

GhazanfarApril 17, 20263 min read

Running the same GA4 BigQuery analysis every morning is a waste of time — and manual queries mean your Looker Studio dashboards are always one step behind. BigQuery Scheduled Queries automate your analysis pipelines, refreshing reporting tables overnight so dashboards show fresh data every morning without anyone touching a query. Here’s how to set them up correctly.

What Scheduled Queries Do

A Scheduled Query runs a BigQuery SQL statement on a defined schedule and writes results to a destination table. Your Looker Studio dashboard connects to that destination table instead of running live queries against raw GA4 data — faster loads, no sampling, full cost control.

Setting Up a Daily GA4 Summary Table

-- Daily scheduled query: process yesterday's data only
-- Set destination: project.dataset.ga4_daily_summary
-- Write preference: APPEND (preserves history)

SELECT
  DATE(_TABLE_SUFFIX) AS date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='source') AS source,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium') AS medium,
  device.category AS device,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT CONCAT(user_pseudo_id,'_',
    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS STRING)
  )) AS sessions,
  COUNTIF(event_name='purchase') AS purchases,
  SUM(CASE WHEN event_name='purchase'
    THEN (SELECT value.float_value FROM UNNEST(event_params) WHERE key='value')
    ELSE 0 END) AS revenue
FROM `project.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY 1,2,3,4;

Creating the Scheduled Query in BigQuery

In BigQuery console: write your query → click “Schedule” → “Create new scheduled query.” Key settings:

Repeats: Daily
Start time: 6:00 AM (after GA4’s ~3 AM data processing completes)
Destination table: your project/dataset/table name
Write preference: Append to table (not overwrite — to preserve history)

BigQuery Scheduled Queries: Automate GA4 Reporting Tables Daily

Backfill-Compatible Query Design

-- Use @run_date parameter for manual backfills
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE(@run_date))
-- BigQuery replaces @run_date with the scheduled run date
-- For manual backfill: select your query → Run → specify date range

Monitoring and Alerting

Set up failure notifications: Scheduled Queries → your query → Notification options → add your email. You’ll get an alert if the query fails. Also add a “data freshness” indicator in Looker Studio showing MAX(date) from your summary table — if it shows two days ago, your pipeline broke before your stakeholders notice the stale data.

Cost Management

Always use partition filters (_TABLE_SUFFIX) in scheduled queries — scanning all historical data every day is wasteful and expensive. A well-optimized daily query processing only yesterday’s data should cost less than $0.01 per run for most mid-size GA4 properties. Monitor slot usage in BigQuery → Admin → Capacity Management.

Scheduling Checklist

☐ Use _TABLE_SUFFIX partition filter (never query events_* without a date range)
☐ Set run time to 6 AM or later — after GA4’s data processing window
☐ Use APPEND write preference to preserve history
☐ Use @run_date parameter for backfill compatibility
☐ Enable failure notifications via email
☐ Add MAX(date) freshness indicator to Looker Studio