50 Salesforce Marketing Cloud SQL Examples

Salesforce Marketing Cloud Engagement’s SQL is one of the most important skills for anyone working not only with Automation Studio but to get basic insights from your marketing platform using query studio. Query Activities allow marketers and developers to segment audiences, deduplicate subscribers, calculate engagement metrics, and prepare data for campaigns.

In this guide you will find 50 practical SQL query examples for Salesforce Marketing Cloud that you can use in Automation Studio Query Activities or quick runs in the query studio. We will be using data views or common tables used by marketing teams all around the world.

Before you run these: a few SFMC SQL realities that affect every query

• You are writing T-SQL style queries for Automation Studio. Some SQL Server features people expect are restricted or behave differently in Marketing Cloud, so always sanity-check against the platform’s supported syntax and functions in the Salesforce Marketing Cloud SQL reference.
• Query Activities write into a target Data Extension. Many teams forget that “overwrite” vs “update” behavior changes the outcome as much as the SQL does and how long it will take.
• Treat Data Extensions like tables, but design for segmentation. Indexing is limited, so you win with clean keys, fewer columns, tight WHERE clauses, and pre-aggregating when needed.

Salesforce Marketing Cloud SQL Best Practices

While Salesforce Marketing Cloud supports a large subset of SQL Server syntax, the environment is optimized for segmentation and automation workflows rather than heavy analytical processing. Following a few best practices can help your queries run faster, produce predictable results, and prevent unexpected failures in Automation Studio.

SQL Query Examples

Select soft bounced subscribers

SELECT
   cm.SubsriberKey,
   b.BounceCategory
FROM _bounce b
JOIN Customer_Master_DE cm ON cm.SubscriberKey=b.SubsriberKey
WHERE b.BounceCategory in ('Block bounce','Soft bounce')

Hard bounced contacts can be selected by using Hard bounce as bounce category.

Sent events with email name

SELECT
    j.JobID,
    j.EmailName,
    s.EventDate,
    s.SubscriberKey
FROM _Sent s
JOIN _Job j 
    ON s.JobID = j.JobID

Sent emails for a specific email name

SELECT
    s.SubscriberKey,
    j.EmailName,
    s.EventDate
FROM _Sent s
JOIN _Job j
    ON s.JobID = j.JobID
WHERE j.EmailName = 'Welcome Email'

Clickers per email

SELECT
    j.EmailName,
    COUNT(DISTINCT c.SubscriberKey) AS Clickers
FROM _Click c
JOIN _Job j
    ON c.JobID = j.JobID
GROUP BY j.EmailName

Clicked specific url

SELECT DISTINCT 
   c.EventDate
   c.SubscriberKey, 
   j.emailname
FROM _Click c
JOIN _Job j ON c.JobID = j.JobID
WHERE LinkContent LIKE '%pricing%'

Unique clicks for email

SELECT 
    COUNT(*) AS totalUniqueClicks,
    j.EmailName
FROM _Click c
JOIN _Job j 
    ON c.JobID = j.JobID
WHERE c.IsUnique = 1
AND j.EmailName = 'Welcome campaign'
GROUP BY j.EmailName

Find duplicate email addresses

SELECT
EmailAddress,
COUNT(*) AS Count
FROM Customers_DE
GROUP BY EmailAddress
HAVING COUNT(*) > 1

Unique email sends on particular day

SELECT 
    COUNT(*) AS campaigns
FROM (
    SELECT jobid
    FROM _Sent s
    JOIN _Job j ON j.JobId = s.JobId
    WHERE s.EventDate >= '2026-03-08'
      AND s.EventDate < '2026-03-09'
    GROUP BY j.EmailName
) x

Select records in particular time zone

Salesforce Marketing Cloud dates are stored in the database using the CST timezone, regardless of where your Business Unit is located. If you need to select dates and times in your local timezone, you should use AT TIME ZONE.

SELECT
    j.EmailName,
    COUNT(*) AS Sends
FROM _Sent s
JOIN _Job j 
    ON s.JobID = j.JobID
WHERE
    s.EventDate AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'UTC'
    BETWEEN '2026-03-15 00:00:00' AND '2026-03-15 23:59:59'
GROUP BY j.EmailName

SELECT
    j.EmailName,
    s.EventDate AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'UTC' as EventDateUTC
FROM _Sent s
JOIN _Job j 
    ON s.JobID = j.JobID
WHERE
     s.EventDate AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'UTC' BETWEEN '2026-03-15 00:00:00' AND '2026-03-15 23:59:59'

Top 20 emails sent per domain

This query analyzes the distribution of email domains in your subscriber database and ranks them by how frequently they appear. Understanding which email providers dominate your audience can help identify deliverability patterns, monitor ISP exposure, and better understand the makeup of your database.

The script extracts the domain portion of each email address by locating the @ symbol and returning everything that follows it. The result is converted to lowercase to ensure consistent grouping, so domains such as Gmail.com and gmail.com are treated as the same value.

Once the domain is extracted, the query groups records by domain and counts how many subscribers belong to each one. The ROW_NUMBER() window function is then used to rank the domains from the most common to the least common based on their subscriber counts.

The final output provides a ranked list of email domains along with the number of subscribers associated with each domain, making it easier to quickly identify the most common email providers in your dataset.

Version to be run on query strudio

SELECT
    rn AS Rank_Position,
    Email_Domain,
    Domain_Count
FROM (
    SELECT TOP 100 PERCENT
        Email_Domain,
        COUNT(*) AS Domain_Count,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM (
        SELECT
            LOWER(
                SUBSTRING(
                    EmailAddress,
                    CHARINDEX('@', EmailAddress) + 1,
                    LEN(EmailAddress)
                )
            ) AS Email_Domain
        FROM Master_DE
        WHERE EmailAddress IS NOT NULL
          AND CHARINDEX('@', EmailAddress) > 0
    ) src
    GROUP BY Email_Domain
) ranked
WHERE rn <= 20

Version to be run on query definition activity within automation.

SELECT
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS Rank_Position,
    LOWER(
        SUBSTRING(
            EmailAddress,
            CHARINDEX('@', EmailAddress) + 1,
            LEN(EmailAddress)
        )
    ) AS Email_Domain,
    COUNT(*) AS Domain_Count
FROM Master_DE
WHERE EmailAddress IS NOT NULL
AND CHARINDEX('@', EmailAddress) > 0
GROUP BY
    LOWER(
        SUBSTRING(
            EmailAddress,
            CHARINDEX('@', EmailAddress) + 1,
            LEN(EmailAddress)
        )
    )

Identify the most clicked link per email

his query determines the most frequently clicked link within each email campaign. Click data is retrieved from the _Click data view and joined with the _Job data view to associate each click event with the corresponding email.

The query groups click events by both EmailName and URL and counts the number of clicks for each link. This produces a dataset showing how many times each link was clicked within each email.

To identify the most popular link, a window function is applied using ROW_NUMBER() partitioned by EmailName. Links are ordered by their click count in descending order, and only the top-ranked link for each email is returned.

The result provides a concise view of which link generated the most engagement within every campaign, making it useful for analyzing content performance and optimizing email design.

SELECT
    EmailName,
    URL,
    Click_Count
FROM (
    SELECT
        j.EmailName,
        c.URL,
        COUNT(*) AS Click_Count,
        ROW_NUMBER() OVER (
            PARTITION BY j.EmailName
            ORDER BY COUNT(*) DESC
        ) AS rn
    FROM _Click c
    JOIN _Job j
        ON c.JobID = j.JobID
    GROUP BY j.EmailName, c.URL
) ranked
WHERE rn = 1

Get the most recent record per email address

This situation commonly occurs in implementations where the SubscriberKey is not based on the email address but instead uses a unique identifier from a source system such as Salesforce Contact ID or Account ID. In these setups, duplicate email addresses may appear in Salesforce Marketing Cloud because multiple records in the source system share the same email address.

This is especially common in service-based businesses such as telecom, television, or mobile providers. In these environments, a single email address can be associated with multiple customer accounts, subscriptions, or contracts. For example, a household might manage several mobile lines or services under different account IDs but use the same email address for communication.

Because of this structure, the SubscriberKey must represent the source system identifier rather than the email address itself. This preserves the relationship between the Marketing Cloud subscriber record and the original system record. As a result, duplicate email addresses in the database are not always data quality issues but can reflect legitimate business relationships.

SELECT
    t.SubscriberKey,
    t.EmailAddress,
    t.Status,
    t.DateJoined
FROM (
    SELECT
        SubscriberKey,
        EmailAddress,
        Status,
        DateJoined,
        ROW_NUMBER() OVER (
            PARTITION BY LOWER(TRIM(EmailAddress))
            ORDER BY DateJoined DESC
        ) AS rn
    FROM _Subscribers
    WHERE EmailAddress IS NOT NULL
) t
WHERE t.rn = 1

Majority of the SFMC projects using some sort of Master_DE in that case the createdDate is captured in external system.

SELECT
    t.SubscriberKey,
    t.EmailAddress,
    t.createdAT 
FROM (
    SELECT
        SubscriberKey,
        EmailAddress,
        createdAT,
        ROW_NUMBER() OVER (
            PARTITION BY LOWER(TRIM(EmailAddress))
            ORDER BY createdAT DESC
        ) AS rn
    FROM Master_DE
    WHERE EmailAddress IS NOT NULL
) t
WHERE t.rn = 1

Deduplicate by EmailAddress but keep the “Active” subscribers status

SELECT
   t.EmailAddress,
   t.SubscriberKey,
   t.Status
FROM (
SELECT
   EmailAddress,
   SubscriberKey,
   Status,
   ROW_NUMBER() OVER (
   PARTITION BY EmailAddress
     ORDER BY CASE WHEN Status = 'Active' THEN 1 ELSE 2 END
   ) AS rn
 FROM _Subscribers
) t
WHERE t.rn = 1

Detect conflicting duplicates (same email, different SubscriberKey)

SELECT
   EmailAddress,
   COUNT(DISTINCT SubscriberKey) AS DistinctKeys
   FROM Master_DE
GROUP BY EmailAddress
HAVING COUNT(DISTINCT SubscriberKey) > 1

Build a “suppression” list of duplicates (only the extra rows)

SELECT
  d.SubscriberKey,
  d.EmailAddress
FROM (
  SELECT
    SubscriberKey,
    EmailAddress,
    ROW_NUMBER() OVER (PARTITION BY EmailAddress ORDER BY CreatedDate DESC) AS rn
      FROM Master_DE
) d
WHERE d.rn > 1

Get latest record from transactional data extension

I often use this when trying to proof or preview changes to a transactional template. Instead of searching for records within the preview in Email Studio, I first look up the SubscriberKey in the Data Extension used for the send.

Normally you would write something like below query and run it in query studio.

SELECT TOP 10
    SubscriberKey,
    CreatedAT
FROM TXN_DE
WHERE Country = 'sk'
ORDER BY CreatedAT DESC

The problem I noticed is that it does not work as expected. Instead of giving me 10 records sorted by the CreatedAT date, it gives me 10 random records and then sorts them.

ou can fix this in two ways.

The first option is to rewrite the query using ROW_NUMBER() with PARTITION BY, which ensures correct ordering before limiting the results.

The second option is to set the TOP value higher than the total number of rows in the Data Extension. For example, if your Data Extension contains 1,000 records, you can use TOP 1001 to force proper sorting and get accurate results.

However, this approach is not practical for large datasets with millions of records. In such cases, using ROW_NUMBER() with PARTITION BY is the recommended and scalable solution.

SELECT
    SubscriberKey,
    Country,
    CreatedAT
FROM (
    SELECT
        SubscriberKey,
        Country,
        CreatedAT,
        ROW_NUMBER() OVER (
            PARTITION BY Country
            ORDER BY CreatedAT DESC
        ) AS rn
    FROM TXN_DE
) t
WHERE rn <= 10

Prefer non-null values when deduping (simple coalesce approach)

SELECT
SubscriberKey,
MAX(EmailAddress) AS EmailAddress
FROM Customers_DE
GROUP BY SubscriberKey

Surface duplicates created in the last 7 days

SELECT
SubscriberKey,
COUNT(1) AS Cnt
FROM Master_DE
WHERE CreatedDate >= DATEADD(day, -7, GETDATE())
GROUP BY SubscriberKey
HAVING COUNT(1) > 1

Created in the last 24 hours

SELECT
SubscriberKey,
EmailAddress,
CreatedDate
FROM Master_DE
WHERE CreatedDate >= DATEADD(day, -1, GETDATE())

Created in the last 30 days

SELECT
SubscriberKey,
EmailAddress
FROM Master_DE
WHERE CreatedDate >= DATEADD(day, -30, GETDATE())

Created this month (month-to-date)

SELECT
SubscriberKey,
EmailAddress
FROM Master_DE
WHERE CreatedDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

Not updated in 90 days (stale records)

SELECT
SubscriberKey,
EmailAddress,
LastModifiedDate
FROM Master_DE
WHERE LastModifiedDate < DATEADD(day, -90, GETDATE())

Birthday campaigns (month/day match)

SELECT
SubscriberKey,
EmailAddress,
BirthDate
FROM Master_DE
WHERE MONTH(BirthDate) = MONTH(GETDATE())
AND DAY(BirthDate) = DAY(GETDATE())

Last email sent to subscriber

his query retrieves the most recent email send event for each subscriber using the _Sent and _Job data views. It can be useful when you want to understand the latest communication a subscriber received and enrich your audience datasets with send history.

To build a reliable historical view, this query is typically used inside a daily Automation Studio workflow that stores the results in a Data Extension. Running the query daily allows you to maintain a continuously updated picture of subscriber activity over time. You can create data extension called Subscriber_LastActivity_DE

Field	Type	Description
SubscriberKey	Text	Unique subscriber identifier
EmailAddress	Email	Subscriber email
LastEmailName	Text
LastEmailDate	Date	Last send date
LastEngagementDate	Date	Last click/open date
LastEngagementType	Text	Click or Open

For the initial run, you should extend the time window to capture historical data. For example:

• DATEADD(DAY,-30,GETDATE())
• DATEADD(DAY,-90,GETDATE())
• Remove the WHERE to get last 180 days (data views retention is 180 days)

This ensures you populate the dataset with enough past activity to be meaningful.

After the initial load, the automation can run daily with a shorter window, such as:

• DATEADD(DAY,-1,GETDATE())

SELECT
    SubscriberKey,
    LastEmailSent,
    EmailName,
    EmailAddress
FROM (
    SELECT
        s.SubscriberKey,
        s.EventDate AS LastEmailSent,
        j.EmailName,
        sub.EmailAddress,
        ROW_NUMBER() OVER (
            PARTITION BY s.SubscriberKey
            ORDER BY s.EventDate DESC
        ) AS rn
    FROM _Sent s
    JOIN _Job j
        ON s.JobID = j.JobID
    LEFT JOIN _Subscribers sub
        ON s.SubscriberKey = sub.SubscriberKey
    WHERE s.EventDate >= DATEADD(DAY,-1,GETDATE())
) ranked
WHERE rn = 1
AND EmailAddress IS NOT NULL

Last engaged time of subscriber

In Salesforce Marketing Cloud, engagement data is stored across multiple system data views such as _Click and _Open. However, simply using those tables directly has two challenges.

First, many corporate email security systems automatically scan links in incoming emails. These scanners may click every link in the message immediately after delivery, generating click events that do not represent real user activity.

Second, Apple Mail Privacy Protection automatically loads tracking pixels, which can produce open events even when the user has not actually viewed the email.

Because of this, engagement queries should include some basic filtering logic to reduce false signals.

The query below builds a LastEngaged table, which stores the most recent interaction for each subscriber. It combines click and open events, filters out likely bot clicks, and keeps only the latest activity per subscriber.

This table can then be refreshed daily through Automation Studio and used as a reliable engagement reference across segmentation workflows.

SELECT
    SubscriberKey,
    ActivityDate AS LastEngagedDate,
    ActivityType
FROM (
    SELECT
        SubscriberKey,
        ActivityDate,
        ActivityType,
        ROW_NUMBER() OVER (
            PARTITION BY SubscriberKey
            ORDER BY ActivityDate DESC
        ) AS rn
    FROM (

        /* Filtered Click Events */
        SELECT
            c.SubscriberKey,
            c.EventDate AS ActivityDate,
            'Click' AS ActivityType
        FROM (
            SELECT
                c.*,
                COUNT(*) OVER (
                    PARTITION BY c.SubscriberKey, c.JobID, c.EventDate
                ) AS clicks_same_time
            FROM _Click c
        ) c
        JOIN _Sent s
            ON c.SubscriberKey = s.SubscriberKey
            AND c.JobID = s.JobID
        WHERE
            clicks_same_time <= 3
            AND DATEDIFF(second, s.EventDate, c.EventDate) > 10

        UNION ALL

        /* Open Events */
        SELECT
            SubscriberKey,
            EventDate,
            'Open'
        FROM _Open

    ) activity
) ranked
WHERE rn = 1

• Combine click and open activity: The query first merges engagement data from the _Click and _Open data views. Clicks are generally considered the strongest engagement signal, while opens are weaker but still useful when no clicks exist. Both event types are combined into a single dataset using UNION ALL.
• Filter suspicious click activity: Corporate email security gateways often scan links automatically, which can generate multiple click events within milliseconds after delivery. These clicks typically target every link in the email. To reduce this noise, the query filters out clicks where multiple links were clicked at the exact same timestamp or where the click occurred only a few seconds after the email was sent, which usually indicates automated scanning rather than real user interaction.
• Rank engagement events per subscriber: After collecting all engagement events, the query ranks them for each subscriber using ROW_NUMBER() OVER (PARTITION BY SubscriberKey ORDER BY ActivityDate DESC). This assigns a ranking where the most recent engagement receives rank 1.
• Keep the latest engagement event:Finally, the outer query keeps only the most recent event for each subscriber using WHERE rn = 1. The result is a table where each subscriber has exactly one record representing their last known engagement activity.

Segment by recency buckets

SELECT
SubscriberKey,
EmailAddress,
CASE
WHEN LastEngagedDate >= DATEADD(day, -7, GETDATE()) THEN '0-7 days'
WHEN LastEngagedDate >= DATEADD(day, -30, GETDATE()) THEN '8-30 days'
WHEN LastEngagedDate >= DATEADD(day, -90, GETDATE()) THEN '31-90 days'
ELSE '90+ days'
END AS RecencyBucket
FROM LastEngaged_DE

Use DATEDIFF for a numeric “days since” last engaged

SELECT
SubscriberKey,
EmailAddress,
DATEDIFF(day, LastEngagedDate, GETDATE()) AS DaysSinceEngaged
FROM LastEngaged_DE

Exclude today’s records (handy for late-arriving feeds)

SELECT
SubscriberKey,
EmailAddress
FROM Master_DE
WHERE CAST(CreatedDate AS date) < CAST(GETDATE() AS date)

Identify records with invalid or missing dates

SELECT
SubscriberKey,
EmailAddress
FROM Master_DE
WHERE BirthDate IS NULL
OR BirthDate > GETDATE()

Inner join customers to orders (only buyers)

SELECT
c.SubscriberKey,
c.EmailAddress,
o.OrderId,
o.OrderDate
FROM Master_DE c
INNER JOIN Orders_DE o
ON c.SubscriberKey = o.SubscriberKey

Left join to find non-buyers

SELECT
c.SubscriberKey,
c.EmailAddress
FROM Master_DE c
LEFT JOIN Orders_DE o
ON c.SubscriberKey = o.SubscriberKey
WHERE o.SubscriberKey IS NULL

Join to preferences for opt-in segmentation

SELECT
c.SubscriberKey,
c.EmailAddress,
p.EmailOptIn
FROM Master_DE c
INNER JOIN Preferences_DE p
ON c.SubscriberKey = p.SubscriberKey
WHERE p.EmailOptIn = 1

Multi-table join (customers + orders + order items)

SELECT
c.SubscriberKey,
c.EmailAddress,
o.OrderId,
oi.Sku,
oi.Quantity
FROM Master_DE c
INNER JOIN Orders_DE o
ON c.SubscriberKey = o.SubscriberKey
INNER JOIN OrderItems_DE oi
ON o.OrderId = oi.OrderId

Join on email address (use carefully)

SELECT
c.SubscriberKey,
c.EmailAddress,
l.Source
FROM Master_DE c
INNER JOIN Leads_DE l
ON c.EmailAddress = l.EmailAddress

Retrieve Subscriber Suppression Information

In many Salesforce Marketing Cloud implementations, suppression lists are stored across multiple Data Extensions. These lists can represent different sources such as legal opt-outs, invalid email addresses, regional privacy requirements, or automatically generated suppression imports. When troubleshooting deliverability or understanding why a subscriber was excluded from a campaign, it is often necessary to consolidate these sources into a single view.

The query below gathers suppression records from several Data Extensions and combines them into one dataset. Each source is labeled with a suppression reason so it is clear which list the subscriber belongs to. The results are then aggregated by email address so that all suppression sources for the same subscriber appear in a single record.

SELECT 
    EmailAddress, 
    STRING_AGG(supression, ', ') AS exclusion_reason
FROM (
    SELECT DISTINCT 
        [Email Address] AS EmailAddress,
        supression
    FROM (
        SELECT [Email Address], 'supression_1' AS supression
        FROM supression_1
        UNION
      SELECT [Email Address], 'supression_2' AS supression
        FROM supression_2
    ) src
) combined
GROUP BY EmailAddress

The script works in two main steps. First, it unions multiple suppression tables into one temporary dataset and assigns a readable suppression label to each record. Second, it groups the results by email address and uses STRING_AGG to combine all suppression sources into a single exclusion_reason field. This produces a concise summary that shows exactly why a subscriber is suppressed.

The resulting table can be used for troubleshooting, auditing suppression logic, or enriching exclusion reporting when analyzing campaign results.

Suppress a global suppression DE

SELECT
c.SubscriberKey,
c.EmailAddress
FROM Customers_DE c
LEFT JOIN GlobalSuppression_DE s
ON c.SubscriberKey = s.SubscriberKey
WHERE s.SubscriberKey IS NULL

Build a reactivation list (inactive + not suppressed)

When building re-engagement or win-back campaigns, it is common to target subscribers who have not interacted with emails for a certain period of time. At the same time, it is important to ensure that contacts who are part of suppression lists are excluded from the audience to avoid sending messages to users who have opted out or should not be contacted.

The query below selects subscribers from a master Data Extension who have not engaged with emails in the last 90 days or who have never engaged at all. It then checks multiple suppression lists and removes any subscriber whose email address appears in those tables. This ensures that the resulting audience only contains inactive subscribers who are still eligible to receive communications.

SELECT
    c.SubscriberKey,
    c.EmailAddress
FROM Master_DE c
WHERE 
    (c.LastEngagedDate < DATEADD(day,-90,GETDATE()) 
    OR c.LastEngagedDate IS NULL)

AND NOT EXISTS (
    SELECT 1 
    FROM supression_1_DE s 
    WHERE s.EmailAddress = c.EmailAddress
)

AND NOT EXISTS (
    SELECT 1 
    FROM supression_2_DE s 
    WHERE s.EmailAddress = c.EmailAddress
)

You could also write this using NOT IN, but it has some downsides, that you might reconsider when using NOT IN

Identify orphaned orders (no matching customer)

SELECT
o.SubscriberKey,
o.OrderId
FROM Orders_DE o
LEFT JOIN Customers_DE c
ON o.SubscriberKey = c.SubscriberKey
WHERE c.SubscriberKey IS NULL

Join to a product catalog for enriched personalization fields

SELECT
oi.OrderId,
oi.Sku,
p.ProductName,
p.Category
FROM OrderItems_DE oi
INNER JOIN ProductCatalog_DE p
ON oi.Sku = p.Sku

Identify the most engaged domain per campaign

This query analyzes engagement by email domain and identifies which domain generated the most clicks for each campaign.

The domain portion of each email address is extracted using the SUBSTRING() and CHARINDEX() functions. The result is converted to lowercase to ensure consistent grouping, preventing domains such as Gmail.com and gmail.com from being treated as separate values.

Click events from the _Click data view are joined with _Sent to access subscriber email addresses and _Job to retrieve the campaign name. The query then groups engagement events by campaign and email domain and counts the number of clicks generated by each domain.

A ROW_NUMBER() window function is applied to rank domains within each campaign based on the number of clicks they produced. Finally, the query returns only the top-ranked domain per campaign.

This analysis can help identify which email providers generate the most engagement for each campaign, which can be useful for deliverability monitoring, audience analysis, and campaign optimization.

SELECT
    EmailName,
    Email_Domain,
    Engagement_Count
FROM (
    SELECT
        j.EmailName,
        LOWER(
            SUBSTRING(
                s.EmailAddress,
                CHARINDEX('@', s.EmailAddress) + 1,
                LEN(s.EmailAddress)
            )
        ) AS Email_Domain,
        COUNT(*) AS Engagement_Count,
        ROW_NUMBER() OVER (
            PARTITION BY j.EmailName
            ORDER BY COUNT(*) DESC
        ) AS rn
    FROM _Click c
    JOIN _Sent s
        ON c.SubscriberKey = s.SubscriberKey
        AND c.JobID = s.JobID
    JOIN _Job j
        ON c.JobID = j.JobID
    WHERE s.EmailAddress IS NOT NULL
      AND CHARINDEX('@', s.EmailAddress) > 0
    GROUP BY
        j.EmailName,
        LOWER(
            SUBSTRING(
                s.EmailAddress,
                CHARINDEX('@', s.EmailAddress) + 1,
                LEN(s.EmailAddress)
            )
        )
) ranked
WHERE rn = 1

Count orders per subscriber

SELECT
SubscriberKey,
COUNT(1) AS OrderCount
FROM Orders_DE
GROUP BY SubscriberKey

Total revenue per subscriber

SELECT
SubscriberKey,
SUM(OrderTotal) AS Revenue
FROM Orders_DE
WHERE orderStatus = 'fullfilled'
GROUP BY SubscriberKey

Average order value (AOV) per subscriber

SELECT
SubscriberKey,
AVG(OrderTotal) AS AvgOrderValue
FROM Orders_DE
GROUP BY SubscriberKey

Last purchase date per subscriber

SELECT
SubscriberKey,
MAX(OrderDate) AS LastOrderDate
FROM Orders_DE
GROUP BY SubscriberKey

First purchase date per subscriber

SELECT
SubscriberKey,
MIN(OrderDate) AS FirstOrderDate
FROM Orders_DE
GROUP BY SubscriberKey

RFM-style scoring (simple example: recency and frequency)

SELECT
o.SubscriberKey,
DATEDIFF(day, MAX(o.OrderDate), GETDATE()) AS RecencyDays,
COUNT(1) AS FrequencyOrders
FROM Orders_DE o
GROUP BY o.SubscriberKey) AS FirstOrderDate
FROM Orders_DE
GROUP BY SubscriberKey

High-value customers (threshold example)

SELECT
SubscriberKey,
SUM(OrderTotal) AS Revenue
FROM Orders_DE
GROUP BY SubscriberKey
HAVING SUM(OrderTotal) >= 500

Category affinity (top category by quantity per subscriber)

SELECT
t.SubscriberKey,
t.Category,
t.Qty
FROM (
SELECT
o.SubscriberKey,
p.Category,
SUM(oi.Quantity) AS Qty,
ROW_NUMBER() OVER (
PARTITION BY o.SubscriberKey
ORDER BY SUM(oi.Quantity) DESC
) AS rn
FROM Orders_DE o
INNER JOIN OrderItems_DE oi
ON o.OrderId = oi.OrderId
INNER JOIN ProductCatalog_DE p
ON oi.Sku = p.Sku
GROUP BY o.SubscriberKey, p.Category
) t
WHERE t.rn = 1

Identify one-time buyers (exactly one order)

SELECT
SubscriberKey
FROM Orders_DE
GROUP BY SubscriberKey
HAVING COUNT(1) = 1
ON o.OrderId = oi.OrderId
INNER JOIN ProductCatalog_DE p
ON oi.Sku = p.Sku
GROUP BY o.SubscriberKey, p.Category
) t
WHERE t.rn = 1

Find subscribers eligible for a winback (one-time buyer + 180 days since purchase)

SELECT
o.SubscriberKey
FROM Orders_DE o
GROUP BY o.SubscriberKey
HAVING COUNT(1) = 1
AND DATEDIFF(day, MAX(o.OrderDate), GETDATE()) >= 180

Share With Others