Count Multiple Events in One Query

Use this query to count different events in one query using CASE instead of JOIN.
Each column will show a count of a different event.


SUM(CASE WHEN event_name = 'event1name' THEN 1 ELSE 0 END) AS event1count,


  DATE(event_time_ts) AS date,
  SUM(CASE WHEN event_name = 'Signup' THEN 1 ELSE 0 END) AS Signup,
  SUM(CASE WHEN event_name = 'Login' THEN 1 ELSE 0 END) AS Login
FROM cooladata
WHERE date_range(context) AND slicers(context)


date Signup Login
2013-10-01 8 20
2013-10-02 13 19
2013-10-03 13 24
2013-10-04 18 28
2013-10-05 10 14
2013-10-06 15 29
2013-10-07 8 15
2013-10-08 11 23
2013-10-09 17 25
2013-10-10 13 20
Print Friendly, PDF & Email

Average Session Duration

Use this query to track a user’s average session duration. The session_duration is the length of session time in milliseconds. This can be useful for comparing session durations between different segments, and can be used in any CQL query.

So for example, if the session took one minute, the session_duration will show 60,000 milliseconds).


SELECT avg(session_duration) as avg_session_duration
FROM cooladata
WHERE date_range(context) and filters(context)

Tips/Best Practices:

Print Friendly, PDF & Email

Last Event Before A Target Event

This query is ideally used to identify the last event (for example, a ‘share’ or ‘like’), before a target event, that caused a user to reach a specific goal that interests you.

You can use the results of this query to analyze whether certain activities contributed or did not contribute to attaining a desired goal (such as a purchase). You can then change/adjust any events as needed, to increase conversion rates for your goal.


SELECT left(right(path_to_string(path), 2), 1) as event_before_subscription, count(session_internal_id) as cnt 
FROM cooladata
WHERE date_range(doc.date_range)
ENDING WITH event_name = 'updated successfully'
GROUP BY event_before_subscription

 understanding what happens

Tips/Best Practices:

  • With this query, you can go back and look at a specific path of events to determine the entire path before the target event. In the sample syntax above, entering select left(right(path_to_string(path), 2), 1) as event_before_subscription, count(session_id) as cnt from cooladata, allows you to go 2 steps back, and show 1 event.

Print Friendly, PDF & Email

Last Event in the Session (Exit Event)

Use this query to view common last events (‘exit events’), before the end of a user’s session. For example, for an online quiz or survey: question_displayed, question_answered, and quiz_complete.

This query represents a great tool for getting insight into user behavior, and is easy to do with CoolaData.

By running this query, you can:

  • Learn what the most popular last events are before users left a session.
  • Understand why users ended a session with a wanted or unwanted action.
  • Set a KPI for changing the commonality/popularity of a specific exit event.

For example, you can check why users abandoned the shopping cart and did not complete their purchase, say if they saw an error page or banner ad before before completing their purchase which caused them to exit.


SELECT last(path_to_string(path), 1) as last_event, count(session_internal_id) as cnt
FROM cooladata
WHERE date_range(doc.date_range)
GROUP BY last_event

Example results:

exit events

Print Friendly, PDF & Email

Show a TimeStamp as Date Time

Time properties in Cooladata are collected under the event_time_ts field, to the millisecond level. When queried , it presents the time in YYYY-MM-DD format. You can format it differently using format_utc_usec, as shown in the sample syntax below.

Querying different timeframes can allow you to identify certain trends, such as daily trends or hourly trends, or to get more detailed results (for example, by the minute or second).


left(format_utc_usec (event_time_ts),19) as DateTime

 screen 1

Tips/Best Practices:

  • Entering only (event_time_ts) in the query above will show only the date (in this example, 2014-05-14). To see the complete string including the time, enter the entire code string as shown above.

  • The 19 in the example code above indicates 19 characters for the USER_TIME string, and returns the USER_TIME in seconds, as shown in the results in the screen shot above. Use 16 characters for the minute level (i.e., 2014-05-14 18:24), and 13 characters for the hour level (i.e., 2014-05-14 18).

Print Friendly, PDF & Email