Building a dashboard is easy. Keeping a reliable, up-to-date dashboard without touching it every morning is another challenge. Here’s how to build an end-to-end automated reporting pipeline.
The target architecture
The full flow looks like this:
GA4 → BigQuery export (daily) → Materialized tables (scheduled queries) → Looker Studio
Every link is automated. You set it up once, and the dashboard updates itself every day.
Prepare the data in BigQuery
Rather than connecting Looker Studio straight to the raw GA4 tables (slow and expensive), create optimized intermediate tables:
-- Scheduled query: runs every day at 8am
CREATE OR REPLACE TABLE `project.dataset.daily_metrics` AS
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp)) AS date,
traffic_source.source,
traffic_source.medium,
COUNT(DISTINCT user_pseudo_id) AS users,
COUNTIF(event_name = 'page_view') AS pageviews,
COUNTIF(event_name = 'purchase') AS transactions,
SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) AS revenue
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY 1, 2, 3
Schedule this query through BigQuery Scheduled Queries so it runs automatically.
Connect Looker Studio
In Looker Studio, add a BigQuery data source pointing to your daily_metrics table. The benefits:
- Fast queries: the table is already aggregated
- Controlled costs: no full scan on every refresh
- Fresh data: refreshed automatically each morning
Dashboard best practices
A few rules for a dashboard that lasts:
- One dashboard = one goal. Don’t mix acquisition and conversion in the same view
- Use page-level filters (date, source, country) to make the dashboard interactive
- Add a freshness indicator: a field that shows the latest available date
- Document your metrics in a dedicated tab
To go further on the source data, see the guides on the GA4 BigQuery export and GTM Server-Side.