Customer Engagement Query

Customer Engagement Query

Lev’s Director of Project Services, Stephen Rosenfeld, has built a query you can modify and use to segment your list into very engaged, moderately engaged, not engaged, and dead.

 

You can adjust as needed, but below is a great starting point.

 

(click_count * 2 + open_count_wo_click ) / (number of sends * 2) = RANK Percentage

 

Then, we compare that rank to this chart to determine their temperature.

 

Hot: >= 85%

Warm:  <85% & >=65%

Cool:  <65 & >=35%

Cold:  <35 & >1%

Dead:  0

The easiest way to run this will be through Salesforce's Hub App Query Studio . The query may require the ent. syntax to be removed when running at an enterprise BU.

 

 

select subscriberkey, Range1_Rank as SevenDay_Rank, Range2_Rank as ThirtyDay_Rank, Range3_Rank as NintyDay_Rank

FROM (

select sub.subscriberkey

, CASE WHEN sub.DateJoined >= dateadd(dd,-7,getdate()) THEN 'New'                                                  WHEN Range1_Sends = 0 or Range1_Sends is null THEN 'NoSends' WHEN Range1_Points / Range1_Sends = 0 THEN 'Dead' WHEN Range1_Points / Range1_Sends < 35 THEN 'Cold' WHEN Range1_Points / Range1_Sends < 65 THEN 'Cool' WHEN Range1_Points / Range1_Sends < 85 THEN 'Warm' WHEN Range1_Points / Range1_Sends < 100 THEN 'HOT' ELSE 'Unknown' END as Range1_Rank

, CASE WHEN sub.DateJoined <  dateadd(dd,-7,getdate()) AND sub.DateJoined >= dateadd(dd,-30,getdate()) THEN 'New' WHEN Range2_Sends = 0 or Range2_Sends is null THEN 'NoSends' WHEN Range2_Points / Range2_Sends = 0 THEN 'Dead' WHEN Range2_Points / Range2_Sends < 35 THEN 'Cold' WHEN Range2_Points / Range2_Sends < 65 THEN 'Cool' WHEN Range2_Points / Range2_Sends < 85 THEN 'Warm' WHEN Range2_Points / Range2_Sends < 100 THEN 'HOT' ELSE 'Unknown' END as Range2_Rank

, CASE WHEN sub.DateJoined <  dateadd(dd,-30,getdate()) AND sub.DateJoined >= dateadd(dd,-90,getdate()) THEN 'New' WHEN Range3_Sends = 0 or Range3_Sends is null THEN 'NoSends' WHEN Range3_Points / Range3_Sends = 0 THEN 'Dead' WHEN Range3_Points / Range3_Sends < 35 THEN 'Cold' WHEN Range3_Points / Range3_Sends < 65 THEN 'Cool' WHEN Range3_Points / Range3_Sends < 85 THEN 'Warm' WHEN Range3_Points / Range3_Sends < 100 THEN 'HOT' ELSE 'Unknown' END as Range3_Rank

from

ent._subscribers sub

left join (

select s.subscriberkey

, SUM(case when s.eventdate >= dateadd(dd,-7,getdate()) AND c.subscriberkey is not null then 2 WHEN s.eventdate >= dateadd(dd,-7,getdate()) AND o.subscriberkey is not null then 1 else 0 END) as Range1_Points

, CAST(SUM(case when s.eventdate >= dateadd(dd,-7,getdate()) then 1 else 0 END) AS DECIMAL) as Range1_Sends

, CAST(SUM(case when s.eventdate >= dateadd(dd,-30,getdate()) AND s.eventdate < dateadd(dd,-7,getdate()) then 1 else 0 END ) AS DECIMAL) as Range2_Sends

, SUM(case when s.eventdate >= dateadd(dd,-30,getdate()) AND s.eventdate < dateadd(dd,-7,getdate()) AND c.subscriberkey is not null then 2 when s.eventdate >= dateadd(dd,-30,getdate()) AND s.eventdate < dateadd(dd,-7,getdate()) AND o.subscriberkey is not null then 1 else 0 END) as Range2_Points

, CAST(SUM(case when s.eventdate >= dateadd(dd,-90,getdate()) AND s.eventdate < dateadd(dd,-30,getdate()) then 1 else 0 END ) AS DECIMAL) as Range3_Sends

, SUM(case when s.eventdate >= dateadd(dd,-90,getdate()) AND s.eventdate < dateadd(dd,-30,getdate()) AND c.subscriberkey is not null then 2 when s.eventdate >= dateadd(dd,-90,getdate()) AND s.eventdate < dateadd(dd,-30,getdate()) AND o.subscriberkey is not null then 1 else 0 END) as Range3_Points

from _sent s

left join _open o

   on o.subscriberkey = s.subscriberkey

   and o.jobid = s.jobid

   and s.listid = o.listid

   and s.batchid = o.batchid

   and o.IsUnique = 1

left join _click c

   on c.subscriberkey = s.subscriberkey

   and c.jobid = s.jobid

   and s.listid = c.listid

   and s.batchid = c.batchid

   and c.IsUnique = 1

WHERE s.eventdate >= dateadd(dd,-90,getdate())

group by s.subscriberkey) data on data.subscriberkey = sub.subscriberkey

) data2

 
Previous Article
Marketing During a Pandemic: Live Recording
Marketing During a Pandemic: Live Recording

Bobby and Cole cover the do's and don'ts of marketing during a global pandemic during this special live rec...

Next Article
Five Actions You Can Take Right Now to Make an Impact in Marketing Cloud
Five Actions You Can Take Right Now to Make an Impact in Marketing Cloud

For marketers working in Marketing Cloud there are still actionable steps you can take right now to make su...

×

First Name
Last Name
Company Name
Thank you!
Error - something went wrong!