Export google CrUX data

I am trying to move a subset of the CrUX data to .csv file(s) for analysis with tools not available on google search console.

I tried to export one or more .csv file from a query like so to a google cloud storage bucket (or any other place really):

SELECT
fcp
FROM
`chrome-ux-report.all.201809`,
UNNEST(first_contentful_paint.histogram.bin) AS fcp
WHERE origin = 'https://developers.google.com'

I have tried two different approaches:

A. export query results to a .csv

Following this approach, I end up with something like this:

EXPORT DATA OPTIONS(
uri='gs://nha-1234.appspot.com/crux/201809*.csv',
format='CSV',
overwrite=true,
header=true,
field_delimiter=';') AS
SELECT
origin, fcp_start, fcp_density, fcp_end
FROM
`chrome-ux-report.all.201809`,
first_contentful_paint.histogram.bin.start AS fcp_start,
first_contentful_paint.histogram.bin.density AS fcp_density
first_contentful_paint.histogram.bin.end AS fcp_end
WHERE
origin = 'https://developers.google.com'

I am met with an error like so:

Invalid project ID ‘first_contentful_paint.histogram’. Project IDs must contain 6-63 lowercase letters, digits, or dashes. Some project IDs also include domain name separated by a colon. IDs must start with a letter and may not end with a dash.

I take it the CrUX project is not recognized.

B. export a subset of the data to a summary table

According to the documentation for exporting data a .csv export might not be possible directly. So the idea is to create a smaller table with a subset of the CrUX data, then use the above (A) to export it to .csv in a subsequent step.

I seem to be hitting a wall here as well, maybe because the CrUX dataset is not listed as one of the public data sets?

It seems like this should be possible still but I cannot seem to make this work – if using one of the SDKs, which projectId/datasetName/tableName should I use?

Answer

A big query can be made to get the data from a given report:

  SELECT
  origin,
  `chrome-ux-report`.experimental.PERCENTILE(ARRAY_AGG(fcp), 75) AS p75_fcp,
  `chrome-ux-report`.experimental.PERCENTILE(ARRAY_AGG(fid), 75) AS p75_fid,
  `chrome-ux-report`.experimental.PERCENTILE(ARRAY_AGG(lcp), 75) AS p75_lcp
FROM
  `chrome-ux-report.all.202109`,
  UNNEST(first_contentful_paint.histogram.bin) AS fcp,
  UNNEST(largest_contentful_paint.histogram.bin) AS lcp,
  UNNEST(first_input.delay.histogram.bin) AS fid,
  UNNEST(layout_instability.cumulative_layout_shift.histogram.bin) AS cls
WHERE
  origin in (
    'https://www.example.com'
  )
group by origin