Analyze · Cohort & Channel ROI

Build a Channel-by-Channel ROI Report

Turn spend, leads, and closed-won data into a per-channel ROI report with CAC, payback, and reallocation guidance.

foundermanagerIntermediate⏱ 4-6 hours
When to use
Use monthly or before any quarterly budget reset. Best when you have at least one full sales cycle of attribution per channel. Surfaces which channels you should double down on, hold, or kill.
The prompt
You are a growth analyst for a digital marketing agency analyzing the agency's own GTM ROI across every paid and owned acquisition channel.
Agency: [AGENCY_NAME] — [SERVICES] | Period: [PERIOD] | Data:
[CHANNEL_SPEND] (channel, spend, leads, SQLs, closed-won, avg MRR)
Build a per-channel ROI report. Calculate CAC, gross LTV, LTV:CAC ratio, and payback in months. Rank channels and recommend a concrete % budget reallocation for next quarter.

- Show every formula: CAC = spend / closed-won; LTV = AOV × gross_margin × avg_lifespan_months; Payback = CAC / (AOV × gross_margin); LTV:CAC = LTV / CAC
- Use [GROSS_MARGIN] and [AVG_LIFESPAN] as inputs; if missing, assume 60% margin and 14-month lifespan and label as assumption
- Flag any channel with 
Table: Channel | Spend | Leads | Closed-Won | CAC | LTV | LTV:CAC | Payback (mo) | Recommendation (Scale / Hold / Cut). Below table: 3-bullet "why" summary and reallocation block.
Variables
  • [AGENCY_NAME] — Your agency name
  • [SERVICES] — Services sold
  • [PERIOD] — Reporting window
  • [CHANNEL_SPEND] — Per-channel rows with spend, leads, SQLs, closed-won, avg MRR
  • [GROSS_MARGIN] — Blended gross margin % on retainers
  • [AVG_LIFESPAN] — Average client lifespan in months
Example input
Agency: Outpost Media — Paid social + creative retainers | Period: Q1 2026 | Gross margin 60% | Avg lifespan 14 mo | Data:
Channel | Spend | Leads | Closed-Won | Avg MRR
LinkedIn Ads | $9,000 | 42 | 3 | $4,500
Google Search | $6,000 | 31 | 4 | $3,800
Outbound (Apollo) | $3,500 | 88 | 2 | $3,200
Referrals | $0 | 11 | 5 | $5,100
Podcast sponsorship | $7,500 | 14 | 1 | $4,000 (started Feb)
Example output
Channel | Spend | Leads | CW | CAC | LTV | LTV:CAC | Payback | Rec
LinkedIn | $9,000 | 42 | 3 | $3,000 | $37,800 | 12.6 | 1.1mo | Scale
Google | $6,000 | 31 | 4 | $1,500 | $31,920 | 21.3 | 0.7mo | Scale hard
Outbound | $3,500 | 88 | 2 | $1,750 | $26,880 | 15.4 | 0.9mo | Hold
Referrals | $0 | 11 | 5 | $0 | $42,840 | ∞ | 0mo | Protect/Invest
Podcast | $7,500 | 14 | 1 | $7,500 | $33,600 | 4.5 | 2.2mo | Early signal — hold

Why: Google + LinkedIn are both <2-month payback; Outbound delivers volume but only converts 2.3%; Podcast needs 2 more months before judging.

Reallocation (Q2): Google 40% / LinkedIn 30% / Outbound 15% / Podcast 10% / Referral enablement budget 5%.
Pro tips
  • Always include $0-spend channels (referrals, organic) so leadership sees the full picture, not just paid.
  • Recalculate quarterly — a channel can flip from Scale to Cut in 90 days as CAC inflates.
  • If LTV:CAC is above 5x on a channel, you are probably under-spending, not over-performing.
Works with
ClaudeChatGPTGemini
Done with prompts? Time to install the system
Book a STAOS call
Related prompts