Email Clicks

-- ==========================================
-- 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);

-- Total ESP-logged clicks per click date (repeats counted), on delivered marketing sends only
SELECT
  DATE(c.click_timestamp) AS click_date,
  COUNT(*) AS email_clicks
FROM `your-project.crm.esp_clicks` AS c
JOIN `your-project.crm.esp_sends` AS s
  ON c.send_id = s.send_id
JOIN `your-project.crm.esp_campaigns` AS cp
  ON s.campaign_id = cp.campaign_id
-- Bucket by the date the click occurred, not the send date
WHERE DATE(c.click_timestamp) BETWEEN start_date AND end_date
  -- Marketing campaigns and automated flows only; transactional, order and shipping mail excluded
  AND cp.email_type = 'marketing'
  AND s.delivery_status = 'delivered'
  -- Bot and security-scanner prefetch clicks flagged by the ESP excluded
  AND COALESCE(c.is_bot_flagged, FALSE) = FALSE
  -- Unsubscribe and preference-centre links excluded
  AND c.link_type NOT IN ('unsubscribe', 'preference_center')
  -- Test and seed lists and internal recipient domains excluded
  AND s.list_type NOT IN ('test', 'seed')
  AND LOWER(s.recipient_domain) NOT IN ('yourcompany.com')
GROUP BY click_date
ORDER BY click_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window: last 90 complete days
WITH params AS (
  SELECT
    CAST(DATE_SUB(CURRENT_DATE, 90) AS DATE) AS start_date,
    CAST(DATE_SUB(CURRENT_DATE, 1) AS DATE) AS end_date
)
-- Total ESP-logged clicks per click date (repeats counted), on delivered marketing sends only
SELECT
  CAST(c.click_timestamp AS DATE) AS click_date,
  COUNT(*) AS email_clicks
FROM aep_backend_dataset_esp_clicks AS c
JOIN aep_backend_dataset_esp_sends AS s
  ON c.send_id = s.send_id
JOIN aep_backend_dataset_esp_campaigns AS cp
  ON s.campaign_id = cp.campaign_id
CROSS JOIN params AS p
-- Bucket by the date the click occurred, not the send date
WHERE CAST(c.click_timestamp AS DATE) BETWEEN p.start_date AND p.end_date
  -- Marketing campaigns and automated flows only; transactional, order and shipping mail excluded
  AND cp.email_type = 'marketing'
  AND s.delivery_status = 'delivered'
  -- Bot and security-scanner prefetch clicks flagged by the ESP excluded
  AND COALESCE(c.is_bot_flagged, FALSE) = FALSE
  -- Unsubscribe and preference-centre links excluded
  AND c.link_type NOT IN ('unsubscribe', 'preference_center')
  -- Test and seed lists and internal recipient domains excluded
  AND s.list_type NOT IN ('test', 'seed')
  AND LOWER(s.recipient_domain) NOT IN ('yourcompany.com')
GROUP BY CAST(c.click_timestamp AS DATE)
ORDER BY click_date;