Skip to content

Instantly share code, notes, and snippets.

@tunetheweb
Created September 14, 2023 18:53
Show Gist options
  • Save tunetheweb/fa1aa45372c062f0a677a66e4ea2659b to your computer and use it in GitHub Desktop.
Save tunetheweb/fa1aa45372c062f0a677a66e4ea2659b to your computer and use it in GitHub Desktop.
CWVs Summary
#standardSQL
# Core WebVitals by origin
CREATE TEMP FUNCTION IS_GOOD (good FLOAT64, needs_improvement FLOAT64, poor FLOAT64) RETURNS BOOL AS (
SAFE_DIVIDE(good, (good + needs_improvement + poor)) >= 0.75
);
CREATE TEMP FUNCTION IS_POOR (good FLOAT64, needs_improvement FLOAT64, poor FLOAT64) RETURNS BOOL AS (
SAFE_DIVIDE(poor, (good + needs_improvement + poor)) >= 0.25
);
CREATE TEMP FUNCTION IS_NI (good FLOAT64, needs_improvement FLOAT64, poor FLOAT64) RETURNS BOOL AS (
NOT IS_GOOD(good, needs_improvement, poor) AND
NOT IS_POOR(good, needs_improvement, poor)
);
CREATE TEMP FUNCTION IS_NON_ZERO (good FLOAT64, needs_improvement FLOAT64, poor FLOAT64) RETURNS BOOL AS (
good + needs_improvement + poor > 0
);
WITH
base AS (
SELECT
origin,
SUM(fast_fid) / SUM(fast_fid + avg_fid + slow_fid) AS fast_fid,
SUM(avg_fid) / SUM(fast_fid + avg_fid + slow_fid) AS avg_fid,
SUM(slow_fid) / SUM(fast_fid + avg_fid + slow_fid) AS slow_fid,
SUM(fast_inp) / SUM(fast_inp + avg_inp + slow_inp) AS fast_inp,
SUM(avg_inp) / SUM(fast_inp + avg_inp + slow_inp) AS avg_inp,
SUM(slow_inp) / SUM(fast_inp + avg_inp + slow_inp) AS slow_inp,
SUM(fast_lcp) / SUM(fast_lcp + avg_lcp + slow_lcp) AS fast_lcp,
SUM(avg_lcp) / SUM(fast_lcp + avg_lcp + slow_lcp) AS avg_lcp,
SUM(slow_lcp) / SUM(fast_lcp + avg_lcp + slow_lcp) AS slow_lcp,
SUM(small_cls) / SUM(small_cls + medium_cls + large_cls) AS small_cls,
SUM(medium_cls) / SUM(small_cls + medium_cls + large_cls) AS medium_cls,
SUM(large_cls) / SUM(small_cls + medium_cls + large_cls) AS large_cls,
SUM(fast_fcp) / SUM(fast_fcp + avg_fcp + slow_fcp) AS fast_fcp,
SUM(avg_fcp) / SUM(fast_fcp + avg_fcp + slow_fcp) AS avg_fcp,
SUM(slow_fcp) / SUM(fast_fcp + avg_fcp + slow_fcp) AS slow_fcp,
SUM(fast_ttfb) / SUM(fast_ttfb + avg_ttfb + slow_ttfb) AS fast_ttfb,
SUM(avg_ttfb) / SUM(fast_ttfb + avg_ttfb + slow_ttfb) AS avg_ttfb,
SUM(slow_ttfb) / SUM(fast_ttfb + avg_ttfb + slow_ttfb) AS slow_ttfb
FROM
`chrome-ux-report.materialized.metrics_summary`
WHERE
yyyymm = 202308
GROUP BY
origin
)
SELECT
COUNT(DISTINCT origin) AS total_origins,
# Good CWV with optional FID
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(fast_fid, avg_fid, slow_fid) IS NOT FALSE AND
IS_GOOD(fast_lcp, avg_lcp, slow_lcp) AND
IS_GOOD(small_cls, medium_cls, large_cls), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_lcp, avg_lcp, slow_lcp) AND
IS_NON_ZERO(small_cls, medium_cls, large_cls), origin, NULL))) AS pct_cwv_good,
# Good CWV with optional INP
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(fast_inp, avg_inp, slow_inp) IS NOT FALSE AND
IS_GOOD(fast_lcp, avg_lcp, slow_lcp) AND
IS_GOOD(small_cls, medium_cls, large_cls), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_lcp, avg_lcp, slow_lcp) AND
IS_NON_ZERO(small_cls, medium_cls, large_cls), origin, NULL))) AS pct_cwv_good_inp,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(fast_lcp, avg_lcp, slow_lcp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_lcp, avg_lcp, slow_lcp), origin, NULL))) AS pct_lcp_good,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_NI(fast_lcp, avg_lcp, slow_lcp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_lcp, avg_lcp, slow_lcp), origin, NULL))) AS pct_lcp_ni,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_POOR(fast_lcp, avg_lcp, slow_lcp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_lcp, avg_lcp, slow_lcp), origin, NULL))) AS pct_lcp_poor,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(fast_fid, avg_fid, slow_fid), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_fid, avg_fid, slow_fid), origin, NULL))) AS pct_fid_good,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_NI(fast_fid, avg_fid, slow_fid), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_fid, avg_fid, slow_fid), origin, NULL))) AS pct_fid_ni,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_POOR(fast_fid, avg_fid, slow_fid), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_fid, avg_fid, slow_fid), origin, NULL))) AS pct_fid_poor,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(fast_inp, avg_inp, slow_inp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_inp, avg_inp, slow_inp), origin, NULL))) AS pct_inp_good,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_NI(fast_inp, avg_inp, slow_inp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_inp, avg_inp, slow_inp), origin, NULL))) AS pct_inp_ni,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_POOR(fast_inp, avg_inp, slow_inp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_inp, avg_inp, slow_inp), origin, NULL))) AS pct_inp_poor,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(small_cls, medium_cls, large_cls), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(small_cls, medium_cls, large_cls), origin, NULL))) AS pct_cls_good,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_NI(small_cls, medium_cls, large_cls), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(small_cls, medium_cls, large_cls), origin, NULL))) AS pct_cls_ni,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_POOR(small_cls, medium_cls, large_cls), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(small_cls, medium_cls, large_cls), origin, NULL))) AS pct_cls_poor,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(fast_fcp, avg_fcp, slow_fcp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_fcp, avg_fcp, slow_fcp), origin, NULL))) AS pct_fcp_good,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_NI(fast_fcp, avg_fcp, slow_fcp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_fcp, avg_fcp, slow_fcp), origin, NULL))) AS pct_fcp_ni,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_POOR(fast_fcp, avg_fcp, slow_fcp), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_fcp, avg_fcp, slow_fcp), origin, NULL))) AS pct_fcp_poor,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_GOOD(fast_ttfb, avg_ttfb, slow_ttfb), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_ttfb, avg_ttfb, slow_ttfb), origin, NULL))) AS pct_ttfb_good,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_NI(fast_ttfb, avg_ttfb, slow_ttfb), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_ttfb, avg_ttfb, slow_ttfb), origin, NULL))) AS pct_ttfb_ni,
SAFE_DIVIDE(
COUNT(DISTINCT IF(
IS_POOR(fast_ttfb, avg_ttfb, slow_ttfb), origin, NULL)),
COUNT(DISTINCT IF(
IS_NON_ZERO(fast_ttfb, avg_ttfb, slow_ttfb), origin, NULL))) AS pct_ttfb_poor
FROM
base
ORDER BY
total_origins DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment