Contact Us
Home » Insights » How to Create a Unified Salesforce Lead and Contact Data Extension in Marketing Cloud (With SQL Query)

How to Create a Unified Salesforce Lead and Contact Data Extension in Marketing Cloud (With SQL Query)

… Last Updated:

A close up of the SQL call that is a puzzle piece to many in Salesforce
“Puzzle of complexity” by PictureWendy is licensed under CC BY-NC 2.0 .

The Problem

When using Salesforce and Marketing Cloud together, you’ll often need to send marketing communications to both Leads and Contacts. While Marketing Cloud Connect syncs these as separate objects, creating campaigns that target both requires manual list management or duplicate sends.

This creates several challenges:

  • Duplicate communications if someone exists as both a Lead and Contact
  • Data fragmentation across multiple lists
  • Campaign complexity managing separate audience segments
  • Reporting confusion when trying to measure unified campaign performance

What You’ll Accomplish

By the end of this tutorial, you’ll have:

  • A single Data Extension containing both Leads and Contacts
  • Automatic deduplication with Contact data prioritized over Lead data
  • Proper opt-out preference handling
  • A reusable SQL query for ongoing automation

Prerequisites

Before starting, ensure you have:

  • Salesforce Marketing Cloud account with SQL Query activities enabled
  • Marketing Cloud Connect configured between Salesforce and Marketing Cloud
  • Lead_Salesforce and Contact_Salesforce Data Extensions already syncing
  • Automation Studio access permissions
  • Basic familiarity with Data Extensions and SQL Query activities

Step 1: Create Your Target Data Extension

  1. Navigate to Email Studio > Subscribers > Data Extensions
  2. Click Create and choose Standard Data Extension
  3. Name it: Unified_Leads_Contacts
  4. Configure these fields:
    • Id (Text, 50, Primary Key)
    • Email (EmailAddress, 254)
    • HasOptedOutOfEmail (Boolean)
    • FirstName (Text, 40)
    • LastName (Text, 80)
    • ObjectType (Text, 10)
  5. Important: Check “Is Sendable” and set Email as the send relationship
  6. Save the Data Extension

Step 2: Create the SQL Query Activity

  1. Go to Automation Studio > Activities
  2. Create new SQL Query Activity
  3. Name it: Merge Leads and Contacts
  4. Set Target Data Extension to your Unified_Leads_Contacts
  5. Choose Overwrite for Data Action (this replaces all data each run)

Step 3: The SQL Query

Paste this query into your SQL Query Activity:

sql

SELECT 
    COALESCE(c._ContactKey, l._ContactKey) as Id,
    COALESCE(c.Email, l.Email) as Email,
    COALESCE(c.HasOptedOutOfEmail, l.HasOptedOutOfEmail) as HasOptedOutOfEmail,
    COALESCE(c.FirstName, l.FirstName) as FirstName,
    COALESCE(c.LastName, l.LastName) as LastName,
    CASE 
        WHEN c._ContactKey IS NULL THEN 'Lead' 
        ELSE 'Contact' 
    END AS ObjectType
FROM Lead_Salesforce l
FULL OUTER JOIN Contact_Salesforce c 
    ON l.[_ContactKey] = c.[_ContactKey]
WHERE 
    (c.HasOptedOutOfEmail = 'false') 
    OR (l.HasOptedOutOfEmail = 'false')

Step 4: Understanding the Query Logic

COALESCE Function:

  • COALESCE(c.Email, l.Email) returns Contact email if it exists, otherwise Lead email
  • This prioritizes Contact data over Lead data for all fields

FULL OUTER JOIN:

  • Captures all Leads AND all Contacts
  • Includes records that exist in only one table
  • Links records using _ContactKey (Salesforce’s unique identifier)

ObjectType Logic:

  • If c._ContactKey is NULL, the record came from Lead table only
  • Otherwise, Contact data exists (and is prioritized)

WHERE Clause:

  • Only includes records where opt-out is false
  • Respects email preferences from either object type

Step 5: Test the Query

  1. Validate SQL in the query builder
  2. Run the activity manually first
  3. Check your target Data Extension for results
  4. Verify the logic:
    • Look for records with ObjectType = ‘Lead’ vs ‘Contact’
    • Confirm no duplicate _ContactKey values
    • Check that Contact data appears when both Lead and Contact exist

Step 6: Set Up Automation

  1. Create new Automation in Automation Studio
  2. Add your SQL Query Activity
  3. Schedule to run:
    • Daily if you have frequent Lead/Contact updates
    • Weekly for most use cases
    • Before major campaigns at minimum
  4. Consider running after your Marketing Cloud Connect sync completes

Step 7: Using Your Unified Data Extension

Your new Data Extension can now be used for:

  • Email sends targeting both Leads and Contacts
  • Journey Builder audience selection
  • Reports with unified Lead/Contact metrics
  • Segmentation that spans both object types

Common Troubleshooting

Query Returns No Results:

  • Verify Lead_Salesforce and Contact_Salesforce exist and contain data
  • Check that _ContactKey field exists in both source Data Extensions
  • Confirm Marketing Cloud Connect is syncing properly

Duplicate Records:

  • This usually means _ContactKey values don’t match between Lead and Contact
  • Check Salesforce for data quality issues
  • Consider using Email as secondary join criteria

Missing Data:

  • Verify all field names match your source Data Extensions exactly
  • Check for case sensitivity in field names
  • Ensure source fields contain the expected data types

Advanced Variations

Add More Fields:

sql

COALESCE(c.Company, l.Company) as Company,
COALESCE(c.Phone, l.Phone) as Phone

Filter by Record Type:

sql

WHERE (c.HasOptedOutOfEmail = 'false' OR l.HasOptedOutOfEmail = 'false')
AND (c.RecordType = 'Customer' OR l.Status = 'Qualified')

Best Practices

  • Monitor performance – Large datasets may require query optimization
  • Document your automation – Include business logic notes for future updates
  • Test with sample data before deploying to production
  • Coordinate with Salesforce admins on data quality standards
  • Set up error notifications if the automation fails

When to Use This Approach

This solution works best when:

  • You need unified marketing communications
  • Contact data should take precedence over Lead data
  • You want automated deduplication
  • Your Lead/Contact volumes are manageable (under 1M records)

For larger datasets or more complex logic, consider Salesforce-side solutions or Marketing Cloud’s Contact Builder.

Need help with complex Salesforce and Marketing Cloud integrations?  Contact Knihter for professional Salesforce Marketing Cloud development services. We specialize in custom data solutions and advanced automation implementations.

Related Services: