pla_impact.sql

with page_catalog_events as (
  SELECT
    session_id,
    uid,
    JSON_EXTRACT_SCALAR(event_misc, "$.hid") AS phid,
    JSON_EXTRACT_SCALAR(event_misc, "$.st") AS search_term,
    locale,
    SUBSTR(locale, 4, 2) AS country_code,
    event_date,
  FROM
    `noonbitechit.admon.instant_nat_events`
  WHERE
    event_type = 'page_catalog'
    AND JSON_EXTRACT_SCALAR(event_misc, "$.plx") = 'page_catalog'
    AND event_date  BETWEEN '2024-12-01' AND  '2024-12-31' 
    -- AND event_date = current_date() - 1
    AND JSON_EXTRACT_SCALAR(event_misc, "$.mc") ='nooninstant'
    AND JSON_EXTRACT_SCALAR(event_misc, "$.st") IS NOT NULL
    AND JSON_EXTRACT_SCALAR(event_misc, "$.plx") ='page_catalog'
    AND JSON_EXTRACT_SCALAR(event_misc, "$.st") IS NOT NULL
    AND JSON_EXTRACT_SCALAR(event_misc, "$.st") != ""
),
product_impression_events as (
  SELECT 
  event_date,
  session_id,
  SPLIT(JSON_EXTRACT_SCALAR(event_misc, "$.phid"), ':')[SAFE_OFFSET(1)] AS phid,
  SUM(CASE WHEN JSON_EXTRACT_SCALAR(event_misc, "$.isAd") = "true" THEN 1 ELSE 0 END) as ad_impression_count,
  COUNT(1) AS total_product_impressions,
  FROM
  noonbitechit.admon.instant_nat_events ne
  WHERE 
  ne.event_date BETWEEN '2024-12-01' AND  '2024-12-31' 
  AND event_type = 'product_impression'
  AND SPLIT(JSON_EXTRACT_SCALAR(event_misc,"$.phid"), ':')[SAFE_OFFSET(1)] IS NOT NULL
  GROUP BY ALL
),
atc_pdp_events AS (
    SELECT
    event_date,
    session_id,
    SPLIT(JSON_EXTRACT_SCALAR(event_misc, "$.phid"), ':')[SAFE_OFFSET(1)] AS phid,
    COUNT(1) as atc_pdp_count,
  FROM
    `noonbitechit.admon.instant_nat_events`
  WHERE
    event_type IN ('add_to_cart', 'page_detail')
    -- AND event_date = current_date() - 1
    AND event_date  BETWEEN '2024-12-01' AND  '2024-12-31' 
    AND JSON_EXTRACT_SCALAR(event_misc, "$.mc") = 'nooninstant'
    AND SPLIT(JSON_EXTRACT_SCALAR(event_misc,"$.phid"), ':')[SAFE_OFFSET(1)] IS NOT NULL
    GROUP BY ALL
)

SELECT country_code, 
sum(total_searches) as total_searches,
sum(searches_with_ads) as searches_with_ads,
avg(overall_ctr) as overall_ctr,
avg(ctr_with_no_ads) as ctr_with_no_ads,
avg(ctr_with_ads_present) as ctr_with_ads_present
FROM (
SELECT 
country_code,
event_date,
search_term, 
COUNT(distinct hid) as total_searches,
SUM(CASE WHEN ad_impression_count >= 1 THEN 1 ELSE 0 END) as searches_with_ads, 
ROUND(SUM(case when atc_pdp_count >= 1 THEN 1 ELSE 0 END) * 100 / count(1), 2) as overall_ctr,
ROUND(SUM(case when ad_impression_count = 0 AND atc_pdp_count >= 1 THEN 1 ELSE 0 END) * 100 / SUM(case when ad_impression_count = 0 THEN 1 ELSE 0 END), 2) as ctr_with_no_ads,
ROUND(SAFE_DIVIDE(SUM(case when ad_impression_count >= 1 AND atc_pdp_count >= 1 THEN 1 ELSE 0 END) * 100, SUM(case when ad_impression_count >= 1 THEN 1 ELSE 0 END)) , 2) as ctr_with_ads_present,

-- ROUND(SUM(case when ad_impression_count = 0 AND atc_pdp_count >= 1 THEN 1 ELSE 0 END) / count(distinct phid), 2) as organic_ctr,
FROM (
  SELECT 
  event_date, 
  session_id,
  country_code,
  locale,
  search_term,
  uid,
  phid as hid,
  ad_impression_count,
  atc_pdp_count,
  total_product_impressions,
  FROM
  page_catalog_events 
  LEFT JOIN product_impression_events USING (event_date, session_id, phid)
  LEFT JOIN atc_pdp_events USING (event_date, session_id, phid)
) 
WHERE 1=1
AND search_term = 'water'
GROUP BY ALL
HAVING ctr_with_ads_present is not null
-- HAVING organic_ctr > 0
-- AND organic_ctr - ctr_with_ads_present > 5
-- ORDER BY organic_ctr DESC
) GROUP BY ALL
;