Campaign And Channel Segmentation Queries

Why this matters

Marketing Analysts answer where users come from, which campaigns work, and where budget should go next. You will regularly segment users and revenue by channel and campaign, compare cohorts, and connect spend to outcomes to steer strategy and spend.

• Report new vs returning users by channel for the last 30 days.
• Attribute conversions to first-touch or last-touch channels.
• Summarize campaign ROI: sessions, clicks, cost, orders, revenue, CPA, and ROAS.
• Find underperforming sources and reallocate budget.

Who this is for

• Marketing Analysts and Growth Analysts who need reliable channel and campaign insights.
• Data-savvy marketers validating performance claims from ad platforms.
• Product/Analytics folks connecting acquisition to downstream revenue.

Prerequisites

• Basic SQL: SELECT, WHERE, GROUP BY, HAVING.
• Intermediate SQL: JOINS and window functions (ROW_NUMBER, MIN OVER).
• Comfort with date filtering and casting.

Concept explained simply

Segmentation queries group people or events by a marketing label (channel, source, campaign) and compute totals and rates at a chosen time grain. Add an attribution rule (first-touch or last-touch) to decide which label gets credit.

Core patterns you will use

• Distinct counts by segment: COUNT(DISTINCT user_id) BY channel/campaign.
• First-touch: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_date), keep rn=1.
• Last-touch within a window: ROW_NUMBER() OVER (PARTITION BY user_id, order_id ORDER BY session_date DESC), filter sessions before order.
• Date windows: session_date >= CURRENT_DATE - INTERVAL '30' DAY.
• Join spend to outcomes by channel/campaign and date grain.

Worked examples

Example 1: New vs Returning users by channel (last 30 days)

-- Classify users as New if their first-ever session falls in the last 30 days
WITH win AS (
  SELECT CURRENT_DATE - INTERVAL '30' DAY AS window_start
), first_touch AS (
  SELECT user_id, MIN(session_date) AS first_session_date
  FROM sessions
  GROUP BY 1
), recent AS (
  SELECT s.user_id, s.channel
  FROM sessions s
  CROSS JOIN win
  WHERE s.session_date >= win.window_start
), labeled AS (
  SELECT r.channel,
         CASE WHEN f.first_session_date >= (SELECT window_start FROM win)
              THEN 'New' ELSE 'Returning' END AS user_type,
         r.user_id
  FROM recent r
  JOIN first_touch f USING (user_id)
)
SELECT channel, user_type,
       COUNT(DISTINCT user_id) AS users
FROM labeled
GROUP BY 1,2
ORDER BY users DESC;

Example 2: First-touch channel conversion rate (30-day window)

-- Attribute each user to their first-touch channel, then check if they purchased within 30 days
WITH first_channel AS (
  SELECT user_id, channel AS first_channel, session_date AS first_date
  FROM (
    SELECT user_id, channel, session_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_date) AS rn
    FROM sessions
  ) t
  WHERE rn = 1
), purchasers AS (
  SELECT DISTINCT o.user_id
  FROM orders o
  JOIN first_channel fc ON fc.user_id = o.user_id
  WHERE o.order_date <= fc.first_date + INTERVAL '30' DAY
)
SELECT fc.first_channel AS channel,
       COUNT(*) AS users,
       SUM(CASE WHEN p.user_id IS NOT NULL THEN 1 ELSE 0 END) AS purchasers,
       ROUND(100.0 * SUM(CASE WHEN p.user_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_rate_pct
FROM first_channel fc
LEFT JOIN purchasers p ON p.user_id = fc.user_id
GROUP BY 1
ORDER BY conversion_rate_pct DESC;

Example 3: Campaign ROI (last 14 days, first-touch revenue)

-- Join campaign spend to first-touch-attributed revenue
WITH win AS (
  SELECT CURRENT_DATE - INTERVAL '14' DAY AS start_date, CURRENT_DATE AS end_date
), fc AS (
  SELECT user_id,
         FIRST_VALUE(campaign) OVER (PARTITION BY user_id ORDER BY session_date) AS first_campaign,
         FIRST_VALUE(channel)  OVER (PARTITION BY user_id ORDER BY session_date) AS first_channel,
         MIN(session_date) AS first_date
  FROM sessions
  GROUP BY user_id
), revenue_by_campaign AS (
  SELECT fc.first_channel AS channel, fc.first_campaign AS campaign,
         SUM(o.revenue) AS revenue,
         COUNT(DISTINCT o.order_id) AS orders
  FROM fc
  JOIN orders o ON o.user_id = fc.user_id
  CROSS JOIN win
  WHERE o.order_date BETWEEN win.start_date AND win.end_date
  GROUP BY 1,2
), spend AS (
  SELECT channel, campaign,
         SUM(spend) AS spend,
         SUM(clicks) AS clicks
  FROM campaign_spend cs
  CROSS JOIN win
  WHERE cs.spend_date BETWEEN win.start_date AND win.end_date
  GROUP BY 1,2
), sessions_agg AS (
  SELECT channel, campaign, COUNT(*) AS sessions, COUNT(DISTINCT user_id) AS users
  FROM sessions s
  CROSS JOIN win
  WHERE s.session_date BETWEEN win.start_date AND win.end_date
  GROUP BY 1,2
)
SELECT COALESCE(s.channel, r.channel) AS channel,
       COALESCE(s.campaign, r.campaign) AS campaign,
       COALESCE(sa.sessions, 0) AS sessions,
       COALESCE(sa.users, 0) AS users,
       COALESCE(s.clicks, 0) AS clicks,
       COALESCE(s.spend, 0)  AS spend,
       COALESCE(r.orders, 0) AS orders,
       COALESCE(r.revenue, 0) AS revenue,
       CASE WHEN COALESCE(s.clicks,0) > 0 THEN ROUND(s.spend::numeric / s.clicks, 2) ELSE NULL END AS cpc,
       CASE WHEN COALESCE(r.orders,0) > 0 THEN ROUND(s.spend::numeric / r.orders, 2) ELSE NULL END AS cpa,
       CASE WHEN COALESCE(s.spend,0) > 0 THEN ROUND(r.revenue::numeric / s.spend, 2) ELSE NULL END AS roas
FROM spend s
FULL OUTER JOIN revenue_by_campaign r ON r.channel = s.channel AND r.campaign = s.campaign
FULL OUTER JOIN sessions_agg sa ON sa.channel = COALESCE(s.channel, r.channel)
                              AND sa.campaign = COALESCE(s.campaign, r.campaign)
ORDER BY roas DESC NULLS LAST;

Exercises you can run

Use the same example tables: users, sessions, orders, campaign_spend. Adjust date functions to your SQL dialect if needed.

Exercise 1 (ex1): Cohort conversion by acquisition channel

Build a report by signup month and first-touch channel with users, purchasers within 30 days, and conversion_rate_pct. Sort by signup_month descending, then purchasers descending.

Exercise 2 (ex2): Campaign performance dashboard (last 14 days)

Produce campaign, channel, sessions, users, clicks, spend, orders, revenue, cpc, cpa, roas. Keep all campaigns that spent even if orders are zero.

• Checklist before you run:
  • Window dates are explicit and consistent across CTEs.
  • Null/unknown channels are handled (kept or filtered on purpose).
  • Rates divide by zero safely (use CASE WHEN).

Common mistakes and self-checks

• Double counting users after joins. Self-check: compare counts before and after joins; use DISTINCTs where needed.
• Mismatched date windows between spend and revenue. Self-check: make a single window CTE and reuse it.
• Mixing first-touch and last-touch in the same report. Self-check: name columns explicitly (first_channel vs last_channel).
• String case and whitespace differences. Self-check: normalize with LOWER(TRIM(channel)).
• Missing rows when a metric is zero. Self-check: use FULL OUTER JOIN or a calendar/scaffold to retain keys.

Practical projects

• Weekly Acquisition Performance Pack: one SQL view that outputs by day x channel: sessions, new_users, orders, revenue, cvr, cpa, roas. Share as a simple dashboard.
• Attribution A/B: create two SQL models (first-touch vs last-touch) and compare channel rankings and budget implications.

Learning path

1. Refresh GROUP BY, DISTINCT, and WHERE on dates.
2. Practice INNER/LEFT/FULL joins with small samples.
3. Get comfortable with ROW_NUMBER, MIN OVER for attribution.
4. Build cohort reports (DATE_TRUNC + windows).
5. Add spend joins and compute CPA/ROAS safely.
6. Harden with data quality checks and scaffolding for zero rows.

Mini challenge

In one query, output by channel for the last 30 days: new_users, returning_users, orders, revenue, cvr (orders/new_users), and roas using first-touch revenue and spend. Document your attribution choice in comments.

Next steps

• Parameterize your date windows so teammates can reuse the queries.
• Create views or temp tables for first-touch and last-touch to avoid repeating logic.
• Add sanity checks that flag channels with suspiciously high CVR or $0 spend.

Ready for the quick test?

Take the quick test to lock in the concepts. Available to everyone; only logged-in users get saved progress.