Marketing

GA4 + BigQuery: 10 Marketing Dashboards From One SQL Set, Generated by Claude

September 25, 2025

Contents

Tuesday, 8:14 AM. Slack had five unanswered messages, all variations of "can you put together a quick dashboard for X." By 8:42 I had a list: channel mix, funnel drop-off, landing-page revenue, campaign performance, device split, new vs returning, geo, top content, multi-touch paths, and engagement quality. Ten dashboards, all of which someone needed "by tomorrow morning."

Two years ago I would have said no, opened Looker Studio, and spent the rest of the week dragging fields around. Last week I said yes, and by 4:30 PM the same day I had ten working dashboards. The trick is not magic. The trick is to stop treating the dashboard as the asset and start treating the SQL as the asset. Once the SQL is right, the dashboard is just paint.

This post is the workflow I used: the ten base SQL queries, the prompt I gave Claude to turn them into Looker Studio JSON, the two iterations I needed, and the four traps I hit. If you can write basic SQL and you have GA4 exporting to BigQuery, you can ship this in a day. The rest of this article is the receipts.

Prerequisites (15 minutes, not 15 hours)

You need three things, all of which you probably already have:

A GA4 property with BigQuery export enabled. Admin → Property → Product Links → BigQuery Links. Free for standard properties. After the first daily export finishes, you'll see events_* intraday tables and events_* daily tables in your BigQuery project.
BigQuery access. Either the GCP console, the bq CLI, or — what I actually use — the BigQuery panel inside Looker Studio. If you can run a SELECT 1 against your dataset, you're good.
A Claude Pro or API account. I used Claude Sonnet 4.5 for this; Sonnet 3.5 is fine. You will be generating JSON, not prose, so the model needs to be willing to be terse.

I am not assuming you know the GA4 schema cold. The two things you have to internalize are: GA4 is event-based, not session-based, and every user-level dimension lives inside a nested record that you have to UNNEST to read. The most common beginner mistake is writing WHERE source = 'google' and getting zero rows back, because source is inside traffic_source which is a STRUCT, not a column. Once you UNNEST it, the queries below will work as written. Replace your-project.analytics_123456789 with your actual project and property ID.

The 10 base SQL queries

These are the queries I actually ran. I have not prettied them up. Each one is paired with the dashboard it powers and the question it answers. I keep them in a single dashboards/ folder in the project; the names map 1:1 to the dashboard names.

1. Channel mix (dashboard: "Where did the traffic come from?")

sqlSELECT
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS sessions,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1, 2
ORDER BY sessions DESC

Answer: "What fraction of last month's traffic and revenue came from organic, paid, email, and direct?" This is the dashboard your CMO opens first.

2. Funnel drop-off (dashboard: "Where are we losing them?")

sqlWITH steps AS (
  SELECT user_pseudo_id,
    MAX(IF(event_name = 'page_view', 1, 0)) AS s1,
    MAX(IF(event_name = 'view_item', 1, 0)) AS s2,
    MAX(IF(event_name = 'add_to_cart', 1, 0)) AS s3,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS s4,
    MAX(IF(event_name = 'purchase', 1, 0)) AS s5
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  GROUP BY 1
)
SELECT 'Visit' AS step, SUM(s1) AS users UNION ALL
SELECT 'View item', SUM(s2) UNION ALL
SELECT 'Add to cart', SUM(s3) UNION ALL
SELECT 'Begin checkout', SUM(s4) UNION ALL
SELECT 'Purchase', SUM(s5)

Answer: "Between which two steps do we lose the most users?" This is the dashboard your growth lead opens first. A bar chart with absolute counts beats a Sankey diagram for marketing; people read the gap.

3. Landing page revenue (dashboard: "Which landing pages actually print money?")

sqlSELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', 1, 0)) AS purchases,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND event_name IN ('page_view', 'purchase')
GROUP BY 1
HAVING users > 100
ORDER BY revenue DESC
LIMIT 50

Answer: "If I had to cut half the landing pages, which ones would I keep?" Filter users > 100 is deliberate — pages with three visitors and a $1,200 order are noise.

4. Campaign performance (dashboard: "Which UTMs are actually pulling?")

sqlSELECT
  traffic_source.name AS campaign,
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND traffic_source.name != '(not set)'
GROUP BY 1, 2, 3
ORDER BY revenue DESC

Answer: "For every campaign name, how much did it bring in?" This is the one you send to the paid team on a Monday morning. The (not set) filter cuts Google's noise.

5. Device split (dashboard: "Where is the mobile gap?")

sqlSELECT
  device.category,
  device.operating_system,
  device.web_info.browser,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1, 2, 3

Answer: "What is our conversion rate on mobile Safari versus desktop Chrome, and which one is dragging down AOV (Average Order Value, 客单价)?" Look at the revenue column before you blame the design team.

6. New vs returning (dashboard: "Is retention a real number?")

sqlWITH first_seen AS (
  SELECT user_pseudo_id, MIN(PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)) AS first_day
  FROM `your-project.analytics_123456789.events_*`
  GROUP BY 1
)
SELECT
  CASE WHEN f.first_day >= DATE_SUB('2025-09-25', INTERVAL 30 DAY) THEN 'New' ELSE 'Returning' END AS user_type,
  COUNT(DISTINCT e.user_pseudo_id) AS users,
  SUM(IF(e.event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*` e
JOIN first_seen f USING (user_pseudo_id)
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1

Answer: "Of the users who bought last month, what fraction were new and what fraction came back?" Returning users are where the LTV (Lifetime Value, 用户生命周期价值) hides.

7. Geographic (dashboard: "Where in the world is the money?")

sqlSELECT
  geo.country,
  geo.city,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1, 2
HAVING users > 50
ORDER BY revenue DESC
LIMIT 200

Answer: "Which cities are we profitable in, and which ones are we burning paid budget to reach?" A map chart here is fine, but a sortable table is more useful — people actually scroll a table, they don't pan a map.

8. Top content (dashboard: "Which pages earn the most engagement?")

sqlSELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  COUNT(DISTINCT user_pseudo_id) AS readers,
  AVG(IF(event_name = 'page_view', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec'), 0)) AS avg_engagement_ms
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND event_name = 'page_view'
GROUP BY 1
HAVING readers > 200
ORDER BY avg_engagement_ms DESC
LIMIT 50

Answer: "Which pages hold attention and which ones get bounced off in eight seconds?" This is the dashboard you send to the content team.

9. Multi-touch path (dashboard: "What was the journey?")

sqlWITH ordered AS (
  SELECT
    user_pseudo_id,
    event_name,
    traffic_source.source,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS step
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
    AND event_name IN ('purchase', 'view_item', 'add_to_cart')
)
SELECT
  STRING_AGG(CONCAT(step, ':', source), ' → ' ORDER BY step) AS path,
  COUNT(*) AS conversions
FROM ordered
GROUP BY user_pseudo_id
HAVING COUNT(*) >= 2

Answer: "In the journey that ends in a purchase, which sources appear at which step?" This is the most expensive query in the bunch — BigQuery will scan a lot of bytes — so I schedule it weekly, not on every dashboard load.

10. Engagement quality (dashboard: "Are the sessions even real?")

sqlSELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS day,
  COUNT(DISTINCT user_pseudo_id) AS daily_users,
  COUNT(DISTINCT IF(
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') > 10000,
    user_pseudo_id, NULL)) AS engaged_users
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND event_name = 'session_start'
GROUP BY 1
ORDER BY 1

Answer: "Of the people who showed up today, what fraction actually spent more than 10 seconds on a page?" An engaged-session ratio above 50% is healthy. Below 30% means either bot traffic, instant bounces, or a content problem.

The Claude prompt that turns SQL into dashboards

This is the part that took 30 minutes to dial in. My first attempt gave me syntactically valid JSON that Looker Studio silently refused. The second attempt produced 70 fields per dashboard, most of them junk. The version below is what actually worked.

You are a Looker Studio JSON generator. For each of the 10 SQL queries I will give you, output exactly one JSON object with this shape:

{
  "name": "",
  "title": "",
  "dataSource": {
    "type": "bigquery",
    "projectId": "your-project",
    "datasetId": "analytics_123456789",
    "query": ""
  },
  "fields": [
    {"name": "", "type": "dimension|metric", "dataType": "string|number", "aggregation": null|"SUM|COUNT_DISTINCT|AVG"}
  ],
  "charts": [
    {"id": "chart_1", "type": "table|bar|pie|geo|timeseries", "title": "...", "dimensions": ["..."], "metrics": ["..."]}
  ]
}

Rules:
- Keep the SQL exactly as I gave it, including _TABLE_SUFFIX date filters.
- Every column returned by the SQL must appear in `fields` with the correct dataType.
- Use SUM for revenue, COUNT_DISTINCT for users/sessions, AVG for engagement_time_ms.
- For each dashboard, suggest 2-4 chart types that fit the SQL shape (table + one visualization minimum).
- Output one JSON object per query, in the same order I gave them.
- Do not invent fields the SQL does not return. If a metric is ambiguous, mark it "needs_review".

Here are the 10 SQL queries:

Two things to know. First, paste the SQL one at a time, not all ten in one prompt, if you want clean output. I tried both. Ten at once gave me 10 dashboards, but the JSON had drift — Claude would quietly rename a field in dashboard 7, and Looker Studio's chart 3 would render a blank. One at a time, I got 10 dashboards that all used consistent field names, which matters when you later want to share filters across reports.

Second, the dataSource.query field is the load-bearing piece. Looker Studio accepts a custom BigQuery query inside the report definition; that means your dashboard is not bound to a saved view or a scheduled-query table. The trade-off is load time — every dashboard refresh re-runs the SQL, and the multi-touch path query (number 9) will hit BigQuery's bytes-scanned billing. For nine of the ten this is fine. For number 9, save the result to a daily table and point the dashboard there instead.

The four traps I hit

Trap 1: Streaming export has no traffic_source. The first version of the channel-mix query returned zero rows. The reason: GA4's intraday export (events_intraday_*) does not populate traffic_source.source/medium/name. I switched to the daily export and the rows appeared. The data is delayed ~24 hours, which is fine for a daily-refreshed dashboard.

Trap 2: event_params is a repeated STRUCT, not a flat row. The pattern (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') looks ugly but it is the canonical way to extract a single event parameter. You can also UNNEST(event_params) once at the top of the query and then reference key and value.string_value directly, but the subquery form keeps the row count at 1 per event, which matters for revenue totals.

Trap 3: engagement_time_msec is the only reliable engagement signal. I tried building an engagement dashboard off user_engagement events first. The numbers were off by a factor of three. The reason: user_engagement fires only on engaged sessions, and GA4's "engaged" definition is more conservative than the raw 10-second threshold most marketers expect. engagement_time_msec inside page_view events is the truth.

Trap 4: Looker Studio's custom-query data sources are read-only and un-shareable as views. You cannot publish a custom-query data source to a shared Looker Studio library the way you can a normal table connection. If you want teammates to reuse the data source, you have to schedule the query in BigQuery to write to a destination table, then point Looker Studio at that table. For an internal team, the custom-query path is faster. For a client deliverable, schedule the queries first.

The day's timeline, for the curious

8:14 AM — Slack messages. Started a dashboards/ folder and a dashboards.sql file with the 10 SQL queries above.

9:30 AM — All 10 SQL queries validated against the sample public dataset bigquery-public-data.ga4_obfuscated_sample_ecommerce. The shape was right; the column names matched what I expected.

10:15 AM — First Claude attempt. Valid JSON, but Claude invented a conversion_rate field on dashboard 2 that the SQL did not return. Caught it on the first render.

11:00 AM — Second Claude attempt, one query at a time. Clean output.

12:30 PM — Lunch. The 10 dashboards were 70% built. The funnel and channel-mix ones were already usable.

2:00 PM — Scheduled the expensive multi-touch query (#9) to a daily table. Connected dashboard 9 to the table instead of the live query.

3:00 PM — Realized I'd missed a 11th ask buried in a thread: "and can we have one for content decay?" Wrote an 11th SQL on the fly, fed it to Claude, had a dashboard in 20 minutes.

4:30 PM — Shared the Looker Studio link in #marketing-reports. Got three "wow" emojis, one "can you add a filter for region", and a DM from finance asking if I could do a CAC version for them next week.

The asset is the SQL, not the dashboard

Here is the reframe I want to leave you with. Most marketing dashboards die because someone hard-codes a calculation into a chart, six months later nobody can remember which filter is the right one, and the whole report gets rebuilt from scratch. The SQL version doesn't have that problem. The SQL is plain text. The SQL can be diffed in git. The SQL can be run by anyone with BigQuery access. The SQL can be turned into another dashboard — Looker, Tableau, Power BI, a Notion chart, a Slack message — by anyone, with or without Claude.

So the workflow is really: write the SQL once, ship the dashboard with Claude as the renderer, treat the dashboard as a derivative work, and the SQL is the asset that compounds. A year from now you will still be able to answer a new question in five minutes by tweaking query 3 and dropping it back into Claude. A year from now you will not remember which Looker Studio field was which.

That is the point of the day. The 4:30 PM emoji reaction is just the receipt.

Twitter LinkedIn Facebook Reddit Email

Cohort Retention Without a Data Team: Claude + Klaviyo/Shopify SQL You Can Actually Read Funnel Drop-Off Diagnosis: Find the Leak in 7 Steps (Claude + GA4) Feed GSC + GA4 Exports Into Claude: A 4-Step Content Analysis Workflow (and the 3 Questions It Answers) Looker Studio + Claude: a Prompt Library for Marketing Dashboards (12 Chart Types and the Questions to Ask)