Summarizing Subscriber Activity

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 */
(Visited 37 times, 1 visits today)
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments