posthog-acquisition-channels-retention

# Which channels bring customers who stick? **Question:** Which first-touch acquisition channels bring customers who retain, by retained revenue and CAC payback? **For:** Marketing · **Difficulty:** Advanced · **Shape:** a modeled query **Data sources:** PostHog events (first-touch / UTMs) + Stripe (revenue & retention) + Google/Meta Ads (spend) ## What this produces A saved PostHog insight ranking first-touch channels by retained Stripe revenue and CAC payback — so the user can shift budget toward the channels that bring customers who last, not just the cheapest signups. ## Workflow First read `references/posthog-workflow.md` for the shared setup: confirm the PostHog MCP is connected, ensure Stripe and (for CAC) Google Ads / Meta Ads sources exist (secure connect-link flow if not), and learn the real schema. This is the most involved question — verify each source before querying. Then: ### 1. Identify the pieces in this project - **First-touch channel.** Attribute each person to a channel from first-touch UTM properties (`$initial_utm_source` / `$initial_utm_medium`) or a stored referrer. Confirm which properties the user actually captures with `property-definitions`. - **Revenue & retention.** From Stripe: revenue per customer and whether they're still active (or how many months they retained). Approximate retained revenue as revenue from customers still subscribed after N months. - **Spend per channel.** From Google/Meta Ads tables: spend by campaign/source over the period. CAC = spend ÷ customers acquired from that channel. - **Join keys.** Person → Stripe by email; channel comes from the person's own properties, so no external join is needed for attribution itself. ### 2. Build and validate the query This is genuinely multi-step — build it in pieces, validating each CTE with `query-run` before combining. Adapt names throughout. ```sql -- Channels ranked by retained revenue and (roughly) CAC payback. -- Adapt: UTM properties, the retention window, revenue logic, and ad-spend tables. WITH first_touch AS ( SELECT person.id AS person_id, lower(person.properties.email) AS email, coalesce(person.properties.$initial_utm_source, 'direct/unknown') AS channel FROM persons ), revenue AS ( SELECT lower(email) AS email, sum(amount) / 100.0 AS revenue, max(created) >= now() - INTERVAL 30 DAY AS still_active -- crude retention proxy FROM stripe_invoice WHERE status = 'paid' GROUP BY lower(email) ), spend AS ( SELECT campaign_source AS channel, sum(spend) AS spend FROM google_ads_campaign_stats -- union with meta ads if used WHERE date >= now() - INTERVAL 180 DAY GROUP BY campaign_source ) SELECT ft.channel, count(DISTINCT ft.person_id) AS customers, round(sum(if(r.still_active, r.revenue, 0)), 2) AS retained_revenue, round(any(s.spend), 2) AS ad_spend, round(any(s.spend) / nullif(count(DISTINCT ft.person_id), 0), 2) AS cac FROM first_touch AS ft INNER JOIN revenue AS r ON ft.email = r.email LEFT JOIN spend AS s ON ft.channel = s.channel GROUP BY ft.channel ORDER BY retained_revenue DESC ``` Retention and CAC payback have real modeling choices (cohort window, gross vs net revenue, blended vs paid CAC). State the choices you made; offer to refine them with the user rather than presenting one number as definitive. ### 3. Save the insight Save as a SQL/HogQL table insight named "Channels by retained revenue & CAC", documenting the retention window and CAC method. Return the URL and name the channels that punch above their spend. ## Self-driving development (offer this) With channels ranked by retained revenue, the user can shift budget toward the ones that bring customers who last. Offer to help set this up as a recurring materialized view and to build experiments that test reallocating spend — spending smarter without the guesswork.