GTM Metric Calculations

Total ACV of open opportunities at Stage 2+ (Qualified or later). This is the single most contested metric in GTM analytics — Sales, Marketing, and Finance almost always define it differently. The SQL below implements the Rev Ops standard: stage-gated, excludes closed deals, and uses the current ACV field (not the original estimate).

-- Qualified Pipeline: Current ACV of all open opportunities at Stage 2+
-- Owner: Rev Ops | Refresh: Daily 6:00 AM ET | Source: Salesforce Opportunity
WITH stage_map AS (
SELECT stage_name,
CASE stage_name
WHEN 'Prospecting'  THEN 1
WHEN 'Qualified'    THEN 2
WHEN 'Demo'         THEN 3
WHEN 'Proposal'     THEN 4
WHEN 'Negotiation'  THEN 5
WHEN 'Closed Won'   THEN 6
WHEN 'Closed Lost'  THEN 7
END AS stage_order
FROM salesforce.opportunity
)
SELECT
o.owner_name                          AS rep,
a.specialty_segment                   AS specialty,
o.lead_source                         AS source_channel,
COUNT(DISTINCT o.opportunity_id)      AS deal_count,
SUM(o.acv)                            AS pipeline_acv,
AVG(o.acv)                            AS avg_deal_size,
MEDIAN(o.acv)                         AS median_deal_size
FROM salesforce.opportunity o
JOIN salesforce.account a
ON o.account_id = a.account_id
JOIN stage_map sm
ON o.stage_name = sm.stage_name
WHERE sm.stage_order BETWEEN 2 AND 5     -- Qualified through Negotiation
AND o.is_closed = FALSE               -- Exclude won and lost
AND o.acv >= 5000                     -- Floor: exclude sub-$5K trials
AND o.is_deleted = FALSE
GROUP BY 1, 2, 3
ORDER BY pipeline_acv DESC;