--- name: stripe-cac-and-payback-by-channel description: "Monthly: what each new paying customer cost on Google Ads and Meta, matched on first-touch source, and how many months it takes to earn it back. Use when the user asks for this, or when it runs on a schedule." --- # CAC and payback by channel: ad spend, first touch and Stripe Monthly: what each new paying customer cost on Google Ads and Meta, matched on first-touch source, and how many months it takes to earn it back. Google Ads and Meta each report a cost per conversion by their own attribution rules, and both happily count a sign-up that never paid. Stripe knows who paid but not which ad brought them. CAC per channel sits in the gap between the two, usually in a spreadsheet rebuilt the week before a board meeting. Once a month the agent takes spend from both ad platforms, finds every customer whose first paid invoice landed in the window, looks up where each one first arrived in PostHog or Stripe metadata, and divides. Payback uses that first month's revenue and the gross margin you set, so "Meta seems fine" turns into "Meta takes 11 months to earn back what it costs". ## Two ways to get the data Use the API or MCP route when you have an MCP server, a CLI or an API token for the app. Otherwise, if you can drive a browser the user is signed in to (Claude in Chrome, a Playwright or Chrome DevTools MCP server), use the browser route. Say which one you used. ## Route A: API or MCP server ### What you need - UTM tags on every ad link, and a first-touch source per customer: PostHog identifying people by the same email or Stripe customer ID that Stripe holds, or `utm_source` and `utm_medium` written to Stripe customer metadata at checkout. - Read-only Google Ads access (customer ID), Meta `ads_read` on the ad account, and a Stripe restricted key with Read on Invoices, Customers and Subscriptions. - A PostHog personal API key with `query:read`, or Viewer access to GA4 as the weaker fallback described in the FAQ. If something is missing, say what and stop; do not invent numbers. ### Steps 1. **Pull spend from each ad platform** (Google Ads): For the 30 days ending yesterday, run `SELECT metrics.cost_micros FROM customer WHERE segments.date BETWEEN '' AND ''` in Google Ads and divide by 1,000,000. For Meta, call `GET /act_{ad_account_id}/insights?level=account&fields=spend&time_range={"since":"","until":""}`. Convert both to your Stripe currency if the ad accounts bill in another one. 2. **List the new paying customers** (Stripe): Page through `GET /v1/invoices?status=paid&created[gte]=&created[lt]=` and keep invoices with `amount_paid > 0`. A customer is new when `GET /v1/invoices?customer=&status=paid&created[lt]=&limit=1` returns nothing, which also catches trials that converted this month. Keep the customer ID, the lowercased `customer_email` and first-month MRR (the subscription's items normalized to a month, after discounts). 3. **Look up each customer's first touch** (PostHog): Use Stripe customer `metadata.utm_source` and `metadata.utm_medium` first if your checkout writes them. For the rest, run HogQL through `/api/projects/:id/query/`: `SELECT properties.email, properties.$initial_utm_source, properties.$initial_utm_medium, properties.$initial_gclid, properties.$initial_fbclid FROM persons WHERE lower(properties.email) IN ()`. 4. **Join on email and assign one channel**: Match on lowercased, trimmed email (or the Stripe customer ID, if that is what you pass to `posthog.identify`). A `gclid`, or source `google` with medium `cpc`, `ppc` or `paid`, is Google Ads; an `fbclid`, or source `facebook`, `fb`, `instagram`, `ig` or `meta` with a paid medium, is Meta; any other source is organic and referral. Customers with no person and no metadata go to Unattributed; if that is over 25% of new customers, say the channel CACs are overstated and why. 5. **Work out CAC and payback**: CAC per channel is its spend divided by the customers it got; blended CAC is all ad spend over all new paying customers. Payback months = CAC / (average first-month MRR of that channel's customers x gross margin, default 80%). Read last month's card with `GET https://briefkin.dev/v1/cards/insight-stripe-cac-and-payback-by-channel` and post blended CAC with its history. ## Route B: the browser ### What you need - Signed in to Google Ads, Meta Ads Manager, Stripe (a role that can view invoices and customers) and PostHog, all in this browser. If something is missing, say what and stop; do not invent numbers. ### Steps Start at https://ads.google.com/aw/campaigns. The user is signed in already; never type passwords or codes, and ask if a sign-in page appears. 1. **Read Google Ads spend**: Pick the account if Google Ads asks, open Campaigns, set the date range to `Last 30 days` and read the Cost figure in the total row at the bottom of the table. 2. **Read Meta spend**: Open `https://adsmanager.facebook.com/adsmanager/manage/campaigns`, choose the ad account, set the date picker to `Last 30 days` and read Amount spent in the results row under the campaign list. 3. **List new paying customers in Stripe**: Open `https://dashboard.stripe.com/invoices`, filter Status `Paid` and Date to the same 30 days. Note each customer's email and amount, and open the customer to check this is their first paid invoice; a trial that started earlier but paid first this month still counts as new. 4. **Find each customer's first touch**: On the Stripe customer page, read Metadata for `utm_source`. If it is empty, open People in PostHog, search the email, and read Initial UTM source, Initial UTM medium and any initial gclid or fbclid on the Properties tab. 5. **Assign channels and divide**: A gclid or google with a cpc, ppc or paid medium is Google Ads; an fbclid or facebook, instagram or meta with a paid medium is Meta; other sources are organic; no data is Unattributed. CAC is spend over customers; payback is CAC over (average first-month payment x 80%). Note: Looking up first touch one customer at a time works for a few dozen new customers a month. Past that, export People from PostHog to CSV and join in a spreadsheet, or use the API route. Stripe's invoice list shows amounts after discounts but mixes in annual plans: divide an annual invoice by 12 for the first-month figure. ## The report - **Blended CAC** for the last 30 days, with the monthly trend. - **Per channel**: spend, new paying customers, CAC, average first-month MRR and payback months. - **Unattributed**: how many new customers had no first-touch source, and the likely reason. - One line on what to do first, for example "Hold Meta budget flat until its landing page converts closer to Google's: same plan, $128 more per customer". ## Post it to Briefkin When `BRIEFKIN_TOKEN` is set, or `~/.config/briefkin/env` exists (the installer from https://briefkin.dev/docs writes it), also send the result to the user's Briefkin board as one card. Keep the `item_key` the same on every run so the card updates in place, use a new `event_id` per run, and put today's real numbers in place of the example ones. If the request fails, say so once and carry on. ```bash [ -n "${BRIEFKIN_TOKEN:-}" ] || . ~/.config/briefkin/env curl -sS -X POST "${BRIEFKIN_URL:-https://briefkin.dev}/v1/events" \ -H "authorization: Bearer $BRIEFKIN_TOKEN" \ -H "content-type: application/json" \ -d @- <<'JSON' { "v": 1, "event_id": "stripe-cac-and-payback-by-channel/", "item_key": "stripe-cac-and-payback-by-channel", "project": "acme-web", "source": "CAC and payback by channel: ad spend, first touch and Stripe skill", "kind": "metric", "title": "Google Ads pays back in 7 months, Meta in 11", "metric": { "label": "Blended CAC", "value": 163, "prefix": "$", "period_label": "Last 30 days", "better": "lower", "period_start": "", "period_end": "" }, "glance": [ { "type": "Grid", "props": { "columns": 2 }, "children": [ { "type": "Stat", "props": { "label": "Google Ads CAC", "value": 212, "prefix": "$", "better": "lower" } }, { "type": "Stat", "props": { "label": "Meta CAC", "value": 340, "prefix": "$", "better": "lower" } } ] }, { "type": "Badges", "props": { "items": [ { "label": "Google payback 7 mo", "tone": "ok" }, { "label": "Meta payback 11 mo", "tone": "warn" }, { "label": "9 unattributed", "tone": "neutral" } ] } } ], "widget": [ { "type": "Table", "props": { "columns": [ "Channel", "Spend", "New customers", "CAC", "Payback" ], "align": [ "left", "right", "right", "right", "right" ], "rows": [ [ "Google Ads", "$8,480", 40, "$212", "7.0 mo" ], [ "Meta", "$6,800", 20, "$340", "11.2 mo" ], [ "Organic and referral", "$0", 25, "n/a", "n/a" ], [ "Unattributed", "n/a", 9, "n/a", "n/a" ], [ "All channels", "$15,280", 94, "$163", "5.4 mo" ] ] } }, { "type": "Callout", "props": { "text": "Customers from both channels start on the same $38 a month at an 80% margin, so the gap is acquisition cost: Meta pays $128 more per customer.", "tone": "warn" } } ], "size": "md" } JSON ``` Widgets come from the Briefkin catalog (https://briefkin.dev/v1/catalog). Without a token, just give the user the report. Set `period_start` and `period_end` to the window the number covers: the tile names it from them ("Last 7 days", "Today", "This month"). For a count right now (open items, a balance), send `"period_label": ""` so no period shows; for another name, send it as `period_label` (at most 24 characters). ## Run it on a schedule Suggested: 1st of the month at 9:00 (`0 9 1 * *`). From cron, with the agent signed in on that machine: ```bash 0 9 1 * * claude -p "Run the stripe-cac-and-payback-by-channel skill" # Codex: codex exec, Gemini CLI: gemini -p, Copilot CLI: copilot -p, OpenCode: opencode run ``` Or use the agent's own scheduler (Claude Code routines, Codex automations, Claude or ChatGPT scheduled tasks): https://briefkin.dev/skills/stripe-cac-and-payback-by-channel#schedule Source: https://briefkin.dev/skills/stripe-cac-and-payback-by-channel