CRM marketing analytics: connect campaigns to sales outcomes

CRM marketing analytics follows recorded campaign cohorts from lead creation into opportunities and won sales. Start with stable identifiers and equal observation windows, then check that every opportunity amount appears once. The result supports campaign and sales follow-up decisions without pretending that campaign membership proves causation.

Set the cohort before opening the opportunity report

Choose the question first: which campaign cohorts reached sales opportunities within the agreed window? A June acquisition cohort reviewed after each lead has had 90 days answers a different question from opportunities that happened to close during June.

Export campaign membership with lead or contact ID, campaign ID, membership date, and source. Export opportunities with opportunity ID, linked customer or contact ID, creation date, dated stage changes, close date, and amount in USD. Preserve the original files and the reporting cutoff so someone else can rebuild the comparison.

Assign each lead to one mutually exclusive acquisition cohort for this report. Keep other campaign memberships in a separate file. This assignment is a reporting convention, not an allocation of marketing credit; marketing attribution methods address that separate question.

Resolve merged leads and converted contacts before matching. If identifiers changed between exports, use the documented crosswalk described in identifier drift. An unmatched lead remains an exception to investigate, rather than silently becoming a lead with no opportunity.

Build a small, auditable cohort table

Consider a US industrial services company comparing webinar and guide cohorts. Every lead below has completed the same observation window. Stages are the recorded positions at the end of that window, not today's latest stages.

The example deliberately assigns each opportunity to one lead and each lead to no more than one opportunity. Real buying groups need a distinct-opportunity table alongside the lead table. Never repeat an opportunity amount on every participating contact.

Place those columns in A:E, with headers on row 1 and data on rows 2:9. Empty opportunity cells are genuinely blank. Won amount means the recorded won contract amount in this example; it is not accounting revenue recognized during the period. The zeros on other rows mean no won amount at cutoff, rather than a missing contract value.

Add an opportunity flag in F2 and a won flag in G2, then fill down:

=--(C2<>"")
=--(D2="Won")

Keep missing opportunity links in a separate exception list before setting the flags. A clean flag cannot repair an incomplete export. The CRM data quality scorecard provides a broader check on the records behind this table.

Calculate progression and won amounts

Put Webinar in J2 and Guide in J3. Count leads in K2, then fill down:

=COUNTIFS($B$2:$B$9,J2)

COUNTIFS counts records meeting the specified criteria, and its criteria ranges must have matching dimensions, as the Google Sheets function documentation explains.

Calculate opportunity-bearing leads in L2, won leads in M2, and won amount in N2:

=SUMIFS($F$2:$F$9,$B$2:$B$9,J2)
=SUMIFS($G$2:$G$9,$B$2:$B$9,J2)
=SUMIFS($E$2:$E$9,$B$2:$B$9,J2,$D$2:$D$9,"Won")

SUMIFS adds values meeting all the supplied criteria; the official function reference documents that behavior. Covirage is not affiliated with Google.

Use =IF(K2=0,"",L2/K2) for lead-to-opportunity conversion, =IF(K2=0,"",M2/K2) for lead-to-win conversion, and =IF(K2=0,"",N2/K2) for won amount per lead.

Percentages are rounded to one decimal place. Webinar conversion is 3 / 4 = 75.0%; total conversion is 5 / 8 = 62.5%. Total won amount per lead is 80,000 / 8 = $10,000. Divide the combined amounts by the combined counts when cohorts differ in size.

Prove the totals before choosing a campaign

The detailed table contains five distinct opportunity IDs: one for each flagged lead. Its stages reconcile as two won, two open, and one lost opportunity. Those counts must match the filtered opportunity export for this cohort and cutoff.

Won amounts reconcile as $60,000 + $20,000 = $80,000. Check that against the distinct won-opportunity rows before joining any additional campaign memberships. Also confirm that four webinar leads plus four guide leads equal the eight original cohort members.

The webinar has more opportunity progression and a larger recorded won amount, while lead-to-win conversion is equal. That supports reviewing deal size, lead qualification, and follow-up. It does not establish that moving budget to webinars will reproduce the difference. For the wider account context, see B2B marketing analytics.

Where it goes wrong

• Comparing unequal ages. A recent cohort has had less time to progress. Apply the same elapsed window and retain dated stage evidence.
• Multiplying opportunities. Several campaign members can point to one opportunity. Reconcile amounts at the opportunity level before summarizing.
• Treating missing links as failure. An unmatched identifier is a data exception until reviewed, not proof that the lead produced nothing.
• Changing the revenue definition. Contract value, annual recurring value, invoiced sales, and recognized revenue are different measures. Use one named definition throughout.
• Calling association incremental impact. Campaign selection and sales effort can differ between cohorts. Record those differences before proposing an experiment.

Do this on your own file in Covirage

Bring your campaign membership and opportunity exports, including stable IDs, dated stages, and the amount definition. Covirage maps the columns; its tools compute and check the cohort comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See sales insights for the supporting sales analysis context.