This article includes full instructions to set up my (Tony McCreath) Core Web Vitals report using your users Core Web Vitals experience on your website. Set up is via Google Analytics 4 (gtag or GTM), BigQuery and Google Data Studio.

Google Data Studio - Core Web Vitals
Core Web Vitals report overview – Click on the image to open the live sample report

Table of contents

  1. They stole my thunder
  2. What is this Core Web Vitals thing all about?
  3. Adding the code to your site (for GA4 using gtag)
  4. Adding the code to your site (for GA4 using GTM)
  5. Adding GA4 definitions (optional)
  6. Create a BigQuery materialised table
  7. Schedule updates of the materialised table (optional)
  8. Create a Data Source in Data Studio
  9. Make your own copy of the Core Web Vitals report
  10. Using the reports
  11. Feedback

They stole my thunder

At Google I/O 2021 the web.dev team made a Core Web Vitals presentation explaining how to Measure and debug performance with Google Analytics 4 and BigQuery. Basically, how to set up cool Core Web Vitals reports using your own websites visitor data. This was backed up with a technical article explaining how they did it.

The web.dev team at Google I/O
This image has an empty alt attribute; its file name is Shocker-1024x585.jpg
It takes a lot to stun me

I was stunned. I’d spent the last few days working on the same thing for a presentation that I’m going to make at the DeepSEOcon conference later this year. They’d stolen my thunder 😲

I paced around trying to work out what to do next.

Hmmm

Eventually it became clear. web.dev are the rulers of Web Vitals and the code I use to track those metrics. I had to pull my stuff inline so that it worked with their solution. i.e. make it so people can use my report on the same setup. Time to get to work.

Tony Computing
Should I use my modified Commodore 64 or the BBC? Or both. Let’s async multitask this.

web.dev’s article details the technical work involved. This article provides the specific steps you can follow to set it up if you are using the standard gtag or GTM implementation for GA4.

What is this Core Web Vitals thing all about?

Tracking LCP status over time. Green needs to cross the 75th percentile line for a good result.

Core Web Vitals are part of the upcoming page experience ranking signals from Google. They measure real users experiences as they view pages of a website via the Chrome browser (CrUX). Website owners want to give these users a good experience to benefit from the upcoming ranking factor.

Tools like Lighthouse and the Chrome performance report provide lab data. i.e. the developers own experience. This is good for detailed analysis and testing but does not accurately reflect the experience from your real visitors who will have different network connections from different locations with different devices. Users also interact with the pages unlike these testing tools. Interaction is required to calculate FID and affects CLS.

Lighthouse
Lighthouse reports don’t get FID and their CLS may be low.

CrUX data gives you a real user view of the web vitals (field data) and can be accessed via tools like Page Speed Insights. However, this data is highly aggregated so you only get a high level view of your performance. In many cases you will not get page level data and will have to work from origin (domain) level data. The results are also an average over the last 28 days meaning there is a big lag from making a change to fully seeing its affect in the report. Pages need to be visted by a threshold of opted in Chrome users in the 28 days to be included in the report.

Page Speed Insights
No field data or origin data for a popular page of mine. I’m not popular enough it seems.

The Google Search Console has a Core Web Vitals report that also uses the CrUX data. Which means it suffers from the same limitations like the 28 day lag. When there is not enough data for individual URLs the Search Console reports on aggregated sets of URLs or even just the whole site. This means you often don’t see data at the page level and is why people often see sets of URLs change from good to bad at the same time. The reported URLs are often samples from these sets.

It’s also worth noting that the Core Web Vitals report is based on CrUX data and not the Google search index. It has nothing to do with the page being indexed for search. It’s not uncommon for low trafficked sites to see no URLs in this report due to a lack of CrUX data, and drastic changes in URL counts can happen. This does not mean those URLs are no longer performing in search.

Search Console Core Web Vitals indicating that all URLs are grouped under the one origin level score

Another option is for website owners to directly gather their own Core Web Vitals data for their own site visitors. The advantage is that this includes data at the page view level for all visitors that can be tracked. This granular data makes it far easier to quicky spot where issues are and how improvements are affecting your metrics.

This article details how you can set this up on your own website to generate reports in Google Data Studio that help you fix your Core Web Vitals issues. Let’s get started…

Adding the code to your site (for GA4 using gtag)

This solution assumes you have already implemented your GA4 tracking via gtag. See the next section if you use GTM. For BigCommerce stores using our Tag Rocket app, this is already done for you (and more) when you enable the GA4 tag.

web.dev have a web-vitals github repository that provides a lot of details on code you can use to track the Core Web Vitals metrics. They have also documented some extra code to help you Debug Web Vitals in the field. We will be using all that code with some additions from me.

Cumulative Layout Shift Causes Report
Digging into the details of CLS issues, down to the on page elements thanks to the debug script

You need to place the following code on all pages. It send all your Web Vitals events to your GA4 property. The code can go anywhere. I suggest just after your base GA4 code.

<script id="web-vitals-debug">
/*
 * Debug Web Vitals in the field
 * 
 * Functions to add Web Vitals debug info
 * 
 * https://web.dev/debug-web-vitals-in-the-field/
 * 
 */
function getSelector(node, maxLen = 100) {
  var sel = '';
  try {
    while (node && node.nodeType !== 9) {
      const part = node.id ? '#' + node.id : node.nodeName.toLowerCase() + (
        (node.className && node.className.length) ?
        '.' + Array.from(node.classList.values()).join('.') : '');
      if (sel.length + part.length > maxLen - 1) return sel || part;
      sel = sel ? part + '>' + sel : part;
      if (node.id) break;
      node = node.parentNode;
    }
  } catch (err) {
    // Do nothing...
  }
  return sel;
}

function getLargestLayoutShiftEntry(entries) {
  return entries.reduce((a, b) => a && a.value > b.value ? a : b);
}

function getLargestLayoutShiftSource(sources) {
  return sources.reduce((a, b) => {
    return a.node && a.previousRect.width * a.previousRect.height >
        b.previousRect.width * b.previousRect.height ? a : b;
  });
}

function wasFIDBeforeDCL(fidEntry) {
  const navEntry = performance.getEntriesByType('navigation')[0];
  return navEntry && fidEntry.startTime < navEntry.domContentLoadedEventStart;
}

function getDebugInfo(name, entries = []) {
  // In some cases there won't be any entries (e.g. if CLS is 0,
  // or for LCP after a bfcache restore), so we have to check first.
  if (entries.length) {
    if (name === 'LCP') {
      const lastEntry = entries[entries.length - 1];
      return {
        debug_target: getSelector(lastEntry.element),
        event_time: lastEntry.startTime,
      };
    } else if (name === 'FID') {
      const firstEntry = entries[0];
      return {
        debug_target: getSelector(firstEntry.target),
        debug_event: firstEntry.name,
        debug_timing: wasFIDBeforeDCL(firstEntry) ? 'pre_dcl' : 'post_dcl',
        event_time: firstEntry.startTime,
      };
    } else if (name === 'CLS') {
      const largestEntry = getLargestLayoutShiftEntry(entries);
      if (largestEntry && largestEntry.sources) {
        const largestSource = getLargestLayoutShiftSource(largestEntry.sources);
        if (largestSource) {
          return {
            debug_target: getSelector(largestSource.node),
            event_time: largestEntry.startTime,
          };
        }
      }
    }
  }
  // Return default/empty params in case there are no entries.
  return {
    debug_target: '(not set)',
  };
}
</script>
<script id="web-vitals-rating">
/*
 * Get Web Vital Rating
 * 
 */
function getRating(name, value) {
    switch (name) {
        case 'LCP': return calculateRating(value,2500,4000);
        case 'FID': return calculateRating(value,100,300);
        case 'CLS': return calculateRating(value,0.1,0.25);
        case 'FCP': return calculateRating(value,2000,4000); // Page Speed Insights is 1000 and 3000, lighthouse and web.dev does 2000 and 4000
        case 'TTFB': return calculateRating(value,500,1500); // CrUX Data Studio report says NI is 500ms to 1500ms
        default: return '(not set)';
    }
}
function calculateRating(value, good, poor) {
    if (!value && value !== 0) return '(not set)';
    if (value > poor) return 'poor';
    if (value > good) return 'ni';   
    return 'good';
}

</script>
<script id="web-vitals-ga4">
/*
 * Send Core Web Vitals to Google Analytics 4 
 * 
 * https://github.com/GoogleChrome/web-vitals#using-gtagjs-google-analytics-4
 * 
 * Modified to call getRating and getDebugInfo to add extra event data
 * 
 */
function sendToGoogleAnalytics({name, delta, value, id, entries}) {
  // Assumes the global `gtag()` function exists, see:
  // https://developers.google.com/analytics/devguides/collection/ga4
  gtag('event', name, {
    // Built-in params:
    value: delta, // Use `delta` so the value can be summed.
    // Custom params:
    metric_id: id, // Needed to aggregate events.
    metric_value: value, // Optional.
    metric_delta: delta, // Optional.

    // OPTIONAL: any additional params or debug info here.
    // See: https://web.dev/debug-web-vitals-in-the-field/
    // metric_rating: 'good' | 'ni' | 'poor',
    metric_rating: getRating(name, value), //  not used by my report
    // debug_info: '...',
    ...getDebugInfo(name, entries)
  });
}
</script>
<script id="web-vitals-cdn">
/*
 * Using the web-vitals script from a CDN
 * 
 * https://github.com/GoogleChrome/web-vitals#from-a-cdn
 * 
 * Modified to call the sendToGoogleAnalytics function on events
 * 
 */
(function() {
  var script = document.createElement('script');
  script.src = 'https://unpkg.com/web-vitals';
  script.onload = function() {
    // When loading `web-vitals` using a classic script, all the public
    // methods can be found on the `webVitals` global namespace.
    webVitals.getCLS(sendToGoogleAnalytics);
    webVitals.getFID(sendToGoogleAnalytics);
    webVitals.getLCP(sendToGoogleAnalytics);
  }
  document.head.appendChild(script);
}())
</script>

We’ve found that tracking page types can be very useful in segmenting the data and narrowing down which parts of the website are having trouble.

Core Web Vitals By Page Type Report
Looks like my search results pages and my tools page have a bit of a CLS issue. I should look deeper into them.

I also think gathering the effective connection type of a user (e.g. 4g) can be of value.

To enable these you need to alter your core gtag to send the ‘page_type’ and ‘effective_connection_type’ parameters. Changes are in green. Don’t forget to replace ‘PAGE TYPE NAME’ (in red) with your code to dynamically get the page type name. How you set page_type is down to your CMS and how you want to segment your pages.

<script>
function getEffectiveConnectionType(){
    var connection = navigator.connection || navigator.mozConnection || navigator.webkitConnection;
    
    if(connection && connection.effectiveType) return connection.effectiveType;

    return 'unknown';
}
</script>
<!-- Global site tag (gtag.js) - Google Analytics -->
<script async="async" src="https://www.googletagmanager.com/gtag/js?id=G-0BQR1PRHYJ"></script>
<script>
  window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments);}
  gtag('js', new Date());

  gtag('config', 'G-0BQR1PRHYJ', {
    page_type: 'PAGE TYPE NAME',
    effective_connection_type: getEffectiveConnectionType()
 });
</script>

Tracking page types can be useful for reporting on a lot of things. GA4 also has reports that support the ‘content_group’ parameter which I currently set to the same value.

Once you have added that you will be sending the same Web Vitals events and parameters as used by the solution web.dev provided. This includes the standard metrics parameters (metric_id, metric_value, metric_delta), the debug parameters (debug_target, debug_event, debug_timing, event_time), the rating for the metric (metric_rating) plus the optional page_type parameter.

Adding the code to your site (for GA4 using GTM)

Simo Ahava has written a great article (as usual) on using GTM to send CWV events to GA4 which uses a Custom Template he developed. Unfortunately it only does the basic metrics due to a limitation of custom template, and it uses a different set of GA4 parameters to those used by web.dev and me. This section provides an alternate GTM solution that supports the debug info and uses parameters compatible with the web.dev report and mine.

I’ll assume you are familiar with GTM and have added the GA4 Configuration tag already.

Create a Custom HTML tag for all pages that contains the following code. It’s very similar to the gtag one but it sends the Core Web Vitals data to the dataLayer and the JavaScript is downgraded to work with GTM. I’ve followed Simo’s dataLayer event naming convention so that it should make our solutions compatible.

<script id="web-vitals-debug">
/*
 * Debug Web Vitals in the field
 * 
 * Functions to add Web Vitals debug info
 * 
 * https://web.dev/debug-web-vitals-in-the-field/
 * 
 */
function getSelector(node, maxLen) {
  maxLen = maxLen || 100;
  var sel = '';
  try {
    while (node && node.nodeType !== 9) {
      var part = node.id ? '#' + node.id : node.nodeName.toLowerCase() + (
        (node.className && node.className.length) ?
        '.' + Array.from(node.classList.values()).join('.') : '');
      if (sel.length + part.length > maxLen - 1) return sel || part;
      sel = sel ? part + '>' + sel : part;
      if (node.id) break;
      node = node.parentNode;
    }
  } catch (err) {
    // Do nothing...
  }
  return sel;
}

function getLargestLayoutShiftEntry(entries) {
  return entries.reduce(function(a, b) {return a && a.value > b.value ? a : b});
}

function getLargestLayoutShiftSource(sources) {
  return sources.reduce(function(a, b) {
    return a.node && a.previousRect.width * a.previousRect.height >
        b.previousRect.width * b.previousRect.height ? a : b;
  });
}

function wasFIDBeforeDCL(fidEntry) {
  var navEntry = performance.getEntriesByType('navigation')[0];
  return navEntry && fidEntry.startTime < navEntry.domContentLoadedEventStart;
}

function getDebugInfo(name, entries) {
  entries = entries || []; 
  // In some cases there won't be any entries (e.g. if CLS is 0,
  // or for LCP after a bfcache restore), so we have to check first.
  if (entries.length) {
    if (name === 'LCP') {
      var lastEntry = entries[entries.length - 1];
      return {
        debug_target: getSelector(lastEntry.element),
        event_time: lastEntry.startTime,
      };
    } else if (name === 'FID') {
      var firstEntry = entries[0];
      return {
        debug_target: getSelector(firstEntry.target),
        debug_event: firstEntry.name,
        debug_timing: wasFIDBeforeDCL(firstEntry) ? 'pre_dcl' : 'post_dcl',
        event_time: firstEntry.startTime,
      };
    } else if (name === 'CLS') {
      var largestEntry = getLargestLayoutShiftEntry(entries);
      if (largestEntry && largestEntry.sources) {
        var largestSource = getLargestLayoutShiftSource(largestEntry.sources);
        if (largestSource) {
          return {
            debug_target: getSelector(largestSource.node),
            event_time: largestEntry.startTime,
          };
        }
      }
    }
  }
  // Return default/empty params in case there are no entries.
  return {
    debug_target: '(not set)',
  };
}
</script>
<script id="web-vitals-rating">
/*
 * Get Web Vital Rating
 * 
 */
function getRating(name, value) {
    switch (name) {
        case 'LCP': return calculateRating(value,2500,4000);
        case 'FID': return calculateRating(value,100,300);
        case 'CLS': return calculateRating(value,0.1,0.25);
        case 'FCP': return calculateRating(value,2000,4000); // Page Speed Insights is 1000 and 3000, lighthouse and web.dev does 2000 and 4000
        case 'TTFB': return calculateRating(value,500,1500); // CrUX Data Studio report says NI is 500ms to 1500ms
        default: return '(not set)';
    }
}
function calculateRating(value, good, poor) {
    if (!value && value !== 0) return '(not set)';
    if (value > poor) return 'poor';
    if (value > good) return 'ni';   
    return 'good';
}

</script>
<script id="web-vitals-ga4">
/*
 * Send Core Web Vitals to the DataLayer 
 * 
 */
function sendToDataLayer(metric) {
  var webVitalsMeasurement =  {
    name: metric.name,
    id: metric.id, 
    value: metric.value,
    delta: metric.delta,
    rating: getRating(metric.name, metric.value),
    valueRounded: Math.round(metric.name === 'CLS' ? metric.value * 1000 : metric.value),
    deltaRounded: Math.round(metric.name === 'CLS' ? metric.value * 1000 : metric.value)
  };

  var debugInfo = getDebugInfo(metric.name, metric.entries);

  if(debugInfo.debug_target) webVitalsMeasurement.debugTarget = debugInfo.debug_target;
  if(debugInfo.debug_event) webVitalsMeasurement.debugEvent = debugInfo.debug_event;
  if(debugInfo.debug_timing) webVitalsMeasurement.debugTiming = debugInfo.debug_timing;
  if(debugInfo.event_time) webVitalsMeasurement.eventTime = debugInfo.event_time;

  dataLayer.push({
      event: 'coreWebVitals', 
      webVitalsMeasurement: webVitalsMeasurement
  });
}
</script>
<script id="web-vitals-cdn">
/*
 * Using the web-vitals script from a CDN
 * 
 * https://github.com/GoogleChrome/web-vitals#from-a-cdn
 * 
 * Modified to call the sendToGoogleAnalytics function on events
 * 
 */
(function() {
  var script = document.createElement('script');
  script.src = 'https://unpkg.com/web-vitals';
  script.onload = function() {
    // When loading `web-vitals` using a classic script, all the public
    // methods can be found on the `webVitals` global namespace.
    webVitals.getCLS(sendToDataLayer);
    webVitals.getFID(sendToDataLayer);
    webVitals.getLCP(sendToDataLayer);
  }
  document.head.appendChild(script);
}())
</script>

You also want to add a tag sequence to this tag to make sure the GA4 Configuration tag fire before this one. We don’t want to send events to GA4 before it exists.

GTM Core Web Vitals Custom HTML Tag
GTM Core Web Vitals Custom HTML Tag firing after the GA4 Configuration Tag

We now need to define all the user defined variables that we send in the dataLayer. Again I’ve followed Simos lead on the convention. Note that my report does not use the rounded values or the rating, but I’ve kept them for compatibility with other solutions.

Variable nameData Layer Variable Name
DLV – webVitalsMeasurement.namewebVitalsMeasurement.name
DLV – webVitalsMeasurement.idwebVitalsMeasurement.id
DLV – webVitalsMeasurement.valuewebVitalsMeasurement.value
DLV – webVitalsMeasurement.deltawebVitalsMeasurement.delta
DLV – webVitalsMeasurement.valueRoundedwebVitalsMeasurement.valueRounded
DLV – webVitalsMeasurement.deltaRoundedwebVitalsMeasurement.deltaRounded
DLV – webVitalsMeasurement.debugTargetwebVitalsMeasurement.debugTarget
DLV – webVitalsMeasurement.debugEventwebVitalsMeasurement.debugEvent
DLV – webVitalsMeasurement.debugTimingwebVitalsMeasurement.debugTiming
DLV – webVitalsMeasurement.eventTimewebVitalsMeasurement.eventTime
DLV – webVitalsMeasurement.ratingwebVitalsMeasurement.rating

We’re getting there. Isn’t GTM meant to make things easy?

Time to create a trigger for the coreWebVitals event. Like this…

GTM Event Core Web Vitals
The key thing is to name the event ‘coreWebVitals’.

We’re now on to the GA4 event tag itself. Here we use our new trigger and add all the parameters we want to send.

First set the Event name to {{DLV – webVitalsMeasurement.name}}

The following table shows what to send so it works with the web.dev report and mine.

Parameter nameValue
metric_name{{DLV – webVitalsMeasurement.name}}
metric_id{{DLV – webVitalsMeasurement.id}}
metric_value{{DLV – webVitalsMeasurement.value}}
value{{DLV – webVitalsMeasurement.delta}}
metric_delta{{DLV – webVitalsMeasurement.delta}}
debug_target{{DLV – webVitalsMeasurement.debugTarget}}
debug_event{{DLV – webVitalsMeasurement.debugEvent}}
debug_timing{{DLV – webVitalsMeasurement.debugTiming}}
event_time{{DLV – webVitalsMeasurement.eventTime}}
metric_rating{{DLV – webVitalsMeasurement.rating}}
GA4 - Event - Core Web Vitals
GA4 – Event – Core Web Vitals

Like with the gtag implementation we recommend sending a page_type parameter to GA4 so that you can segment your reports. How you determine the value of the page_type is down to you. You will then have to send it in the dataLayer, create a variable for it, and add it to your GA4 Configuration fields.

GA4 Configuration Page Type
I get my page type from a schema.org based variable I send to the dataLayer on all pages

Setting up the effective connection type requires the following Custom JavaScript varable called ‘Effective connection type’ to be created:

function () {
  
  var connection = navigator.connection || navigator.mozConnection || navigator.webkitConnection;
  
  if(connection && connection.effectiveType) return connection.effectiveType;

  return 'unknown';  
}

You can then add it as a field called effective_connection_type in your GA4 Connection tag.

GA4 Config Effective connection type
All events will now return page_type and effective_connection_type

I’ll leaved it up to you on how you test and finally publish it. Simo’s article has a good section on testing.

Adding GA4 definitions (optional)

This solution does not need you to add these parameters as definitions in the GA4 admin. However, defining them lets you directly report on them in GA4 and when directly connecting to Data Studio. It also gives me an opportunity to briefly explain what they do.

Clicking on a cell will copy its content to your clipboard.

Dimension NameScopeDescriptionEvent Parameter
Web Vitals metric IDEventThis is used to group web vitals that happen in the same page viewmetric_id
Debug targetEventThis identifies the selector path to the element that contributed most to the metricdebug_target
Debug timingEventFor FID events it indicates if the event was before ‘pre_dcl’ or after ‘post_dcl’ the content was loadeddebug_timing
Event timeEventThe time when the Web Vitals event happenedevent_time
Web Vitals ratingEvent‘good’, ‘ni’ or ‘poor’. Based on the scores set by web.dev. Used in the LCP, CLS, FID eventsmetric_rating
Page typeEventUsed to identify the type of page (e.g. template name) for segmenting page based reportspage_type
Effective connection typeEventUses the Network Information API to get the effective connection speed of the user. 4g, 3g, 2g, slow-2g and unknowneffective_connection_type
Metric NameScopeDescriptionEvent ParameterUnit of measure
Web Vitals valueEventThe value of a web vital. Used in the LCP, FID and CLS events.metric_valueStandard
Web Vitals deltaEventThe difference since the last report for this web vital. ‘value’ is also set to the deltametric_deltaStandard

Connect to BigQuery

web.dev and I went the route of using BigQuery for our reports. The reason for this is the limitations of directly connecting GA4 to Data Studio.

  1. The mapping of custom GA4 properties to Data Studio fields is not reliable. Changing the data source causes the fields to shuffle around in the report. Hopefully they will fix this at some point.
  2. Data Studio has no mechanism to group the Web Vitals events by page view to determine the final value for a Web Vital. We use BigQuery to pre-group the data for us.

First you want to set up a Google Cloud account and create a project to connect GA4 to.

Then go into the GA4 admin, selecting ‘BigQuery Linking’ and clicking on the Link button. You will then be able to select your new project and complete the linking process. You need to enable Daily export.

GA4 Admin BigQuery Linking
Connecting GA4 to BigQuery

Once complete it will take about 24 hours before your first GA4 export table is created.

You can check the project by selecting the project in your Google Cloud account and then selecting BigQuery from the side bar. You should see the project listed. Once the first export is complete you will be able to expand it to see the exported tables. This is what mine looks like once the table was created.

BigQuery GA4 Export
A BigQuery project containing a GA4 exported event table.

Time for a break. See you tomorrow…

 Sunset in Glenelg
Glenelg beach near Adelaide Australia. A great place to unwind after a BigQuery day

Create a BigQuery materialised table

Morning.

After the first events table is exported we can move to the next step. We need to convert the data so that it can be easily used by DataStudio. As mentioned before, the main task is to work out the final Web Vitals scores for each page view so that Data Studio can deal with it.

We perform this conversion by adding an SQL query that creates a new table with the data we need. The query we use is based on the one documented by web.dev with a few extras added so that it can support my report.

If you’ve already created the materialised table as per web.devs instructions, you will need to add the ‘Tony’s additions’ sections to that SQL for my report to work.

Otherwise, click the ‘compose new query’ button and add the following SQL to the editor. You will need to edit it to make it use your project and dataset. I’ve highlighted what needs editing in red.

#  Replace target table name
CREATE OR REPLACE TABLE your_project.analytics_123456789.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,
  
  # Tony's additions 1 START
  CASE event_name
    WHEN 'CLS' THEN 
        CASE 
            WHEN metric_value <= 0.1 THEN 'Good'
            WHEN metric_value <= 0.25 THEN 'Needs Improvement'
            ELSE 'Poor'
        END
    WHEN 'LCP' THEN 
        CASE 
            WHEN metric_value <= 2.500 THEN 'Good'
            WHEN metric_value <= 4.000 THEN 'Needs Improvement'
            ELSE 'Poor'
        END
    WHEN 'FID' THEN 
        CASE 
            WHEN metric_value <= 100 THEN 'Good'
            WHEN metric_value <= 300 THEN 'Needs Improvement'
            ELSE 'Poor'
        END
  END AS metric_status
  # Tony's additions 1 END

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,

            # Tony's additions 2 START
            page_location,
            page_type,
            continent,
            region,
            device_browser,
            effective_connection_type
            # Tony's additions 2 END

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

                # Tony's additions 3 START
                ANY_VALUE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')) AS page_location,
                ANY_VALUE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_type')) AS page_type,
                ANY_VALUE(geo.continent) AS continent,
                ANY_VALUE(geo.region) AS region,
                ANY_VALUE(device.web_info.browser) AS device_browser,
                ANY_VALUE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'effective_connection_type')) AS effective_connection_type
                # Tony's additions 3 END

            FROM
              # Replace source table name
              `your_project.analytics_123456789.events_*`
            WHERE
              event_name IN ('LCP', 'FID', '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');

Once you have edited it you can run it for the first time. If it does not work you may have not got your table names right.

When it works it will create a table called ‘web_vitals_summary’ in the same place as your GA4 export. We’re going to reference that later.

BigQuery Materialised Table
Just after running the materialised table SQL script

Save the query as ‘Web Vitals Summary’ so you can re-run it later. You can find saved queries via an option in the footer.

Creating this materialised table not only makes it easier to create reports, it also may save you money. Queries cost money once you’ve used up your free monthly allowance of 1 Terabyte. This table reduces the size of the queries you make to BigQuery when viewing reports, making them faster and cheaper.

Schedule updates of the materialised table (optional)

At the moment you would need to periodically re-run the query to get their latest data into your report. You may want to leave this as a manual process if you are hitting the free monthly allowance. If not, you can set the query up to be scheduled every day.

To do that, when editing the query you will see a Schedule option at the top. Create one and name it ‘Web Vitals Summary’. You may have to enable the API, refresh the page, login and maybe go back to the saved query to get it to work (a bit flaky is this one). You can find scheduled queries from the expandable side bar.

GA4 does not seem to be consistent in the time it does its daily export so you may have to live with a day or two lag in the report (or run it manually as needed)

Create a Data Source in Data Studio

Data Studio uses data sources to provide the data for reports. The next step is to create a data source based on our new BigQuery materialised table.

Open our Core Web Vitals BigQuery data source and click on the copy icon. Then select BigQuery, Authorize and then find your ‘web_vitals_summary’ table. Then click reconnect.

Core Web Vitals BigQuery Data Studio Data Source

You may want to rename the source by clicking on its name at the top right. Otherwise, we’re all done here.

From v2.0 onwards the report uses the functions provided by this data source. This is so we can create data sources using different connectors. To create a new data source using a different connector you would need to implement these functions (using the exact same field ids).

Make your own copy of the Core Web Vitals report

Almost there. We just need to create the report using your data source.

Open the Core Web Vitals report and click ‘Use Template’ at the top right. Then select your data source and click ‘Copy Report’. You may need to do a refresh to get your new data source showing.

Rename the report (top right) to whatever you want to call it. Then switch to view mode for a better experience.

Using the reports

Over time more daily data will be imported from GA4 making the time based reports great for tracking progress.

Browse through the different pages in the report via the left side menu.

The dropdown filters at the top affect the whole report. Why not tunnel down to a specific location, browser, page type or even if the user was engaged or not.

Core Web Vitals Filter
Looks like my web application pages just pass LCP for engaged desktop Chrome users in Russia. Phew!

Try clicking on table rows. Many will further filter down the data for that page.

Page Type Table Filter
Yep, some of my web application pages have a LCP issue. Definitely need to look into that.

Page URLs and the PSI (Page Speed Insights) columns are links.

I think there may be some interesting data to come from the distribution reports with regard to engagement. I’m already seeing longer LCP values causing a reduction in engagement with mobile users.

LCP Engagement
Engagement rates drop as LCP increases.

This is your own copy of the report. Feel free to edit it to make it fit your needs. e.g. Changing the above chart to a 100% stacked chart will probably work well once there is enough data. If you improve it or add something great…

Feedback

Please send me any feedback you have. Ideas, issues or just to show off your report scores. My @TonyMcCreath Twitter account is a good place to do that. I’ll be waiting.

Tony Waiting
What should I do next?

4 Responses

  1. Hi
    Thanks for sharing this complete guide for checking coreWebVitals, I think there is a mismatch in GTM, in the parameters table.
    value : {{DLV – webVitalsMeasurement.delta}}

    1. Hi Amin, could you clarify what this missmatch is? My solution does not use the delta, so it would have not been tested!

  2. Thanks for the writeup! This topic is going to be very important in the next several months. I hope you continue to share more on GA4, CWV, and BigQuery!

Leave a Reply

Your email address will not be published. Required fields are marked *