Here’s a scalable method I’ve used to aggregate subscriber-level engagement data. I’ve used the results of this data to populate high/low engagement audiences. I will typically drop this in as soon as I can — just to get the data accumulating. It’s intended to run daily.
Automation
Subscriber_Activity_Summary
Step 1 – SQL Query: Subscriber_Activity_Summary_Status
Step 2 – SQL Query: Subscriber_Activity_Summary_Sends
Step 3 – SQL Query: Subscriber_Activity_Summary_Opens
Step 4 – SQL Query: Subscriber_Activity_Summary_Clicks
Step 5 – SQL Query: Subscriber_Activity_Summary_MostRecentInteraction
Data Extensions
Subscriber_Activity_Summary
Subscriber_Activity_Summary
| Name | Data Type | Len | PK | Req’d | Default |
|---|---|---|---|---|---|
| SubscriberId | Number | 0 | true | true | |
| EmailAddress | EmailAddress | 254 | false | false | |
| SubscriberKey | Text | 254 | false | false | |
| Status | Text | 15 | false | false | |
| SendCount | Number | false | false | 0 | |
| LastSend | Date | false | false | ||
| OpenCount | Number | false | false | 0 | |
| LastOpen | Date | false | false | ||
| ClickCount | Number | false | false | 0 | |
| LastClick | Date | false | false | ||
| mostRecentInteraction | Date | false | false | ||
| InsertedDate | Date | false | false | GetDate() |
Queries
Subscriber_Activity_Summary_Status
NOTE: Add the ent. prefix on _Subscribers if you’re in a child BU.
select
s.subscriberid
, s.subscriberkey
, s.emailAddress
, s.status
from _Subscribers s
/* name: Subscriber_Activity_Summary_Status */
/* target: Subscriber_Activity_Summary */
/* action: update */Subscriber_Activity_Summary_Sends
select
x.subscriberid
, x.subscriberkey
, isnull(sas.SendCount,0) + x.SendCount SendCount
, x.LastSend
from (
select
y.subscriberid
, y.subscriberkey
, count(*) SendCount
, max(y.eventDate) LastSend
from (
select
a.subscriberid
, a.subscriberkey
, a.eventDate
from _Sent a
where a.EventDate >= convert(date, getDate()-1)
and a.EventDate < convert(date, getDate())
) y
group by
y.subscriberid
, y.subscriberkey
) x
left join Subscriber_Activity_Summary sas on sas.subscriberid = x.subscriberid
where
sas.subscriberid is null
or x.lastSend > isnull(sas.lastSend,'1980-01-01')
/* name: Subscriber_Activity_Summary_Sends */
/* target: Subscriber_Activity_Summary */
/* action: update */Subscriber_Activity_Summary_Opens
select
x.subscriberid
, x.subscriberkey
, isnull(sas.OpenCount,0) + x.OpenCount OpenCount
, x.LastOpen
from (
select
y.subscriberid
, y.subscriberkey
, count(*) OpenCount
, max(y.eventDate) LastOpen
from (
select
a.subscriberid
, a.subscriberkey
, a.eventDate
from _Open a
where a.EventDate >= convert(date, getDate()-1)
and a.EventDate < convert(date, getDate())
) y
group by
y.subscriberid
, y.subscriberkey
) x
left join Subscriber_Activity_Summary sas on sas.subscriberid = x.subscriberid
where
sas.subscriberid is null
or x.lastOpen > isnull(sas.lastOpen,'1980-01-01')
/* name: Subscriber_Activity_Summary_Opens */
/* target: Subscriber_Activity_Summary */
/* action: update */Subscriber_Activity_Summary_Clicks
select
x.subscriberid
, x.subscriberkey
, isnull(sas.ClickCount,0) + x.ClickCount ClickCount
, x.LastClick
from (
select
y.subscriberid
, y.subscriberkey
, count(*) ClickCount
, max(y.eventDate) LastClick
from (
select
a.subscriberid
, a.subscriberkey
, a.eventDate
from _Click a
where a.EventDate >= convert(date, getDate()-1)
and a.EventDate < convert(date, getDate())
) y
group by
y.subscriberid
, y.subscriberkey
) x
left join Subscriber_Activity_Summary sas on sas.subscriberid = x.subscriberid
where
sas.subscriberid is null
or x.lastClick > isnull(sas.lastClick,'1980-01-01')
/* name: Subscriber_Activity_Summary_Clicks */
/* target: Subscriber_Activity_Summary */
/* action: update */Subscriber_Activity_Summary_MostRecentInteraction
select
a.subscriberid
, a.subscriberkey
, case
when a.lastOpen > coalesce(a.lastClick, convert(datetime, '1980-01-01')) then a.lastOpen
else a.lastClick
end mostRecentInteraction
from Subscriber_Activity_Summary a
where a.lastOpen is not null
or a.lastClick is not null
/* name: Subscriber_Activity_Summary_MostRecentInteraction */
/* target: Subscriber_Activity_Summary */
/* action: update */