Step_funnel_olivopro

WITH
Step1 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`
  WHERE event_name = 'page_view'
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
),

Step2 AS (
  SELECT DISTINCT a.user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(a.event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(a.event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(a.event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*` a
  JOIN (
    SELECT user_pseudo_id, MIN(event_timestamp) AS first_impression_timestamp
    FROM `olivo-pro.analytics_352085723.events_2024*`
    WHERE event_name = 'page_view'
      AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
           REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
    GROUP BY user_pseudo_id
  ) b ON a.user_pseudo_id = b.user_pseudo_id
  WHERE a.event_timestamp >= b.first_impression_timestamp + 15000
  GROUP BY a.user_pseudo_id
),

Step3 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`,
  UNNEST(event_params) AS params
  WHERE event_name = 'click_config'
    AND params.key = 'link_classes'
    AND REGEXP_CONTAINS(params.value.string_value, 'wpforms-screen-reader-element')
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
  HAVING COUNT(event_name) >= 2
),
Step4 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`,
  UNNEST(event_params) AS params
  WHERE event_name = 'click_config'
    AND params.key = 'link_classes'
    AND REGEXP_CONTAINS(params.value.string_value, 'wpforms-screen-reader-element')
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
  HAVING COUNT(event_name) >= 4
),
Step5 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`,
  UNNEST(event_params) AS params
  WHERE event_name = 'click_config'
    AND params.key = 'link_classes'
    AND REGEXP_CONTAINS(params.value.string_value, 'wpforms-screen-reader-element')
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
  HAVING COUNT(event_name) >= 5
),
Step6 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`,
  UNNEST(event_params) AS params
  WHERE event_name = 'click_config'
    AND params.key = 'link_classes'
    AND REGEXP_CONTAINS(params.value.string_value, 'wpforms-screen-reader-element')
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
  HAVING COUNT(event_name) >= 6
),
Step7 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`,
  UNNEST(event_params) AS params
  WHERE event_name = 'click_config'
    AND params.key = 'link_classes'
    AND REGEXP_CONTAINS(params.value.string_value, 'wpforms-screen-reader-element')
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
  HAVING COUNT(event_name) >= 7
),
Step8 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`,
  UNNEST(event_params) AS params
  WHERE event_name = 'click_config'
    AND params.key = 'link_classes'
    AND REGEXP_CONTAINS(params.value.string_value, 'wpforms-screen-reader-element')
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
  HAVING COUNT(event_name) >= 8
),

Step9 AS (
  SELECT DISTINCT user_pseudo_id, DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS event_date,
    CASE
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer'), ''), r'^https://olivo.pro/form-zerbino/')) THEN 'form-zerbino'
      WHEN MAX(REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/')) THEN 'crea-zerbino'
    END AS page_tag
  FROM `olivo-pro.analytics_352085723.events_2024*`
  WHERE event_name = 'generate_lead'
    AND (REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer'), ''), r'^https://olivo.pro/form-zerbino/') OR
         REGEXP_CONTAINS(ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer'), ''), r'^https://olivo.pro/crea-il-tuo-zerbino-personalizzato-olivo-pro/'))
  GROUP BY user_pseudo_id
)

SELECT
  event_date,
  page_tag,
  SUM(CASE WHEN step_index = 1 THEN 1 ELSE 0 END) AS impression_lead_count,
  SUM(CASE WHEN step_index = 2 THEN 1 ELSE 0 END) AS aware_lead_count,
  SUM(CASE WHEN step_index = 3 THEN 1 ELSE 0 END) AS engaged_lead_count3,
  SUM(CASE WHEN step_index = 4 THEN 1 ELSE 0 END) AS engaged_lead_count4,
  SUM(CASE WHEN step_index = 5 THEN 1 ELSE 0 END) AS engaged_lead_count5,
  SUM(CASE WHEN step_index = 6 THEN 1 ELSE 0 END) AS engaged_lead_count6,
  SUM(CASE WHEN step_index = 7 THEN 1 ELSE 0 END) AS engaged_lead_count7,
  SUM(CASE WHEN step_index = 8 THEN 1 ELSE 0 END) AS engaged_lead_count8,
  SUM(CASE WHEN step_index = 9 THEN 1 ELSE 0 END) AS generate_lead_config_count
FROM (
  SELECT user_pseudo_id, event_date, page_tag, 1 AS step_index FROM Step1
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 2 AS step_index FROM Step2
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 3 AS step_index FROM Step3
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 4 AS step_index FROM Step4
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 5 AS step_index FROM Step5
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 6 AS step_index FROM Step6
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 7 AS step_index FROM Step7
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 8 AS step_index FROM Step8
  UNION ALL
  SELECT user_pseudo_id, event_date, page_tag, 9 AS step_index FROM Step9
)
GROUP BY event_date, page_tag
ORDER BY event_date, page_tag;