> AI agents: this is one page from PostHog's docs. Full index of Markdown docs for LLMs: https://posthog.com/llms.txt

# Which channels bring customers who stick? – Data Stack pocket guide

[](/pocket-guides.md)

AA

Contents

-   [Introduction](/pocket-guides/data-stack.md)
-   Ask a quick question

    [Features & revenue01](/pocket-guides/data-stack/features-drive-revenue.md)
-   [Onboarding conversion02](/pocket-guides/data-stack/onboarding-conversion.md)
-   [Upsell-ready accounts03](/pocket-guides/data-stack/upsell-ready-accounts.md)
-   [Pre-cancellation behavior04](/pocket-guides/data-stack/pre-cancellation-behavior.md)
-   [Tickets & churn05](/pocket-guides/data-stack/support-tickets-churn.md)
-   [ARR vs engagement06](/pocket-guides/data-stack/value-vs-engagement.md)
-   [Channels & retention07](/pocket-guides/data-stack/acquisition-channels-retention.md)
-   [Lead scoring08](/pocket-guides/data-stack/lead-scoring.md)
-   [Feature revenue impact09](/pocket-guides/data-stack/feature-revenue-impact.md)
-   Data modeling

    [Foundations10](/pocket-guides/data-stack/warehouse-foundations.md)
-   [Revenue11](/pocket-guides/data-stack/revenue-metrics.md)
-   [Conversion12](/pocket-guides/data-stack/conversion-metrics.md)
-   [Activation13](/pocket-guides/data-stack/activation-metrics.md)
-   [Usage14](/pocket-guides/data-stack/product-usage-metrics.md)
-   [Dimensions15](/pocket-guides/data-stack/dimension-tables.md)

[](/pocket-guides.md)Aa

[](/pocket-guides/data-stack/value-vs-engagement.md)[](/pocket-guides/data-stack/lead-scoring.md)

# Which channels bring customers who stick? – Data Stack pocket guide

The cheapest channel for signups is rarely the best channel for revenue. This Skill ranks first-touch acquisition channels by retained Stripe revenue and CAC payback, using ad spend from Google or Meta, so you can allocate budget where you attract customers who last, rather than customers who convert once.

## Example data and how to read it

The Skill looks for patterns like Fig. 1: paid social brought in the most customers, but organic search and content brought in more *retained* revenue at a fraction of the CAC, the real signal for where budget belongs.

| Channel | Customers | Retained revenue | CAC |
| --- | --- | --- | --- |
| Organic search | 340 | $112,000 | $38 |
| Content / SEO | 210 | $84,600 | $52 |
| Paid social | 480 | $71,200 | $146 |

Fig. 1 – Example data: channels ranked by the revenue of the customers who stuck around.

## Get an agent to do the work

Copy the Skill below and add it to your agent. The agent attributes each person to a channel from first-touch UTM properties, pulls revenue and a retention proxy from Stripe, and spend by campaign from Google or Meta Ads. It builds this in pieces (validating each part before combining) because retention windows and CAC method are modeling choices. It states which choices it made rather than presenting one number as definitive, and offers to refine them with you.

This is about acquisition source and marketing spend. For in-product feature revenue instead, that's [Features & revenue](/pocket-guides/data-stack/features-drive-revenue.md); for a specific rollout's revenue impact, that's [Feature revenue impact](/pocket-guides/data-stack/feature-revenue-impact.md).

posthog-acquisition-channels-retention/SKILL.md

```markdown
---
name: posthog-acquisition-channels-retention
description: >
  Rank a PostHog user's first-touch acquisition channels by retained Stripe revenue and CAC payback, using ad spend
  from Google/Meta, saved as a reusable PostHog insight. Use this whenever someone asks which marketing channels or
  sources bring customers who retain, wants channel ROI by retained revenue, CAC or payback by channel, wants to
  decide where to shift ad budget, or which UTMs/campaigns bring lasting customers. Triggers on phrasings like
  'which channels bring the best customers', 'channel ROI', 'CAC payback by source', 'retained revenue by channel',
  'where should we spend ad budget', or 'which UTMs convert to lasting customers'. Boundary: this is about
  acquisition source and marketing spend, not in-product feature revenue (use posthog-features-drive-revenue) or
  experiment impact (use posthog-feature-revenue-impact). It sets up the Stripe and ad sources if needed and builds
  the insight end-to-end.
---

# 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.
```

Show full example

Fig. 2 – The Skill itself, copy and paste this into your agent to answer the question.

## Doing this by hand

1.  **Connect [Stripe](/docs/cdp/sources/stripe.md), and [Google Ads](/docs/cdp/sources/google-ads.md) or [Meta Ads](/docs/cdp/sources/meta-ads.md)**, via [Data pipeline > Sources](/docs/data-warehouse/sources.md).
2.  **Build a [Trends](/docs/product-analytics/trends/overview.md) insight of signups broken down by [`$initial_utm_source`](/docs/data/utm-segmentation.md)**: native to PostHog, no join needed for this part.
3.  **Pull revenue and spend per channel separately** from Stripe's and your ad platform's own reports, then match them up using the [SQL editor](/docs/data-warehouse/sql.md) or PostHog AI.

See also: [Features & revenue](/pocket-guides/data-stack/features-drive-revenue.md) · [Feature revenue impact](/pocket-guides/data-stack/feature-revenue-impact.md)

[‹ ARR vs engagement](/pocket-guides/data-stack/value-vs-engagement.md)[All guides](/pocket-guides.md)p. 7 of 15[Lead scoring ›](/pocket-guides/data-stack/lead-scoring.md)