Category: SQL Queries

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

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 */

If you ever need an automated way to clear a data extension, you can simply use a SQL query that does an overwrite. The query doesn’t need a from clause if you specify any required columns in the target.

For example, if EmailAddress is required in the target data extension, simply set the value to null and add an alias: