Lära Campaign Performance Metrics | Attribution and Campaign Performance

To measure the success of your marketing campaigns, you need to track and analyze several key performance metrics. These metrics help you understand how users interact with your campaigns and where improvements can be made. The most commonly used campaign performance metrics are:

Impressions: the number of times a campaign is displayed to users;
Clicks: the number of times users interact with a campaign by clicking;
Conversions: the number of users who complete a desired action, such as making a purchase, after interacting with a campaign;
Conversion rate: the percentage of users who convert out of the total users reached;
ROI (Return on Investment): the profit generated relative to the campaign's cost.

By calculating these metrics, you can compare campaigns, optimize performance, and allocate budgets more effectively.


              12345678910111213
            
SELECT
  c.campaign_id,
  c.campaign_name,
  COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) AS total_conversions,
  COUNT(DISTINCT CASE WHEN e.event_type IN ('impression', 'click', 'purchase') THEN e.user_id END) AS users_reached,
  ROUND(
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) * 100.0
    / NULLIF(COUNT(DISTINCT CASE WHEN e.event_type IN ('impression', 'click', 'purchase') THEN e.user_id END), 0)
  , 2) AS conversion_rate_percent
FROM campaigns c
LEFT JOIN events e ON c.campaign_id = e.campaign_id
GROUP BY c.campaign_id, c.campaign_name
ORDER BY c.campaign_id;

Once you have calculated these metrics, you can use SQL to directly compare the effectiveness of each campaign. This is especially useful when you want to see which campaigns are generating the most conversions or achieving the highest conversion rates. Ranking campaigns by conversion rate and ROI allows you to quickly identify top performers and those that may need adjustment. By structuring your queries to include these calculations, you can present actionable insights to your marketing team.


              1234567891011121314151617181920212223242526272829
            
SELECT 
  c.campaign_id,
  c.campaign_name,
  COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) AS conversions,
  ROUND(
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) * 1.0
    / NULLIF(COUNT(DISTINCT CASE WHEN e.event_type IN ('impression', 'click', 'purchase') THEN e.user_id END), 0)
  , 4) AS conversion_rate,
  -- Assume a fixed cost per campaign for demonstration
  CASE c.campaign_id
    WHEN 101 THEN 500
    WHEN 102 THEN 800
    WHEN 103 THEN 300
    ELSE 0
  END AS campaign_cost,
  -- Assume each conversion yields $200 revenue
  (COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) * 200
   - CASE c.campaign_id
       WHEN 101 THEN 500
       WHEN 102 THEN 800
       WHEN 103 THEN 300
       ELSE 0
     END
  ) AS roi
FROM campaigns c
LEFT JOIN
  events e ON c.campaign_id = e.campaign_id
GROUP BY c.campaign_id, c.campaign_name
ORDER BY conversion_rate DESC, roi DESC;

This query evaluates marketing campaign performance by calculating conversion metrics and return on investment (ROI) using conditional aggregation and calculated fields.

It starts from the campaigns table and uses a LEFT JOIN to the events table to ensure that campaigns with no recorded events are still included in the results. Conversions are calculated with conditional COUNT(DISTINCT …) expressions, counting unique users who generated a purchase event for each campaign.

The conversion rate is derived by dividing the number of converting users by the total number of unique users who interacted with the campaign (impression, click, or purchase). The NULLIF function is used to safely handle cases where a campaign has no interactions, preventing division-by-zero errors. The result is rounded for reporting clarity.

Campaign costs are modeled using a CASE expression, assigning a fixed cost based on campaign_id. ROI is then calculated as a derived metric, subtracting campaign cost from assumed revenue (a fixed revenue per conversion) using arithmetic expressions combined with aggregated values.

Finally, the query groups results at the campaign level and orders them by conversion efficiency and profitability, making it suitable for comparing campaign effectiveness and prioritizing marketing spend.

Uppgift

Swipe to start coding

Write a SQL query to analyze campaign performance using the provided tables.

For each campaign, calculate the total number of impressions (event_type = 'impression');
Calculate the total number of clicks (event_type = 'click');
Calculate the total number of purchases (event_type = 'purchase');
Calculate the conversion rate as (number of purchases / number of impressions) * 100, rounded to 2 decimal places, and display this value as conversion_rate_percent;
Include the columns: campaign_id, campaign_name, impressions, clicks, purchases, conversion_rate_percent in your result.
Show all campaigns, even those with zero impressions, clicks, or purchases.

Lösning

Var allt tydligt?

Tack för dina kommentarer!

Avsnitt 4. Kapitel 2

single

Fråga AI

Fråga vad du vill eller prova någon av de föreslagna frågorna för att starta vårt samtal

Suggested prompts:

Can you explain how the ROI calculation works in this query?

What changes would I need to make if each conversion had a different revenue value?

How can I include additional metrics like total impressions or clicks in the results?

Svep för att visa menyn

Impressions: the number of times a campaign is displayed to users;
Clicks: the number of times users interact with a campaign by clicking;
Conversions: the number of users who complete a desired action, such as making a purchase, after interacting with a campaign;
Conversion rate: the percentage of users who convert out of the total users reached;
ROI (Return on Investment): the profit generated relative to the campaign's cost.