Getting started
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://console.cloud.google.com/bigquery?p=chrome-ux-report&d=all&page=dataset. 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://console.cloud.google.com/bigquery?p=chrome-ux-report&d=all&t=201710&page=table.
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
each row.
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.
Example queries
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:
#standardSQL |
The first line is a directive that you want to use the standard SQL syntax. |
SELECT |
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. |
FROM |
The FROM clause expresses the project, dataset, and table that you want to query. |
WHERE |
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
LIKE
operator to compare each origin against a pattern. This query matches all
origins that end with “://example.com”. This is to account for a domain
that may support both HTTP and HTTPS.
|
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
SUM
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
UNNEST
on Line 13 to convert the bins from a single row of repeated bins into
many rows of a single bin.
| start | density |
|---|---|
| 0 | 20.85% |
| 200 | 20.18% |
| 400 | 16.54% |
| 600 | 10.62% |
| 800 | 7.18% |
| 1000 | 4.83% |
| 1200 | 3.46% |
| 1400 | 3.21% |
| 1600 | 2.51% |
| … | … |
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.