Skip to content

Instantly share code, notes, and snippets.

@rviscomi

rviscomi/lazy-loading-crux-lcp-wordpress.sql

Last active August 30, 2021 09:25
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rviscomi/44d80c1a0f4dec9cbafb37347c770278 to your computer and use it in GitHub Desktop.
Save rviscomi/44d80c1a0f4dec9cbafb37347c770278 to your computer and use it in GitHub Desktop.
Download ZIP
Exploration into the correlation between native image lazy loading and LCP performance.
Raw
lazy-loading-crux-lcp-wordpress.sql
# Distribution of LCP performance on WordPress pages across desktop/mobile with and without native image lazy loading.
SELECT
_TABLE_SUFFIX AS client,
percentile,
usesLazyLoading,
COUNT(0) AS pages,
COUNTIF(pctGoodLCP IS NOT NULL) AS pagesWithCrUXData,
APPROX_QUANTILES(pctGoodLCP, 1000)[OFFSET(percentile * 10)] AS pctGoodLCP,
APPROX_QUANTILES(p75LCP, 1000)[OFFSET(percentile * 10)] AS p75LCP
FROM (
SELECT
_TABLE_SUFFIX,
url,
IF(IFNULL(REGEXP_CONTAINS(JSON_EXTRACT(payload, "$['_img-loading-attr']"), r'(?i)\blazy\b'), FALSE), 'Yes', 'No') AS usesLazyLoading,
JSON_QUERY(payload, "$._CrUX.metrics.largest_contentful_paint.histogram[0].density") AS pctGoodLCP,
JSON_QUERY(payload, "$._CrUX.metrics.largest_contentful_paint.percentiles.p75") AS p75LCP
FROM
`httparchive.pages.2021_06_01_*`)
JOIN (
SELECT DISTINCT
_TABLE_SUFFIX,
url
FROM
`httparchive.technologies.2021_06_01_*`
WHERE
app = 'WordPress')
USING (url, _TABLE_SUFFIX),
UNNEST([10, 25, 50, 75, 90]) AS percentile
GROUP BY
client,
percentile,
usesLazyLoading
ORDER BY
client,
percentile,
usesLazyLoading
Raw
lazy-loading-crux-lcp.sql
# Distribution of LCP performance across desktop/mobile pages with and without native image lazy loading.
SELECT
_TABLE_SUFFIX AS client,
percentile,
usesLazyLoading,
COUNT(0) AS pages,
COUNTIF(pctGoodLCP IS NOT NULL) AS pagesWithCrUXData,
APPROX_QUANTILES(pctGoodLCP, 1000)[OFFSET(percentile * 10)] AS pctGoodLCP,
APPROX_QUANTILES(p75LCP, 1000)[OFFSET(percentile * 10)] AS p75LCP
FROM (
SELECT
_TABLE_SUFFIX,
url,
IF(IFNULL(REGEXP_CONTAINS(JSON_EXTRACT(payload, "$['_img-loading-attr']"), r'(?i)\blazy\b'), FALSE), 'Yes', 'No') AS usesLazyLoading,
JSON_QUERY(payload, "$._CrUX.metrics.largest_contentful_paint.histogram[0].density") AS pctGoodLCP,
JSON_QUERY(payload, "$._CrUX.metrics.largest_contentful_paint.percentiles.p75") AS p75LCP
FROM
`httparchive.pages.2021_06_01_*`),
UNNEST([10, 25, 50, 75, 90]) AS percentile
GROUP BY
client,
percentile,
usesLazyLoading
ORDER BY
client,
percentile,
usesLazyLoading
Raw
lazy-loading-wp-cms-timeseries.sql
# Timeseries of lazy loading adoption on Wordpress, other CMSs, and non-CMS websites.
SELECT
CONCAT('202', REGEXP_REPLACE(SUBSTR(_TABLE_SUFFIX, 0, 7), r'_', r'-')) AS date,
IF(ENDS_WITH(_TABLE_SUFFIX, 'desktop'), 'desktop', 'mobile') AS client,
COUNT(0) AS total,
COUNTIF(NOT usesLazyLoading) AS notUsingLazyLoading,
COUNTIF(usesLazyLoading) AS usesLazyLoading,
COUNTIF(usesLazyLoading AND isWordPress) AS usesLazyLoadingWordPress,
COUNTIF(usesLazyLoading AND isCMS AND NOT isWordPress) AS usesLazyLoadingOtherCMS
FROM (
SELECT
_TABLE_SUFFIX,
url,
IFNULL(REGEXP_CONTAINS(JSON_QUERY(payload, '$."_img-loading-attr"'), r'(?i)\blazy\b'), FALSE) AS usesLazyLoading
FROM
`httparchive.pages.202*`)
LEFT JOIN (
SELECT
_TABLE_SUFFIX,
url,
COUNTIF(category = 'CMS') > 0 AS isCMS,
COUNTIF(app = 'WordPress') > 0 AS isWordPress
FROM
`httparchive.technologies.202*`
GROUP BY
1, 2)
USING (url, _TABLE_SUFFIX)
GROUP BY
date,
client
ORDER BY
date,
client
Raw
lazy-loading-wp-cms.sql
# Adoption of native image lazy loading broken down by WordPress, other CMSs, and non-CMS websites.
SELECT
_TABLE_SUFFIX AS client,
COUNTIF(usesLazyLoading AND isWordPress) / COUNTIF(usesLazyLoading) AS pctWordPressLazyLoading,
COUNTIF(usesLazyLoading AND isCMS AND NOT isWordPress) / COUNTIF(usesLazyLoading) AS pctOtherCMSLazyLoading,
COUNTIF(usesLazyLoading AND NOT isCMS) / COUNTIF(usesLazyLoading) AS pctNonCMSLazyLoading
FROM (
SELECT
_TABLE_SUFFIX,
url,
IFNULL(REGEXP_CONTAINS(JSON_QUERY(payload, '$."_img-loading-attr"'), r'(?i)\blazy\b'), FALSE) AS usesLazyLoading
FROM
`httparchive.pages.2021_06_01_*`)
JOIN (
SELECT
_TABLE_SUFFIX,
url,
COUNTIF(category = 'CMS') > 0 AS isCMS,
COUNTIF(app = 'WordPress') > 0 AS isWordPress
FROM
`httparchive.technologies.2021_06_01_*`
GROUP BY
1, 2)
USING (url, _TABLE_SUFFIX)
GROUP BY
client
Raw
lcp-lazy-loaded.js
// Snippet to warn when the LCP element was lazy loaded.
new PerformanceObserver((entryList) => {
const entries = entryList.getEntries();
const latestEntry = entries[entries.length - 1];
if (latestEntry?.element?.getAttribute('loading') == 'lazy') {
console.warn('Warning: LCP element was lazy loaded', latestEntry);
}
}).observe({type: 'largest-contentful-paint', buffered: true});
@rviscomi
Copy link
Author

rviscomi commented Jul 12, 2021 •
edited

Results:

client WordPress Other Non-CMS
mobile 84.13% 2.33% 13.53%
desktop 83.20% 2.52% 14.29%

All pages:

client percentile usesLazyLoading pages pagesWithCrUXData pctGoodLCP p75LCP
desktop 10 No 5,317,825 1,857,697 38% 1,433
desktop 10 Yes 1,113,071 347,128 26% 1,660
desktop 25 No 5,317,825 1,857,697 57% 1,902
desktop 25 Yes 1,113,071 347,128 44% 2,222
desktop 50 No 5,317,825 1,857,697 75% 2,645
desktop 50 Yes 1,113,071 347,128 65% 3,102
desktop 75 No 5,317,825 1,857,697 87% 3,791
desktop 75 Yes 1,113,071 347,128 80% 4,383
desktop 90 No 5,317,825 1,857,697 94% 5,519
desktop 90 Yes 1,113,071 347,128 89% 6,002
mobile 10 No 6,168,650 2,150,466 28% 1,654
mobile 10 Yes 1,278,977 405,446 19% 1,955
mobile 25 No 6,168,650 2,150,466 47% 2,165
mobile 25 Yes 1,278,977 405,446 34% 2,592
mobile 50 No 6,168,650 2,150,466 68% 2,922
mobile 50 Yes 1,278,977 405,446 54% 3,546
mobile 75 No 6,168,650 2,150,466 83% 4,008
mobile 75 Yes 1,278,977 405,446 73% 4,908
mobile 90 No 6,168,650 2,150,466 91% 5,447
mobile 90 Yes 1,278,977 405,446 85% 6,501

WordPress:

client percentile usesLazyLoading pages pagesWithCrUXData pctGoodLCP p75LCP
desktop 10 No 1,180,825 370,676 26% 1,673
desktop 10 Yes 925,294 270,111 23% 1,789
desktop 25 No 1,180,825 370,676 44% 2,234
desktop 25 Yes 925,294 270,111 40% 2,394
desktop 50 No 1,180,825 370,676 65% 3,118
desktop 50 Yes 925,294 270,111 60% 3,336
desktop 75 No 1,180,825 370,676 80% 4,381
desktop 75 Yes 925,294 270,111 77% 4,652
desktop 90 No 1,180,825 370,676 89% 5,971
desktop 90 Yes 925,294 270,111 87% 6,247
mobile 10 No 1,408,618 448,269 20% 1,976
mobile 10 Yes 1,073,562 323,137 17% 2,081
mobile 25 No 1,408,618 448,269 35% 2,579
mobile 25 Yes 1,073,562 323,137 31% 2,751
mobile 50 No 1,408,618 448,269 55% 3,495
mobile 50 Yes 1,073,562 323,137 50% 3,768
mobile 75 No 1,408,618 448,269 74% 4,795
mobile 75 Yes 1,073,562 323,137 69% 5,176
mobile 90 No 1,408,618 448,269 85% 6,332
mobile 90 Yes 1,073,562 323,137 82% 6,753

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment