Measure and debug performance with Google Analytics 4 and BigQuery

Why standard field tools aren’t enough

Search Console, PageSpeed Insights, and the Chrome User Experience Report (CrUX) give you a useful high-level picture of real-user performance with zero setup. But they share structural limitations that make them insufficient for ongoing performance work:

• They report on monthly or 28-day windows, so after you ship a change you wait a long time to see whether it moved the numbers.
• You can only segment by a few fixed dimensions—country, connection type, device category—never by business-specific slices like engagement cohorts or experiment groups.
• They answer what your performance is, not why it is that way. You get no data to help you track down the cause of a regression.

The practical answer is to monitor Core Web Vitals inside the analytics tool you already use. Google Analytics 4 (GA4) makes that viable with two features in particular: zero-configuration custom event parameters and free BigQuery export so you can query your raw event data in SQL.

Setting this up takes three steps:

1. Create a GA4 property and a BigQuery project.
2. Enable BigQuery export in the GA4 property config so incoming data lands automatically in BigQuery tables.
3. Add the web-vitals JavaScript library to your site, configured to send metric values (including attribution data) to GA4.

Querying Web Vitals data in BigQuery

Once events are flowing, you can inspect the data with a standard BigQuery query against the exported event tables.

One aggregation detail matters before you run any analysis: a metric can be reported more than once for the same page. Values that change after the initial report—common with CLS—result in additional events with updated numbers. The last value sent for a metric is always the most accurate one, so your queries need to filter for the final event per metric ID.

The web-vitals library's GA4 integration includes a unique ID with each metric event precisely for this. A subquery that keeps only the last-received value per metric ID is the foundation for every analysis query that follows.

Common performance queries

With the deduplication subquery in place, a handful of SQL patterns covers most day-to-day performance questions:

• Site-wide LCP, INP, and CLS at the 75th percentile (p75): the standard percentile for Core Web Vitals reporting, computed across all events.
• All individual LCP values, highest to lowest: useful for spotting outliers and understanding the full distribution rather than just the tail.
• p75 LCP for the ten most popular pages: combines performance with traffic so you prioritize fixes where they matter most.
• Top ten pages by worst CLS (p75): surfaces the pages where layout stability is most broken.

These queries tell you where performance stands and how it trends. When scores are worse than they should be, you need to know what is causing the problem—not just how bad it is.

Debugging with field data

Adding per-metric debug information to your analytics events lets you investigate root causes directly in BigQuery. The key field is debug_target: a CSS selector string identifying the element most relevant to the metric value on the page.

• For CLS, debug_target is the largest element from the largest layout shift that contributed to the CLS value. If no elements shifted, it is null.
• The same technique applies to LCP and INP—you swap the metric in the query's WHERE clause and get the corresponding target elements.

Querying CLS grouped by debug_target and ordered by p75 lists pages and their shifting elements from worst to best. The result makes the offending UI components immediately visible.

A caution worth remembering: the element reported in the field may not be the same element you see shifting when you debug locally. That discrepancy is exactly why capturing this data from real users matters—you cannot fix problems you do not realize exist on devices and network conditions you never test.

Visualizing Query Results

Raw query output is rarely the fastest path to insight. For example, a query that returns daily 75th percentile LCP values produces a table of numbers that makes trends and outliers hard to spot:

# Subquery all Web Vitals events from the last 28 days
WITH web_vitals_events AS (
  SELECT event_name as metric_name, * EXCEPT(event_name, is_last_received_value) FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'metric_id') ORDER BY (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'metric_start_time') DESC
    ) = 1 AS is_last_received_value
    FROM `bigquery_project_id.analytics_XXXXX.events_*`
    WHERE event_name in ('CLS', 'INP', 'LCP') AND
      _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
  ) WHERE is_last_received_value
)
# Main query logic
SELECT
  event_date,
  metric_name,
  APPROX_QUANTILES(ROUND(metric_value, 2), 100)[OFFSET(75)] AS p75
FROM
  (
    SELECT
      event_date,
      metric_name,
      ROUND((SELECT COALESCE(value.double_value, value.int_value) FROM UNNEST(event_params) WHERE key = 'metric_value'), 3) AS metric_value
    FROM web_vitals_events
    WHERE
      metric_name = 'LCP'
  )
GROUP BY
  1, 2
ORDER BY event_date

In these cases, turning the data into charts helps you understand what is actually happening at a glance.

Quick Charts with Looker Studio

You can visualize any BigQuery result without leaving the console. After running a query in the BigQuery UI, click Explore Data and select Explore with Looker Studio. This opens a new Looker Studio exploration linked directly to your query results:

In this view, you can select fields, choose chart types, set up filters, and build ad hoc charts. For the LCP query above, a simple line chart reveals the trend over time:

This direct link works well for quick looks at individual queries. For ongoing analysis, however, you will want an interactive dashboard that combines several charts and supports drilling into the data—without manually rerunning queries each time.

To build such a dashboard, use the native BigQuery connector in Looker Studio. Navigate to datastudio.google.com, create a new data source, pick the BigQuery connector, and select the dataset you want to work with:

Materialize the Web Vitals Data

Querying the raw Google Analytics 4 export directly for a dashboard is inefficient. The GA4 event structure and the preprocessing required to compute Web Vitals metrics mean parts of your query will be repeated on every chart load, which hurts both dashboard performance and BigQuery costs.

BigQuery's sandbox mode and free usage tier cover the first 1 TB of query data per month. For the analysis described here, you will likely stay within that limit unless you have a very large dataset. But for a high-traffic site with a regularly used interactive dashboard, materialize your Web Vitals data using partitioning, clustering, and caching to keep queries fast and cheap.

The following script preprocesses the source GA4 table and writes a materialized target table. You may want to add a date range filter on the source to reduce the amount of data processed:

# Materialize Web Vitals metrics from GA4 event export data

# Replace target table name
CREATE OR REPLACE TABLE bigquery_project_id.ga4_demo_dev.web_vitals_summary
  PARTITION BY DATE(event_timestamp)
  CLUSTER BY metric_name
AS
SELECT
  ga_session_id,
  IF(
    EXISTS(SELECT 1 FROM UNNEST(events) AS e WHERE e.event_name = 'first_visit'),
    'New user',
    'Returning user') AS user_type,
  IF(
    (SELECT MAX(session_engaged) FROM UNNEST(events)) > 0, 'Engaged', 'Not engaged')
    AS session_engagement,
  evt.* EXCEPT (session_engaged, event_name),
  event_name AS metric_name,
  FORMAT_TIMESTAMP('%Y%m%d', event_timestamp) AS event_date
FROM
  (
    SELECT
      ga_session_id,
      ARRAY_AGG(custom_event) AS events
    FROM
      (
        SELECT
          ga_session_id,
          STRUCT(
            country,
            device_category,
            device_os,
            traffic_medium,
            traffic_name,
            traffic_source,
            page_path,
            debug_target,
            event_timestamp,
            event_name,
            metric_id,
            IF(event_name = 'LCP', metric_value / 1000, metric_value) AS metric_value,
            user_pseudo_id,
            session_engaged,
            session_revenue) AS custom_event
        FROM
          (
            SELECT
              (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
                AS ga_session_id,
              (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'metric_id')
                AS metric_id,
              ANY_VALUE(device.category) AS device_category,
              ANY_VALUE(device.operating_system) AS device_os,
              ANY_VALUE(traffic_source.medium) AS traffic_medium,
              ANY_VALUE(traffic_source.name) AS traffic_name,
              ANY_VALUE(traffic_source.source) AS traffic_source,
              ANY_VALUE(
                REGEXP_SUBSTR(
                  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
                  r'^[^?]+')) AS page_path,
              ANY_VALUE(
                (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'debug_target'))
                AS debug_target,
              ANY_VALUE(user_pseudo_id) AS user_pseudo_id,
              ANY_VALUE(geo.country) AS country,
              ANY_VALUE(event_name) AS event_name,
              SUM(ecommerce.purchase_revenue) AS session_revenue,
              MAX(
                (
                  SELECT
                    COALESCE(
                      value.double_value, value.int_value, CAST(value.string_value AS NUMERIC))
                  FROM UNNEST(event_params)
                  WHERE key = 'session_engaged'
                )) AS session_engaged,
              TIMESTAMP_MICROS(MAX(event_timestamp)) AS event_timestamp,
              MAX(
                (
                  SELECT COALESCE(value.double_value, value.int_value)
                  FROM UNNEST(event_params)
                  WHERE key = 'metric_value'
                )) AS metric_value,
            FROM
              # Replace source table name
              `bigquery_project_id.analytics_XXXXX.events_*`
            WHERE
              event_name IN ('LCP', 'INP', 'CLS', 'first_visit', 'purchase')
            GROUP BY
              1, 2
          )
      )
    WHERE
      ga_session_id IS NOT NULL
    GROUP BY ga_session_id
  )
CROSS JOIN UNNEST(events) AS evt
WHERE evt.event_name NOT IN ('first_visit', 'purchase');

This materialized table provides several benefits:

• A flattened structure that is simpler to query than raw GA4 events.
• Retains only Web Vitals events, dropping unrelated traffic.
• Session ID, user type (new vs. returning), and session engagement as direct columns.
• Partitioned by date and clustered by metric name, reducing data scanned per query.
• Eliminates wildcard scans, which allows BigQuery to cache results for up to 24 hours.
• Compatible with BigQuery BI Engine optimized SQL functions and operators.

You can query this materialized table directly in the BigQuery UI or use it as a data source in Looker Studio via the BigQuery connector.

Use the Web Vitals Connector

Building a dashboard from scratch takes time. To skip that step, use the packaged Web Vitals connector for Looker Studio. First, make sure your Web Vitals table is materialized with the query above. Then open the connector at goo.gle/web-vitals-connector.

After a one-time authorization, you will see the configuration screen:

Enter the materialized BigQuery table ID (the target table) and your BigQuery billing project ID. After you click connect, Looker Studio generates a templated dashboard pre-associated with your data. You can edit, modify, and share this dashboard freely. If you only need one dashboard for a dataset, you won't need to revisit the connector link again.

Using the Dashboard

The resulting dashboard has several tabs. The Summary tab shows daily trends for the Web Vitals metrics along with usage statistics like users and sessions.

In User Analysis, pick a metric to see its percentile distribution and user counts broken down by various usage and business dimensions.

Page Path Analysis helps identify problem areas. Select a metric to view an overview, along with a scatter plot of page paths: the y-axis shows the metric percentile and the x-axis the record count. This plot surfaces pages with unexpectedly poor performance. After you select pages in the scatter chart, the Page path table updates, and you can drill into a specific problem area via the Debug Target table.

The Revenue Analysis tab pairs business and performance metrics. It plots sessions where a purchase occurred, letting you compare revenue against user experience for the same session.

Going Further with the Dataset

Once you are familiar with the data, extend the dashboard for your own needs:

• Set up a scheduled BigQuery query to run daily and append new data to the materialized table, keeping the dashboard current.
• Join first-party data (e.g., CRM) by adding user_id as a column in the materialized table. Load the data into BigQuery or connect to an external federated source.
• Send your site or app version as a parameter to Google Analytics and add it as a column in the materialized table. Use the version as a chart dimension to correlate releases with performance changes.
• If you expect consistently heavy query or dashboard load, consider BigQuery BI Engine paid capacity.