sprignaturemoves

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

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