Connect campaign data to revenue by storing attribution on the enquiry, linking that enquiry to a CRM opportunity, and applying a documented credit rule. Report qualified pipeline and closed-won value separately, and show how much value has no known source.
A paid search campaign generates 40 enquiries and a partner campaign generates eight. Months later, the partner campaign has produced more closed-won value. You can see that difference only if the campaign data survives from the website request to the deal.
Decide which record answers each question
| Record | Useful fields | Question it answers |
|---|---|---|
| Enquiry | Enquiry ID, campaign, acceptance time | Which campaign was captured on this request? |
| Contact | Contact ID, acquisition source | Where did this person first enquire? |
| Opportunity | Opportunity ID, originating enquiry ID, stage, amount, currency | Which request became a deal? |
| Closed-won outcome | Close date, won amount, currency | What value was booked in the CRM? |
An enquiry can be associated with multiple opportunities, and multiple people can be involved in one opportunity. Define the relationship your report will use. For a simple sourced report, record one originating enquiry per opportunity, with an audit trail for corrections.
Worked example using one credit rule
Assume a fictional agency credits each deal to the campaign on its originating enquiry. The report uses USD and groups opportunities created from enquiries accepted in November.
| Opportunity | Campaign on originating enquiry | Outcome | CRM value |
|---|---|---|---|
| O-101 | Paid search | Open, qualified | USD 25,000 |
| O-102 | Partner newsletter | Closed won | USD 18,000 |
| O-103 | Unknown | Open, qualified | USD 12,000 |
The report shows USD 37,000 in open qualified pipeline, of which USD 12,000 has an unknown source. It also shows USD 18,000 in closed-won value credited to the partner newsletter. If accounting tracks invoices or collected cash separately, use those figures only in a report that joins the appropriate finance records.
Avoid counting an opportunity more than once
A contact may submit several forms before a deal opens. Joining opportunities to all enquiries for that contact can multiply the deal amount. Join via the originating enquiry ID for a single-source report, and check that the result has at most one row per opportunity before aggregating.
For an influenced report, a deal may appear under several campaigns. Label it accordingly. If you allocate value across campaigns, define weights that sum to 100% per deal and validate them before reporting. Attribution expresses how the report assigns credit. An experiment would be needed to estimate incremental impact.
Keep date, currency and stage rules visible
An enquiry cohort groups results by when enquiries were accepted. A sales-period report groups wins by close date. Use both when the sales cycle spans months and state which view readers are looking at.
Keep the original currency on each opportunity. If you need a combined currency view, apply an agreed conversion date and rate source. Define whether qualified pipeline uses current open deals or every deal that ever reached qualification, since those answer different questions.
Before publishing, reconcile opportunity totals to the CRM under the same filters. Check for duplicate opportunity IDs, broken enquiry links, unknown campaigns and late stage changes. The report should let a reader trace an aggregate back to the deals that make it up.
