Store Selections

{
  "event": [
    {
      "eventInfo": {
        "eventName": "select_content"
      },
      "attributes": {
        "event_id": "evt-1",
        "content_type": "store",
        "store_id": "STORE-1",
        "store_selection_method": "store_locator",
        "fulfillment_method": "pickup",
        "page_type": "product",
        "traffic_type": "external"
      }
    }
  ]
}

{
  "event": "select_content",
  "event_id": "evt-1",
  "content_type": "store",
  "store_id": "STORE-1",
  "store_selection_method": "store_locator",
  "fulfillment_method": "pickup",
  "page_type": "product",
  "traffic_type": "external"
}

{
  "event": "select_content",
  "event_id": "evt-1",
  "content_type": "store",
  "store_id": "STORE-1",
  "store_selection_method": "store_locator",
  "fulfillment_method": "pickup",
  "page_type": "product",
  "traffic_type": "external"
}

{
  "_id": "evt-1",
  "eventType": "select_content",
  "timestamp": "2026-01-01T00:00:00Z",
  "_tenantId": {
    "storeSelection": {
      "contentType": "store",
      "storeId": "STORE-1",
      "selectionMethod": "store_locator",
      "fulfillmentMethod": "pickup",
      "pageType": "product",
      "trafficType": "external"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

WITH store_select AS (
  -- Store selection events (select_content, content_type = 'store') with their parameters pivoted from event_params
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    user_pseudo_id,
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_id') AS event_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_type') AS content_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'store_id') AS store_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'store_selection_method') AS store_selection_method,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'select_content'
),
deduped AS (
  -- Explicit selections with a store_id from external traffic, one row per event_id (re-fires count once)
  SELECT *
  FROM store_select
  WHERE content_type = 'store'
    AND store_id IS NOT NULL
    AND COALESCE(store_selection_method, '') NOT IN ('geolocation', 'profile_default')
    AND COALESCE(traffic_type, 'external') NOT IN ('internal', 'bot')
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY COALESCE(event_id, CONCAT(user_pseudo_id, '-', CAST(event_timestamp AS STRING)))
    ORDER BY event_timestamp
  ) = 1
),
sequenced AS (
  -- Store selected just before, within the same session
  SELECT
    *,
    LAG(store_id) OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) AS previous_store_id
  FROM deduped
)
-- Store selections per event date and selection method; re-confirming the store already selected in the session is not counted
SELECT
  event_date,
  store_selection_method,
  COUNT(*) AS store_selections
FROM sequenced
WHERE previous_store_id IS NULL OR previous_store_id != store_id
GROUP BY event_date, store_selection_method
ORDER BY event_date, store_selection_method;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative reporting window and reporting time zone (set reporting_tz to the property's reporting time zone)
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date, 'UTC' AS reporting_tz
),
sessionized AS (
  -- Every event per ECID in the window, sessionized with a 30-minute inactivity timeout (the XDM mapping carries no session id)
  SELECT
    to_date(from_utc_timestamp(e.timestamp, p.reporting_tz)) AS event_date,
    e.identityMap['ECID'][0].id AS ecid,
    e.timestamp,
    e._id AS event_id,
    e.eventType AS event_type,
    e._tenantId.storeSelection.contentType AS content_type,
    e._tenantId.storeSelection.storeId AS store_id,
    e._tenantId.storeSelection.selectionMethod AS store_selection_method,
    e._tenantId.storeSelection.trafficType AS traffic_type,
    SESS_TIMEOUT(e.timestamp, 60 * 30) OVER (PARTITION BY e.identityMap['ECID'][0].id ORDER BY e.timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sess
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE to_date(from_utc_timestamp(e.timestamp, p.reporting_tz)) BETWEEN p.start_date AND p.end_date
),
deduped AS (
  -- Explicit store selections with a store_id from external traffic, one row per event_id (re-fires count once)
  SELECT *
  FROM (
    SELECT s.*, ROW_NUMBER() OVER (PARTITION BY COALESCE(s.event_id, s.ecid || '-' || CAST(s.timestamp AS STRING)) ORDER BY s.timestamp) AS rn
    FROM sessionized s
    WHERE s.event_type = 'select_content'
      AND s.content_type = 'store'
      AND s.store_id IS NOT NULL
      AND COALESCE(s.store_selection_method, '') NOT IN ('geolocation', 'profile_default')
      AND COALESCE(s.traffic_type, 'external') NOT IN ('internal', 'bot')
  ) d
  WHERE rn = 1
),
sequenced AS (
  -- Store selected just before, within the same session
  SELECT
    *,
    LAG(store_id) OVER (PARTITION BY ecid, sess.num ORDER BY timestamp) AS previous_store_id
  FROM deduped
)
-- Store selections per event date and selection method; re-confirming the store already selected in the session is not counted
SELECT
  event_date,
  store_selection_method,
  COUNT(*) AS store_selections
FROM sequenced
WHERE previous_store_id IS NULL OR previous_store_id != store_id
GROUP BY event_date, store_selection_method
ORDER BY event_date, store_selection_method;