Average Time Between Two Events

Use this query to select the average time (in milliseconds), between two user events. You can use this, for example, to compare how much time elapsed between registration and a purchase for users in two different countries.

Syntax:

select avg(time_between("updated successfully","open help center frame"))
from cooladata
where date_range(doc.date_range)

 screen2

Tips/Best Practices:

  • Use this function together with any interesting segment or dimension (for example, country, browser, etc.), to learn more about the difference in how long it takes for different groups to complete target events.

Print Friendly, PDF & Email

Top 5 Paths Ordered by “Visit Count”

Use this query to find out the most popular paths your users take in a single session. Results with the highest number of VISIT_COUNTS are the most popular, and so on.

Use path analysis to analyze consecutive events by a user, or to determine popular user paths in your website. For example, a user logs in and continues to navigate through a game, registering additional events (‘up level’, ‘message’, ‘buy’, etc.), during a single usage session.

Please see our Path Analysis documentation for more information on path-related queries.

Syntax:

SELECT count(session_internal_id) AS visit_count
    , path_to_string(path) AS path
from cooladata
WHERE date_range(BETWEEN 2014-02-01 AND 2014-02-28)
CLUSTER PATH BY session REMOVE DUPLICATES MATCH any
WHERE date_range(BETWEEN 2014-02-01 AND 2014-02-28)
cluster path by session REMOVE DUPLICATES MATCH any 
GROUP BY path
ORDER BY visit_count
DESC LIMIT 20

path cql

Tips/Best Practices:

  • Use REMOVE DUPLICATES to remove duplicate actions, and treat repeated events as one event. For example, if a user watches a YouTube playlist and clicks ‘next’ several times to continue to the next song in the playlist, you can filter out and remove the duplicate actions.

  • When using CLUSTER PATH BY, it is possible (although not mandatory) to use HAVING PATH MATCH (an inner clause of CLUSTER PATH BY). This clause allows you to apply filtering.

  • Please see our Path Analysis documentation for additional path-related tips and best practices.

Print Friendly, PDF & Email

2nd Day (Bucket) Retention

As an analyst, you give high priority to retention. CQL allows you to easily create retention rate trends. Below is an example of a trend over 2nd day retention.

Syntax:

SELECT right(cohort_name,5) as Month_Day,
    cohort_id,
    cohort_size,
    bucket_id,
    (COUNT(DISTINCT user_id)/cohort_size)*100 AS Percent
FROM cooladata
WHERE date_range(doc.date_range)
CLUSTER COHORT BY 1 DAYS EACH
STARTS WITH event_name = "user_login"
FOLLOWED BY event_name = "level_up"
BUCKET BY 1 DAYS ALL
HAVING (cohort_id < 60 AND bucket_id=1)
GROUP BY 1,2,3,4
ORDER BY 2

cql last

Tips/Best Practices:

  • Use bucket_id=6 to look at 7-day retention, and bucket_id=29 to look at 30-day retention.

Print Friendly, PDF & Email

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.

Print Friendly, PDF & Email