Data Analyst Interview Questions & Answers: Set 13, Digital Marketing & Ads

SQL

1. 1.Calculate click-through rate (CTR) and conversion rate per campaign.

   Hide answer

   Answer: CTR is clicks divided by impressions, and conversion rate is conversions divided by clicks, aggregated per campaign. Assume a table campaign_stats(campaign_id, stat_date, impressions, clicks, conversions). Sum the counts first and then divide, rather than averaging daily rates. Cast to numeric so integer division does not return 0, and use NULLIF to avoid divide-by-zero. Trap: some teams define conversion rate per session or per impression, so confirm the definition first.

   SELECT campaign_id,
          SUM(impressions) AS impressions,
          SUM(clicks)      AS clicks,
          SUM(conversions) AS conversions,
          ROUND(100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), 2)
            AS ctr_pct,
          ROUND(100.0 * SUM(conversions) / NULLIF(SUM(clicks), 0), 2)
            AS conv_rate_pct
   FROM campaign_stats
   GROUP BY campaign_id
   ORDER BY ctr_pct DESC;

2. 2.Find campaigns where cost per acquisition (CPA) is above the target.

   Hide answer

   Answer: CPA is total spend divided by total conversions, so compute it per campaign and keep only those above target using HAVING. Assume campaign_stats(campaign_id, spend, conversions) and campaigns(campaign_id, campaign_name, target_cpa). Campaigns with zero conversions have undefined CPA; NULLIF makes them NULL, so list them separately because they may be the worst performers of all.

   SELECT c.campaign_id,
          c.campaign_name,
          c.target_cpa,
          SUM(s.spend) / NULLIF(SUM(s.conversions), 0) AS cpa
   FROM campaign_stats s
   JOIN campaigns c ON c.campaign_id = s.campaign_id
   GROUP BY c.campaign_id, c.campaign_name, c.target_cpa
   HAVING SUM(s.spend) / NULLIF(SUM(s.conversions), 0) > c.target_cpa
       OR (SUM(s.conversions) = 0 AND SUM(s.spend) > 0)
   ORDER BY cpa DESC NULLS FIRST;