Katkat
Back

Calculate CTR and ad revenue

MetaData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests your ability to clean and join datasets, handle currency conversion, and aggregate metrics at different granularities.

Problem Statement

You are given two tables representing ad delivery and performance metrics. You need to calculate the Click-Through Rate (CTR) and total revenue per ad campaign, converted to USD.

Tables

  1. impressions table:

    • impression_id: string
    • campaign_id: string
    • timestamp: datetime

  2. clicks table:

    • click_id: string
    • impression_id: string

  3. spend table:

    • campaign_id: string
    • amount: float
    • currency: string ('USD', 'EUR', 'GBP')

  4. exchange_rates table:

    • currency: string
    • rate_to_usd: float

Task

Write a query or Python function to return a table with campaign_id, CTR, and total_revenue_usd.

  • CTR is defined as count(clicks) / count(impressions).
  • Revenue must be converted to USD using the exchange_rates table.
  • If a campaign has no clicks, the CTR should be 0.

Example

Input:

  • Impression IDs A, B exist for Campaign 1.
  • Click ID 1 exists for Impression A.
  • Spend for Campaign 1 is 10 EUR, rate 1 EUR = 1.1 USD.

Output:

  • campaign_id: 1, CTR: 0.5, total_revenue_usd: 11.0

Constraints

  • impression_id is unique in impressions.
  • Every currency has an entry in exchange_rates.
  • Assume campaign_id exists in all tables if needed for joins.

Hints

Hint 1

Perform a LEFT JOIN between the impressions table and the clicks table to identify which impressions resulted in clicks.

Hint 2

Aggregate impressions and clicks at the campaign level first, then join with the spend table.

Hint 3

Ensure you multiply the spend amount by the corresponding rate from the exchange_rates table before grouping.

Follow-up questions

  • How would you modify your query if some exchange rates were missing for certain dates?
  • If this data was extremely large, how would you optimize the join operations?
  • What steps would you take to validate the data quality before performing these aggregations?