Data Mapping

Pipeline KPIs

The exact formula behind every Pipeline Overview number — leads, appointments, won, revenue, ROAS, and the cost-per-X metrics.

Where it lives

The Pipeline Overview tab lives inside the Client Portal at src/components/portal/sections/PipelineSection.tsx. It is the highest-traffic surface in the dashboard for the agency leadership.

Inputs

The component accepts these props (all sourced from ClientDashboard):

Prop	Type	Source
ghlMetrics	GHLMetricsData	/api/metrics/ghl
googleAdsData	GoogleAdsMetrics	/api/metrics/google-ads
metaAdsData	any	/api/metrics/meta-ads
gbpMetrics	GBPMetricsData	/api/metrics/gbp
ghlLeadsBySource	Record<string, number>	/api/metrics/ghl (leadsBySource)
ghlWeeklyTrend	Array of weekly buckets	/api/metrics/ghl (weeklyTrend)
ghlStageBreakdown	Record<string, number>	/api/metrics/ghl (stageBreakdown)
externalData	Record<string, ExternalMetric>	/api/metrics/external/[clientId]

Atomic counters from GHL

const leads     = ghlMetrics?.opportunities.total ?? 0   // every opportunity in date range
const booked    = ghlMetrics?.appointmentsBooked?.value ?? 0
const showed    = ghlMetrics?.appointmentsShowed?.value ?? 0
const cancelled = ghlMetrics?.appointmentsCancelled?.value ?? 0
const won       = ghlMetrics?.wonDeals.value ?? 0
const revenue   = ghlMetrics?.pipelineValue.value ?? 0   // sum of won deal monetaryValue

Rates

const leadToAppt    = leads > 0 ? (booked / leads) × 100 : 0      // %
const leadToSale    = leads > 0 ? (won / leads) × 100 : 0         // %
const apptToSale    = booked > 0 ? (won / booked) × 100 : 0       // %
const bookRate      = leads > 0 ? (booked / leads) × 100 : 0
const showRate      = booked > 0 ? (showed / booked) × 100 : 0
const cancelRate    = booked > 0 ? (cancelled / booked) × 100 : 0
const closeRate     = showed > 0 ? (won / showed) × 100 : 0

The Pipeline Rates chart (PipelineRatesChart.tsx) plots book rate / show rate / cancel rate / close rate over the last 12 weeks. Source data is the all-time ghlWeeklyTrend, not the date-range-filtered counters above.

Costs and ROAS

const googleSpend = googleAdsData?.adSpend.value ?? 0
const metaSpend   = metaAdsData?.adSpend?.value ?? 0
const adSpend     = googleSpend + metaSpend

const avgTicket   = won > 0   ? `$${(revenue / won).toLocaleString()}` : ext('avg_ticket_value') ?? '-'
const costPerLead = adSpend > 0 && leads > 0  ? `$${(adSpend / leads).toFixed(2)}` : ext('cost_per_lead') ?? '-'
const costPerAppt = adSpend > 0 && booked > 0 ? `$${(adSpend / booked).toFixed(2)}` : ext('cost_per_appointment') ?? '-'
const costPerSale = adSpend > 0 && won > 0    ? `$${(adSpend / won).toFixed(2)}`    : ext('cost_per_sale') ?? '-'
const roas        = adSpend > 0 ? `${((revenue / adSpend) × 100).toFixed(1)}%`     : ext('roas') ?? '-'

Live → external fallback

The pattern is consistent: if we can compute the metric from live data (we have ad spend AND we have GHL revenue), we use the live calc. Otherwise we fall back to whatever value n8n pushed (ext('cost_per_lead')). If neither exists, the row hides via metricHasValue.

Leads by source

The Leads by Source card mixes attribution from GHL (organic / referral / direct / other) with platform truth from Google Ads / GBP:

Row	Logic
Leads from Google Ads	ghlLeadsBySource.google_ads (GHL contacts marked as google_ads source)
Leads from GBP	ghlLeadsBySource.gbp
Leads from Organic SEO	ghlLeadsBySource.organic_seo
Leads from Social Media	ghlLeadsBySource.social_media
Leads from Direct	ghlLeadsBySource.direct
Leads from Other Referral	ghlLeadsBySource.direct_referral
Leads from Other Advertising	ghlLeadsBySource.other
Leads from AI Overview / LLMs	AIEO — not yet integrated

Source tagging is the bottleneck

These numbers are only as accurate as the contact source field in GHL. Account managers must standardise contact tagging at intake (UTM auto-population on web forms, consistent labels for offline calls). When a client's breakdown looks suspicious, it is almost always a tagging issue, not a code bug.

Lead Type Breakdown

Different from Leads by Source — this card splits inbound leads by channel (call vs form vs chat) rather than by acquisition source.

Phone Calls - Total — from GBP Performance API (callClicks).
Qualified Phone Calls — calls ≥60 seconds from GHL conversation history (pending wiring).
Unique Qualified Inbound Calls — first-time callers, deduped on phone number.
Webform Leads / Webchat Leads — GHL conversations split by channel.
Walk-ins — manual entry, currently null.
Outbound Contact Attempts — counts of outbound calls/emails initiated by staff.

Pipeline Stages chart

Distribution of opportunities across pipeline stages, from ghlStageBreakdown. Data shape is { 'Stage Name': count }. Rendered by PipelineStagesChart.tsx as a horizontal bar chart.

Auto-hide behaviour

Every row uses MetricRow; MetricRow returns null when the value is null, undefined, '', or '-'. A 0 value still renders. This means a card that depends entirely on disconnected sources (e.g. Lead Type Breakdown when GHL is not yet wired) shows nothing — which is correct behaviour, not a bug.

PreviousVariable → Source Next Lead Attribution