Ranking

The Ranking report is used to identify the best or worst performing items within a dimension, such as: channels, regions, affiliates, products, and so on, based on any of the measurements that Cooladata records. For example: the top ten countries with the most users, or the five campaigns with the least total orders.

The ranking settings include the following options:

  • Ranking criteria: top or bottom X items
  • Dimension to be ranked: e.g., city, device, etc.
  • Function and measure to rank by: e.g., count distinct users, sum of purchases, etc.
  • Sort order: ascending (ASC) or descending (DESC).

You can select to display the ranking report using any of the visualizations.

Print Friendly, PDF & Email

CQL

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.
    6-53
  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:
    6-54
  8. Click “Compute” to see the results, and choose any visualization you wish.
    basic cql
  9.  

    Publish the report:

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

      6-56

Print Friendly, PDF & Email

Fusion

The fusion reports allows you to join results from multiple reports into a single output. You can join reports running on different data sources, for example a report running on CoolaData and a report running on your linked MySQL server, and join both data sources to one table/chart. This allows you to create client side joins on multiple data sources, which is not possible in a single report.

Defining a fusion report:

To define a fusion report you must set the following:

  • Join type: fusion supports the 4 common join types:
    • Inner join: show rows that match on all reports. Any missing rows will not be included
    • Left join: show all rows in the first report, and matching, or if not available – empty, rows in all following reports
    • Right join: show all rows in the last report, and matching, or if not available – empty, rows in all preceding reports
    • Full join: show all rows in all reports, matching, or if not available – empty
  • Report name: choose a report from your saved reports to join.
  • Join column: the column that will be used as the match criteria. The rows will be matched if the values of these columns are exactly identical between the reports.
  • Included columns: which columns will be shown in the report output. This list includes all the selected report’s columns. The selected columns will be shown in the output in the order selected, left to right, top to bottom (between reports).

 

Notes:

  • Funnel and other fusion reports are not available in fusion report
  • Once a report is selected it takes a few seconds to compute its content – a loading sign will be shown
  • You can join up to 5 reports in a single fusion report
  • You can also select only one report in the fusion – this would allow you to only show specific columns from that report. This can be useful for data security, or to show a more concise report.
  • You can apply any visualization to the output

 

Example:

This report to uses Fusion to join tables from different sources – it calculates the Profit and ROI from Facebook Ads using income data collected in CoolaData:

  • amount_usd is the income from a campaign (from CoolaData)
  • total_spent is the cost of a campaign (from Facebook Integration)
  • Profit and ROI are calculated columns (using pivot table)

fusion-example

Print Friendly, PDF & Email