CoolaData’s Structured Query Language – CoolaSQL, or CQL – is a flexible behavioral enrichment of the popular SQL query language. CQL has been specifically designed so that even a beginner SQL programmer can utilize its powerful clauses to query and analyze the data stored in CoolaData’s behavioral data service.
CQL allows for simple and flexible analysis of typical behavioral patterns, such as path analysis, cohort analysis and funnel analysis. CQL proactively queries user actions to analyze and predict future trends, ultimately leading to better business decision-making. We added two CQL enhancements over the summer:
- Use the First/Last Event CQL behavioral function to analyze events that users performed before they churned.
- Examine a few paths together as one cohesive segment, instead of analyzing each individual path separately. This function is called Alternative Start Points.
What Did Users Do Before They Churned?
The First/Last Event function provides an analysis of the event or events that were performed after starting a session, or just before ending it. This can be utilized either to analyze the distribution of users who performed certain events immediately after logging in, or to look at the last event patterns of churned users.
‘Last’ is a great tool for finding out what may have triggered the end of a session, by viewing the distribution of “last” events: what proportion of users ended their session after completing a level, losing a game, viewing an ad, or viewing an in-app offer? This CQL function is especially important because it leads to an analysis of the effectiveness of incentives, or positive events in general, so that they can be quantified. It also clearly highlights problematic events that lead to churn. This feature is therefore tremendously useful for changes and improvements in both product and marketing.
Sample CQL Code
Function: first(path_to_string(path),[number_of_events]). For example: first(path_to_string(path),2).
This example will lead to learning the first two events that users performed in a session, and identify which users performed specific events once starting a session. This function is intended for use in the query’s SELECT clause.
Combining Paths into One Segment
Instead of analyzing two or three different paths separately, you can now look at paths together as one segment generating a whole new set of insights, which aren’t there when analyzing each one on its own. This analysis is unique to CoolaData.
The CQL Alternative Start Points behavioral function enables the ability to see all relevant starting points together. This is a more complex path than what we’ve offered previously, because it has more than one starting point, so it helps analyze users who started a path at either event A or B – such as those who started either anonymously, or registered. Or those who received an in-app offer together with and those who did not. This function can be used to export a list of users and then provide them with incentives via email or mobile push notifications.
Sample CQL Code
SELECT count(session_id) AS visit_count, path_to_string(path) AS path
CLUSTER PATH BY session REMOVE DUPLICATES match
starts with event_name= “Login” or event_name=”Register”
followed by event_name= “Search”
followed by event_name= “Add to Cart”
followed by any
GROUP BY path
ORDER BY visit_count DESC
These are just a couple of the cool new CQL queries now at your disposal. For more CQL query tips – like to find out what sequence of events led to a particular action – please see our recent blog post on CQL tips and best practices. You can also check out our CQL Tutorial for guidance on why we execute CQL queries and how they can benefit you.