Data Model & Management in SFMC

🎚️

Adjust Technical Level

Select your expertise level to customize content

SFMC's data architecture centers on a flexible system of relational data extensions integrated with a subscriber-centric contact model, enabling complex customer data management, segmentation, and personalization. The platform implements both traditional list-based subscriber management and modern relational database structures connected through Contact Builder, supporting integrated multi-channel marketing efforts while providing mechanisms for data import/export, transformation through SQL queries, and programmatic manipulation via SOAP and REST APIs.

Core Data Structures

SFMC Data Model Architecture

Key data structures and their relationships in Salesforce Marketing Cloud

100%

🔍 Use Ctrl+Scroll to zoom

Data Relationship Types

Components

Core Contact

Subscriber Structures

Data Extensions

Contact Relationships

Channel-Specific

Automation Structures

Connection Types

Identity

Membership

Relationship

Inheritance

Specialization

Data Extensions Explained

Technical Implementation

Technical

Data Extension Business Applications

Data Extensions enable sophisticated, data-driven marketing by providing a flexible foundation for customer data management and segmentation.

Marketing Capabilities

• Customer Segmentation: Precisely target audiences based on multiple attributes and behaviors
• Personalization: Support dynamic content based on customer data attributes
• Journey Orchestration: Power customer journeys with data-driven entry and decision points
• Cross-Channel Consistency: Maintain consistent customer data across all marketing channels
• Campaign Automation: Enable triggered communications based on data changes

Strategic Data Uses

• Customer Profiles: Comprehensive view of customer attributes and preferences
• Transaction Data: Purchase history, account activity, and financial information
• Engagement History: Track interactions across channels and campaigns
• Product Catalogs: Store product information for dynamic content and recommendations
• Campaign Performance: Track results and analytics for marketing optimization

Business Benefits

• Marketing Agility: Quickly adapt campaigns based on changing customer data
• Operational Efficiency: Automate processes based on data conditions
• Improved Relevance: Deliver more targeted communications based on customer attributes
• Enhanced Analysis: Better understand customer segments and campaign performance
• Data Integration: Connect marketing data with other business systems

Implementation Strategy

• Start with Core Data: Begin with essential customer attributes and expand gradually
• Design for Scalability: Structure data to accommodate growth in volume and complexity
• Balance Detail and Performance: Collect necessary data without overcomplicating structures
• Consider Data Lifecycle: Plan for data updates, archiving, and retention needs
• Align with Business Processes: Design data structures to support marketing workflows

Business Value

Non-Technical

Data Extension Technical Architecture

Data Extensions are the primary relational data structures in SFMC, functioning as database tables with defined schemas and relationships.

Technical Characteristics

• Relational Structure: Tables with columns (fields) and rows (records)
• Field Types: Text, Number, Date, Boolean, Email, Phone, Decimal, Locale
• Primary Keys: Single or composite keys for unique record identification
• Indexes: Optional secondary indexes to optimize query performance
• Field Constraints: Nullable, required, default values, min/max length
• Storage Location: Enterprise/shared (accessible across BUs) or local (BU-specific)

Special Data Extension Types

• Sendable Data Extensions: Linked to subscribers for direct email targeting
• Synchronized Data Extensions: Auto-synced from Salesforce CRM objects
• Filtered Data Extensions: Dynamic subsets based on filter criteria
• Random Data Extensions: Randomly sampled data for testing/control groups
• Data Extensions from Template: Pre-configured schemas for specific use cases

Technical Limitations

• Maximum Records: Recommended limit of 20 million rows per DE
• Maximum Fields: Up to 4,000 fields per Data Extension
• Field Size Limits: Text fields up to 4,000 characters
• Query Complexity: 30-minute timeout for SQL operations
• Retention: Configurable retention periods (default varies by edition)

Data Extension Management Code

SOAP API example for creating a Data Extension programmatically:

// SOAP API example to create a Data Extension
var dataExtension = {
"CustomerKey": "MyContactDE",
"Name": "My Contact Data Extension",
"Description": "Stores customer profile data",
"IsSendable": true,
"IsTestable": true,
"SendableDataExtensionField": {"Name": "Email"},
"SendableSubscriberField": {"Name": "Subscriber Key"},
"Fields": [
  {
    "Name": "Email",
    "FieldType": "EmailAddress",
    "IsPrimaryKey": true,
    "IsRequired": true,
    "MaxLength": 254
  },
  {
    "Name": "FirstName",
    "FieldType": "Text",
    "MaxLength": 50
  },
  {
    "Name": "LastName",
    "FieldType": "Text",
    "MaxLength": 50
  },
  {
    "Name": "JoinDate",
    "FieldType": "Date",
    "DefaultValue": "GetDate()"
  }
]
};

Subscriber Management

All Subscribers List

All Subscribers List

The All Subscribers list provides a centralized view of your entire email audience, managing subscription status and basic profile information for all email recipients.

Marketing Value

• Centralized Subscription Management: Single source of truth for subscriber status
• Global Suppression: Automatically prevents sending to unsubscribed or bounced emails
• Compliance Support: Helps maintain CAN-SPAM and GDPR compliance by honoring opt-out requests
• List Health Management: Tracks bounces and problem addresses to maintain sender reputation
• Audience Overview: Provides high-level metrics on total addressable audience

Business Applications

• Email Campaign Planning: Understanding total available audience size
• Deliverability Management: Monitoring list health metrics
• Compliance Reporting: Documenting opt-out processing
• Basic Segmentation: Filtering on standard status fields
• Subscriber Identity Management: Especially when using Subscriber Key

Strategic Considerations

• Subscriber Key Implementation: Critical decision affecting long-term contact management
• Data Extension Strategy: Using Data Extensions with All Subscribers for advanced targeting
• Cross-Channel Identity: Planning how email identity relates to other channels
• Compliance Strategy: Ensuring proper unsubscribe handling across all marketing
• List Growth Strategy: Balancing list size with engagement quality

Subscriber Key

Subscriber Key

Subscriber Key transforms how you identify and manage customer relationships in Marketing Cloud, creating a foundation for true 360-degree customer views and personalized cross-channel journeys.

Strategic Value

• Contact Continuity: Maintain relationship history when email addresses change
• Identity Resolution: Connect customer interactions across multiple addresses
• Cross-Channel Coordination: Link email identity with mobile, social, and web channels
• System Integration: Align customer identity with CRM and other business systems
• Data Portability: Migrate customer data between instances or business units

Business Use Cases

• Account-Based Marketing: Managing communications to multiple contacts at same account
• Lifecycle Marketing: Tracking customer journey across email changes over time
• Regulatory Compliance: Implementing "right to be forgotten" across all customer data
• B2B Marketing: Managing contacts who change companies but keep relationships
• Loyalty Programs: Connecting loyalty account with all customer communications

Implementation Strategy

• Source System Selection: Identify authoritative system for customer IDs
• ID Format Standardization: Establish consistent format across systems
• Process Development: Create reliable processes for maintaining ID consistency
• Data Governance: Define ownership and procedures for identity management
• Change Management: Train teams on proper use of Subscriber Key in all processes

Publication Lists

Publication Lists

Publication Lists enable permission-based marketing by providing a framework for capturing, tracking, and honoring subscriber preferences for different types of communications.

Marketing Value

• Preference-Based Marketing: Send only content types subscribers have explicitly requested
• Granular Consent Management: Track permissions by communication category
• Compliance Support: Document specific opt-ins for regulatory requirements
• Subscriber Experience: Allow subscribers to choose content relevant to their interests
• Engagement Optimization: Improve response rates by respecting preferences

Business Applications

• Newsletter Programs: Managing subscriptions to different newsletter types
• Product Updates: Segmenting communications by product interest
• Event Communications: Managing opt-ins for webinars and event notifications
• Regional Communications: Managing geography-specific alerts and updates
• Frequency Preferences: Segmenting by communication frequency preference

Strategic Considerations

• Preference Architecture: Designing the right granularity of subscription options
• Preference Center Design: Creating intuitive interfaces for preference management
• Default Settings: Determining appropriate initial preference states
• Preference Campaigns: Periodically encouraging preference updates
• Compliance Documentation: Maintaining records of opt-in sources and timestamps

Contact Builder

Technical Implementation

Technical

Contact Builder Business Applications

Contact Builder enables a unified customer view that powers personalized multi-channel journeys and comprehensive customer insights.

Marketing Capabilities

• Customer 360 View: Aggregate all customer information from multiple sources in one place
• Cross-Channel Orchestration: Coordinate marketing across email, mobile, web, and other channels
• Advanced Personalization: Access related customer data to personalize communications
• Life Cycle Marketing: Track and respond to customer status changes throughout the relationship
• Household/Account Views: Connect individual contacts to households or business accounts

Business Use Cases

• Multi-Channel Campaigns: Coordinate consistent messaging across all customer touchpoints
• Customer Journey Mapping: Track progression through acquisition, onboarding, growth, and retention
• Purchase History Personalization: Recommend products based on past purchases and browsing
• Service Integration: Connect marketing communications with customer service interactions
• Loyalty Program Management: Track points, status, and rewards across all touchpoints

Organizational Benefits

• Data Unification: Break down data silos between channels and departments
• Marketing Efficiency: Reuse customer data across multiple campaigns and journeys
• Improved Customer Experience: Create more relevant and consistent interactions
• Enhanced Analytics: Gain comprehensive view of customer engagement across channels
• Faster Time to Market: Build campaigns more quickly using pre-established data relationships

Implementation Strategy

• Start with Core Identity: Establish consistent customer identification first
• Prioritize Key Relationships: Focus on the most valuable data connections initially
• Develop Incrementally: Build the customer view in phases based on campaign needs
• Document Data Model: Maintain clear documentation of relationships and attributes
• Governance Framework: Establish processes for maintaining data quality and consistency

Business Value

Non-Technical

Contact Builder Technical Framework

Contact Builder provides a technical framework for creating a unified customer view by establishing relationships between data sources and enabling identity resolution across channels.

Key Technical Components

• Contact Model: Central entity that represents a unified customer identity
• Data Sources: Collections of Data Extensions categorized for organizational purposes
• Attribute Sets: Logical groupings of attributes from different Data Extensions
• Data Relationships: Defined connections between Data Extensions based on key fields
• Contact Key: Primary identifier that links records across channels (often the same as Subscriber Key)

Relationship Types

• One-to-One: Direct 1:1 relationships between Contact and Data Extension record
• One-to-Many: One Contact associated with multiple records in a Data Extension
• Many-to-Many: Requires junction object pattern with two one-to-many relationships
• Population/Sendable Relationships: Special relationships for targeting Data Extensions
• Channel Address Order: Priority sequence for determining primary contact methods

Technical Limitations

• Maximum relationship path length for attribute retrieval
• Performance impact of complex data relationships on queries
• Limitations on real-time data access across relationships
• Need for consistent keys and data types across related tables
• Challenges with many-to-many relationship modeling

Implementation Code Example

/* AMPScript to retrieve data across relationships */

/* Get Customer ID for current subscriber */
SET @subscriberKey = _subscriberkey
SET @customerID = @subscriberKey

/* Get data from related Data Extensions using relationships */
SET @customerRow = LookupRows("Customer_DE", "CustomerID", @customerID)
SET @customerStatus = Field(Row(@customerRow, 1), "CustomerStatus")

/* Get most recent order using one-to-many relationship */
SET @orderRows = LookupOrderedRows("Orders_DE", 1, "OrderDate DESC", "CustomerID", @customerID)
SET @lastOrderDate = FormatDate(Field(Row(@orderRows, 1), "OrderDate"), "MM/dd/yyyy")
SET @lastOrderTotal = Field(Row(@orderRows, 1), "OrderTotal")

/* Get product preferences from junction object pattern */
SET @prefRows = LookupRows("CustomerProductPreferences_DE", "CustomerID", @customerID)

Data Management Best Practices

Area	Best Practice	Technical Implementation	Business Impact
Data Structure Design	Implement normalized data model with appropriate relationships	Design Data Extensions with proper primary keys, indexes, and relationship fields	Enables flexible segmentation, efficient updates, and comprehensive reporting
Subscriber Key Strategy	Use consistent unique identifier across all systems	Implement Subscriber Key with CRM ID or other persistent identifier	Creates unified customer view and maintains history through email changes
Data Import Processes	Establish reliable, automated data synchronization	Build Automation Studio workflows with error handling and notification	Ensures up-to-date customer data for relevance and personalization
Data Quality Management	Implement validation rules and monitoring	Use SQL queries with validation logic and data cleansing automation	Reduces errors, improves deliverability, and enhances campaign effectiveness
Performance Optimization	Design for query and processing efficiency	Create appropriate indexes, optimize SQL queries, use filtered DEs	Enables faster campaign execution and better system responsiveness
Data Governance	Establish clear ownership and processes	Document data dictionary, field definitions, and update procedures	Ensures consistent usage and interpretation across the organization
Security & Privacy	Implement appropriate access controls and encryption	Use role-based permissions, field-level security, and encryption where needed	Protects sensitive data and supports compliance requirements
Data Retention	Define appropriate data lifecycle policies	Configure retention settings and archive/purge automation	Manages storage costs while preserving necessary historical data
Cross-Channel Identity	Connect identities across all marketing channels	Build complete Contact Builder model with all channel identifiers	Enables coordinated multi-channel experiences and complete customer view
Integration Architecture	Design sustainable integration patterns	Implement reliable API integration with retry logic and monitoring	Creates consistent data flow between marketing and other business systems

Data Import Methods

Manual Import

Manual File Import

Manual imports provide a simple way to add or update marketing data without technical resources, ideal for occasional updates or small datasets.

Business Uses

• Ad-hoc Marketing Lists: Import event attendees or promotion participants
• Campaign-Specific Data: Load one-time data for specific campaigns
• Testing and Validation: Import sample data for campaign testing
• Small Data Updates: Update specific customer records or attributes
• Supplemental Information: Add supporting data for campaigns

Operational Considerations

• User Training: Ensure marketers understand proper import procedures
• Data Preparation: Establish templates and guidelines for file formatting
• Quality Control: Define verification steps for imported data
• Documentation: Track sources and reasons for manual imports
• Governance: Clear roles and permissions for who can import data

When to Use

• Low-frequency updates that don't justify automation
• Small datasets or targeted updates
• Situations where marketing teams need direct control
• Testing or pilot programs before setting up automation
• Environments with limited technical resources

Automated SFTP

Automated SFTP Imports

SFTP-based automation enables reliable, scheduled data updates from enterprise systems to Marketing Cloud, supporting consistent and timely marketing communications.

Business Applications

• Regular Data Synchronization: Daily/weekly updates from CRM or ERP systems
• Transaction Data Import: Regular import of purchase, billing, or activity data
• Product Catalog Updates: Refresh product information for marketing content
• List Refreshes: Update marketing segments based on current business data
• Multi-step Campaign Automation: Trigger campaign processes after data imports

Business Benefits

• Marketing Reliability: Consistent data flow without manual intervention
• Operational Efficiency: Automated processes reduce manual effort
• Improved Timeliness: More current data for relevant communications
• Reduced Errors: Standardized processes minimize human error
• Scalability: Handles growing data volumes and complexity

Implementation Strategy

• Start with Critical Data: Prioritize most important customer data flows
• Establish Clear Ownership: Define responsibilities for file generation and monitoring
• Implement Monitoring: Create alerts for missed or failed transfers
• Document Processes: Maintain clear documentation of automated data flows
• Build Redundancy: Plan for handling occasional failures or delays

API Integration

API-Based Data Integration

API integration enables real-time marketing by connecting customer touchpoints directly to Marketing Cloud, supporting immediate response to customer actions and maintaining synchronized customer data across systems.

Business Applications

• Transactional Messaging: Send immediate confirmation emails or SMS after purchases
• Real-time Personalization: Update customer profiles as soon as preferences change
• Website Integration: Connect web behavior directly to Marketing Cloud
• Mobile App Integration: Sync mobile app activity with marketing campaigns
• CRM Integration: Keep sales and marketing systems synchronized

Business Benefits

• Timely Communication: Respond instantly to customer actions
• Improved Relevance: Use current data for more targeted communications
• Enhanced Customer Experience: Provide seamless cross-channel experiences
• Operational Agility: Update marketing data without manual processes or delays
• Technology Ecosystem: Connect Marketing Cloud with your broader technology stack

Strategic Considerations

• Real-time vs. Batch: Determine which data needs immediate synchronization
• Integration Architecture: Direct integration vs. middleware/iPaaS solutions
• Resource Planning: Technical skills required for development and maintenance
• Data Governance: Policies for data consistency across integrated systems
• Monitoring Strategy: Processes to ensure reliable integration operation

SQL Query Activities

Technical Implementation

Technical

SQL Query Business Applications

SQL Query Activities unlock powerful data manipulation capabilities that enable sophisticated segmentation, personalized marketing, and automated data management.

Marketing Applications

• Advanced Segmentation: Create precisely targeted audiences based on multiple criteria
• Dynamic Content Population: Prepare personalized content data for campaigns
• Campaign Automation: Automate audience selection for recurring campaigns
• Progressive Profiling: Gradually enhance customer profiles with derived insights
• Engagement Scoring: Calculate and update customer engagement metrics

Data Management Use Cases

• Data Cleansing: Standardize formats and correct common data issues
• Data Integration: Combine information from multiple sources into unified views
• List Maintenance: Automate suppressions, exclusions, and preference updates
• Automated Reporting: Generate regular performance data summaries
• Data Archiving: Implement data lifecycle management processes

Business Benefits

• Marketing Precision: More accurate targeting leads to better campaign performance
• Operational Efficiency: Automate complex data tasks that would be manual otherwise
• Data Utilization: Derive more value from existing marketing data
• Rapid Adaptation: Quickly adjust segmentation as business needs change
• Resource Optimization: Perform sophisticated data operations without developer resources

Strategic Implementation

• Start with Core Use Cases: Begin with critical segmentation and data management needs
• Build Reusable Patterns: Create templates for common query operations
• Document Logic: Maintain clear documentation of query logic and business rules
• Test Thoroughly: Validate query results with sample data before full implementation
• Monitor Performance: Watch for query optimization opportunities as data grows

Business Value

Non-Technical

SQL Query Technical Capabilities

SQL Query Activities in Automation Studio provide a powerful tool for data manipulation, transformation, and selection within Marketing Cloud.

Technical Capabilities

• Data Selection: Extract specific records and fields from Data Extensions
• Data Transformation: Modify, combine, or format data during processing
• Data Aggregation: Calculate totals, averages, and other summary statistics
• Record Filtering: Select specific data subsets based on criteria
• Data Combination: Join data from multiple Data Extensions

Query Structure

• Target Data Extension: Destination for query results (existing or created by query)
• SQL Statement: SELECT statement defining data selection and manipulation
• Execution Options: Overwrite/append behavior, error handling settings
• Scheduling: One-time or recurring execution through Automation Studio

Technical Limitations

• Timeout: Maximum 30-minute execution time
• SQL Dialect: Based on SQL Server with Marketing Cloud-specific limitations
• Function Support: Limited set of supported functions and features
• Data Type Handling: Some data type conversions require explicit casting
• Query Complexity: Very complex queries may encounter performance issues

Common SQL Patterns

/* Basic data selection with filtering */
SELECT EmailAddress, FirstName, LastName, Status
FROM Customers
WHERE Status = 'Active' AND JoinDate > DATEADD(month, -3, GETDATE())

/* Join multiple Data Extensions */
SELECT c.EmailAddress, c.FirstName, c.LastName, o.OrderID, o.OrderDate, o.OrderTotal
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate > DATEADD(day, -30, GETDATE())

/* Aggregate data for reporting */
SELECT ProductCategory, COUNT(OrderID) AS OrderCount, SUM(OrderTotal) AS CategoryRevenue,
     AVG(OrderTotal) AS AverageOrderValue
FROM Orders
GROUP BY ProductCategory
ORDER BY CategoryRevenue DESC

/* Create a segment with complex criteria */
SELECT c.EmailAddress, c.SubscriberKey
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID AND o.OrderDate > DATEADD(month, -6, GETDATE())
WHERE c.Status = 'Active' AND c.EmailOptIn = 1 AND o.OrderID IS NULL

/* Update data using subqueries */
SELECT c.SubscriberKey, c.EmailAddress, c.FirstName, c.LastName,
     (SELECT TOP 1 OrderDate FROM Orders o
      WHERE o.CustomerID = c.CustomerID ORDER BY OrderDate DESC) AS LastOrderDate
FROM Customers c
WHERE c.Status = 'Active'

Mobile and Social Data

Cross-Channel Marketing Strategy

Mobile Channel Integration

Integrating mobile messaging into your marketing strategy provides immediate, high-engagement touchpoints:

• Transactional Messaging: Immediate notifications for purchases, appointments, or updates
• Service Alerts: Time-sensitive information about services or account status
• Location-Based Marketing: Geo-targeted messages to drive in-store traffic
• Two-Way Engagement: Interactive messaging for surveys, polls, or support
• Cross-Channel Coordination: Using mobile as part of integrated journeys with email

Social Media Marketing Integration

Social channels provide unique audience engagement opportunities when integrated with other marketing:

• Audience Expansion: Reach prospects not available through owned channels
• Community Building: Foster engagement and relationship development
• Content Amplification: Extend reach of marketing content through social sharing
• Reputation Management: Monitor and manage brand sentiment
• Social Advertising: Targeted paid media integrated with organic content

Web and Mobile App Coordination

Digital property integration creates seamless customer experiences:

• Behavioral Targeting: Personalize outbound marketing based on website behavior
• Abandoned Cart Recovery: Re-engage customers who leave without purchasing
• Cross-Device Journeys: Create consistent experiences as customers switch devices
• Progressive Engagement: Gradually increase involvement through sequential touchpoints
• In-App Messaging: Deliver personalized content within mobile applications

Omni-Channel Strategy Development

Building a comprehensive approach to multi-channel marketing:

• Channel Orchestration: Coordinate messaging across all customer touchpoints
• Channel Preference Respect: Honor customer preferences for communication methods
• Consistent Experience: Maintain brand voice and messaging across all channels
• Channel-Specific Optimization: Tailor content to each channel's unique characteristics
• Attribution Modeling: Understand how channels work together in the customer journey

Data Model Implementation Steps

1. Audit Current Data Sources

   • Inventory all customer data sources and attributes
   • Document existing data flows and integration points
   • Identify data quality issues and gaps

2. Define Data Strategy

   • Determine Subscriber Key approach
   • Plan Data Extension architecture and relationships
   • Establish data governance principles
   • Define data retention and archiving policies

3. Design Core Data Structures

   • Create primary customer Data Extensions
   • Set up Contact Builder relationships
   • Configure publication and suppression lists
   • Design data model for cross-channel identity

4. Implement Data Integration

   • Build data import automation
   • Configure API connections
   • Set up synchronization with CRM and other systems
   • Establish data quality validation processes

5. Develop Segmentation Framework

   • Create reusable SQL queries for common segments
   • Build filtered Data Extensions for key audiences
   • Establish attribute groupings for personalization
   • Document segment definitions and criteria

6. Configure Data Security

   • Implement role-based access controls
   • Set up field-level security for sensitive data
   • Configure data encryption where needed
   • Establish audit trails for data access

7. Train Marketing Teams

   • Educate on data model structure and usage
   • Provide training on data import procedures
   • Document best practices for data management
   • Create reference guides for data fields and definitions

8. Establish Monitoring and Maintenance

   • Implement data quality monitoring
   • Set up alerts for failed data processes
   • Create dashboard for data health metrics
   • Schedule regular data model reviews and optimization

Resources and Next Steps

To continue building your SFMC data management expertise, consider these next steps:

1. Explore Advanced Topics: Dive into our guides on AMPScript Programming and SQL Query Activities for advanced data manipulation techniques.

2. Learn Journey Implementation: See how to use your data model effectively in our Journey Builder guide.

3. Understand Automation: Explore data automation in our Automation Studio.

4. Review Integration Options: Learn about connecting SFMC with other systems.

5. Consult Official Documentation: Visit Salesforce's Help Documentation for detailed technical specifications and updates.