elementor-features.sql

CREATE TEMP FUNCTION IS_GOOD(good FLOAT64, needs_improvement FLOAT64, poor FLOAT64)
RETURNS BOOL
AS (
  good / (good + needs_improvement + poor) >= 0.75
);

CREATE TEMP FUNCTION IS_NON_ZERO(good FLOAT64, needs_improvement FLOAT64, poor FLOAT64)
RETURNS BOOL
AS (
  good + needs_improvement + poor > 0
);

CREATE TEMP FUNCTION getFeatures(payload STRING)
RETURNS STRING
LANGUAGE js
AS '''
  try {
    var $ = JSON.parse(payload);
    var almanac = JSON.parse($._almanac);
    var generators = almanac['meta-nodes'].nodes.find(node => node.name == 'generator' && node.content.startsWith("Elementor"));
    if ( generators.length == 0 ) {
      return "";
    }
    var content =  generators.content;
    return content;
  } catch (e) {
    return "";
  }
''';

WITH
  elementorURLFeatures
  AS (
    SELECT
      url,
      feature
    FROM
      (
        SELECT
          url,
          REGEXP_EXTRACT_ALL(
            REGEXP_EXTRACT(getFeatures(payload), '; features: (.+);'), '([A-Za-z0-9_-]+)')
            AS features
        FROM
          `httparchive.pages.2023_03_01_*`
        JOIN
          (
            SELECT DISTINCT url
            FROM
              `httparchive.technologies.2023_03_01_*`
            WHERE
              app = 'Elementor'
              AND info != ''
          )
          USING (url)
      ),
          UNNEST(features) AS feature
  ),
  cwvs AS (
    SELECT
      date,
      CONCAT(origin, '/') AS origin,
      IF(device = 'desktop', 'desktop', 'mobile') AS client,
      IS_NON_ZERO(fast_fid, avg_fid, slow_fid) AS any_fid,
      IS_GOOD(fast_fid, avg_fid, slow_fid) AS good_fid,
      IS_NON_ZERO(small_cls, medium_cls, large_cls) AS any_cls,
      IS_GOOD(small_cls, medium_cls, large_cls) AS good_cls,
      IS_NON_ZERO(fast_lcp, avg_lcp, slow_lcp) AS any_lcp,
      IS_GOOD(fast_lcp, avg_lcp, slow_lcp) AS good_lcp,
      (IS_GOOD(fast_fid, avg_fid, slow_fid) OR fast_fid IS NULL)
        AND IS_GOOD(small_cls, medium_cls, large_cls)
        AND IS_GOOD(fast_lcp, avg_lcp, slow_lcp) AS good_cwv
    FROM
      `chrome-ux-report.materialized.device_summary`
    WHERE
      date = '2023-03-01'
      AND device IN ('desktop', 'tablet', 'phone')
      AND CONCAT(origin, '/')
        IN (
          SELECT url
          FROM elementorURLFeatures
        )
  )
SELECT
  client,
  COUNT(DISTINCT origin) AS origins,
  COUNT(DISTINCT IF(good_fid, origin, NULL)) AS origins_with_good_fid,
  COUNT(DISTINCT IF(good_cls, origin, NULL)) AS origins_with_good_cls,
  COUNT(DISTINCT IF(good_lcp, origin, NULL)) AS origins_with_good_lcp,
  COUNT(DISTINCT IF(any_fid, origin, NULL)) AS origins_with_any_fid,
  COUNT(DISTINCT IF(any_cls, origin, NULL)) AS origins_with_any_cls,
  COUNT(DISTINCT IF(any_lcp, origin, NULL)) AS origins_with_any_lcp,
  COUNT(DISTINCT IF(good_cwv, origin, NULL)) AS origins_with_good_cwv,
  COUNT(DISTINCT IF(any_lcp AND any_cls, origin, NULL)) AS origins_eligible_for_cwv,
  SAFE_DIVIDE(COUNTIF(good_cwv), COUNTIF(any_lcp AND any_cls))
    AS pct_eligible_origins_with_good_cwv,
  feature
FROM
  (
    SELECT *
    FROM
      cwvs
    JOIN
      elementorURLFeatures
      ON cwvs.origin = elementorURLFeatures.url
  )
GROUP BY
  client,
  feature