sfmc // SQLPRODUCTION

Subscriber Engagement — 120 Day Flags

Create recent-open/recent-click flags and last engagement dates for an audience.

sqlengagementopenclick120d

Trust & provenancePRODUCTION

Evidence tierPRODUCTIONOfficial sources0Other sources0Last verifiedNot recorded

Evidence tier describes my hands-on relationship with the content. Official sources describe platform contracts/documentation. Production observations should not automatically be treated as official limits.

SELECT
    b.SubscriberKey,
    MAX(o.EventDate) AS LastOpenDate,
    MAX(c.EventDate) AS LastClickDate,
    CASE WHEN MAX(o.EventDate) >= DATEADD(day,-120,GETDATE()) THEN 1 ELSE 0 END AS Opened120D,
    CASE WHEN MAX(c.EventDate) >= DATEADD(day,-120,GETDATE()) THEN 1 ELSE 0 END AS Clicked120D
FROM [Base_Ativa] b
LEFT JOIN _Open o
    ON o.SubscriberKey = b.SubscriberKey
    AND o.IsUnique = 1
LEFT JOIN _Click c
    ON c.SubscriberKey = b.SubscriberKey
    AND c.IsUnique = 1
GROUP BY b.SubscriberKey

Operational notes

Joining opens and clicks directly can multiply rows; aggregates protect the final grain, but intermediate size can still grow.

For very large volumes, pre-aggregate each event view into intermediate DEs.

Pre-Aggregate Tracking Before Joining

Reduce event grain before combining behavior tables.

→

Subscriber Engagement — 120 Day Flags

Keep following the context.