The problem
1,056 closed sponsorship opportunities were sitting in HubSpot. Deal values, close dates, won and lost reasons, associated contacts. All of it real, none of it usable for re-engagement.
Nobody could answer the only question that mattered: who have we already dealt with, what happened, and who can we go back to.
The system
Pull closed opportunities from HubSpot. Segment won from lost. Resolve each opportunity to its company. Retrieve contacts at the account level rather than the deal level. Consolidate, deduplicate, and check email availability. Output a single dataset joining deals to contacts, with the outcome reasons carried alongside them.
The numbers
- 1,056 closed sponsorship opportunities analysed
- 826 carried contact emails, resolving to 306 unique contacts
- 233 closed won, 73 closed lost
- 294 mailable contacts after validation
- Final dataset: 654 rows across 40 columns
The decision
Opportunity-level contacts were the obvious answer and the wrong one.
The brief could have been read as “pull the contacts attached to each deal”. That takes an afternoon and produces something almost useless, because a deal-attached contact is a snapshot of one conversation.
The person who signed a sponsorship in 2024 may have left. The person who blocked one may have been replaced by someone who would not. And the four other people at that company who never touched the deal are invisible.
So I rebuilt it at the account level. Not “who was on this deal” but “who is at this company, and what happened with them before”.
The first version answered the question I was asked. The second answered the question underneath it. That gap is most of the job.