Filter Applications

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageName": "category:womens-running-shoes",
        "pageType": "category"
      },
      "category": {
        "primaryCategory": "Shoes/Running"
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "filter_apply",
          "eventAction": "apply",
          "type": "filter"
        },
        "category": {
          "primaryCategory": "product discovery"
        },
        "attributes": {
          "filterName": "size",
          "filterValue": "8|8.5",
          "filterAction": "apply",
          "itemListId": "plp_womens_running_shoes",
          "itemListName": "Category Grid - Womens Running Shoes",
          "searchTerm": null,
          "searchResultsCount": 37
        }
      }
    ]
  }
}

{
  "event": "filter_apply",
  "filter_name": "size",
  "filter_value": "8|8.5",
  "filter_action": "apply",
  "item_list_id": "plp_womens_running_shoes",
  "item_list_name": "Category Grid - Womens Running Shoes",
  "page_type": "category",
  "search_term": null,
  "search_results_count": 37
}

{
  "event": "filter_apply",
  "eventInfo": {
    "eventName": "filter_apply",
    "eventAction": "apply"
  },
  "filter": {
    "name": "size",
    "value": "8|8.5",
    "action": "apply"
  },
  "ecommerce": {
    "itemListId": "plp_womens_running_shoes",
    "itemListName": "Category Grid - Womens Running Shoes"
  },
  "search": {
    "term": null,
    "resultsCount": 37
  },
  "page": {
    "pageInfo": {
      "pageName": "category:womens-running-shoes",
      "pageType": "category"
    }
  }
}

{
  "eventType": "web.webinteraction.linkClicks",
  "web": {
    "webPageDetails": {
      "name": "category:womens-running-shoes",
      "siteSection": "category"
    },
    "webInteraction": {
      "name": "filter_apply",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  },
  "_tenant": {
    "productFilter": {
      "filterName": "size",
      "filterValue": "8|8.5",
      "filterAction": "apply",
      "itemListId": "plp_womens_running_shoes",
      "itemListName": "Category Grid - Womens Running Shoes",
      "searchTerm": null,
      "resultsCount": 37
    },
    "trafficType": "external"
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar20": "plp_womens_running_shoes",
          "eVar21": "Category Grid - Womens Running Shoes",
          "eVar23": "size",
          "eVar24": "8|8.5",
          "eVar25": "apply"
        },
        "props": {
          "prop2": "37"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Filter Applications: one count per committed filter change (filter_apply whose filter_action contains 'apply'; a mixed add/remove batch counts once).
-- Window: 30 complete days ending 4 days ago, so every day has cleared the T+3 late-hit window; event_date is already in the property reporting time zone.
-- Traffic exclusions mirror Item List Views; the export holds observed events only and known bots are excluded by GA4 at collection.
-- Pre-filtered landings: a filter_apply on the unchanged session landing URL whose query string already carries that filter_name is not a shopper application.
WITH raw_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    e.event_name,
    e.event_timestamp,
    e.user_pseudo_id,
    e.device.category AS device_category,
    LOWER(e.device.web_info.hostname) AS hostname,
    (
      SELECT AS STRUCT
        MAX(IF(p.key = 'ga_session_id', p.value.int_value, NULL)) AS ga_session_id,
        MAX(IF(p.key = 'page_location', p.value.string_value, NULL)) AS page_location,
        MAX(IF(p.key = 'traffic_type', p.value.string_value, NULL)) AS traffic_type,
        MAX(IF(p.key = 'debug_mode', COALESCE(CAST(p.value.int_value AS STRING), p.value.string_value), NULL)) AS debug_mode,
        MAX(IF(p.key = 'filter_name', p.value.string_value, NULL)) AS filter_name,
        MAX(IF(p.key = 'filter_value', COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING), CAST(p.value.double_value AS STRING)), NULL)) AS filter_value,
        MAX(IF(p.key = 'filter_action', p.value.string_value, NULL)) AS filter_action,
        MAX(IF(p.key = 'item_list_id', p.value.string_value, NULL)) AS item_list_id,
        MAX(IF(p.key = 'item_list_name', p.value.string_value, NULL)) AS item_list_name,
        MAX(IF(p.key = 'page_type', p.value.string_value, NULL)) AS page_type,
        MAX(IF(p.key = 'search_term', p.value.string_value, NULL)) AS search_term,
        MAX(IF(p.key = 'search_results_count', COALESCE(CAST(p.value.int_value AS STRING), CAST(p.value.double_value AS STRING), CAST(p.value.float_value AS STRING), p.value.string_value), NULL)) AS search_results_count_raw
      FROM UNNEST(e.event_params) AS p
    ) AS ep
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 34 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 4 DAY))
    AND e.event_name IN ('page_view', 'filter_apply')
),
qualified AS (
  SELECT
    event_date,
    event_name,
    event_timestamp,
    user_pseudo_id,
    ep.ga_session_id,
    COALESCE(device_category, '(not set)') AS device_category,
    ep.page_location,
    ep.filter_name,
    ep.filter_value,
    ep.filter_action,
    ep.item_list_id,
    ep.item_list_name,
    ep.page_type,
    ep.search_term,
    ep.search_results_count_raw
  FROM raw_events
  WHERE COALESCE(ep.traffic_type, '') NOT IN ('internal', 'developer')
    AND COALESCE(ep.debug_mode, '0') IN ('0', 'false')
    AND NOT REGEXP_CONTAINS(COALESCE(hostname, ''), r'localhost|(^|[.])(staging|stage|dev|qa|uat|test)[.]')
),
session_landings AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    ARRAY_AGG(page_location IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[SAFE_OFFSET(0)] AS landing_page_location
  FROM qualified
  WHERE event_name = 'page_view'
    AND user_pseudo_id IS NOT NULL
    AND ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
),
applications AS (
  SELECT
    q.event_date,
    q.event_timestamp,
    q.user_pseudo_id,
    q.ga_session_id,
    q.device_category,
    COALESCE(q.filter_name, '(not set)') AS filter_name,
    COALESCE(q.filter_value, '(not set)') AS filter_value,
    COALESCE(q.item_list_id, '(not set)') AS item_list_id,
    COALESCE(q.item_list_name, '(not set)') AS item_list_name,
    CASE
      WHEN REGEXP_CONTAINS(LOWER(COALESCE(q.page_type, '')), r'search')
        OR NULLIF(TRIM(q.search_term), '') IS NOT NULL
        OR REGEXP_CONTAINS(LOWER(CONCAT(COALESCE(q.item_list_id, ''), ' ', COALESCE(q.item_list_name, ''))), r'search') THEN 'Search Results'
      WHEN REGEXP_CONTAINS(LOWER(COALESCE(q.page_type, '')), r'category|collection|listing|plp')
        OR COALESCE(q.item_list_id, q.item_list_name) IS NOT NULL THEN 'Product Listing'
      ELSE '(not set)'
    END AS page_type,
    SAFE_CAST(SAFE_CAST(q.search_results_count_raw AS FLOAT64) AS INT64) AS search_results_count,
    SAFE_CAST(q.search_results_count_raw AS FLOAT64) IS NULL AS results_count_invalid,
    COALESCE(
      q.page_location = l.landing_page_location
      AND CONCAT('&', LOWER(COALESCE(REGEXP_EXTRACT(l.landing_page_location, r'[?]([^#]*)'), ''))) LIKE CONCAT('%&', LOWER(q.filter_name), '=%'),
      FALSE
    ) AS is_prefiltered_landing
  FROM qualified AS q
  LEFT JOIN session_landings AS l
    ON l.user_pseudo_id = q.user_pseudo_id
   AND l.ga_session_id = q.ga_session_id
  WHERE q.event_name = 'filter_apply'
    AND 'apply' IN UNNEST(SPLIT(REGEXP_REPLACE(LOWER(COALESCE(q.filter_action, '')), r'[ ,;]', '|'), '|'))
    AND q.event_date BETWEEN DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 33 DAY)
                         AND DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 4 DAY)
),
flagged AS (
  SELECT
    *,
    LAG(event_timestamp) OVER (
      PARTITION BY user_pseudo_id, ga_session_id, item_list_id, filter_name, filter_value, is_prefiltered_landing
      ORDER BY event_timestamp
    ) AS prev_application_timestamp
  FROM applications
),
counted AS (
  SELECT
    *,
    NOT is_prefiltered_landing
      AND (user_pseudo_id IS NULL OR prev_application_timestamp IS NULL OR event_timestamp - prev_application_timestamp > 2000000) AS is_counted
  FROM flagged
),
daily AS (
  SELECT
    event_date,
    COUNTIF(is_counted) AS daily_filter_applications,
    COUNTIF(is_counted AND page_type = '(not set)') AS daily_not_set_applications
  FROM counted
  GROUP BY event_date
)
SELECT
  c.event_date,
  c.page_type,
  c.item_list_name,
  c.filter_name,
  c.device_category,
  COUNTIF(c.is_counted) AS filter_applications,
  COUNTIF(NOT c.is_prefiltered_landing) AS apply_events_observed,
  COUNTIF(NOT c.is_prefiltered_landing AND NOT c.is_counted) AS duplicates_collapsed,
  COUNTIF(c.is_prefiltered_landing) AS prefiltered_landings_excluded,
  COUNTIF(c.is_counted AND c.search_results_count = 0) AS zero_result_applications,
  COUNTIF(c.is_counted AND c.results_count_invalid) AS invalid_results_count_applications,
  SAFE_DIVIDE(COUNTIF(c.is_counted AND c.results_count_invalid), COUNTIF(c.is_counted)) AS invalid_results_count_share,
  COUNT(DISTINCT IF(c.is_counted, CONCAT(c.user_pseudo_id, '.', CAST(c.ga_session_id AS STRING)), NULL)) AS sessions_with_filter_application,
  d.daily_filter_applications,
  SAFE_DIVIDE(d.daily_not_set_applications, d.daily_filter_applications) AS daily_not_set_share,
  CASE
    WHEN d.daily_filter_applications = 0 THEN 'NO DATA'
    WHEN SAFE_DIVIDE(d.daily_not_set_applications, d.daily_filter_applications) < 0.02 THEN 'PASS'
    ELSE 'FAIL'
  END AS not_set_check
FROM counted AS c
JOIN daily AS d
  ON d.event_date = c.event_date
GROUP BY c.event_date, c.page_type, c.item_list_name, c.filter_name, c.device_category, d.daily_filter_applications, d.daily_not_set_applications
ORDER BY c.event_date, filter_applications DESC

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Filter Applications from XDM ExperienceEvents: one count per committed filter change (web.webInteraction.name = 'filter_apply', action containing 'apply').
-- Window: 30 complete local days ending 4 days ago (T+3 late-data window); set the time zone to the reporting property zone.
-- Sessions: 30-minute inactivity timeout with no midnight split, computed over all web events before the filter selection.
-- Fields read from _tenant.productFilter with the Analytics mapping as fallback (eVar20 list id, eVar21 list name, eVar23 name, eVar24 value, eVar25 action, prop2 results count).
-- _tenant is the organisation's tenant namespace (replace with your own _<tenantId>). Traffic exclusions mirror the GA4 side and Item List Views: internal traffic (_tenant.trafficType), QA/staging/dev hostnames and bot user agents.
WITH web_events AS (
  SELECT
    COALESCE(identityMap['ECID'][0].id, endUserIDs._experience.ecid.id) AS ecid,
    timestamp AS event_ts,
    to_date(from_utc_timestamp(timestamp, 'America/New_York')) AS event_date,
    web.webInteraction.name AS interaction_name,
    CASE WHEN COALESCE(web.webPageDetails.pageViews.value, 0) > 0 THEN 1 ELSE 0 END AS is_page_view,
    web.webPageDetails.URL AS page_url,
    lower(regexp_extract(web.webPageDetails.URL, '^https?://([^/:?#]+)', 1)) AS hostname,
    lower(environment.browserDetails.userAgent) AS user_agent,
    device.type AS device_type,
    lower(_tenant.trafficType) AS traffic_type,
    web.webPageDetails.siteSection AS site_section,
    COALESCE(_tenant.productFilter.filterName, _experience.analytics.customDimensions.eVars.eVar23) AS filter_name,
    COALESCE(_tenant.productFilter.filterValue, _experience.analytics.customDimensions.eVars.eVar24) AS filter_value,
    COALESCE(_tenant.productFilter.filterAction, _experience.analytics.customDimensions.eVars.eVar25) AS filter_action,
    COALESCE(_tenant.productFilter.itemListId, _experience.analytics.customDimensions.eVars.eVar20) AS item_list_id,
    COALESCE(_tenant.productFilter.itemListName, _experience.analytics.customDimensions.eVars.eVar21) AS item_list_name,
    _tenant.productFilter.searchTerm AS search_term,
    trim(COALESCE(CAST(_tenant.productFilter.resultsCount AS STRING), _experience.analytics.customDimensions.props.prop2)) AS results_count_raw
  FROM your_aep_web_events_dataset
  WHERE timestamp >= to_timestamp(date_sub(current_date(), 35))
    AND timestamp < to_timestamp(date_sub(current_date(), 2))
),
sessionized AS (
  SELECT
    *,
    SESS_TIMEOUT(event_ts, 60 * 30) OVER (
      PARTITION BY ecid
      ORDER BY event_ts
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM web_events
  WHERE ecid IS NOT NULL
    AND COALESCE(traffic_type, '') NOT IN ('internal', 'developer')
    AND COALESCE(hostname, '') NOT RLIKE 'localhost|(^|[.])(staging|stage|dev|qa|uat|test)[.]'
    AND COALESCE(user_agent, '') NOT RLIKE '(bot|spider|crawler|headless|lighthouse)'
),
landed AS (
  SELECT
    *,
    session.num AS session_num,
    first(CASE WHEN is_page_view = 1 THEN page_url END, true) OVER (
      PARTITION BY ecid, session.num
      ORDER BY event_ts
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS landing_page_url
  FROM sessionized
),
applications AS (
  SELECT
    event_date,
    event_ts,
    ecid,
    session_num,
    COALESCE(device_type, '(not set)') AS device_type,
    COALESCE(filter_name, '(not set)') AS filter_name_key,
    COALESCE(filter_value, '(not set)') AS filter_value_key,
    COALESCE(item_list_id, '(not set)') AS item_list_id_key,
    COALESCE(item_list_name, '(not set)') AS item_list_name_key,
    CASE
      WHEN lower(COALESCE(site_section, '')) RLIKE 'search'
        OR NULLIF(trim(search_term), '') IS NOT NULL
        OR lower(concat_ws(' ', item_list_id, item_list_name)) RLIKE 'search' THEN 'Search Results'
      WHEN lower(COALESCE(site_section, '')) RLIKE 'category|collection|listing|plp'
        OR COALESCE(item_list_id, item_list_name) IS NOT NULL THEN 'Product Listing'
      ELSE '(not set)'
    END AS page_type,
    CASE WHEN results_count_raw RLIKE '^[0-9]+([.]0+)?$' THEN CAST(CAST(results_count_raw AS DOUBLE) AS BIGINT) END AS search_results_count,
    CASE WHEN COALESCE(results_count_raw, '') RLIKE '^[0-9]+([.]0+)?$' THEN 0 ELSE 1 END AS results_count_invalid,
    CASE
      WHEN page_url IS NOT NULL
        AND page_url = landing_page_url
        AND filter_name IS NOT NULL
        AND concat('&', lower(COALESCE(regexp_extract(landing_page_url, '[?]([^#]*)', 1), ''))) LIKE concat('%&', lower(filter_name), '=%')
      THEN 1 ELSE 0
    END AS is_prefiltered_landing
  FROM landed
  WHERE interaction_name = 'filter_apply'
    AND array_contains(split(regexp_replace(lower(COALESCE(filter_action, '')), '[ ,;]', '|'), '[|]'), 'apply')
    AND event_date BETWEEN date_sub(current_date(), 33) AND date_sub(current_date(), 4)
),
flagged AS (
  SELECT
    *,
    LAG(event_ts) OVER (
      PARTITION BY ecid, session_num, item_list_id_key, filter_name_key, filter_value_key, is_prefiltered_landing
      ORDER BY event_ts
    ) AS prev_application_ts
  FROM applications
),
counted AS (
  SELECT
    *,
    CASE
      WHEN is_prefiltered_landing = 0
        AND (prev_application_ts IS NULL OR CAST(event_ts AS DOUBLE) - CAST(prev_application_ts AS DOUBLE) > 2)
      THEN 1 ELSE 0
    END AS is_counted
  FROM flagged
),
daily AS (
  SELECT
    event_date,
    SUM(is_counted) AS daily_filter_applications,
    SUM(CASE WHEN is_counted = 1 AND page_type = '(not set)' THEN 1 ELSE 0 END) AS daily_not_set_applications
  FROM counted
  GROUP BY event_date
)
SELECT
  c.event_date,
  c.page_type,
  c.item_list_name_key AS item_list_name,
  c.filter_name_key AS filter_name,
  c.device_type,
  SUM(c.is_counted) AS filter_applications,
  SUM(1 - c.is_prefiltered_landing) AS apply_events_observed,
  SUM(CASE WHEN c.is_prefiltered_landing = 0 AND c.is_counted = 0 THEN 1 ELSE 0 END) AS duplicates_collapsed,
  SUM(c.is_prefiltered_landing) AS prefiltered_landings_excluded,
  SUM(CASE WHEN c.is_counted = 1 AND c.search_results_count = 0 THEN 1 ELSE 0 END) AS zero_result_applications,
  SUM(c.is_counted * c.results_count_invalid) AS invalid_results_count_applications,
  CAST(SUM(c.is_counted * c.results_count_invalid) AS DOUBLE) / NULLIF(SUM(c.is_counted), 0) AS invalid_results_count_share,
  COUNT(DISTINCT CASE WHEN c.is_counted = 1 THEN concat_ws('-', c.ecid, CAST(c.session_num AS STRING)) END) AS sessions_with_filter_application,
  d.daily_filter_applications,
  CAST(d.daily_not_set_applications AS DOUBLE) / NULLIF(d.daily_filter_applications, 0) AS daily_not_set_share,
  CASE
    WHEN d.daily_filter_applications = 0 THEN 'NO DATA'
    WHEN CAST(d.daily_not_set_applications AS DOUBLE) / d.daily_filter_applications < 0.02 THEN 'PASS'
    ELSE 'FAIL'
  END AS not_set_check
FROM counted AS c
JOIN daily AS d
  ON d.event_date = c.event_date
GROUP BY c.event_date, c.page_type, c.item_list_name_key, c.filter_name_key, c.device_type, d.daily_filter_applications, d.daily_not_set_applications
ORDER BY c.event_date, filter_applications DESC