Campaign Goals
Start Timer
0:00:00
Let’s say we have a schema that represents advertiser campaigns and impressions. Each campaign has a goal: the total number of impressions the advertiser wants to reach over the campaign.
Write a query that reports, for each campaign, how it delivered during its first 7 days. Round every output column to 4 decimals.
Using this data, how do we evaluate how each campaign is delivering and by what heuristic do we surface promos that need attention?
Rules for part 1:
- Each row in
ad_impressionsis one impression (impression_idis a label, not a unique key), so count rows. - The first 7 days are the 7 calendar days starting on
start_dt, and only impressions beforeend_dtcount. - Campaign length in days is the number of days from
start_dttoend_dt. first_seven_days_averageis first-7-day impressions divided by 7;expected_daily_averageisgoaldivided by campaign length;percent_deliveredis first-7-day impressions as a percent ofgoal;percent_days_passedis 7 as a percent of campaign length.- Return one row per campaign that had at least one impression in its first 7 days.
Example:
Input:
ad_impressions table
| Column | Type |
|---|---|
dt |
DATETIME |
user_id |
INTEGER |
campaign_id |
INTEGER |
impression_id |
VARCHAR |
campaigns table
| Column | Type |
|---|---|
id |
INTEGER |
start_dt |
DATETIME |
end_dt |
DATETIME |
goal |
INTEGER |
Output
| Column | Type |
|---|---|
campaign_id |
INTEGER |
first_seven_days_average |
DECIMAL |
expected_daily_average |
DECIMAL |
percent_delivered |
DECIMAL |
percent_days_passed |
DECIMAL |
.
.
.
.
.
.
.
.
.
Comments