Loyalty Segments – Session Counter
Use this query to track user loyalty. For example, the number of times in a certain date range that a user returned to shop on Amazon.com.
Syntax:
SELECT
CASE
when session_counter between 1 and 5 then '1-5'
when session_counter between 6 and 10 then '6-10'
when session_counter between 11 and 15 then '11-15'
else '>15' end as cntr ,
count(*) as users_amount
FROM
(SELECT user_id, count(distinct session_internal_id ,exact) as session_counter
FROM cooladata
WHERE date_range(doc.date_range)
cluster path by session match any
GROUP BY user_id )
GROUP BY cntr
Tips/Best Practices:
Use this query to specifically target loyal users.
Use sessioning to observe how your users behave. For example, you can segment by specified sessions in order to track the most popular paths that your users choose to navigate through.