Write SQL for Pixel Signal Metrics

You are working on Meta Ads Pixel analytics. Assume all timestamps are stored in UTC, and analyze the last 30 complete calendar days.

Tables

1. advertisers

• advertiser_id BIGINT
• vertical STRING
• created_at TIMESTAMP

2. pixels

• pixel_id BIGINT
• advertiser_id BIGINT
• installed_at TIMESTAMP

3. pixel_signal_daily

• ds DATE
• pixel_id BIGINT
• events_received BIGINT
• valid_event_rate DOUBLE -- share of received events that pass validation, from 0 to 1
• match_rate DOUBLE -- share of valid events that can be matched back to ad traffic, from 0 to 1

4. ad_performance_daily

• ds DATE
• advertiser_id BIGINT
• impressions BIGINT
• clicks BIGINT
• spend_usd DOUBLE
• attributed_conversions BIGINT
• attributed_revenue_usd DOUBLE

Relationships

• advertisers has a 1:N relationship with pixels
• pixels has a 1:N relationship with pixel_signal_daily
• advertisers has a 1:N relationship with ad_performance_daily

Task 1: Write a SQL query that returns one row per advertiser per day with the following columns:

• ds
• advertiser_id
• active_pixels -- number of the advertiser's pixels that received at least 1 event that day
• total_events_received
• avg_valid_event_rate -- event-weighted average valid_event_rate across the advertiser's active pixels that day

Task 2: Write a SQL query to study the relationship between Pixel signal data quality and ads performance.

Use the following definitions:

• pixel_day_quality_score = valid_event_rate * match_rate
• advertiser_day_quality = event-weighted average pixel_day_quality_score across all active pixels for that advertiser on that day
• CVR = attributed_conversions / NULLIF(clicks, 0)
• ROAS = attributed_revenue_usd / NULLIF(spend_usd, 0)

Bucket each advertiser-day into:

• 'low' if advertiser_day_quality < 0.60
• 'medium' if advertiser_day_quality >= 0.60 and advertiser_day_quality < 0.85
• 'high' if advertiser_day_quality >= 0.85

Return one row per quality bucket for the 30-day window with these output columns:

• quality_bucket
• advertiser_day_count
• avg_quality_score
• total_spend_usd
• total_clicks
• total_conversions
• cvr
• roas