Data Views in SQL: who opened or clicked in the last 30 days

Data Views are system tables in Marketing Cloud Engagement that hold the history of sends, opens, clicks, bounces and unsubscribes. They do not show up in Contact Builder, but you can query them with SQL in a Query Activity (Automation Studio). With them you can build engagement audiences without relying on reports.

The most used Data Views

The columns that tie everything together are SubscriberKey, JobID and EventDate.

Two details that break many queries

1. Six months of history. Data Views keep roughly six months of events. For longer analyses, copy the data into your own Data Extension on a schedule.
2. Time zone. EventDate is stored in Marketing Cloud server time (Central Standard Time, UTC−6, with no daylight saving). For "the last 30 days" this barely matters; for "yesterday, midnight to 11:59 p.m. in my time zone", it does.

The query: engaged in the last 30 days

The goal is an audience of everyone who opened or clicked any email in the last 30 days, with the date of their latest engagement.

SELECT
    e.SubscriberKey,
    MAX(e.EventDate) AS LastEngagement
FROM (
    SELECT o.SubscriberKey, o.EventDate
    FROM _Open o
    WHERE o.EventDate >= DATEADD(DAY, -30, GETDATE())

    UNION ALL

    SELECT c.SubscriberKey, c.EventDate
    FROM _Click c
    WHERE c.EventDate >= DATEADD(DAY, -30, GETDATE())
) e
GROUP BY e.SubscriberKey

Why it is written this way:

• UNION ALL combines opens and clicks without paying for duplicate removal at this stage;
• the final GROUP BY leaves one row per subscriber, which is required if SubscriberKey is the primary key of the target Data Extension;
• MAX(EventDate) keeps the most recent date, useful for segments like "engaged in the last 7 days".

Setting up the Query Activity

1. Create the target Data Extension Engaged_30d with SubscriberKey (Text, primary key) and LastEngagement (Date).
2. In the Query Activity, pick that Data Extension as the target and the Overwrite action: the audience is rebuilt from scratch on every run.
3. Put the Query Activity in a daily automation.

Useful variations

Clickers only (a stronger signal than opens, which are inflated today by email client privacy features):

SELECT c.SubscriberKey, MAX(c.EventDate) AS LastClick
FROM _Click c
WHERE c.EventDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY c.SubscriberKey

Inactive subscribers: everyone who received an email in the last 90 days but did not open or click. Start from _Sent and use NOT EXISTS against _Open and _Click.

Summary

Data Views give you engagement history directly in SQL. Remember the six-month retention and the CST time zone, keep one row per subscriber in the result, and use Overwrite for audiences that are rebuilt every day.