Tracking · Attribution & Source Tracking

Design a Channel Mix Attribution Report

Build one report that shows the full channel mix — paid, organic, outbound, referral — with revenue, spend, and ROI side by side.

foundermanagerAdvanced⏱ 10-14 hours
When to use
Use this before annual or quarterly planning when you need to defend or reshape channel investment. Also use when a new investor, partner, or leadership hire asks 'what does your channel mix look like?'
The prompt
You are a marketing analytics consultant for digital marketing agencies. You design board-grade reports that show the full channel mix without hand-waving.
Agency: [AGENCY_NAME] — [SERVICES] | CRM: [CRM_TOOL] | BI tool: [BI_TOOL] | Channels: [CHANNELS] | Annual marketing+sales budget: [BUDGET] | ARR target: [ARR_TARGET]
Design a single channel-mix attribution report showing for each channel: pipeline created, revenue closed, spend, CAC, LTV:CAC ratio, and % of total mix.

- Use ONE attribution model consistently across the report (call out which and why)
- Include organic and outbound channels even if spend = $0 — opportunity cost still counts (use loaded labor cost)
- Spend must include people cost (loaded SDR/content costs), not just media
- Show YoY or QoQ trend per channel — a snapshot hides shifts
- No 'Other' bucket over 10% — if it's bigger, the report is not ready to ship

1) Report layout: rows = channels, columns = pipeline, revenue, spend, CAC, LTV:CAC, % mix, YoY delta
2) Spend allocation method: how loaded cost is assigned to organic channels
3) Calculation definitions for every column
4) 1-page commentary template for the founder to fill in monthly
5) Distribution: who sees it, how often, what decision it drives
Variables
  • [AGENCY_NAME] — Your agency name
  • [SERVICES] — Service lines
  • [CRM_TOOL] — Your CRM
  • [BI_TOOL] — Looker, Hex, Metabase, native CRM, Sheets
  • [CHANNELS] — Every channel you invest in (paid + organic + outbound + referral)
  • [BUDGET] — Total annual marketing+sales budget incl. headcount
  • [ARR_TARGET] — ARR or revenue target for the year
Example input
Agency: Northbeam Studio | CRM: HubSpot | BI: Hex | Channels: SEO, paid social, LinkedIn outbound, podcast, partner referrals | Budget: $1.2M | Target: $4M ARR
Example output
LAYOUT
Rows: SEO | Paid Social | LinkedIn Outbound | Podcast | Partner Referrals | Direct
Columns: Pipeline $ | Revenue $ | Spend $ | CAC | LTV:CAC | % Mix | YoY %

SPEND ALLOCATION
SEO: $X media + loaded cost of content team time (hrs * $150)
LinkedIn outbound: $0 media + 2 SDRs fully loaded
Podcast: $0 media + founder time (hrs * $300) + producer
Partner: $0 media + AM time + 10% referral fee
All loaded costs allocated using a quarterly time-tracking exercise.

DEFINITIONS
Pipeline $ = sum(ARR of deals created in window, first-touch source = channel)
Revenue $ = sum(ARR of deals closed-won in window, first-touch = channel)
CAC = Spend / closed-won logos
LTV:CAC = (avg ARR * gross margin * avg years) / CAC

COMMENTARY TEMPLATE
'What changed since last month' / 'What we're doubling down on' / 'What we're cutting' / 'What we're testing next quarter'

DISTRIBUTION
Founder + Head of Growth + Head of Sales, monthly. Drives quarterly channel investment shifts.
Pro tips
  • Loaded cost matters — a 'free' channel that takes 20 founder-hours a week is not free, and the report should show it
  • Pick ONE attribution model and stay with it for at least 4 quarters — switching models mid-year breaks YoY comparisons
  • Always include a 'Direct' or 'Self-Reported Word of Mouth' row — pretending it's zero is the fastest way to lose trust in the report
Works with
ClaudeChatGPTGemini
Done with prompts? Time to install the system
Book a STAOS call
Related prompts