Measuring the Impact of a Marketing Campaign Using SQL

SQL Quickies #22

AI Image : app-based marketing campaign

In this post, I am going to break down an interesting SQL interview question that I encountered. Let me set the context of the problem. You’re working on a mobile app that offers in-app purchases. To boost revenue, your team launches a marketing campaign targeting users after their first purchase. The campaign nudges them with call-to-actions, personalized offers, and timely reminders to buy more.

The marketing campaign goes live one day after a user’s first purchase. We want to find users who:

• Made additional purchases after their first purchase day.
• Bought products they didn’t already purchase on that first day.

These users are considered successes of the marketing campaign. So how can we measure this — get a count of how many such users there are?

Also note that we do not count users who:

• Only make purchase(s) on the first day and not afterwards.
• Do purchase after the first day, but only repeat the products they bought on day one.

In this post, we’ll walk through this problem step by step. We’ll build a query to identify users influenced by a post-first-purchase marketing campaign.

You’re given a table called purchases:

purchases (
  user_id        INT,
  purchase_date  DATE,
  product_id     INT,
  price          FLOAT
)

Each row logs a single in-app purchase by a given user.

Step 1: Find Each User’s First Purchase Date

First, we calculate the earliest purchase date per user.

WITH first_purchase_date AS (
  SELECT 
    user_id,
    MIN(purchase_date) AS first_date
  FROM purchases
  GROUP BY user_id
)

This establishes a reference point for when the campaign begins for each user.

Step 2: Identify Products Purchased on Day 1

Next, we retrieve the list of products each user purchased on their first day.

, first_day_products AS (
  SELECT 
    p.user_id,
    p.product_id
  FROM purchases p
  JOIN first_purchase_date fpd
    ON p.user_id = fpd.user_id
    AND p.purchase_date = fpd.first_date
)

These are the products we’ll consider the user’s baseline behavior — anything purchased later that is not present in this list is new.

Step 3: Get Purchases Made After Day 1

Now we fetch all purchases made after the first day — the window where the campaign could influence behavior.

, subsequent_purchases AS (
  SELECT 
    p.user_id,
    p.product_id
  FROM purchases p
  JOIN first_purchase_date fpd
    ON p.user_id = fpd.user_id
  WHERE p.purchase_date > fpd.first_date
)

We don’t care how long after — just that it’s later than day one.

Step 4: Find New Products Bought After Day 1

Here’s the core insight: we want users who bought new products post-campaign — i.e., products not purchased on their first day.

, new_products_after_campaign AS (
  SELECT DISTINCT 
    sp.user_id
  FROM subsequent_purchases sp
  LEFT JOIN first_day_products fdp
    ON sp.user_id = fdp.user_id
    AND sp.product_id = fdp.product_id
  WHERE fdp.product_id IS NULL
)

We use a LEFT JOIN to compare the post-day-one purchases against day-one purchases. If there’s no match (fdp.product_id IS NULL), it’s a new product — potential campaign success.

Step 5: Count Campaign-Influenced Users

Finally, we count the number of unique users who bought new products after their first day.

SELECT COUNT(DISTINCT user_id) AS users_impacted_by_campaign
FROM new_products_after_campaign;

Full Query

Here’s the query in its entirety:

WITH first_purchase_date AS (
    SELECT 
        user_id,
        MIN(purchase_date) AS first_date
    FROM purchases
    GROUP BY user_id
),
first_day_products AS (
    SELECT 
        p.user_id,
        p.product_id
    FROM purchases p
    JOIN first_purchase_date fpd
      ON p.user_id = fpd.user_id AND p.purchase_date = fpd.first_date
),
subsequent_purchases AS (
    SELECT 
        p.user_id,
        p.product_id
    FROM purchases p
    JOIN first_purchase_date fpd
      ON p.user_id = fpd.user_id
    WHERE p.purchase_date > fpd.first_date
),
new_products_after_campaign AS (
    SELECT DISTINCT 
        sp.user_id
    FROM subsequent_purchases sp
    LEFT JOIN first_day_products fdp
      ON sp.user_id = fdp.user_id AND sp.product_id = fdp.product_id
    WHERE fdp.product_id IS NULL
)
SELECT COUNT(DISTINCT user_id) AS users_impacted_by_campaign
FROM new_products_after_campaign;

We’ve broken the problem into modular pieces using Common Table Expressions (CTEs). This not only improves readability but also mirrors how you might think about the problem logically:

• What’s the user’s starting point?
• What did they do next?
• Did their behavior change?

The final output gives you a clean, measurable metric: the number of users influenced by the campaign. Additionally, you could find the ratio of this metric with total users.

This pattern of behavior tracking — isolating a baseline, then identifying divergence — is widely applicable. Whether you’re measuring marketing success, feature adoption, or churn, the core logic stays the same.

Until next time:)