CQL (CoolaSQL) is the query language provided by CoolaData 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 CoolaData – 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 CoolaData Administrator console, click Reports reports or in the Dashboard, click the Add Reports 6-15 button.
  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:
    CQL default

    • 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 CoolaData 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.
    basic cql

    Publish the report:

    • Click the three dots in the top-right corner of the page to display a toolbar and select the Publish option
    • Fill out the window to specify the recipients to receive this report, the frequency and the time of day.


Print Friendly, PDF & Email