)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
When contact emails are stored in HubSpot deal properties instead of proper contact records, native deduplication fails completely. You can build sophisticated deduplication logic that extracts emails from deal properties and creates multi-level validation to identify and merge duplicate HubSpot deals.
This transforms HubSpot’s limitation into a powerful deduplication opportunity using spreadsheet-based logic.
Extract emails from deal properties and build advanced deduplication using Coefficient
Coefficient transforms the challenge of emails trapped in deal properties into a comprehensive deduplication solution. You can extract, normalize, and match emails while building sophisticated validation logic that HubSpot’s native tools cannot achieve.
How to make it work
Step 1. Import deals and normalize email data.
Import all HubSpot deals with their custom email properties. Create a normalized email column using `=LOWER(TRIM(B2))` to standardize formatting. Use REGEXEXTRACT to handle multiple email formats and build domain extraction for company-level deduplication.
Step 2. Build multi-level duplicate detection formulas.
Create primary deduplication: `=COUNTIF(C:C,C2)>1` for exact email matches. Add secondary checks: `=OR(COUNTIFS(D:D,D2,E:E,E2)>1,COUNTIFS(F:F,F2,G:G,”>=”&G2-7,G:G,”<="&G2+7)>1)` to catch company/amount matches and date-proximity duplicates.
Step 3. Create duplicate groups and identify primary deals.
Use RANK functions to create duplicate group IDs. Within each group, identify the “winner” deal based on most recent activity, highest value, most complete data, or latest stage progression. Build merge strategy columns showing which deals to preserve versus archive.
Step 4. Execute staged merge operations.
Create preservation snapshots before merging. Use Coefficient’s conditional export to UPDATE primary deals with merged information, add activity notes documenting the merge source, and sum deal amounts if applicable. Schedule DELETE exports for source deals after verification.
Step 5. Implement ongoing prevention and monitoring.
Schedule hourly imports to catch new deals. Use Formula Auto Fill Down to auto-apply deduplication formulas. Set up Slack alerts for new duplicates and create dashboards showing duplicate rate trends, common sources, and email extraction success rates.
Turn data limitations into deduplication advantages
This approach handles sophisticated pattern matching and bulk operations impossible with HubSpot’s native deduplication when emails are stored in deal properties. You get complete audit trails and can prevent future duplicates through ongoing monitoring. Start building your advanced deduplication system today.
