CQL (CoolaSQL) is the query language provided by Journey Analytics for answering complex behavioral questions using a simple language. The CQL report enables the analyst to type free syntax to generate any query. Using the CQL report you can query any data source in Journey Analytics – internal, external, and integrations. CQL enriches the basic SQL syntax, and has been specifically designed so that even a beginner SQL user can utilize its powerful clauses. CQL supports multiple powerful proprietary clauses, functions, and special fields, which allow easy analysis of typical behavioral patterns such as path analysis, cohort analysis and funnel analysis.

To create a new CQL report:

1. In the Journey Analytics Administrator console, click Reports .

   – OR –

   In the Dashboard window, click the Add Reports
   
   button.A list of reports is then displayed.

   

2. Select the CQL report.
   
   
3. Type in the name of the report in the top-left corner of the page.
4. Enter a query written in CQL into the center of the page. The default contents of the CQL report is the basic general SQL layout, as follows:
   
   
   • select — add any properties and functions here
   • from cooladata — only replace this to query external data sources or integration, otherwise, required.
   • where date_range(context) — allows the report to interact with the date range picker in both the report view (above) and inside any dashboard using it. See more in Date and Time Functions.
   • and slicers(context) — allows the report to interact with the filters in both the report view (above) and inside any dashboard using it.
   • limit 100 — limits the number of results returned. This is recommended to be included in any query, to save processing time and return results faster.

   Any of the text above is fully editable. Add GROUP BY, ORDER BY, HAVING, JOIN, behavioral functions, or any other common SQL clauses to further elaborate the report.

5. Define the report's date range and define the report's filter if you would like it to be different than the dashboard in which it appears.
6. Click the Compute button. For example, the following query counts the number of events received by CoolaData today.

   Select event_name, count(*) as cnt
   
   from cooladata
   
   where data_range(today)
   
   and filters(context)
   
   group by event_name
   
   order by cnt desc

   Events are available in the Journey Analytics database within an hour or two of being received. Before then, the following message may be displayed when you click Compute: No data in the selected date range.

7. Beneath the syntax box you will see a validation check; when red – an error will be displayed specifying any error found in the query. When green, the query is valid and can be computed. A valid query has no errors and will run in CoolaData. Verify whether the query is valid by looking to see the message Query is valid, as shown below:
   
   
8. Click "Compute" to see the results, and choose any visualization you wish.
   
   

9. To publish this report –

   • Click the three dots in the top-right corner of the page to display a toolbar.

     

   • Select the Publish option.

     

   • Fill out the window to specify the recipients to receive this report, the frequency and the time of day.The new report now appears in the Dashboard and is sent to the specified recipients daily.

10. Click Save.