Connect campaign accounts to qualified pipeline, won contracts, and earned revenue. Use CRM and finance exports to compare equally mature cohorts without multiplying opportunities across buying-group contacts.
B2B marketing analytics connects campaign accounts to qualified pipeline, won contracts, and earned revenue. Use an account cohort rather than a contact list, preserve distinct opportunity identifiers, and give each cohort the same time to develop. The resulting comparison shows commercial outcomes without counting the same buying group repeatedly.
Consider a fictional US industrial services company comparing campaign accounts that all enroll on January 1, 2026. Outcomes are observed through September 30, 2026, so every account has the same observation age. Real cohorts with different enrollment dates need matched elapsed observation windows or separate enrollment groups; sharing a quarter and a cutoff alone does not give accounts equal maturity.
Agree what makes an account qualified. Here it means sales has accepted a documented opportunity under the company's qualification rule. A webinar registration alone does not qualify an account. Sales operations owns this definition and the opportunity status history.
Assign each account to one primary campaign for this additive report. Retain other campaign memberships in a supporting file. This is a reporting allocation, not proof that one campaign deserves all the credit. Apply the same rule before looking at outcomes so the strongest results cannot determine their own attribution.
The B2B marketing KPI guide covers metric selection. This workflow concentrates on the account and opportunity matching that makes those measures reproducible.
| File | One row represents | Required fields |
|---|---|---|
| Accounts | One cohort account | Account key, primary campaign, enrollment date, segment |
| Opportunities | One distinct opportunity | Opportunity key, account key, creation date, qualification date, win date, qualification flag, qualified value, won flag, won contract value |
| Finance | One revenue posting | Posting key, opportunity or contract reference, earned revenue, posting date |
Keep contact and campaign membership exports available to verify cohort eligibility. Do not join a contact list directly onto opportunity amounts: every buying-group member could repeat the same amount. Where account identifiers are absent, request a reviewed mapping from the CRM owner; similar company names are not a reliable automatic match.
Filter Accounts to the selected enrollment date. Exclude opportunities created before enrollment from this acquisition report and show them separately if needed. Qualification and win flags must represent eligible events on or after enrollment and through the outcome cutoff. Finance postings must follow those eligible won opportunity or contract references, within the same observation window; exclude postings from older or unrelated contracts. Exclude reversed or invalid opportunities under an explicit rule, and retain unmatched opportunity and finance rows for reconciliation.
Qualified value in this report is the value recorded when an opportunity first qualified. It includes opportunities that later won or lost. It is not the current open pipeline balance, and it must not be added to won value as though they were separate pools.
On Opportunities, use columns A through F for opportunity key, account key, qualified flag, qualified value, won flag, and won contract value. Add creation date in G, qualification date in H, and win date in I. For this example, Inputs!B1 holds the common enrollment date and Inputs!B2 the cutoff. Calculate C2 and E2 as follows, using valid spreadsheet dates:
=--AND(G2>=Inputs!$B$1,H2>=G2,H2<=Inputs!$B$2,H2<>"")
=--AND(C2=1,I2>=H2,I2<=Inputs!$B$2,I2<>"")
An eligible outcome is flagged with 1; otherwise use 0. Review missing or inconsistent event dates before calculating results. For mixed enrollment dates, use each account's enrollment date and matched observation endpoint instead of these common input dates.
On Accounts, use A for account key and B for primary campaign. Add C for qualified-account flag, D for qualified value, E for won-account flag, and F for won contract value:
=IF(A2="","",--(COUNTIFS(Opportunities!$B$2:$B$10000,A2,Opportunities!$C$2:$C$10000,1)>0))
=IF(A2="","",SUMIFS(Opportunities!$D$2:$D$10000,Opportunities!$B$2:$B$10000,A2,Opportunities!$C$2:$C$10000,1))
=IF(A2="","",--(COUNTIFS(Opportunities!$B$2:$B$10000,A2,Opportunities!$E$2:$E$10000,1)>0))
=IF(A2="","",SUMIFS(Opportunities!$F$2:$F$10000,Opportunities!$B$2:$B$10000,A2,Opportunities!$E$2:$E$10000,1))
Extend ranges to the full export. The Google Sheets SUMIFS documentation defines the function as summing a range subject to multiple criteria. Account flags keep an account with several opportunities from becoming several qualified accounts, while value columns retain the distinct opportunities' amounts.
Create Summary columns A through F for campaign, accounts, qualified accounts, qualified value, won accounts, and won contract value. With a campaign name in A2:
=COUNTIF(Accounts!$B$2:$B$10000,A2)
=SUMIF(Accounts!$B$2:$B$10000,A2,Accounts!$C$2:$C$10000)
=SUMIF(Accounts!$B$2:$B$10000,A2,Accounts!$D$2:$D$10000)
=SUMIF(Accounts!$B$2:$B$10000,A2,Accounts!$E$2:$E$10000)
=SUMIF(Accounts!$B$2:$B$10000,A2,Accounts!$F$2:$F$10000)
Qualified-account rate is =IF(B2=0,"",C2/B2). Qualified value per cohort account is =IF(B2=0,"",D2/B2). Show percentages to one decimal place and amounts in whole USD.
| Campaign | Accounts | Qualified accounts | Qualified value (USD) | Won accounts | Won contract value (USD) | Qualified-account rate | Qualified value per account (USD) |
|---|---|---|---|---|---|---|---|
| Webinar | 30 | 12 | 360,000 | 4 | 100,000 | 40.0% | 12,000 |
| Industry event | 20 | 10 | 400,000 | 2 | 110,000 | 50.0% | 20,000 |
| Content program | 50 | 10 | 200,000 | 3 | 45,000 | 20.0% | 4,000 |
| Total | 100 | 32 | 960,000 | 9 | 255,000 | 32.0% | 9,600 |
The total qualified-account rate is 32 / 100 = 32.0%, using counts rather than the average of campaign percentages. The event has the highest qualified value per account, but only two won accounts. Review the remaining opportunities before treating its eventual outcome as settled. The sales benchmark guide explains why comparable groups matter more than a universal target.
Finance maps postings back to the distinct opportunity or contract references. In this example, the matched earned revenue through the same cutoff is:
| Campaign | Won contract value (USD) | Earned revenue to cutoff (USD) |
|---|---|---|
| Webinar | 100,000 | 60,000 |
| Industry event | 110,000 | 44,000 |
| Content program | 45,000 | 27,000 |
| Total | 255,000 | 131,000 |
The revenue column sums the mapped finance postings; it is not a percentage automatically applied to contracts. The SEC's financial statement guide describes an income statement as reporting revenue earned over a specified period. Keep the $255,000 CRM result distinct from the $131,000 finance result.
Campaign account totals must tie to the unique cohort register, opportunity values to distinct CRM records, and earned revenue to the mapped ledger postings. Matched and unmatched postings together must tie to the finance extract. Broader customer and account questions belong in B2B analytics.
Bring a sample of your own campaign account, opportunity, and finance files to Covirage. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the comparisons with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your files.
See sales insights for the supporting business context. Covirage is not affiliated with Google Sheets.
Several contacts can participate in one buying decision. Counting each contact as a separate opportunity repeats the same commercial outcome. An account view preserves those contacts as engagement context while counting each opportunity identifier once in pipeline and contract totals.
No. Closed-won value is a CRM contract measure under the company's booking policy. Earned revenue follows the finance team's recognition policy and timing. Keep both columns when they answer different questions, and label the source and observation date of each.
For an additive campaign report, assign the account to one primary cohort using a documented rule. Alternatively, show overlapping campaign exposure explicitly, but do not add its campaign totals into a unique company total. Preserve the original memberships for review.
Use comparable historical cohorts with the same qualification rule, account segment, and observation window. A rate from a different sales motion can be misleading. Review the account counts beneath the percentages and the remaining open opportunities before setting a target.