CQL

CoolaSQL, otherwise known as CQL, is at the heart of our platform's flexibility. CQL is an extension of SQL, which means we support full SQL with proprietary extensions, as described below. The main difference between CoolaSQL and SQL is that CoolaSQL provides the ability to examine a sequence of events, whereas SQL is more set-based.

CoolaSQL supports multiple powerful proprietary clauses, functions, and special fields – allowing for easy analysis of typical behavioral patterns such as with path analysis, cohort analysis, and funnel analysis. The great value of CQL is when it comes to very detailed, tailor-made questions that need specific answers.

Best Practices

  • Included date_range in every query FROM Journey AnalyticsThe date range function must be included in every query running from Journey Analytics at the same query level (in the WHERE clause directly after the FROM Journey Analytics).
  • Included slicers in queries FROM Journey AnalyticsIncluding the slicers function in the query will enable report and document slicers to affect the query. If omitted they will have no effect. Note that the slicers function must be located at the same query level running from Journey Analytics (in the WHERE clause directly after the FROM Journey Analytics)
  • Use Limit to make queries run fasterJourney Analytics limits the result set that can be viewed in the AD to 25,000 rows. For example, enter limit 10 to limit the number of results to 10. Limit should be the last part of the Select clause. For larger result sets use the Query API or contact your Customer Success Manager for more options.
  • Use user_id in CQL when possibleJourney Analytics generates a user_id for better performance. When applicable always use this user_id instead of customer_user_id or alternative_user_id.
  • When you are using "SELECT * from Journey Analytics …"The database returns only the first 1,000 records. If you like to return a larger number of records , you must specify the columns names in the SELECT statement.