Fill form to unlock content
Error - something went wrong!
Thank you!
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