Web vitals score

Question

I have question about how to get data for Web vitals score. I am using Google Lighthouse and i wanted to edit score values for the latest ones. Right in the source files there are links like this - https://bigquery.cloud.google.com/table/httparchive:lighthouse.2021_05_01_mobile. But I cannot see any values for metrics in there.

I have searched in HTTPArchive quite a lot, but I was not able to find any data solving my problem.

My expectations were to get latest data for Web vitals, specificaly for those metric: FCP, LCP, TBT, CLS, SI, INP, TTI, FID and TTFB separately for mobiles and desktops. While i was inspecting files, I have found that there could be option to restrict those values for only e-commerce sites. Is it really possible?

Answer 1

Right in the source files there are links like this - https://bigquery.cloud.google.com/table/httparchive:lighthouse.2021_05_01_mobile. But I cannot see any values for metrics in there.

That's an old permalink format for bigquery. One equivalent to that today would be https://console.cloud.google.com/bigquery?ws=!1m5!1m4!4m3!1shttparchive!2slighthouse!3s2021_05_01_mobile

But even then, the metrics you're after are nested within the report JSON blob. It's also the older format httparchive tables, which are eventually going away.

Using the latest httparchive.all.pages table, a query for most of the metrics you mention for mobile devices could look like:

SELECT
  CAST(JSON_VALUE(lighthouse, '$.audits."first-contentful-paint".numericValue') AS FLOAT64) AS FCP,
  CAST(JSON_VALUE(lighthouse, '$.audits."largest-contentful-paint".numericValue') AS FLOAT64) AS LCP,
  CAST(JSON_VALUE(lighthouse, '$.audits."total-blocking-time".numericValue') AS FLOAT64) AS TBT,
  CAST(JSON_VALUE(lighthouse, '$.audits."cumulative-layout-shift".numericValue') AS FLOAT64) AS CLS,
  CAST(JSON_VALUE(lighthouse, '$.audits."speed-index".numericValue') AS FLOAT64) AS SI,
  CAST(JSON_VALUE(lighthouse, '$.audits.interactive.numericValue') AS FLOAT64) AS TTI,
  FROM `httparchive.all.pages`
  WHERE
  date = '2024-01-01'
  AND client = 'mobile'
  AND lighthouse IS NOT NULL

Note that Lighthouse doesn't pick up INP or FID by default because those require an interaction. Also note that this query will be expensive! There are some tips on this page for cutting costs.

You may also be more interested in the CrUX data for these metrics, which comes from real users instead of testing in the lab. You didn't ask about that specifically (just tagged it), so I'll just link you to the docs for querying CrUX metrics in BigQuery.