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

loyalty

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.