Project · Salesforce Marketing Cloud · SQL

SFMC Data Extensions

Building and populating a Data Extension with SQL: a custom table that aggregates purchase behavior into the fields an email program actually segments and personalizes on.

Salesforce MC
SQL
Data Extensions
Segmentation
AMPscript
Personalization

What it is

A Data Extension in Salesforce Marketing Cloud is a custom database table. It can be filled by file imports, the API, Journey Builder or CloudPages, or by a SQL Query Activity, as it is here. Its value is that it holds whatever fields you define and can be linked to other Data Extensions and to contacts on a shared key, which is what makes the rest of SFMC work, from send audiences to personalized content blocks.

Why build one

Raw subscriber data rarely answers the questions a campaign asks. Order count, lifetime value and first and last order date don't exist as columns anywhere; they have to be derived. A Data Extension is where that derived view lives, refreshed on a schedule and ready to segment against.

What a Data Extension needs

External Key

The Data Extension's unique identifier (its CustomerKey), used by the SOAP and REST APIs and by SSJS DataExtension.Init(). AMPscript's Lookup() functions reference the Data Extension by name instead. Worth setting deliberately rather than accepting the generated value, since you'll be typing it.

Is Sendable

A boolean marking whether the Data Extension can be the audience for a send. A sendable one also needs a send relationship: one field, usually SubscriberKey, mapped to Subscriber Key. A purely analytical table stays unsendable.

Fields

The columns loaded into the table, each with a data type (plus a length for text and decimal fields), tied to a primary key (typically Subscriber Key). Keying on email address is a legacy pattern that breaks the day someone changes their address.

Example · Client_Purchase_Data

Definition

Name	Client_Purchase_Data
External Key	Client_Purchase_Data
Is Sendable	No, it's joined to a sendable audience when it's time to send
Primary key	SubscriberKey
Populated by	Query Activity in Automation Studio
Data action	Overwrite, so each run rebuilds the table

Fields

Field	Data type
SubscriberKey	Text(254) · primary key
EmailAddress	EmailAddress(254)
FirstName	Text(100)
LastName	Text(100)
OrderCount	Number
LifetimeValue	Decimal(18,2)
FirstOrderDate	Date
LastOrderDate	Date

What each aggregate does

OrderCount: orders per subscriber; [Purchases] holds one row per order, so counting OrderIDs counts orders
LifetimeValue: summed order totals, the field most segmentation leans on
FirstOrderDate: tenure, and the anchor for anniversary sends
LastOrderDate: recency, and the trigger for win-back timing

Why the types matter

If a query result doesn't fit the target field, the activity fails instead of loading. That includes text longer than the field's length, a value that can't convert to its type (like "$12.34" into a Decimal), or a number too large for the Decimal field.

The query

SQLClient_Purchase_Data

SELECT
    s.SubscriberKey,
    MAX(s.EmailAddress)  AS EmailAddress,
    MAX(s.FirstName)     AS FirstName,
    MAX(s.LastName)      AS LastName,
    COUNT(p.OrderID)     AS OrderCount,
    SUM(p.OrderTotal)    AS LifetimeValue,
    MIN(p.OrderDate)     AS FirstOrderDate,
    MAX(p.OrderDate)     AS LastOrderDate
FROM [Master_Subscribers] s
INNER JOIN [Purchases] p
    ON p.SubscriberKey = s.SubscriberKey
WHERE p.OrderStatus IN ('Completed', 'Shipped')
GROUP BY s.SubscriberKey

Purchase data at line-item level? Collapse it to orders first. If each row is a product and every row repeats its order's total, summing those rows counts an order's total once per product. Roll the lines up to one row per order in a subquery, then aggregate. (If each line carries its own amount instead, sum that amount directly and count DISTINCT OrderID.)

SQLClient_Purchase_Data · from line items

SELECT
    s.SubscriberKey,
    MAX(s.EmailAddress)  AS EmailAddress,
    MAX(s.FirstName)     AS FirstName,
    MAX(s.LastName)      AS LastName,
    COUNT(o.OrderID)     AS OrderCount,
    SUM(o.OrderTotal)    AS LifetimeValue,
    MIN(o.OrderDate)     AS FirstOrderDate,
    MAX(o.OrderDate)     AS LastOrderDate
FROM [Master_Subscribers] s
INNER JOIN (
    SELECT OrderID, SubscriberKey,
           MAX(OrderTotal) AS OrderTotal,
           MAX(OrderDate)  AS OrderDate
    FROM [Purchase_Line_Items]
    WHERE OrderStatus IN ('Completed', 'Shipped')
    GROUP BY OrderID, SubscriberKey
) o ON o.SubscriberKey = s.SubscriberKey
GROUP BY s.SubscriberKey

How it works

The INNER JOIN is doing the filtering: only subscribers with matching purchase history come through, so the resulting table is purchasers only.

Grouping by SubscriberKey collapses every order row per subscriber into one. The name and email columns are wrapped in MAX() for the same reason: once you group, every selected column has to be either grouped on or aggregated.

The WHERE clause matters more than it looks. Restricting to Completed and Shipped keeps cancelled and pending orders out of lifetime value. Without it, those orders quietly inflate every downstream segment built on that field.

Switching to a LEFT JOIN? Move the filter. A LEFT JOIN keeps every subscriber, but a status test left in WHERE rejects their null purchase rows and quietly turns it back into an inner join. Put the status test in the ON clause instead.

Run it on sample data

Join

Status filter

NULL lifetime value

Query · Client_Purchase_Data

[Master_Subscribers]4 rows

SubscriberKey	FirstName	EmailAddress

[Purchases]

OrderID	Key	Total	OrderDate	Status

Client_Purchase_Data

SubscriberKey	FirstName	OrderCount	LifetimeValue	FirstOrderDate	LastOrderDate

What it feeds

Audience segmentation

Lifetime value and order count become filter criteria (high-value cohorts, one-time buyers, repeat purchasers) without recomputing anything at send time. Because this table isn't sendable, a second query joins it to a sendable audience Data Extension on SubscriberKey.

Automation timing

Last order date drives win-back entry; first order date drives anniversary and tenure milestones.

Personalization

The same fields resolve through AMPscript Lookup() calls in the template, referencing the Data Extension by name rather than External Key, so content can respond to a subscriber's actual purchase history.