BigQuery Scheduled Queries for Automated Analytics Reporting

What Scheduled Queries Enable

BigQuery Scheduled Queries run your SQL on a defined schedule (daily, hourly, weekly) and write the results to a BigQuery table. This pattern enables automated analytics pipelines: data lands in BigQuery each morning via GA4 export, a scheduled query processes yesterday's data into summary tables at 8am, and Looker Studio dashboards show fresh data without anyone running queries manually.

Setting Up a Scheduled Query

1. BigQuery Console → write your query in the query editor
2. Click "Schedule" → "Create new scheduled query"
3. Configure:
   • Schedule name: "Daily channel summary"
   • Repeat frequency: Daily at 08:00 UTC
   • Destination table: select dataset and table name
   • Write preference: WRITE_APPEND (add new rows) or WRITE_TRUNCATE (replace table)
4. Save

Example: Daily Traffic Summary

-- This query runs daily, appending yesterday's data:
SELECT
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) as date_partition,
  traffic_source.source,
  traffic_source.medium,
  device.category,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='session_id') AS STRING)
  )) as sessions,
  COUNT(DISTINCT IF(event_name='purchase',
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='transaction_id'), NULL)
  ) as purchases,
  SUM(IF(event_name='purchase',
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key='value'), 0)
  ) as revenue
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date_partition, traffic_source.source, traffic_source.medium, device.category

Managing Scheduled Queries

• View all scheduled queries: BigQuery → Scheduled Queries (left sidebar)
• Check run history: click a query → Run History tab → see success/failure of each run
• Set up failure alerts: configure notifications to email if a scheduled query fails

Summary

BigQuery Scheduled Queries automate analytics pipelines: they run SQL on a schedule and write results to destination tables, eliminating manual query execution. Use WRITE_APPEND for daily incremental data (appends yesterday's rows each morning). The pattern: raw events in GA4 export → scheduled query → summary table → Looker Studio dashboard shows fresh data automatically. Monitor run history for failures and set up email notifications to catch pipeline issues promptly.

See our BigQuery Setup service for automated pipeline development.

Need automated BigQuery analytics pipelines? Contact Adslytics.