Design a Pipeline Source vs Revenue Source View

You are a marketing analytics consultant for digital marketing agencies. You build reports that surface channels lying about their value.
Agency: [AGENCY_NAME] — [SERVICES] | CRM: [CRM_TOOL] | BI tool: [BI_TOOL] | Channels: [CHANNELS] | Stages in pipeline: [STAGE_LIST] | Avg deal size: [AVG_DEAL_SIZE]
Design a single-screen view with two stacked charts: Pipeline Created by Source vs Revenue Closed by Source, using the same time window and source taxonomy.

- Both charts use first-touch source from the SAME taxonomy — no double-counting
- Same time window, but show created-date for pipeline and close-date for revenue (annotate the lag)
- Include win rate and avg cycle time per source as a third row
- Highlight sources where pipeline rank != revenue rank (the liars)
- Must answer 'what should I cut, what should I double down on' in under 30 seconds of looking

1) Report spec: chart type, dimensions, metrics, filters per chart
2) Calculated fields needed (win rate, cycle time, gap rank)
3) SQL/HubSpot/Salesforce report build steps
4) Reading guide: 4 patterns to look for and what each means

• [AGENCY_NAME] — Your agency name
• [SERVICES] — Service lines
• [CRM_TOOL] — Your CRM
• [BI_TOOL] — Looker, Hex, Metabase, native CRM dashboards, etc.
• [CHANNELS] — Your active channels
• [STAGE_LIST] — Deal stages in your pipeline
• [AVG_DEAL_SIZE] — Average ARR or project value

Agency: Northbeam Studio | CRM: HubSpot | BI: native HubSpot + Hex | Channels: SEO, podcast, LinkedIn outbound, partner | Stages: SQL -> Proposal -> Negotiation -> Won/Lost | Avg deal: $48k ARR

REPORT SPEC
Chart 1 — Pipeline Created by Source: stacked bar, x=month, y=$ pipeline, color=first-touch source, filter=created last 90 days.
Chart 2 — Revenue Closed by Source: stacked bar, x=month, y=$ ARR closed-won, color=first-touch source, filter=closed last 90 days.
Chart 3 — Win Rate + Cycle by Source: table, columns=Source, Win Rate %, Median Days to Close.

CALCULATED FIELDS
Win Rate = won / (won + lost) by source
Cycle Time = median(close_date - create_date) by source
Gap Rank = rank(pipeline$) - rank(revenue$)

BUILD STEPS
HubSpot: 2 custom deal reports + 1 Hex query joining deals to first-touch source. Pin to a single 'Source Truth' dashboard.

READING GUIDE
Pattern 1: Big pipeline, small revenue -> source attracts tire kickers (cut budget or qualify harder).
Pattern 2: Small pipeline, big revenue -> underinvested gem (double down).
Pattern 3: Both big, slow cycle -> good channel, fix sales process.
Pattern 4: Both small -> kill it.

• Pin this dashboard above all others — it's the only one that answers 'where should the next dollar go'
• Annotate the lag — pipeline created in Jan closes in March, so the two charts will never match in the same month
• Add a fourth row for AOV by source — some channels bring small deals that look like wins but kill your unit economics