> 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? – Context Warehouse pocket guide

[](/pocket-guides.md)Aa

[](/pocket-guides/context-warehouse/value-vs-engagement.md)[](/pocket-guides/context-warehouse/lead-scoring.md)

# Which channels bring customers who stick? – Context Warehouse 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.

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

## 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/context-warehouse/features-drive-revenue.md); for a specific rollout's revenue impact, that's [Feature revenue impact](/pocket-guides/context-warehouse/feature-revenue-impact.md).

posthog-acquisition-channels-retention/SKILL.md

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

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

[‹ Are our biggest accounts our happiest ones?](/pocket-guides/context-warehouse/value-vs-engagement.md)[All guides](/pocket-guides.md)p. 7 of 15[Which leads deserve the sales team's time? ›](/pocket-guides/context-warehouse/lead-scoring.md)