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