Chrome User Experience Report

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://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.

1. Navigate to Google Cloud Platform .
2. Click Create a Project.
3. Give your new project a name like “My Chrome UX Report” and click Create.
4. 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 each row.

Query the dataset

With access to the dataset, querying it is straightforward:

1. Navigate to BigQuery.
2. Click Compose Query on top of the side panel.
3. Enter your query in the main panel.
4. 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'

Run it on BigQuery

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

Run it on BigQuery

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'

Run it on BigQuery

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

Run it on BigQuery

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

Run it on BigQuery

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.