Sales pipeline and revenue-operations analytics

Revenue-operations analytics connects acquisition, product behavior, sales process, and commercial outcomes. It should explain how eligible accounts enter the pipeline, move through stages, stall, close, or return—not merely report the CRM’s current opportunity state.

Keep contacts, accounts, opportunities, contracts, owner assignments, and forecast categories authoritative in the CRM or CPQ system. Send minimized, versioned transitions and snapshots to GraphJSON for analysis.

Define the grains#

Separate:

One account can have several opportunities. One opportunity can involve several people. Do not label opportunity count as customer count.

Prefer pseudonymous IDs and bounded categories. Names, email addresses, phone numbers, call notes, and contract text should stay in their source systems.

Emit effective stage transitions#

{
  "event": "sales_opportunity_stage_changed",
  "opportunity_id": "opp_91",
  "account_id": "acct_7",
  "previous_stage": "discovery",
  "stage": "technical_validation",
  "pipeline": "new_business",
  "stage_model_version": 4,
  "effective_at": "2026-07-25T18:00:00Z",
  "source_record_version": 184,
  "change_id": "crm_history_551"
}

Do not rely only on a latest-opportunity snapshot. It cannot reproduce stage entry, stage exit, skipped stages, regressions, or time in stage.

Use one controlled stage model. Map provider-specific stages to stable analytical stages in a versioned adapter.

Preserve creation, qualification, and closure#

{
  "event": "sales_opportunity_created",
  "opportunity_id": "opp_91",
  "account_id": "acct_7",
  "opportunity_type": "new_business",
  "pipeline": "new_business",
  "origin": "product_qualified",
  "amount_minor": 2400000,
  "currency": "usd",
  "expected_close_month": "2026-09",
  "stage_model_version": 4
}

{
  "event": "sales_opportunity_closed",
  "opportunity_id": "opp_91",
  "account_id": "acct_7",
  "outcome": "won",
  "close_reason": "customer_selected",
  "amount_minor": 2200000,
  "currency": "usd",
  "closed_at": "2026-09-12T20:00:00Z",
  "contract_id": "ctr_61"
}

Requested or estimated opportunity amount is not booked revenue. Preserve the amount meaning and reconcile won outcomes with the contract authority.

Publish pipeline snapshots#

Pipeline value is a point-in-time stock. Publish one governed snapshot by its intended grain:

{
  "event": "sales_pipeline_snapshot",
  "snapshot_at": "2026-07-26T00:00:00Z",
  "pipeline": "new_business",
  "stage": "technical_validation",
  "currency": "usd",
  "open_opportunities": 18,
  "unweighted_amount_minor": 42000000,
  "forecast_category": "pipeline",
  "definition_version": 3,
  "source_complete_through": "2026-07-25T23:45:00Z"
}

Do not sum daily pipeline snapshots. Use them to compare stock at two cutoffs or chart its level over time.

GraphJSON does not calculate an authoritative sales forecast. Probability weighting and forecast categories remain governed by Revenue Operations.

Define product-qualified signals#

A product-qualified account is an analytical evaluation, not an immutable customer trait:

{
  "event": "product_qualification_evaluated",
  "evaluation_id": "pqa:acct_7:2026-07-25:v3",
  "account_id": "acct_7",
  "is_qualified": true,
  "qualification_version": 3,
  "reason_codes": ["multi_user_activation", "usage_limit_reached"],
  "evaluation_at": "2026-07-26T00:00:00Z",
  "data_complete_through": "2026-07-25T23:00:00Z"
}

Keep the model explainable, versioned, and independently validated. Missing events, small accounts, and new accounts need explicit behavior.

Do not automatically create CRM tasks from an unvalidated analytics score.

Record the handoff#

{
  "event": "product_qualified_handoff_completed",
  "account_id": "acct_7",
  "evaluation_id": "pqa:acct_7:2026-07-25:v3",
  "destination": "sales_pipeline",
  "outcome": "opportunity_created",
  "opportunity_id": "opp_91",
  "days_to_handoff": 1
}

This lets teams separate qualification quality from workflow failure. A good signal that was never reviewed cannot be judged by closed-won rate alone.

Query stage conversion and velocity#

WITH transitions AS (
  SELECT
    JSONExtractString(json, 'opportunity_id') AS opportunity_id,
    minIf(
      toDateTime(timestamp),
      JSONExtractString(json, 'stage') = 'qualified'
    ) AS qualified_at,
    minIf(
      toDateTime(timestamp),
      JSONExtractString(json, 'stage') = 'technical_validation'
    ) AS validation_at
  FROM sales_events
  WHERE JSONExtractString(json, 'event') = 'sales_opportunity_stage_changed'
  GROUP BY opportunity_id
)
SELECT
  count() AS qualified_opportunities,
  countIf(
    validation_at >= qualified_at
    AND validation_at < qualified_at + INTERVAL 90 DAY
  ) AS reached_validation,
  reached_validation / nullIf(qualified_opportunities, 0) AS conversion_rate,
  quantileExactIf(
    0.5
  )(
    dateDiff('day', qualified_at, validation_at),
    validation_at >= qualified_at
  ) AS median_days_to_validation
FROM transitions
WHERE
  qualified_at > toDateTime(0)
  AND qualified_at < now() - INTERVAL 90 DAY

The maturity window avoids counting still-open recent opportunities as failures. Choose the window from actual sales-cycle behavior.

Handle regressions, reopenings, and duplicates#

Opportunities can:

• move backward
• skip stages
• close lost and reopen
• split or merge
• be duplicated in the CRM
• change pipeline

Preserve every effective transition and a stable opportunity identity. Define whether stage conversion means “ever reached,” “reached in order,” or “first pass only.”

A reopened opportunity is not automatically a new customer opportunity. Use a controlled reopen reason and link the prior closure.

Join acquisition and product context at event time#

Useful prior context includes:

• acquisition channel and attribution version
• activation and time to value
• collaboration depth
• feature and usage-limit encounters
• plan and trial state
• account size and region
• support or reliability experience

Use state known before opportunity creation or stage entry. Today’s plan and current product usage can leak the eventual outcome into an earlier-stage analysis.

Product engagement can be associated with winning because sales focuses on engaged accounts. It does not prove the behavior caused the sale.

Keep pipeline coverage honest#

Common measures:

stage conversion
  = mature opportunities reaching later stage
  / eligible opportunities entering earlier stage

win rate
  = won opportunities
  / mature closed opportunities or eligible created opportunities

sales velocity
  = stage or close timing distribution for a defined cohort

State whether the denominator excludes open, disqualified, duplicate, or administratively closed opportunities.

Keep currencies separate or use a governed FX policy.

Build the Revenue Operations dashboard#

Show:

1. new eligible accounts, qualifications, and reviewed handoffs
2. opportunities created by origin and pipeline
3. mature stage conversion and time in stage
4. open pipeline snapshots by stage and forecast category
5. won, lost, reopened, and disqualified outcomes
6. product context before qualification and opportunity creation
7. acquisition, segment, and stage-model versions
8. CRM synchronization freshness and reconciliation

Do not rank individual salespeople from analytics events without a reviewed purpose, complete assignment history, and appropriate access.

Launch checklist#

• Lead, account, opportunity, snapshot, and contract grains are distinct.
• CRM and contract systems remain authoritative.
• Effective stage transitions are preserved, not only latest state.
• Pipeline snapshots are never summed across time.
• Product qualification is versioned, explainable, and freshness-aware.
• Handoff completion is measured separately from signal quality.
• Stage rates use mature, explicitly eligible populations.
• Regressions, reopenings, splits, and duplicates have policies.
• Product context precedes the sales outcome being analyzed.
• Money preserves currency and meaning.
• Settled opportunity IDs and amounts reconcile with the CRM.