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

WITH deduped AS (
  -- Paid-placement performance rows in account time zone, one row per platform x account x campaign x ad group x date (latest load wins)
  SELECT
    platform,
    click_date,
    clicks,
    link_clicks
  FROM `your-project.ads.ad_platform_performance_daily`
  WHERE click_date BETWEEN start_date AND end_date
    AND placement_type IN ('SEARCH', 'SHOPPING', 'DISPLAY', 'VIDEO', 'SOCIAL', 'PERFORMANCE_MAX')
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY platform, account_id, campaign_id, ad_group_id, click_date
    ORDER BY loaded_at DESC
  ) = 1
)
-- Daily ad clicks: platform Clicks (Meta: Link Clicks); these fields are already net of platform-filtered invalid clicks
SELECT
  click_date,
  SUM(IF(platform = 'meta_ads', link_clicks, clicks)) AS ad_clicks
FROM deduped
GROUP BY click_date
ORDER BY click_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative reporting window
  SELECT
    DATE_SUB(CURRENT_DATE, 90) AS start_date,
    DATE_SUB(CURRENT_DATE, 1) AS end_date
),
ranked AS (
  -- Paid-placement performance rows in account time zone, ranked per platform x account x campaign x ad group x date (latest load first)
  SELECT
    a.platform,
    a.click_date,
    a.clicks,
    a.link_clicks,
    ROW_NUMBER() OVER (
      PARTITION BY a.platform, a.account_id, a.campaign_id, a.ad_group_id, a.click_date
      ORDER BY a.loaded_at DESC
    ) AS rn
  FROM aep_backend_dataset_ad_platform_performance_daily AS a
  CROSS JOIN params AS p
  WHERE a.click_date BETWEEN p.start_date AND p.end_date
    AND a.placement_type IN ('SEARCH', 'SHOPPING', 'DISPLAY', 'VIDEO', 'SOCIAL', 'PERFORMANCE_MAX')
)
-- Daily ad clicks: platform Clicks (Meta: Link Clicks); these fields are already net of platform-filtered invalid clicks
SELECT
  click_date,
  SUM(CASE WHEN platform = 'meta_ads' THEN link_clicks ELSE clicks END) AS ad_clicks
FROM ranked
WHERE rn = 1
GROUP BY click_date
ORDER BY click_date;