SQL
There were two SQL questions in total, mainly testing aggregation, date filtering and window functions.
ttam_logger: event_timestamp, advertiser_id, current_week_start, previous_week_start (hardcoded date)
Find the advertisers that were active in both this week and last week. You need to clarify the date ranges first, then aggregate by advertiser and check whether the advertiser shows up with activity in both weeks.
Daily time spent by advertiser: compute each advertiser's active time per day from their event log. A session is custom-defined: if there is no action for more than one hour, that starts a new session. So you need LAG() to get the time difference between adjacent events and only add up the gaps that are within one hour.
Product Case
The case was about the creative submission funnel in Ads Manager. The problem noticed that the advertiser submission rate is consistently higher on weekdays than on weekends, and the task was to analyze the possible reasons and the business impact.
The directions for the analysis: first validate the metric definition, logging and timezone, then check advertiser activity, site performance, advertiser size, market, campaign type, and the difference in the mix of new versus existing advertisers. You also need to decide whether the lower weekend submission rate is a real drop in conversion, or whether advertisers are just delaying their submission until the weekdays.
Follow-up: if the weekday vs. weekend difference disappears after you stratify by advertiser type, how do you explain it?
This is more likely an advertiser mix shift, Simpson's paradox: SMB or new advertisers, who convert lower on weekends, make up a larger share of weekend traffic, rather than the same type of advertiser performing worse on weekends. So you should report both the raw submission rate and the mix-adjusted rate, and do further analysis on the advertiser segments that actually have friction.
Discussion
Loading comments…