Meta's Product Growth team wants an AARRR Funnel view of Meta Ads performance across Instagram Reels and Facebook Groups. Write a PostgreSQL query that measures impression to click to install conversion for impressions recorded during the first week of January 2026.
Requirements
- Group results by
campaign_id and placement.
- Count distinct impressions, clicks, and installs. Include groups with impressions but no clicks or installs.
- Attribute an install only when it occurs from the click and within seven days after that click.
- Calculate click-through rate, install-from-click rate, and install-from-impression rate, rounded to two decimals. Use zero when a denominator is zero.
Representative data
Output details
- Return columns in this order:
campaign_id, placement, impressions, clicks, installs, click_through_rate, install_from_click_rate, install_from_impression_rate.
- Order rows by
campaign_id ascending, then placement ascending.
- Treat the first week as
2026-01-01 00:00:00+00 inclusive through 2026-01-08 00:00:00+00 exclusive.
- Treat the seven-day install window as inclusive of both the click timestamp and exactly seven days afterward.
- Render all rate values with exactly two decimal places, including trailing zeroes.