GA4 BigQuery dashboard in Data Studio: a flat daily table, not events_*

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, 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, 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, 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:

<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.
• 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, 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;

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.

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, 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, 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, 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:

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, 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;

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 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, 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, 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 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; the export setup itself is in the GA4 to BigQuery guide.

Google BigQueryIs BigQuery a Data Warehouse or a Data Lake? A Marketing GuideOctober 6, 2026→Google BigQueryHow to Connect GA4 to BigQuery: Setup, Limits and First QueriesOctober 6, 2026→Google BigQueryHow to Connect BigQuery to Google Sheets: Three WaysOctober 6, 2026→

See all articles →