Giftable_trigger.md

WITH
  -- userId, candidateId, attribution (eg click, purchase)
  -- candidateid is listingId https://etsy.slack.com/archives/C01T5STE7DH/p1726850552184059?thread_ts=1726850305.245089&cid=C01T5STE7DH
  attribution_tab AS (
  SELECT
    CAST(userId AS int) AS userId,
    candidateId AS listingId
  FROM
    `etsy-sr-etl-prod.etl_data.search_attribution_hourly`
  WHERE
    DATE(_PARTITIONTIME) = DATE("2025-01-20")),
  -- map gift_idea_id to listing_id
  -- https://etsy.slack.com/archives/C05NS7MEY22/p1737572529312229?thread_ts=1736876291.536729&cid=C05NS7MEY22
  all_gift_idea_deliveries AS (
  SELECT
    (
    SELECT
      value
    FROM
      UNNEST(beacon.properties.key_value)
    WHERE
      KEY = "gift_idea_id") AS gift_idea_id,
    (
    SELECT
      value
    FROM
      UNNEST(beacon.properties.key_value)
    WHERE
      KEY = "listing_ids") AS listing_ids,
  FROM
    `etsy-visit-pipe-prod.canonical.visit_id_beacons`
  WHERE
    DATE(_partitiontime) = CURRENT_DATE()
    AND beacon.event_name = "recommendations_module_delivered"
    AND ( (
      SELECT
        value
      FROM
        UNNEST(beacon.properties.key_value)
      WHERE
        KEY = "module_placement") LIKE "gift_mode_occasion_gift_idea_listings%"
      OR (
      SELECT
        value
      FROM
        UNNEST(beacon.properties.key_value)
      WHERE
        KEY = "module_placement") LIKE "gift_mode_gift_idea_listings%"
      OR (
      SELECT
        value
      FROM
        UNNEST(beacon.properties.key_value)
      WHERE
        KEY = "module_placement") LIKE "boe_gift_mode_gift_idea_listings%"
      OR (
      SELECT
        value
      FROM
        UNNEST(beacon.properties.key_value)
      WHERE
        KEY = "module_placement") LIKE "boe_gift_mode_search_listings%" ) ),
  -- map gift_idea_id to listing_id
  -- https://etsy.slack.com/archives/C05NS7MEY22/p1737572529312229?thread_ts=1736876291.536729&cid=C05NS7MEY22
  gift_to_listing_tab AS (
  SELECT
    ge.gift_idea_id,
    listing_id
  FROM
    all_gift_idea_deliveries AS delivered
  JOIN
    `etsy-data-warehouse-prod.etsy_aux.gift_mode_gift_idea_entity` ge
  ON
    delivered.gift_idea_id = CAST(ge.gift_idea_id AS STRING)  -- Cast ge.gift_idea_id to STRING
  JOIN
    UNNEST(SPLIT(delivered.listing_ids, ',')) AS listing_id  -- Split the listing_ids into separate rows
  LEFT JOIN
    `etsy-data-warehouse-prod.listing_mart.listing_titles` AS listings
  ON
    listing_id = CAST(listings.listing_id AS STRING)  -- Ensure listing_id is properly cast
  GROUP BY
    1,
    2),
  -- gift idea to occasion name
  gift_to_occasion_tab AS (
  SELECT
    gil.gift_idea_id,
    oe.occasion_id,
    oe.slug AS occasion_name,
    oe.recs_context
  FROM
    `etsy-data-warehouse-prod.etsy_aux.gift_mode_gift_idea_relation` gil
  JOIN
    `etsy-data-warehouse-prod.etsy_aux.gift_mode_occasion_entity` oe
  ON
    gil.other_id = oe.occasion_id
    AND gil.other_type = 'occasion'),
  events AS (
  SELECT
    attribution_tab.userId,
    attribution_tab.listingId,
    gift_to_listing_tab.gift_idea_id,
    gift_to_occasion_tab.occasion_name
  FROM
    attribution_tab
  INNER JOIN
    gift_to_listing_tab
  ON
    attribution_tab.listingId = gift_to_listing_tab.listing_id
  INNER JOIN
    gift_to_occasion_tab
  ON
    gift_to_occasion_tab.gift_idea_id = gift_to_listing_tab.gift_idea_id
  WHERE
    userId > 0 ),
  user_interactions_by_occasions AS (
  SELECT
    userId,
    events.occasion_name,
    COUNT(events.occasion_name) AS views_per_occasion
  FROM
    events
  WHERE
    -- exclusion list provided in https://etsy.slack.com/archives/D088PHHJL5S/p1737567374002709
    events.occasion_name NOT IN ("mothers-day",
      "valentines-day",
      "lunar-new-year",
      "eid",
      "christmas",
      "fathers-day")
  GROUP BY
    1,
    2 )
SELECT
  *
FROM
  user_interactions_by_occasions
WHERE
  views_per_occasion > 3
LIMIT
  10