MarquinSmith

-- Description
-- Count orders in Adobe Analytics click stream data
-- BigQuery specific code

-- function to detect the presence of event '1' in event list string
CREATE TEMP FUNCTION purchase_in_string(x STRING)
RETURNS STRING
LANGUAGE js AS r"""
  if (x.split(',').includes('1')) {
      return 1;
  }else {
  return 0;
  };
""";


SELECT
    DATE(date_time) AS date,
    SUM(CAST(purchase_in_string(post_event_list) AS INT64)) AS orders
FROM `adobe_click_stream_table`
-- only look at data for June 2022
WHERE DATE(date_time) >= '2022-06-01'
           AND DATE(date_time) < "2022-07-01"

GROUP BY
    date
ORDER BY
    date ASC