post-event-roi-report

Post-event ROI report

Build a view that answers one question for the review meeting: what did {{EVENT_NAMES}} produce, and what is stuck? The lead list says who we met. The CRM says what became pipeline. When they disagree, the CRM wins.

Template placeholders

Replace every {{...}} before running.

• {{EVENT_NAMES}}: the events being measured, exactly as they appear in the lead list's Event column
• {{EVENT_START_DATE}}: the first day of the first event; the cutoff for new versus pre-existing pipeline
• {{DAY_30_DATE}}, {{DAY_60_DATE}}: the two checkpoint dates
• {{SPREADSHEET}}: where the event lead list lives
• {{ICP_FLOOR}}: the minimum employee count that counts toward the totals
• {{CURRENCY}}: currency for spend and pipeline
• {{TIER_LIST}}: your account tiers
• {{STALL_DEMO_DAYS}}, {{STALL_STAGE_DAYS}}: stall thresholds (for example 10 and 14)
• {{SPECIALIST_TEAM}}: a team follow-ups often wait on, such as RevOps

Inputs

Event lead list ({{SPREADSHEET}}, re-read on every load, never hardcoded). One row per account. Several people at one account stay on one row and count once. Columns used:

• Company, Person, Title, Employees, Tier, Owner, Priority
• Event (must match one of {{EVENT_NAMES}} or "Both")
• Meeting status, Demo held (date), Opp created (date), Opp amount, Opp stage
• Next step, Follow up by (date), Notes

Side tabs. Partners, community and investors go in their own section and stay out of every total. Accounts under {{ICP_FLOOR}} employees stay out of the totals and appear only as a count in a footnote.

CRM deals. Name, amount, stage, create date, close date, date entered current stage, closed-won and closed-lost flags. Fetch every deal created in the last year and match in code. Do not query one account at a time.

Manual input. Event spend in {{CURRENCY}}. The list does not hold it. Store it, and leave the ratios blank until someone enters it.

Attribution rules

1. An account counts only if Event names one of {{EVENT_NAMES}} or "Both". Rows marked "Not an event lead" or "Unknown" go to a separate "Other leads needing a reply" list. They do not count toward ROI.
2. Match accounts to deals by normalized company name (lowercase, letters and digits only). Handle parenthetical aliases. Accept a prefix match only when both names are six or more characters. Report unmatched event accounts so a person can check them.
3. Exclude pre-existing pipeline. A deal created before {{EVENT_START_DATE}} is not event pipeline. Use the CRM create date first. Fall back to a note such as "already in pipeline before the events" or a status of "Existing deal". Show these accounts as "Influenced (existing pipeline)", with the reason, outside the headline.
4. An account with both an old deal and a new deal created on or after the start date counts on the new deal.