Difference between the Data Studio Dashboard and the BigQuery Data

[Peter Vuong]

Hello,

Is there a difference between the data studio dashboard and the big query data for the Chrome User Experience Report looking at the same time period (i.e. 1 month)?

Running into an issue where there are differences between the two, and I'm not sure what the issue may be, especially looking at one origin on both the dashboard and bigquery.

Is there a way that the two could be aligned?

Thanks

Peter

[Rick Viscomi]

Hi Peter,

No, that would be a bug! The Dashboard and BigQuery datasets should be identical. Is there any additional information you could share about the discrepancy?

Rick

[Peter]

Hi Rick,

Here's some examples for July 2020 data for https://www.canadiantire.ca origin. I had taken what you had on this guy https://dev.to/chromiumdev/a-step-by-step-guide-to-monitoring-the-competition-with-the-chrome-ux-report-4k1o and changed it for LCP and FID to start.

Peter

-- Fast LCP

SELECT
  origin,
  SUM(lcp.density) AS FastLCP
FROM
  `chrome-ux-report.all.202007`,
  UNNEST(largest_contentful_paint.histogram.bin) AS lcp
WHERE
  origin IN( 'https://www.canadiantire.ca')
  AND lcp.start <= 2500
GROUP BY
  origin

bigquery: 0.4852
data studio: 46.44%

-- Fast Desktop LCP

SELECT
  origin,
  SUM(lcp.density) AS FastLCP
FROM
  `chrome-ux-report.all.202007`,
  UNNEST(largest_contentful_paint.histogram.bin) AS lcp
WHERE
  origin IN( 'https://www.canadiantire.ca')
  AND lcp.start <= 2500
  AND form_factor.name = 'desktop'
GROUP BY
  origin

bigquery: 0.1765
data studio: 54.78%

-- Fast FID

SELECT
  origin,
  SUM(fid.density) AS FastFID
FROM
  `chrome-ux-report.all.202007`,
  UNNEST(first_input.delay.histogram.bin) AS fid
WHERE
  origin IN( 'https://www.canadiantire.ca' )
  AND fid.start <=100
GROUP BY
  origin

bigquery: 0.8037
data studio: 78.75%

-- Desktop Fast FID

SELECT
  origin,
  SUM(fid.density) AS Desktop_FastFID
FROM
  `chrome-ux-report.all.202007`,
  UNNEST(first_input.delay.histogram.bin) AS fid
WHERE
  origin IN( 'https://www.canadiantire.ca' )
  AND fid.start <=100
  AND form_factor.name = 'desktop'
GROUP BY
  origin

bigquery: 0.2947
data studio: 89.15%

[Rick Viscomi]

Hi Peter,

There are a couple of discrepancies in your queries that could explain the differences:

1. The LCP histogram bin starting at 2500 is 100ms wide, so `lcp.start <= 2500` is effectively including all of the LCP experiences under 2600ms. When evaluating the percent of experiences under a threshold, use "<" rather than "<=".

SELECT

  origin,

  SUM(lcp.density) AS FastLCP

FROM

  `chrome-ux-report.all.202007`,

  UNNEST(largest_contentful_paint.histogram.bin) AS lcp

WHERE

  origin IN( 'https://www.canadiantire.ca')

  AND lcp.start < 2500

GROUP BY

  origin

# 0.46415

2. Densities don't always add up to 100%. This is especially true for FID, because there may be user experiences in which there is no input at all. It's also true for specific form factor records, which represent the density as a percent of all experiences, not just for that form factor. For those reasons, it's important to normalize the percentages by dividing by the total density.

SELECT

  origin,

  ROUND(SUM(IF(lcp.start < 2500, lcp.density, 0)) / SUM(lcp.density), 4) AS FastLCP

FROM

  `chrome-ux-report.all.202007`,

  UNNEST(largest_contentful_paint.histogram.bin) AS lcp

WHERE

  origin IN( 'https://www.canadiantire.ca')

GROUP BY

  origin

# 0.4644

With these two changes combined, we get a fast LCP aligned with the value in the dashboard. Hope that helps.

Rick

[Peter]

Hi Rick,

Thanks for the reply. Would I be able to apply this method to FCP, FID, and CLS as well so that the metrics are aligned?

Peter

[Peter]

Running into a similar issue with Slow LCP;

SELECT
  origin,
  ROUND(SUM(IF(lcp.start >= 4000, lcp.density, 0)) / SUM(lcp.density), 4) AS SlowLCP

FROM
  `chrome-ux-report.all.202007`,
  UNNEST(largest_contentful_paint.histogram.bin) AS lcp
WHERE
  origin IN( 'https://www.canadiantire.ca')
GROUP BY
  origin

where the result is 0.3039 vs data studio is 31.06%

Do you have any pointers for Average LCP?

Thanks
Peter

[Rick Viscomi]

Of course! Here's a desktop fast FID example using the recommended technique:

SELECT

  origin,

  ROUND(SUM(IF(fid.start < 100, fid.density, 0)) / SUM(fid.density), 4) AS FastFID

FROM

  `chrome-ux-report.all.202007`,

  UNNEST(first_input.delay.histogram.bin) AS fid

WHERE

  origin IN( 'https://www.canadiantire.ca') AND

  form_factor.name = 'desktop'

GROUP BY

  origin

# 0.8915

And if you want to abstract away the thresholds, which may change year to year, you can query the `chrome-ux-report.materialized.device_summary` table directly, which is what the CrUX Dashboard is built on:

SELECT

  origin,

  ROUND(SUM(fast_fid) / SUM(fast_fid + avg_fid + slow_fid), 4) AS FastFID

FROM

  `chrome-ux-report.materialized.device_summary`

WHERE

  date = '2020-07-01' AND

  origin IN( 'https://www.canadiantire.ca') AND

  device = 'desktop'

GROUP BY

  origin

# 0.8915

For more best practices I'd recommend watching the advanced video tutorial I made called Mastering CrUX on BigQuery. The demo queries are also available in the CrUX Cookbook on GitHub.

Rick

[Rick Viscomi]

> where the result is 0.3039 vs data studio is 31.06%

31.06% is the proportion of slow LCP on phones, but your query is aggregating over all form factors. Here's how it looks when filtering by phone:

SELECT

  origin,

  ROUND(SUM(IF(lcp.start >= 4000, lcp.density, 0)) / SUM(lcp.density), 4) AS SlowLCP

FROM

  `chrome-ux-report.all.202007`,

  UNNEST(largest_contentful_paint.histogram.bin) AS lcp

WHERE

  origin IN( 'https://www.canadiantire.ca') AND

  form_factor.name = 'phone'

GROUP BY

  origin

# 0.3106

> Do you have any pointers for Average LCP?

I'd recommend using the device_summary table approach described above, and swapping the numerator with whichever metric/speed you're interested in. Alternatively, the way to do it with thresholds would be like this:

SELECT

  origin,

  ROUND(SUM(IF(lcp.start >= 2500 AND lcp.start < 4000, lcp.density, 0)) / SUM(lcp.density), 4) AS AverageLCP

FROM

  `chrome-ux-report.all.202007`,

  UNNEST(largest_contentful_paint.histogram.bin) AS lcp

WHERE

  origin IN( 'https://www.canadiantire.ca')

GROUP BY

  origin

# 0.2318

Rick

[Peter]

Thanks Rick for the reply and the examples. I'll refer to the cookbook for more details.