Web Vitals (P75) / Shopify

shopify-renderer.sql

SELECT count(renderer) FROM (
SELECT
  url,
  CASE
    WHEN JSON_EXTRACT(payload, '$._headers.response') LIKE '%X-Storefront-Renderer-Rendered: 1%' THEN 'new'
  ELSE
  'old'
END
  AS renderer
FROM
  `httparchive.requests.2020_06_01_mobile`
WHERE
  page = url
  AND page IN (
  SELECT
    url
  FROM
    `httparchive.technologies.2020_06_01_mobile`
  WHERE
    app = 'Shopify' )
 
 ) GROUP BY renderer

shopify-vitals.sql

SELECT
  origin,
  p75_ttfb,
  p75_fcp,
  p75_lcp,
  p75_fid,
  p75_cls,
FROM
  `chrome-ux-report.materialized.metrics_summary`
WHERE
  date = '2020-06-01'
  AND origin IN (
  SELECT
    RTRIM(url, '/')
  FROM
    `httparchive.technologies.2020_06_01_mobile`
  WHERE
    app = 'Shopify' )