---
title: "GA4 BigQuery dashboard in Data Studio, step by step"
canonical: "https://www.owox.com/blog/articles/ga4-bigquery-export-building-looker-studio-dashboard"
updated: "2026-10-16"
---

# GA4 BigQuery dashboard in Data Studio: a flat daily table, not events\_\*

Connecting Data Studio straight to the GA4 export tables makes every chart query nested, event-level data. This guide builds a flat daily table of users, sessions, purchases and revenue by date, source and medium with SQL tested on Google's GA4 sample, then connects it to Data Studio: table or custom query, the @DS\_START\_DATE and @DS\_END\_DATE parameters, data freshness, credentials, and the one metric that goes wrong when Data Studio sums it.

[Google BigQuery](/blog/topics/bigquery) · Updated October 16, 2026 · [Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · 11 min read

![GA4 BigQuery dashboard in Data Studio: a flat daily table, not events\_\*](https://cdn.owox.com/www/content-marketing-article/ga4-bigquery-export-building-looker-studio-dashboard/thumbnail-20261016165338.png/public)

A GA4 dashboard on BigQuery data has two parts: a small, flat table that holds the numbers the charts need, and a Data Studio (formerly Looker Studio) data source on top of that table. Pointing Data Studio straight at the raw `events_*` export works, but every chart then reads nested event-level data, and that is slower, costs more and is easy to get wrong.

| Step | What you do | Why |
| --- | --- | --- |
| 1\. Export | Link GA4 to BigQuery (Admin → Product links → BigQuery links) | Gives you `events_YYYYMMDD` tables, one per day |
| 2\. Flatten | One SQL query: users, sessions, purchases, revenue by date, source, medium | Charts read a few hundred rows, not millions of events |
| 3\. Refresh | Rebuild the table daily, after the export lands | The daily export is not ready at midnight |
| 4\. Connect | Data Studio → BigQuery → the table, or a custom query with date parameters | Date controls filter the query, so it reads only the days shown |
| 5\. Check | Compare totals against a direct count | Summed daily users are not unique users |

**_Note:_** _Originally published in February 2025. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026, on Google’s public GA4 sample._

## Why not connect Data Studio straight to events\_\*

The GA4 export writes one row per event. Event parameters sit in a repeated `event_params` record, so even “sessions” needs a subquery that pulls `ga_session_id` out of an array. That is work for SQL, not for a chart field, and each chart sends its own query to BigQuery.

Three things follow:

*   **Every chart refresh scans event data.** On demand, BigQuery bills “the number of bytes processed by each query”; the first 1 TiB a month is free, then $6.25 per TiB, with a minimum of 10 MB per table referenced and per query ([BigQuery pricing](https://cloud.google.com/bigquery/pricing), read in October 2026). A dashboard with ten charts and twenty viewers multiplies that.
*   **Data Studio reads a window you did not choose.** For date-sharded tables such as `events_YYYYMMDD`, Google’s connector page says Data Studio groups the shards under one name and charts query only the last 28 days by default ([Connect to BigQuery](https://docs.cloud.google.com/looker/docs/studio/connect-to-google-bigquery), read in October 2026).
*   **The sample is small; your property is not.** A dry run of `SELECT *` over all 92 days of Google’s sample reports 3,583,657,854 bytes (about 3.3 GiB) for 4,295,584 events. A busy site writes more than that in a week.

Data Studio also has a direct Google Analytics connector. It is fine for standard reports, but it is “subject to Google Analytics Data API (GA4) quotas”, and Google’s own page suggests using BigQuery as an intermediary when you need to fix numbers before they reach the chart ([Connect to Google Analytics](https://docs.cloud.google.com/looker/docs/studio/connect-to-google-analytics), read in October 2026). If you already have the export, build the dashboard on it.

## The flat daily table

The query below turns events into one row per date, source and medium. It runs on `bigquery-public-data.ga4_obfuscated_sample_ecommerce`, Google’s public GA4 sample (1 November 2020 to 31 January 2021), so you can paste it into the BigQuery console as is:

```
WITH sessions AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    traffic_source.source AS source,
    traffic_source.medium AS medium,
    COUNTIF(event_name = 'purchase') AS purchases,
    SUM(IF(event_name = 'purchase', ecommerce.purchase_revenue_in_usd, 0)) AS revenue
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  GROUP BY date, user_pseudo_id, session_id, source, medium
)
SELECT
  date, source, medium,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(*) AS sessions,
  SUM(purchases) AS purchases,
  ROUND(SUM(revenue), 2) AS revenue_usd
FROM sessions
GROUP BY date, source, medium
ORDER BY date, sessions DESC;
```

It processed 61,195,996 bytes (about 58 MiB) and returned 62 rows: seven days, with eight or nine source and medium pairs each. The first day:

| date | source | medium | users | sessions | purchases | revenue\_usd |
| --- | --- | --- | --- | --- | --- | --- |
| 2021-01-01 | google | organic | 741 | 751 | 3 | 203.0 |
| 2021-01-01 | (direct) | (none) | 564 | 571 | 5 | 510.0 |
| 2021-01-01 | <Other> | <Other> | 380 | 380 | 0 | 0.0 |
| 2021-01-01 | <Other> | referral | 212 | 213 | 1 | 19.0 |
| 2021-01-01 | shop.googlemerchandisestore.com | referral | 138 | 138 | 0 | 0.0 |
| 2021-01-01 | google | cpc | 104 | 104 | 1 | 137.0 |
| 2021-01-01 | (data deleted) | (data deleted) | 85 | 88 | 4 | 106.0 |
| 2021-01-01 | <Other> | organic | 63 | 64 | 0 | 0.0 |
| 2021-01-01 | <Other> | (data deleted) | 2 | 2 | 0 | 0.0 |

`<Other>` and `(data deleted)` are the sample’s obfuscation; your own export shows real values. What each piece does:

*   **The inner query is one row per session.** A session is `user_pseudo_id` plus `ga_session_id`; the subquery reads that one key out of `event_params`. How the parameter array works is in [unnesting GA4 event parameters](/blog/articles/unnest-ga4-event-parameters-in-bigquery).
*   **Purchases and revenue are counted per session first,** then summed, so a session with two purchases is still one session.
*   **The outer query is the dashboard grain.** Add `device.category` or `geo.country` to both `GROUP BY` lists if your charts need them; every extra dimension multiplies the rows.

### Trap: traffic\_source is the user’s first source

In the export, `traffic_source` “contains information about the traffic source that first acquired the user” ([GA4 BigQuery Export schema](https://support.google.com/analytics/answer/7029846), read in October 2026). It does not change when the same user returns from a campaign. Google’s sample is from 2021 and has only this record, so the tested query uses it. In a current export, use `session_traffic_source_last_click.cross_channel_campaign.source` and `.medium` for session-level last-click source, which is what most marketers expect a “sessions by source” chart to show. The rest of the query stays the same.

### Trap: filter on \_TABLE\_SUFFIX, not on the date

Both queries below read the same columns for the same seven days. The first filters on the parsed date; the second on the table suffix. Dry runs:

```
WITH v AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) AS date, user_pseudo_id, traffic_source.source AS source
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
)
SELECT date, COUNT(DISTINCT user_pseudo_id) FROM v
WHERE date BETWEEN '2021-01-01' AND '2021-01-07'
GROUP BY date;
```

```
WITH v AS (
  SELECT _TABLE_SUFFIX AS day, PARSE_DATE('%Y%m%d', event_date) AS date, user_pseudo_id, traffic_source.source AS source
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
)
SELECT date, COUNT(DISTINCT user_pseudo_id) FROM v
WHERE day BETWEEN '20210101' AND '20210107'
GROUP BY date;
```

| Filter | Bytes processed |
| --- | --- |
| `date BETWEEN ...` | 131,761,313 |
| `_TABLE_SUFFIX BETWEEN ...` | 7,518,449 |

The date filter scans all 92 daily tables and throws most rows away; the suffix filter opens only seven. Same answer, about 17 times the bytes. A digit range on `_TABLE_SUFFIX` also leaves out `events_intraday_*` tables, because `'intraday_...'` sorts after any date string. More on the shards and dates in [the GA4 events table schema](/blog/articles/ga4-bigquery-export-event-table-schema-and-dates).

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## Keep the table fresh

Save the query as a table partitioned by `date` and rebuild it every day. This is the statement, validated by dry run (not run), with your project and dataset in place of `your_project.reporting`:

```
CREATE OR REPLACE TABLE `your_project.reporting.ga4_daily_traffic`
PARTITION BY date AS
WITH sessions AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    traffic_source.source AS source,
    traffic_source.medium AS medium,
    COUNTIF(event_name = 'purchase') AS purchases,
    SUM(IF(event_name = 'purchase', ecommerce.purchase_revenue_in_usd, 0)) AS revenue
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  GROUP BY date, user_pseudo_id, session_id, source, medium
)
SELECT
  date, source, medium,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(*) AS sessions,
  SUM(purchases) AS purchases,
  ROUND(SUM(revenue), 2) AS revenue_usd
FROM sessions
GROUP BY date, source, medium;
```

The dry run processed the same 61,195,996 bytes as the `SELECT` and returned the same seven columns. On your own export, replace the source table with `your_project.analytics_<property_id>.events_*` and widen the `_TABLE_SUFFIX` range to the period the dashboard covers, then run it as a BigQuery scheduled query.

When to schedule it matters. Google’s export page says the daily export “takes place once a day” and typically runs mid-afternoon in the property’s time zone, sometimes later that day or the next ([BigQuery Export](https://support.google.com/analytics/answer/9358801), read in October 2026). Schedule the rebuild after that, and expect yesterday to be missing on some mornings. Google also advises querying `events_YYYYMMDD` rather than `events_intraday_YYYYMMDD` for a stable day. Standard properties have “a daily BigQuery Export limit of 1 million events”; if you are near it, check the totals before trusting the chart.

## Connect Data Studio to BigQuery

In Data Studio, add data, choose the **BigQuery** connector, and pick one of two routes ([Connect to BigQuery](https://docs.cloud.google.com/looker/docs/studio/connect-to-google-bigquery), read in October 2026):

**The table.** Choose the project, the dataset and `ga4_daily_traffic`. Data Studio supports date-partitioned tables and lets you use the partition column as the main date filter, so a date control reads only the partitions in range. This is the cheapest and simplest route.

**A custom query.** Choose **Custom query**, write Standard SQL, and enable the date range parameters in the connection panel’s **Parameters** section. Data Studio then fills `@DS_START_DATE` and `@DS_END_DATE` from the report’s date control. It “passes all parameter values as text strings” in `YYYYMMDD` form ([Use parameters in a custom query](https://docs.cloud.google.com/looker/docs/studio/use-parameters-in-a-custom-query), read in October 2026), which is exactly the format of `_TABLE_SUFFIX`. So the flat query works on `events_*` directly, with one change:

```
WHERE _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
```

This is the tested query above with the two date strings replaced by the parameters; Data Studio supplies the values, so it does not run in the console as is. Use this route when the dashboard must count unique users over any date range the viewer picks (see the trap below). Each new date range is a new query on event data, so it costs more than the table.

A third parameter, `@DS_USER_EMAIL`, passes the viewer’s email for row-level filtering; viewers must consent to share it, or charts show an authorization error.

### Credentials and billing

Google lists two roles: **BigQuery Data Viewer** to read the tables and **BigQuery Job User** to run queries in the billing project. Who needs them depends on the data source’s credentials:

| Credentials | Who needs the roles | Effect |
| --- | --- | --- |
| Owner’s (default) | only the data source owner | viewers see data without BigQuery access; all queries run as the owner |
| Viewer’s | every viewer | each person sees only what their own access allows |
| Service account | the service account | the report does not depend on one person’s account |

The **Billing Project** setting decides which project pays for custom query execution. With owner’s credentials, a widely shared report runs every viewer’s queries on the owner’s bill.

### Data freshness

Data Studio caches query results. For BigQuery the default freshness is 12 hours, and it can be set from every 1 minute to every 12 hours ([Manage data freshness](https://docs.cloud.google.com/looker/docs/studio/manage-data-freshness), read in October 2026). A cached chart does not query BigQuery; an expired cache or a new filter does. BigQuery costs apply to “manual and automatic data refreshes”. With a table rebuilt once a day, a freshness shorter than a few hours buys nothing.

## Trap: Data Studio sums daily users

A scorecard on the flat table with `users` set to Sum adds each day’s unique users together. A person who visited on Monday and Thursday is counted twice. Checked on the same week:

```
WITH ev AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) AS date, user_pseudo_id,
    traffic_source.source AS source, traffic_source.medium AS medium,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
), daily AS (
  SELECT date, source, medium,
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) AS sessions
  FROM ev GROUP BY date, source, medium
)
SELECT
  (SELECT SUM(users) FROM daily) AS sum_of_daily_users,
  (SELECT COUNT(DISTINCT user_pseudo_id) FROM ev) AS unique_users_week,
  (SELECT SUM(sessions) FROM daily) AS sum_sessions,
  (SELECT COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) FROM ev) AS unique_sessions_week;
```

| sum\_of\_daily\_users | unique\_users\_week | sum\_sessions | unique\_sessions\_week |
| --- | --- | --- | --- |
| 25979 | 22847 | 26374 | 26331 |

Summed users overstate the week by 3,132, about 14%. Sessions are close: 43 sessions crossed midnight and were counted on both days. Sessions, purchases and revenue can be summed; users cannot. Either label the metric “daily users” and use it only on daily charts, or take weekly and monthly unique users from the custom query route above.

## Hold the SQL as a Data Mart

The flat query is now the definition of “sessions by source” for everyone who reads the dashboard. When it lives inside one person’s Data Studio data source, the next report copies it, and in a month two dashboards disagree.

In [OWOX Data Marts](https://www.owox.com/app-signup) an analyst saves that SQL, or the scheduled table it writes, as a Data Mart on the BigQuery project that holds the export, with a description of each column, including the warning that users are daily. GA4 data still arrives through Google’s native BigQuery export; OWOX Data Marts runs the SQL as written in your own project and does not transform the data. Once the Data Mart is published, its **Destinations** tab delivers it to Data Studio through the OWOX Data Marts connector in Data Studio’s connector gallery, and to [Google Sheets](/product/spreadsheets), Excel, email or Slack from the same definition. The Data Studio destination has its own cache lifetime, five minutes by default and up to 12 hours ([Data Studio destination](https://docs.owox.com/docs/destinations/supported-destinations/data-studio/), read in October 2026); on a cache miss it reads the Data Mart’s source again, so for a SQL Data Mart on `events_*` keep the `_TABLE_SUFFIX` window tight, or point the Data Mart at the daily table.

The result is one [dashboard](/product/dashboards) number and one sheet number from the same query. How the export becomes a set of reporting tables is in [GA4 export and data modeling](/blog/articles/ga4-export-bigquery-data-modeling); the export setup itself is in the [GA4 to BigQuery guide](/blog/articles/google-analytics-4-to-bigquery-guide).

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

Can I combine GA4 export data with other sources in one Data Studio dashboard?

Yes. The simplest way is to join the GA4 export with other tables, such as ad spend, in BigQuery and give Data Studio one flat table by date, source and medium. Blending several data sources inside Data Studio also works, but the join logic then lives in one report instead of in SQL everyone can reuse.

How do I add a dimension such as device or country to a GA4 dashboard built on BigQuery?

Add the column, for example device.category or geo.country, to both GROUP BY lists of the flat daily query and rebuild the table. Every extra dimension multiplies the number of rows, so add only what the charts use.

Should Data Studio read a BigQuery table or a custom query for GA4 data?

A table that you rebuild daily is cheaper and simpler: Data Studio can use its date partition as the date filter. A custom query with @DS\_START\_DATE and @DS\_END\_DATE runs on the event data for each date range, which costs more but can count unique users over any period the viewer picks.

How do I keep BigQuery costs down for a Data Studio dashboard on GA4 data?

Point charts at a small flat table instead of events\_\*, filter the export on \_TABLE\_SUFFIX rather than on a parsed date, and keep Data Studio's data freshness at a few hours. In a test on Google's GA4 sample, filtering seven days on \_TABLE\_SUFFIX read 7.5 MB, while the same filter on the parsed date read 131.8 MB.

How do I connect BigQuery to Data Studio?

In Data Studio, add data, choose the BigQuery connector, then pick a project, dataset and table, or choose Custom query and paste Standard SQL. The data source owner needs the BigQuery Data Viewer and BigQuery Job User roles; with viewer's credentials every viewer needs them.

What is the GA4 BigQuery Export and when does the daily table arrive?

It is Google's native export of raw GA4 events to BigQuery, set up in GA4 under Admin, Product links, BigQuery links, with one events\_YYYYMMDD table per day. Google says the daily export typically runs mid-afternoon in the property's time zone and can be later, so schedule dashboard refreshes after it.

## Who wrote this

![Ievgen Krasovytskyi](https://cdn.owox.com/www/webflow/68404586b341508a789a4aa5_.png/public)

[Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.

[LinkedIn](https://www.linkedin.com/in/ievgen-krasovytskyi-a38a1253/) · [All articles](/team/ievgen-krasovytskyi)

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · Is BigQuery a Data Warehouse or a Data Lake? A Marketing Guide · October 6, 2026](/blog/articles/why-gbq-perfect-data-lake)

[Google BigQuery · How to Connect GA4 to BigQuery: Setup, Limits and First Queries · October 6, 2026](/blog/articles/google-analytics-4-to-bigquery-guide)

[Google BigQuery · How to Connect BigQuery to Google Sheets: Three Ways · October 6, 2026](/blog/articles/bigquery-reports-in-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Ievgen Krasovytskyi](https://www.owox.com/team/ievgen-krasovytskyi) — /team/ievgen-krasovytskyi.md
- [unnesting GA4 event parameters](https://www.owox.com/blog/articles/unnest-ga4-event-parameters-in-bigquery) — /blog/articles/unnest-ga4-event-parameters-in-bigquery.md
- [the GA4 events table schema](https://www.owox.com/blog/articles/ga4-bigquery-export-event-table-schema-and-dates) — /blog/articles/ga4-bigquery-export-event-table-schema-and-dates.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [Google Sheets](https://www.owox.com/product/spreadsheets) — /product/spreadsheets.md
- [Data Studio destination](https://docs.owox.com/docs/destinations/supported-destinations/data-studio)
- [dashboard](https://www.owox.com/product/dashboards) — /product/dashboards.md
- [GA4 export and data modeling](https://www.owox.com/blog/articles/ga4-export-bigquery-data-modeling) — /blog/articles/ga4-export-bigquery-data-modeling.md
- [GA4 to BigQuery guide](https://www.owox.com/blog/articles/google-analytics-4-to-bigquery-guide) — /blog/articles/google-analytics-4-to-bigquery-guide.md
- [Google BigQuery · Is BigQuery a Data Warehouse or a Data Lake? A Marketing Guide · October 6, 2026](https://www.owox.com/blog/articles/why-gbq-perfect-data-lake) — /blog/articles/why-gbq-perfect-data-lake.md
- [Google BigQuery · How to Connect BigQuery to Google Sheets: Three Ways · October 6, 2026](https://www.owox.com/blog/articles/bigquery-reports-in-google-sheets) — /blog/articles/bigquery-reports-in-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md