Katkat
Back

Compute ad impression conversion rates

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in time-based event attribution, aggregation of business metrics (impressions, conversion rates, spend and revenue), timestamp handling, and user/ad-level matching within the Data Manipulation (SQL/Python) domain, emphasizing practical application over abstract theory.

You are given two tables for an ads product.

Table: ad_impressions

ColumnTypeDescription
impression_idSTRINGUnique identifier for an ad impression. Primary key.
user_idSTRINGUser who saw the ad.
ad_idSTRINGAd that was shown.
campaign_idSTRINGCampaign that owns the ad.
impression_tsTIMESTAMPTime the impression was served, stored in UTC.
cost_usdNUMERICAdvertiser cost for the impression in USD.

Table: ad_conversions

ColumnTypeDescription
conversion_idSTRINGUnique identifier for a conversion event. Primary key.
user_idSTRINGUser who converted.
ad_idSTRINGAd associated with the conversion event.
conversion_tsTIMESTAMPTime the conversion occurred, stored in UTC.
revenue_usdNUMERICConversion value in USD.

Attribution rule:

  • A conversion can be attributed only to an impression from the same user_id and ad_id.
  • The impression must occur before the conversion.
  • The conversion must occur within 7 days after the impression.
  • If multiple impressions are eligible for the same conversion, attribute the conversion to the most recent eligible impression before the conversion.
  • Each conversion should be attributed at most once.

Write a SQL query for impressions served from 2024-01-01 00:00:00 UTC through 2024-01-31 23:59:59 UTC. For each campaign_id and UTC impression_date, return:

  • campaign_id
  • impression_date
  • impressions: number of impressions
  • unique_impression_users: number of distinct users with impressions
  • attributed_conversions: number of conversions attributed to those impressions
  • conversion_rate: attributed conversions divided by impressions
  • total_spend_usd: total impression cost
  • total_conversion_revenue_usd: total attributed conversion revenue
  • avg_hours_to_conversion: average hours between impression and attributed conversion, across attributed conversions only

Rows with impressions but zero attributed conversions should still appear with zero conversions and zero conversion revenue.