CQL Tips

Business analysts are challenged every day with questions about the online activity of users. Analysts want to understand why and how users behave the way they do and what leads them to a certain decision or action. With CoolaData’s Structured Query Language (CoolaSQL, or CQL), business analysts can proactively analyze user actions to identify behavioral patterns, predict future trends and make better business decisions. It is a behavioral extension of the popular SQL query language. CoolaData’s behavioral analytics is set up to identify and answer the most critical business questions – allowing for simple and powerful analysis of typical behavioral patterns, such as path analysis, cohort analysis and funnel analysis.

Here are just some of the questions that CQL queries can help analysts deal with:

  • Which activities contributed, or did not contribute, to attaining a desired goal such as a purchase, or the earning of virtual goods?
  • What sequence of events led to a specific action?
  • How much time went by between a user’s registration and first purchase, and how does it differ in various geographical regions?
  • Why did users end a session with a wanted or unwanted action?

For example, CoolaData’s Loyalty Segments – Session Counter query tracks user loyalty, such as the number of times a user returned to shop in a certain date range:

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_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

Analysts use the resulting data of this query to observe how your users behaved and track the most popular paths users chose to navigate through. They then use those results to specifically target loyal users in the future.

With CoolaData’s 2nd Day (Bucket) Retention query, analysts create and prioritize important trends related to retention rates. For example, they use bucket_id=6 to look at 7-day retention, and bucket_id=29 to look at 30-day retention:

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

Queries like these offer powerful and valuable tools for understanding why and how users act and get meaningful answers from online or mobile data. CoolaData’s CQL-based queries ensure our platform is open and completely flexible… there are no restrictions to what analysts can query!

General Best Practices

  • Use from cooladata for optimal run. This will tell our query shipment optimization engine to run a query over the most compatible data set.
  • When running CQL from our Analytical Document, use (doc.date_range) so that the date range is updated dynamically and returned according to the Doc’s date range. Using a specific date range, for example the last 30 days (BETWEEN 2014-07-01 AND 2014-07-30), will result in a a static range for those dates (in this case: 30 days from whenever Analytical Doc is opened).
  • The date_range function must be included in every query. This limits the number of records CoolaData queries so that we can be more efficient in pinpointing required results.
  • Enter a numerical limit to limit the number of results a query returns. For example, enter limit 10 to limit the number of results to 10. Limit should be the last part of the SELECT clause. CoolaData limits the result set that can be viewed in up to 25,000 rows. Please contact your CoolaData Customer Success Manager for larger result sets.
  • Some examples have event names or property names hard-coded in. Make sure to rename these with the relevant names and properties for any given account, otherwise queries will not work.
CoolaData CEO Tomer Ben Moshe demonstrating CQL at a Meetup, June 2014
CoolaData CEO Tomer Ben Moshe demonstrating CQL at a Meetup, June 2014

Contact webinfo@cooladata.com to contribute cool CQL queries, or to ask questions about CoolaData’s Structured Query Language.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *