)
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.”
Preparing an Excel file that seamlessly handles both new and existing Salesforce contacts requires strategic data preparation and matching logic. Traditional Excel preparation lacks visibility into existing Salesforce data, creating risks of duplicate creation and missed updates.
Here’s how to create a comprehensive Excel preparation workflow that intelligently manages both contact populations with real-time validation.
Prepare integrated contact files using Coefficient
Coefficient facilitates this through bidirectional synchronization capabilities, allowing you to prepare Excel files with complete visibility into existing Salesforce data while maintaining proper separation between new and existing contact workflows.
How to make it work
Step 1. Create a master contact spreadsheet.
Use Coefficient to import all existing Salesforce contacts with Email, Name, Phone, and Account fields. Import your source Excel contact list into adjacent columns. This creates a unified view of both datasets for comprehensive comparison and validation.
Step 2. Implement email-based matching and classification.
Add a classification column with this formula: =IF(EXACT(SourceEmail,SFEmail),”Existing – Update”, IF(COUNTIF(SFEmailRange,SourceEmail)>0,”Existing – Duplicate”, “New – Insert”)). This automatically categorizes each contact based on email matching against your existing database.
Step 3. Enrich and validate your data.
For “Existing” contacts, merge new information from Excel with existing Salesforce data. For “New” contacts, validate email format and field completeness. Add columns for Record_Action (INSERT/UPDATE), Salesforce_ID, and Validation_Status to track processing requirements.
Step 4. Prepare segmented datasets.
Create separate sheets or sections for Update Records (existing contacts with Salesforce IDs plus new information), Insert Records (new contacts with complete required fields), and Review Queue (questionable matches requiring manual verification).
Step 5. Execute synchronized export process.
Use Coefficient’s UPSERT functionality with Email as External ID. Configure field mapping for both scenarios – include Salesforce ID for existing records and exclude ID field for new record creation. Enable preview mode to review changes before committing.
Step 6. Create unified list views.
Export processed Contact IDs (both updated and newly created) to Campaign Members. Create unified list views containing both existing and new contacts while maintaining audit trails of import source and processing dates.
Eliminate guesswork in Excel preparation
This integrated approach provides real-time visibility into existing Salesforce data during Excel preparation. You’ll handle both new and existing contacts appropriately without creating duplicates or missing updates. Start preparing comprehensive contact files today.
