Autocomplete Click-Through Rate

{
  "event": [
    {
      "eventInfo": {
        "eventName": "view_item_list",
        "eventAction": "Search Suggestion Panel Impression",
        "type": "other"
      },
      "page": {
        "pageInfo": {
          "pageType": "search"
        }
      },
      "listing": {
        "itemListId": "search_suggestions",
        "itemListName": "Search Suggestions",
        "trafficType": "external"
      },
      "search": {
        "searchTerm": "running shoes",
        "suggestionCount": 8
      },
      "product": [
        {
          "productInfo": {
            "productID": "sugg_query_running-shoes",
            "productName": "running shoes"
          },
          "attributes": {
            "itemListId": "search_suggestions",
            "itemListName": "Search Suggestions",
            "listPosition": 1,
            "itemCategory": "query"
          }
        },
        {
          "productInfo": {
            "productID": "sugg_product_12345",
            "productName": "Nike Running Shoes"
          },
          "attributes": {
            "itemListId": "search_suggestions",
            "itemListName": "Search Suggestions",
            "listPosition": 2,
            "itemCategory": "product"
          }
        }
      ],
      "trigger": "Fires once per suggestion-panel open (impression); not re-fired on keystroke refreshes of the same panel."
    },
    {
      "eventInfo": {
        "eventName": "select_item",
        "eventAction": "Search Suggestion Click",
        "type": "other"
      },
      "page": {
        "pageInfo": {
          "pageType": "search"
        }
      },
      "listing": {
        "itemListId": "search_suggestions",
        "itemListName": "Search Suggestions",
        "trafficType": "external"
      },
      "search": {
        "searchTerm": "running shoes"
      },
      "product": [
        {
          "productInfo": {
            "productID": "sugg_product_12345",
            "productName": "Nike Running Shoes"
          },
          "attributes": {
            "itemListId": "search_suggestions",
            "itemListName": "Search Suggestions",
            "listPosition": 2,
            "itemCategory": "product"
          }
        }
      ],
      "trigger": "Fires on a suggestion click, or on Enter while a suggestion is keyboard-highlighted; never fires on Enter submitting typed text with no suggestion highlighted."
    }
  ]
}

{
  "view_item_list": {
    "event": "view_item_list",
    "item_list_id": "search_suggestions",
    "item_list_name": "Search Suggestions",
    "page_type": "search",
    "traffic_type": "external",
    "search_term": "running shoes",
    "suggestion_count": 8,
    "items": [
      {
        "item_id": "sugg_query_running-shoes",
        "item_name": "running shoes",
        "item_category": "query",
        "index": 1,
        "item_list_id": "search_suggestions",
        "item_list_name": "Search Suggestions"
      },
      {
        "item_id": "sugg_product_12345",
        "item_name": "Nike Running Shoes",
        "item_category": "product",
        "index": 2,
        "item_list_id": "search_suggestions",
        "item_list_name": "Search Suggestions"
      }
    ],
    "trigger": "Fires once per suggestion-panel open (impression); not re-fired on keystroke refreshes of the same panel."
  },
  "select_item": {
    "event": "select_item",
    "item_list_id": "search_suggestions",
    "item_list_name": "Search Suggestions",
    "page_type": "search",
    "traffic_type": "external",
    "search_term": "running shoes",
    "items": [
      {
        "item_id": "sugg_product_12345",
        "item_name": "Nike Running Shoes",
        "item_category": "product",
        "index": 2,
        "item_list_id": "search_suggestions",
        "item_list_name": "Search Suggestions"
      }
    ],
    "trigger": "Fires on a suggestion click, or on Enter while a suggestion is keyboard-highlighted; never fires on Enter submitting typed text with no suggestion highlighted."
  }
}

{
  "view_item_list": {
    "event": "view_item_list",
    "item_list_id": "search_suggestions",
    "item_list_name": "Search Suggestions",
    "page_type": "search",
    "traffic_type": "external",
    "search_term": "running shoes",
    "suggestion_count": 8,
    "items": [
      {
        "item_id": "sugg_query_running-shoes",
        "item_name": "running shoes",
        "item_category": "query",
        "index": 1
      },
      {
        "item_id": "sugg_product_12345",
        "item_name": "Nike Running Shoes",
        "item_category": "product",
        "index": 2
      }
    ]
  },
  "select_item": {
    "event": "select_item",
    "item_list_id": "search_suggestions",
    "item_list_name": "Search Suggestions",
    "page_type": "search",
    "traffic_type": "external",
    "search_term": "running shoes",
    "items": [
      {
        "item_id": "sugg_product_12345",
        "item_name": "Nike Running Shoes",
        "item_category": "product",
        "index": 2
      }
    ],
    "trigger": "Fires on a suggestion click, or on Enter while a suggestion is keyboard-highlighted; never fires on Enter submitting typed text with no suggestion highlighted."
  }
}

{
  "view_item_list": {
    "eventType": "commerce.productListViews",
    "commerce": {
      "productListViews": {
        "value": 1
      }
    },
    "productListItems": [
      {
        "SKU": "items[].item_id",
        "name": "items[].item_name",
        "_tenant": {
          "itemCategory": "items[].item_category"
        }
      }
    ],
    "web": {
      "webPageDetails": {
        "pageType": "page_type"
      }
    },
    "_tenant": {
      "item_list_id": "item_list_id",
      "item_list_name": "item_list_name",
      "traffic_type": "traffic_type"
    },
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar1": "search_term",
            "eVar25": "item_list_id",
            "eVar26": "item_list_name"
          }
        }
      }
    },
    "trigger": "Fires once per suggestion-panel open (impression); not re-fired on keystroke refreshes of the same panel."
  },
  "select_item": {
    "eventType": "web.webinteraction.linkClicks",
    "productListItems": [
      {
        "SKU": "items[].item_id",
        "name": "items[].item_name",
        "_tenant": {
          "itemCategory": "items[].item_category"
        }
      }
    ],
    "web": {
      "webPageDetails": {
        "pageType": "page_type"
      },
      "webInteraction": {
        "name": "select_item",
        "type": "other",
        "linkClicks": {
          "value": 1
        }
      }
    },
    "_tenant": {
      "item_list_id": "item_list_id",
      "item_list_name": "item_list_name",
      "traffic_type": "traffic_type"
    },
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar1": "search_term",
            "eVar25": "item_list_id",
            "eVar26": "item_list_name"
          }
        }
      }
    },
    "trigger": "Fires on a suggestion click, or on Enter while a suggestion is keyboard-highlighted; never fires on Enter submitting typed text with no suggestion highlighted."
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Panel impressions are read from view_item_list (item_list_id 'search_suggestions'); with no interaction id, each impression opens an interaction that ends at the next one.
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Panel impressions showing at least one suggestion with page context, plus suggestion clicks; bot and internal traffic excluded
WITH hits AS (
  SELECT
    user_pseudo_id,
    device.category AS device_category,
    event_timestamp,
    event_name,
    IF(event_name = 'select_item', items[SAFE_OFFSET(0)].item_category, NULL) AS suggestion_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 (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_list_id') = 'search_suggestions'
    AND IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type'), '') NOT IN ('internal', 'bot')
    AND (
      (event_name = 'view_item_list'
        AND ARRAY_LENGTH(items) > 0
        AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_type') IS NOT NULL)
      OR event_name = 'select_item'
    )
),
-- Each panel impression is one interaction, lasting until the visitor's next impression (a reopened panel starts a new one)
interactions AS (
  SELECT
    user_pseudo_id,
    device_category,
    event_timestamp AS open_ts,
    LEAD(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_open_ts
  FROM hits
  WHERE event_name = 'view_item_list'
),
-- Flag each interaction once for any suggestion click, and separately for query and product suggestion clicks
flagged AS (
  SELECT
    i.user_pseudo_id,
    i.open_ts,
    i.device_category,
    COUNT(c.event_timestamp) > 0 AS clicked,
    COUNTIF(c.suggestion_type = 'query') > 0 AS clicked_query,
    COUNTIF(c.suggestion_type = 'product') > 0 AS clicked_product
  FROM interactions AS i
  LEFT JOIN hits AS c
    ON c.event_name = 'select_item'
    AND c.user_pseudo_id = i.user_pseudo_id
    AND c.event_timestamp >= i.open_ts
    AND (i.next_open_ts IS NULL OR c.event_timestamp < i.next_open_ts)
  GROUP BY i.user_pseudo_id, i.open_ts, i.device_category
)
-- Interactions with a suggestion click over all interactions with suggestions displayed, by device
SELECT
  device_category,
  COUNTIF(clicked) AS interactions_with_click,
  COUNT(*) AS interactions_with_suggestions,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(clicked), COUNT(*)), 2) AS autocomplete_ctr,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(clicked_query), COUNT(*)), 2) AS query_suggestion_ctr,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(clicked_product), COUNT(*)), 2) AS product_suggestion_ctr
FROM flagged
GROUP BY device_category
ORDER BY interactions_with_suggestions DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Panel impressions are read from commerce.productListViews (_tenant.item_list_id 'search_suggestions'); with no interaction id, each impression opens an interaction that ends at the next one.
-- Panel impressions showing at least one suggestion with page context, plus suggestion clicks, 90 days back to yesterday; bot and internal traffic excluded
WITH hits AS (
  SELECT
    endUserIDs._experience.ecid.id AS visitor_id,
    device.type AS device_category,
    timestamp AS event_ts,
    eventType,
    CASE WHEN eventType = 'web.webinteraction.linkClicks' THEN productListItems[0]._tenant.itemCategory END AS suggestion_type
  FROM aep_web_experience_event_dataset
  WHERE timestamp >= DATE_SUB(CURRENT_DATE, 90)
    AND timestamp < CURRENT_DATE
    AND _tenant.item_list_id = 'search_suggestions'
    AND COALESCE(_tenant.traffic_type, '') NOT IN ('internal', 'bot')
    AND (
      (eventType = 'commerce.productListViews'
        AND SIZE(productListItems) > 0
        AND web.webPageDetails.pageType IS NOT NULL)
      OR (eventType = 'web.webinteraction.linkClicks'
        AND web.webInteraction.name = 'select_item')
    )
),
-- Each panel impression is one interaction, lasting until the visitor's next impression (a reopened panel starts a new one)
interactions AS (
  SELECT
    visitor_id,
    device_category,
    event_ts AS open_ts,
    LEAD(event_ts) OVER (PARTITION BY visitor_id ORDER BY event_ts) AS next_open_ts
  FROM hits
  WHERE eventType = 'commerce.productListViews'
),
-- Flag each interaction once for any suggestion click, and separately for query and product suggestion clicks
flagged AS (
  SELECT
    i.visitor_id,
    i.open_ts,
    i.device_category,
    COUNT(c.event_ts) > 0 AS clicked,
    COUNT(CASE WHEN c.suggestion_type = 'query' THEN 1 END) > 0 AS clicked_query,
    COUNT(CASE WHEN c.suggestion_type = 'product' THEN 1 END) > 0 AS clicked_product
  FROM interactions AS i
  LEFT JOIN hits AS c
    ON c.eventType = 'web.webinteraction.linkClicks'
    AND c.visitor_id = i.visitor_id
    AND c.event_ts >= i.open_ts
    AND (i.next_open_ts IS NULL OR c.event_ts < i.next_open_ts)
  GROUP BY i.visitor_id, i.open_ts, i.device_category
)
-- Interactions with a suggestion click over all interactions with suggestions displayed, by device
SELECT
  device_category,
  COUNT(CASE WHEN clicked THEN 1 END) AS interactions_with_click,
  COUNT(*) AS interactions_with_suggestions,
  ROUND(100.0 * COUNT(CASE WHEN clicked THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS autocomplete_ctr,
  ROUND(100.0 * COUNT(CASE WHEN clicked_query THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS query_suggestion_ctr,
  ROUND(100.0 * COUNT(CASE WHEN clicked_product THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS product_suggestion_ctr
FROM flagged
GROUP BY device_category
ORDER BY interactions_with_suggestions DESC;