Campaign Goals

Start Timer

0:00:00

Upvote
8
Downvote
Save question
Mark as completed
View comments (8)

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.

  1. Write a query that reports, for each campaign, how it delivered during its first 7 days. Round every output column to 4 decimals.

  2. 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_impressions is one impression (impression_id is 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 before end_dt count.
  • Campaign length in days is the number of days from start_dt to end_dt.
  • first_seven_days_average is first-7-day impressions divided by 7; expected_daily_average is goal divided by campaign length; percent_delivered is first-7-day impressions as a percent of goal; percent_days_passed is 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

Loading comments