The Chrome User Experience Report is available to explore on Google BigQuery, which is a part of the Google Cloud Platform (GCP). To get started, you'll need a Google account (sign up), a Google Cloud project that you will use to access the project, and basic knowledge of SQL.
Access the project
The project is available at https://bigquery.cloud.google.com/dataset/chrome-ux-report:all. If you visit that page and get a prompt to create a project like the one below, continue with the following steps to create a new GCP project. Otherwise, you can skip to the next section and start querying the dataset.
- Navigate to Google Cloud Platform .
- Click Create a Project.
- Give your new project a name like “My Chrome UX Report” and click Create.
- Provide your billing information if prompted — see Why do I need to provide a credit card? Note: The Chrome User Experience Report is free to access and explore up to the limits of the free tier, which is renewed monthly and provided by BigQuery. Additionally, new GCP users may be eligible for a signup credit to cover expenses beyond the free tier.
Now you’re ready to start querying the dataset.
Understanding the dataset schema
Refer to the methodology documentation for an overview of provided metrics, dimensions, and high-level overview of the schema. For a more hands-on view, now that you have the GCP project setup, navigate to https://bigquery.cloud.google.com/table/chrome-ux-report:all.201710.
The dataset is a collection of tables in the format
YYYYMM. The schema for
201710 (October 2017) will be displayed, outlining the detailed structure of
Query the dataset
With access to the dataset, querying it is straightforward:
- Navigate to BigQuery.
- Click Compose Query on top of the side panel.
- Enter your query in the main panel.
- Execute the query by clicking Run Query.
Let’s start with a basic query to see if a particular origin is available in the dataset. Paste the query below into the query editor and click Run Query to execute it.
#standardSQL SELECT DISTINCT origin FROM `chrome-ux-report.all.201710` WHERE origin LIKE '%://example.com'
Here’s how it works:
||The first line is a directive that you want to use the standard SQL syntax.|
||The SELECT clause expresses the output of the query. In this example, we’re looking for a list of origins which may be HTTP or HTTPS.|
||The FROM clause expresses the project, dataset, and table that you want to query.|
Without the WHERE clause on Line 4, the example query would return all of
the distinct origins in the dataset. Since we’re only interested in a
particular origin, we can use the
Once we know the origin we would like to examine more closely, we can dive deeper into the user experience data. Let’s write a query to visualize the distribution of the histogram.
#standardSQL SELECT bin.start, SUM(bin.density) AS density FROM `chrome-ux-report.all.201710`, UNNEST(first_contentful_paint.histogram.bin) AS bin WHERE origin = 'http://example.com' GROUP BY bin.start ORDER BY bin.start
The query above produces the data for the histogram by using the
function to add up the densities for each bin. This query also utilizes a
nested query on Line 6 to select the first contentful paint
(FCP) histogram’s bins and uses
on Line 13 to convert the bins from a single row of repeated bins into
many rows of a single bin.
Plug this data into your favorite visualization program and you’ll see a histogram similar to the one below.
Now that we have a way to look at the data, let’s write a couple of queries to compute additional summary metrics. For example, this histogram looks skewed towards the bins under 1000 ms, let’s compute the density sum for this range.
#standardSQL SELECT SUM(bin.density) AS density FROM `chrome-ux-report.chrome_ux_report.201710`, UNNEST(first_contentful_paint.histogram.bin) AS bin WHERE bin.start < 1000 AND origin = 'http://example.com'
In the example above we’re adding all of the density values in the FCP histogram for “http://example.com” where the FCP bin’s start value is less than 1000 ms. The result is 0.7537, which indicates that ~75.4% of page loads experience the FCP in under a second.
We can go one step further and also segment the dataset via one of the provided dimensions. For example, we can use the effective connection type dimension to understand how the above experience varies for users with different connection speeds.
#standardSQL SELECT effective_connection_type.name AS ect, SUM(bin.density) AS density FROM `chrome-ux-report.chrome_ux_report.201710`, UNNEST(first_contentful_paint.histogram.bin) AS bin WHERE bin.end <= 1000 AND origin = 'http://example.com' GROUP BY ect ORDER BY density DESC
The result of this query shows the fraction of users that experience the FCP in under one second, split by effective connection type. At first glance, users on a 3G connection may have significantly worse performance, but it's important to remember that the resulting value is relative to the overall population; the reported value is also function of 3G population size, which may be lower — see analysis tips & best practices for more. If desired, we can normalize the value against the relative population size of each effective connection type.
Finally, we can slice the results above even further by making use of the
per-country datasets available for tables
201712 and newer.
#standardSQL WITH countries AS ( SELECT *, 'All' AS country FROM `chrome-ux-report.all.201712` UNION ALL SELECT *, 'India' AS country FROM `chrome-ux-report.country_in.201712` UNION ALL SELECT *, 'US' AS country FROM `chrome-ux-report.country_us.201712`) SELECT country, effective_connection_type.name AS ect, SUM(bin.density) AS density FROM countries, UNNEST(first_contentful_paint.histogram.bin) AS bin WHERE bin.end <= 1000 AND origin = 'http://example.com' GROUP BY country, ect ORDER BY density DESC
This query combines the All, India, and US tables together and queries their union for each country and ECT combination.
Using these queries as a foundation, you’re ready to start mining the Chrome UX Report for insightful data. Should you need it, feel free to ask the discussion group for help.