CrUX Dashboard&Data Strategy Lifecycle

Last Blog I demonstrated the data pipeline we can use CrUX to analyze the site performance. This is from a BI developer perspective.

However, for a company, especially the leadership team, what they want is the final dashboard that generated from BI department, so management plan can be gained.

I already wrote how to query from Bigquery and what site speed metrics we can use from the introduction of CrUX blog and public dataset analysis blog.

So this blog I will show you what kind of dashboard we can generate after the steps of data collection from Google public dataset and ETL.

What data visualization tool we need to use?

There are bunches of data visualization tools we can use, e.g., Data Studio, Power BI etc. This time I take Tableau for an example.

I took www.flightcentre.co.nz(blue line) and www.rentalcars.com(red line) as the origin for comparison, set customer’s device is ‘desktop’ (we also can put a filter on it too).

And there are 4 sheets on a dashboard, i.e., Slow FCP Percentage, Fast FCP Percentage, Fast FID Percentage and Slow FID Percentage.

What they actually mean?

  1. Slow FCP Percentage(the percentage of users that experienced a first contentful paint time of 1 second or less)
  2. Fast FCP Percentage(the percentage of users that experienced a first contentful paint time of 2.5 seconds or more)
  3. Fast FID Percentage(the percentage of users that experienced a first input delay time of 50 ms or less)
  4. Slow FID Percentage(the percentage of users that experienced a first input delay time of 250 ms or more)

After this graph, we can roughly see that flightcentre has a higher site speed than rentalcars in user experience.

What we can do in the next step?

After that, we can inform devs and communicate impact according to show exactly the area that the site is falling down. We can point to the fact that it’s from real users and how people actually experiencing the site.

The second part is the data strategy lifecycle in a company.

What is the data strategy lifecycle in a company?

Develop the strategy–>Create the roadmap–>Change management plan–>Analytics lifestyle–>Measurement plan

Perspectives:

  1. Scope and Purpose: What data will we manage? How much does our data worth? How do we measure success?
  2. Data collection: Archiving, what data where and when, single source of truth(data lake), integrating data silos
  3. Architecture: Real time vs Batch, data sharing, data management and security, data modelling, visualization
  4. Insights and analysis: Data Exploration, self-service, collaboration, managing results
  5. Data governance: Identify data owners, strategic leadership. data stewardship. data lineage, quality, and cost
  6. Access and security: RBAC, encryption, PII, access processes, audits, regulatory
  7. Retention and SLAs: Data tiers and retention, SLA’s to the business

Wrapping up

This post explored the CrUX dashboard BI team can generate and the data strategy in a company. In the future, I will write more.

If you are interested in or have any problems with CrUX or Business Intelligence, feel free to contact me.

Or you can connect with me through my LinkedIn.

Tutorial: Using BigQuery to Analyze CrUX Data

Last blog I gave some examples of how we can use the Chrome User Experience report (CrUX) to gain some insights about site speed. This blog I will continue to show you how to use bigquery to compare your site with the competitors.

Prerequisite:

  1.  Log into Google Cloud,
  2. Create a project for the CrUX work
  3. Avigate to BigQuery console
  4. Add the chrome-ux-report dataset and explore the way the tables are structured in ‘preview’

Step one: Figure out what is the origin of your site and the competitor site

like syntax is preferred (Take care of the syntax difference between Standard SQL and T-SQL)

 -- created by: Jacqui Wu
  -- data source: Chrome-ux-report(202003)
  -- last update: 12/05/2020
  
SELECT
  DISTINCT origin
FROM
  `chrome-ux-report.all.202003`
WHERE
  origin LIKE '%yoursite'

Step two: Figure out what should be queried in the select clause?

What we can query from CrUX?

The specific elements that Google is sharing are:

  1. “Origin”, which consists of the protocol and hostname, as we used in step one, which can make sure the URL link
  2. Effective Connection Type (4G, 3G, etc), which can be queried as the network
  3. Form Factor (desktop, mobile, tablet), which can be queried as the device
  4. Percentile Histogram data for First Paint, First Contentful Paint, DOM Content Loaded and onLoad (these are all nested, so if we want to query them, we need to unnest them)

Here I create a SQL query of FCP percentage in different sites, which measures the time from navigation to the time when the browser renders the first bit of content from the DOM.

This is an important milestone for users because it provides feedback that the page is actually loading.

SQL queries: 

  -- created by: Jacqui Wu
  -- data source: Chrome-ux-report(202003) in diffrent sites
  -- last update: 12/05/2020
  -- Comparing fcp metric in Different Sites

SELECT origin, form_factor.name AS device, effective_connection_type.name AS conn, "first contentful paint" AS metric, bin.start/1000 AS bin, SUM(bin.density) AS volume
FROM(  
SELECT origin, form_factor, effective_connection_type, first_contentful_paint.histogram.bin as bins
FROM `chrome-ux-report.all.202003`
WHERE origin IN ("your site URL link", "competitor A site URL link", "competitor B site URL link")
)
CROSSS JOIN UNNEST(bins) AS bin
GROUP BY origin, device, conn, bin

Step 3: Export the results to the Data Studio(Google visualization tool)

Here are some tips may be useful

  1. Line chart is preferred for comparing different sites in Visual Selection
  2. Set x-axis to bin(which we already calculate it to seconds) and y-axis to percentage of fcp
  3. Set filter(origin, device, conn) in Filtering section

Wrapping up

This post explored the data pipeline we can use CrUX report to analyze the site performance. In the future, I will write more about CrUX.

If you are interested in or have any problems with CrUX or Business Intelligence, feel free to contact me.

Or you can connect with me through my LinkedIn.

How to use CrUX to analyze your site?

What is CrUX?

CrUX stands for the Chrome User Experience Report. It provides real world and real user metrics gathered from the millions of Google Chrome users who load millions of websites (include yours) each month. Of course, they all opt-in to syncing their browsing history and have usage statistic reporting enabled.

According to Google, its goal is ‘capture the full range of external factors that shape and contribute to the final user experience’.

In this post, I will walk you through how to use it to get insights of your site’s performance.

Why we need CrUX?

We all know faster site results in a better user experience and a better customer loyalty, compared to the sites of competitors. It results in the revenue increasing. Google confirmed some details about how they understand the speed. They are available in CrUX.

What are CrUX metrics?

  1. FP(First Paint): when everything loads on the page
  2. FCP(First Content loaded): when some text or an image loaded
  3. DCL(DOM content loaded): when DOM is loaded
  4. ONLOAD: when any additional scripts have loaded
  5. FID(First Input Delay): the time between when a user interacts with your site to when the server actually responds to that

How to generate the CrUX report on PageSpeed Insights?

PageSpeed Insights is a tool for people to understand what a page’s performance is and how to improve it.

It uses the lighthouse to audit the given page and identify opportunities to improve performance. It also integrates with the CrUX to show how real users experience performance on the page.

Take Yahoo as the example, after a few seconds, lighthouse audits will be performed and we will see sections for field and lab data.

In the field data section, we can see FCP and FID (please see the table below as we can see the FCP and FID values).

MetricFastAverageSlow
FCP0-1000ms1000ms-2500ms2500ms+
FID0-50ms50-250ms250ms+

We can see the Yahoo site is in ‘average’ according to the table. To achieve the ‘fast’, both FCP and FID must be categorized as fast.

Also, a percentile can be shown in each metric. For FCP, the 75th percentile is used and for FID, it is the 95th. For example, 75% of FCP experiences on the page are 1.5s or less.

How to use it in BigQuery?

In BigQuery, we can also extract insights about UX on our site.

SELECT origin, form_factor.name AS device, effective_connection_type.name  AS conn, 
       ROUND(SUM(onload.density),4) as density
FROM `chrome-ux-report.all.201907`,
    UNNEST (onload.histogram.bin) as onload
WHERE origin IN ("https://www.yahoo.com")
GROUP BY origin, device, conn

Then we can see the result in BigQuery.

The raw data is organized like a histogram, with bins have a start time, end time and density value. For example, we can query for the percent of ‘fast’ FCP experiences, where ‘fast’ is defined as happening under a second.

We can compare Yahoo with bing. Here is how the query look:

SELECT
  origin,
  SUM(fcp.density) AS fast_fcp
FROM
  `chrome-ux-report.all.201907`,
  UNNEST (first_contentful_paint.histogram.bin) AS fcp
WHERE
  fcp.start<1000
  AND origin IN ('https://www.bing.com',
    'https://www.yahoo.com')
GROUP BY
  origin

Wrapping up

This post explored some methods to get site insights with CrUX report. In the future, I will write more about CrUX.

If you are interested in or have any problems with CrUX or Business Intelligence, feel free to contact me.

Or you can connect with me through my LinkedIn.