Campaign evidence guide
Follow the same lead into the completed job
Use the CSV pair to preserve the source, permission reference, operating stages, and mature-job economics. The blank files contain headers only. The completed example is fictional and contains no customer contact data.
- Blank lead and job tracker
- Blank campaign-cost sheet
- 100-row fictional example and matching example costs
Import and record rules
- Open or import the CSV into Excel, Google Sheets, or the approved reporting system. Dates use YYYY-MM-DD, money is numeric USD, and there are no embedded formulas or macros.
- Use one lead-sheet row per raw source event. Preserve invalids and duplicates so the raw denominator matches the exported source. Enter actual dates only; a future appointment date belongs in the next-action field until that stage occurs.
- Use
crm_lead_idto join to the operating system. A duplicate retains its raw-event ID and points to the original CRM record induplicate_of_crm_lead_id. Do not count that duplicate again as qualified or copy revenue onto it. - Keep one cost row per cohort. Join by
cohort_id, but sum cohort cost once. Repeating a $5,000 invoice across 100 lead rows would overstate media spend by 100 times. - Count a job once by
job_id, and require both completed and collected milestones for the completed-and-collected denominator. If one lead creates multiple jobs, add a separate job ledger keyed to CRM lead ID rather than duplicating lead-stage counts. - Retain only permission references here. Store personal details, exact disclosures, messages, and job records in approved systems. A populated reference is not proof of permission without the underlying record.
Lead-field dictionary
| Field or group | Definition |
|---|---|
| record_kind | Use a value identifying live records in your working file. Keep FICTIONAL EXAMPLE rows out of live reporting. |
| cohort_id, raw_event_id, source_date | Frozen acquisition cohort, unique source event, and actual source date. |
| campaign_id, ad_id, creative_id, form_or_destination_version | References linking the record to the exact ad promise and destination. |
| crm_lead_id, duplicate_of_crm_lead_id | Stable operating record and duplicate relationship. Preserve the original source when records merge. |
| valid_unique, qualification_status, qualification_reason | Use yes/no for valid unique; qualified/not qualified/duplicate/invalid for qualification. Record why; validity is not qualification. |
| permission_record_reference, contact_channel | Reference to the actual permission evidence and the requested channel, subject to the current suppression state in the operating system. |
| contacted_date | Date of a two-way exchange, not an attempted call or sent message. |
| appointment_set_date, appointment_held_date | Date the appointment was agreed, then the actual date it was held. |
| agreement_signed_date, job_id, job_status | Actual accepted-agreement date, unique job record, and current status, including cancellation. |
| completed_date, collected_date | Actual milestones under the company’s declared completion and collection policy. |
| collected_revenue_usd, actual_direct_job_cost_usd | Finance records for that unique job. Direct job cost excludes acquisition costs already in the campaign file. |
| outcome_reason, owner, next_action, next_action_due_date | Actual disposition and the next accountable step. Leave unreached stage dates blank. |
Campaign-cost dictionary
Record reporting start/end, maturity cutoff, and analysis date separately. Freeze the attribution rule and qualification definition. Record media, management, creative, page/form, tracking, attributable intake labor, and other nonmedia costs separately. Document allocation rules in accounting_policy_note; do not silently change them between cohorts.
Reconcile the fictional 100-lead example
| Measure | Expected value |
|---|---|
| Raw / valid unique / qualified | 100 / 80 / 50 |
| Contacted / set / held | 40 / 25 / 20 |
| Signed / completed and collected | 6 / 5; one signed job cancelled |
| Media / nonmedia / total acquisition cost | $5,000 / $2,500 / $7,500 |
| Media raw CPL / fully loaded raw CPL | $5,000 ÷ 100 = $50 / $7,500 ÷ 100 = $75 |
| Qualified / held / signed / collected job cost | $150 / $375 / $1,250 / $1,500 |
| Collected revenue / direct job cost / gross profit | $100,000 / $75,000 / $25,000 |
| Contribution after acquisition | $25,000 − $7,500 = $17,500 |
This demonstrates the definitions and joins. It is not a roofing benchmark, account result, promise of $50 leads, or forecast. Use the existing marketing ROI model after your own records reconcile.