Filters

Filters enable you to filter (slice) the data displayed in reports without changing the original report or query.

Filters can be used in many sections of the platform:

  • Report: each report has a built in filter function, that runs on the entire report query. Some reports also include internal filters on specific sections, such as KPI measures and Funnel steps. Additionally, each report can be set to Ignore External Filters, i.e. Sheet or Dashboard filters.
  • Sheet: enables you to select a specific group of reports in a specific dashboard sheet to be filtered. This option displays a filter in the dashboard that lets users easily change the value of each condition. It can also enable dashboard viewers to change filter values.
  • Dashboard: allows you to filter all the reports, in all the sheets in the dashboard. Viewer users (guests) do not see dashboard filter indicators. This allows you to filter the dashboard to only show customer specific data, without displaying these conditions to customers viewing the dashboard. Cooladata Users/Admins can set their own filters for the dashboard. They will get the same filters as yours when you first share the dashboard with them, but if you later change your dashboard filters, theirs will remain the same.

 

Report

  1. Click the Filter 3-12 button at the top of the report. The following opens:
    3-13
  2. Fill in the conditions of this filter:
    1. Property: Select an event property from the Where dropdown field on the left.
    2. Operator: Select an operator to act upon the selected property.
    3. Value: Type in or select one or more values.
    4. Case sensitivity: “Contains” and “Doesn’t contain” conditions can also be set as case sensitive/insensitive. Click the Aa icon next to the condition to switch between the two modes.
      3-14
  3. To add an additional filter, click the + button on the right. Specify whether there is an AND relationship (all filters define a match) or an OR relationship (any filter defines a match).
  4. Click the Apply button. The counter on the filter icon indicates how many filters are applied. A filter icon also appears in the report preview, specifying the conditions that apply to it.

To set a report to ignore external filters:

  1. In the report page, click the Options button at the top right and choose Report Settings.
  2. Turn the options to Ignore External Filters on or off. When On, only filters defined in the report itself will be used. This applies to any use of the report, both in a dashboard and Embedded report.

 

Sheet

  1. Click the Add Reports 3-16 button in the top right of the dashboard, and choose Filter. The following opens:
    3-18
  2. In the Affected reports field, select one or more reports in this dashboard sheet to which to apply this filter.
  3. Fill in the conditions of this filter:
    1. Property: Select an event property from the Where dropdown field on the left.
    2. Operator: Select an operator to act upon the selected property.
    3. Value: Type in or select one or more values.
    4. Case sensitivity: “Contains” and “Doesn’t contain” conditions can also be set as case sensitive/insensitive. Click the Aa icon next to the condition to switch between the two modes.
      3-14
  4. To add an additional filter, click the + button on the right. Specify whether there is an AND relationship (all filters define a match) or an OR relationship (any filter defines a match).
  5. To allow dashboard viewers (and project Viewer users) to select the filter values from the sheet itself, set the “Can be updated by dashboard viewers” toggle to On. Note that only dashboard editors will be able to change the selected properties and operators.
  6. Click the Apply button. A filter icon also appears in each report that is affected, specifying the conditions that apply to it.
  7. By default, the filter is added to the top left corner of the sheet. However, you can drag it anywhere in the sheet, as well as resize it.
    3-20
  8. To change the filter condition values click the filter fields and select a value. Click the Update button to apply the change and reload the effected reports. Note that changes to these values will be saved for 30 minutes, and then reset to what you defined the in the filter settings.
  9. Changes to this filter’s settings in your dashboard will effect all users sharing it, and will update the next time they open the dashboard.

 

Dashboard

  1. Click the Filter 3-12 button at the top of the dashboard. The following opens:
    3-13
  2. Fill in the conditions of this filter:
    1. Property: Select an event property from the Where dropdown field on the left.
    2. Operator: Select an operator to act upon the selected property.
    3. Value: Type in or select one or more values.
    4. Case sensitivity: “Contains” and “Doesn’t contain” conditions can also be set as case sensitive/insensitive. Click the Aa icon next to the condition to switch between the two modes.
      3-14
  3. To add an additional filter, click the + button on the right. Specify whether there is an AND relationship (all filters define a match) or an OR relationship (any filter defines a match).
  4. Click the Apply button. The counter on the filter icon indicates how many filters are applied. A filter icon also appears in each report that is affected, specifying the conditions that apply to it.

 

Example: Building a Parametric Dashboard

A parametric dashboard is comprised of multiple sheets, each containing the same reports. The data in each sheet is sliced according to the value of a specific event property.
For example, a parametric dashboard could show multiple sheets, each containing the same reports, where each sheet shows the data of a specific country.
The same thing could apply to a dashboard that has a sheet for each branch, department, game provider or customer.
You can add the same report(s) to a sheet multiple times, each with a different filter. Placing them side-by-side enables easy comparison.

To define a multi-sheet parametric dashboard:

  1. Create a new dashboard.
  2. Add all the reports that you would like to appear in the dashboard to the sheet.
  3. Add a filter to the sheet, with the condition your wish to show on this specific sheet. Note that reports for which the Ignore Dashboard Filters option is On are not affected by dashboard filters.
  4. Duplicate the sheet, and rename it to describe the data it will display.
  5. Edit the filter (edit-1 button in the top right of the window) to change each sheet’s conditions, and select the values to be displayed in this sheet. For example, to show different countries in each sheet, you can select ip_country is in list United States for one sheet (so that it only shows data from the United States) and ip_country is in list Canada for another sheet (so that it only shows data from Canada).
  6. Click Apply.
Print Friendly, PDF & Email

Embedded Reports

CoolaData enables you to embed your CoolaData reports in any web page framework, while enjoying the full benefit of CoolaData’s visualization options.

The embedded report will look like this:

Get Your CoolaData User API Token

To get your CoolaData API token click the Profile icon at the top right of the window and copy your API Token.

Get the Report ID

  1. Create/Open a Report in CoolaData. See Creating Reports.
  2. In the Report Editing window, copy the report ID which is the last part of its URL.
    5-6

 

Prepare the CoolaData Report Request

The following are the mandatory parts of a request for embedding a CoolaData report script in your site.

Use the retrieved User API Token as described in Step 2 in, and the retrieved Report ID, as described in Step 3.

Method: Post

Request URI: https://app.cooladata.com/1/embeddedReports

Content Type: application/json

Payload:

{ 
  "apiToken": "<apiToken>", 
  "reportId": "<reportId>"
}

The response to the request above is a single report token according to the parameters of the request. A token expires immediately after being used or within five minutes, if it is not used.

{
  "token": "<tokenId>"
}

 

More Options for the CoolaData payload request

Here are a few more options that you can include in your CoolaData report request in order to get back the visualization and data that you want.

Dashboard ID

Including a dashboard ID in the request returns the same view as is currently displayed in this dashboard of this user (the user is determined by the API token). This means that the report script represents the same date range and filters as in the dashboard.

To get the dashboard ID, open the dashboard and then copy the dashboard ID from the right of its URL:

https://app.cooladata.com/#/dashboards/56a63385e4b0e8504dbe8770

Note: If the report has an active date range or filter override, then the report’s date range and filters are used. Otherwise, the dashboard’s date range and filters are used, as set by the user of this API token.

Date Range

If the request does not include a specific date range, then the report’s date range is returned.

Adding a date range to the request takes precedence over the date range defined in the CoolaData console for this report and dashboard.

To specify a date range, use one of the following options:

  • Date Range Expression:
    "dateRange": {
      "mode": "TODAY | YESTERDAY | LAST_7_DAYS | LAST_30_DAYS | LAST_90_DAYS | CURRENT_WEEK | CURRENT_MONTH"
      }

– OR –

  • Custom Range: Epoch milliseconds – you can send any millisecond during the date. CoolaData converts it to the first second of the Start date and the last second of the End date.
    Start date (actualFrom): is converted to “YYYY-MM_DD 00:00:00”.
    End date (actualTo): is converted to “YYYY-MM-DD 23:59:59”.

    "dateRange": {
     "mode": "CUSTOM",
     "actualFrom": 1439275953685,
     "actualTo": 1439275953685
     }

 

Filters

If the request does not include a specific filter (as described below), then the report’s filters determine the data that is returned.

Adding filters to the request takes precedence over the filter defined in the CoolaData console for this report and dashboard. Multiple conditions can be entered in the slicer section of the request. All conditions can either be specified with an AND or an OR Boolean operator.

Specify the name of an event property, an operator and a value.

"slicer": {
  "bitwiseOperator": "AND",
  "conditions": [{
    "property": "property_name",
    "operator": "INTEGER_EQUALS | INTEGER_NOT_EQUALS | STRING_EQUALS | STRING_NOT_EQUALS | INTEGER_IN_LIST | INTEGER_NOT_IN_LIST | STRING_IN_LIST | STRING_NOT_IN_LIST | CONTAINS | NOT_CONTAINS | EXISTS | NOT_EXISTS | REGEX | AT_LEAST | NO_MORE_THAN | BETWEEN | TIME_BETWEEN | LATER_THAN | EARLIER_THAN",
    "values": ["value1", "value2"]
    }]
  }

To use segmentation in filters, add the following condition:

{
    "property": "COOLADATA_SEGMENT",
    "operator": "IS_IN_SEGMENT_TABLE | NOT_IN_SEGMENT_TABLE"
    "values": ["segment_name"]
}

You can use multiple segment conditions in the same query.

 

Fetch the CoolaData Report

Use the report token that you received (as described above) in the following API to fetch the report visualization (Method – GET). Then, embed it in any webpage framework.

https://app.cooladata.com/reports/<reportToken>

 

Example

Using all the above options.

Request the Report Token:

{
    "apiToken": "aRG1DbdzN0FC1z8Ii7VUjFwlQvaogiBm6LE6oxFs",
    "reportId": "55cca707e4b03f8a48f9299f",
    "documentId": "55bf81c2e4b0d15ad906453e",
    "dateRange": {
        "mode": "LAST_7_DAYS"
        },
    "slicer": {
        bitwiseOperator: "AND",
        "conditions": [{
            "property": "browser_name",
            "operator": "STRING_IN_LIST",
            "values": ["Chrome", "Android Browser"]
            }]
}

Response:

{
    "token": "55d2f2cee4b067eb33fe778"
}

Fetch the report:

https://app.cooladata.com/reports/55d2f2cee4b067eb33fe778

 

Print Friendly, PDF & Email

Report Options

The report options are available from inside the report, in the top right corner next to the report date range:

 

 

 

 

 

 

 

 

Report Settings

The report settings allow you to control specific configurations of the report:

Ignore dashboard date range

When turned on – the report will only run on the date range defined inside the report, ignoring the dashboard date range.

Ignore dashboard filters

When turned on – the report will only filter the data by the filters defined inside the report, ignoring the dashboard filters.

Read Only

When turned on – saving changes in the report will be blocked. Remember to Save after turning this toggle on for it to take effect. Turning off the Read only setting can only be done by an ADMIN or the owner of the report.

Private

When turned on – The report will only be visible to the report owner in the reports list, but can still be used in dashboards.

Use Cached Results

When turned on – Cooladata will attempt to use results from a previous run of this query. If cached results are returned, the query summary row will show “cached” and query runtime will be minimized.
Results from past runs will be used as long as the queried tables were not modified and the date range and filters of the query are the same.

Use sessions aggregation for query optimization

When turned on – Cooladata will attempt to optimize performance by automatically shifting your query to run on top of the sessions table if all the data you are querying is available there.
Notice: Cooladata stores your data in two separate tables: one is your event table, in which every row is an event, and the other is your sessions table, in which every row is a session. The event table holds all the event-level data and event-scope properties, as well as user and session scope properties. The sessions table holds session-specific properties (such as session duration and the session path) as well as session and user scope properties.  Shifting between the sessions and events table might cause for slight differences due to the fact that when running over the sessions table, the date range is filtered according to the session start time (session_start_time_ts) whereas when running over the events table the date range is filtered according to the event (event_time_ts)  timestamp.

Drill-through Reports

Drillthrough Reports enable you to drill through (zoom in) to a section of a report by clicking it, and opening another preconfigured report, filtered to that category’s data.

For more information on setting drill through reports see Drill through Reports.

Duplicate

This will create a copy of the original report. When clicked on from inside a dashboard, this will add the copy of the report to the dashboard as well.

Publish

This will create a publication (email report) with the report results and send it to a list of recipients. Project ADMINs can also choose the publication’s frequency.

For more information see publications.

Add to Dashboard

This allows you to add the report to a specific Sheet in a specific Dashboard you have permissions to edit.

Show CQL

This opens up the underlying CQL of the report. You can copy and paste it in a CQL report to modify it.

Print Friendly, PDF & Email

Querying with Segmentation

Segmentation can be used in CQL for breakdown or filtering. Filtering supports using segments (to filter by users), or any other table in your project (Aggregation Tables, Integration tables, etc.).
Read more: Creating and managing segments, Using segments in reports and dashboards

 

Breakdown by Segments

To display users from specific segments: add the following expression to the select clause. In the brackets, include the names of all the segments you want to include in the query. Add the term “other” to also include users not included in these segments. The query output will include the segment name as a data column.

select segment_group(<segment_names>,[other])

Example:

select segment_group(paying_users, non_paying_users, other), count (*) as users
from cooladata
where date_range(context)
and filters(context) 
group by 1
order by 2

 

Filter by Segments

To filter any CQL query by users in a segment: add the following expression to the where clause. Use the segment name as saved.

where|and segment(<segmentName>)

To filter a query by data NOT in the specific segment: precede the segment statement by “NOT”.

where|and NOT segment(<segmentName>)

Filter by multiple segments: it is possible to slice a query using multiple segments connected by an “AND” logical operator. “NOT segment” can also be used, and can be combined with regular segment statements. Only results that belong to the intersection of all the segments will be shown.
For example, the following would be a valid query:

SELECT user_id 
FROM cooladata 
WHERE date_range(last 7 days) 
AND NOT segment(segment_new_users) 
AND segment(segment_news.customer_type) 
AND (event_name="Login" OR user_type="new")

Notes:

  • “OR” cannot be used on segments: To use an “OR” condition in conjunction with segments (on condition other than the segment statement), enclose it in brackets, with “And” operator. For example:
    SELECT user_id
    FROM cooladata
    WHERE date_range(last 7 days)
    AND NOT segment(segment_new_users)
    AND (is_new=1 OR user_type="new")
  • Cohort and Funnel can only be segmented by “user_id”: it is only possible to segment Cohort and Funnel queries by the “user_id” property (no need to declare it specifically).
  • Cannot segment by property “event_name”: the property “event_name” cannot be used when filtering by segment.

Example:

We’ll use a segment “new_users” we created to see the activity of these users over time.

SELECT date(event_time_ts) AS day, count(distinct user_id) AS users
FROM cooladata
WHERE date_range(context) 
   AND slicers(context)
   AND segment(new_users)
GROUP BY day
ORDER BY day

 

Filter by table

Filter by segment can also be used with external tables (other than segments).
To filter using a table: specify the table name instead of the segment name, and append “.<propery_name>” to specify the segmentation property  (default is “user_id” and doesn’t have to be stated).

where|and segment(<tableName>[.<propertyName>])

To filter a query by data NOT in the specific table: precede the segment statement by “NOT”.

where|and NOT segment(<tableName>[.<propertyName>])

Example:

We’ll use an emails list we built to easily create an email campaign (using Query API):

SELECT email
FROM cooladata
WHERE date_range(last 7 days)
   AND segment(churned_users.email)
GROUP BY email
Print Friendly, PDF & Email

Other Report Settings

The other reports settings are available from inside the report, in the top bar, each has his own icon:

Report Tags

Tags can be given to any reports. The tag’s design shows you an indication of the number of tags used in the report. Click the button to add or edit tags.
Use the filter column in the reports list in order to filter your reports by tags.

Save and Save as

Save as located inside the “save” dropdown.
Use it to save changes you’ve made to a new report, without affecting the original. This will switch you to the new saved report.

Report Folders

Reports folder is a new Cooladata feature which allows you to located easier and faster your project’s reports.
You could create a folder from the report itself or from the reports list, as long as you are the report owner or you have permission to edit it.

Access to the folders is available through the Report editor or through the Reports list.

Folders through the Report Editor

The report folders are available from inside the report, in the top bar next to the report filters

Folders through the Reports List

There are three ways to edit or add folders from the reports list:

A. Through the Folders menu-

Hover the folders names and use the sub-menu (3 dots) to edit.

Click the + to create new folder.

In order to delete just the folder, without the content, just move all the folder reports’ to another folder or delete the folder with the contained reports through the folder sub-menu.

B. Through the report sub-menu

Hover the reports and use the sub-menu (3 dots) in order move the report to another folder.

C. Through the bulked reports options

Click on the report’ check box and mark it, then click the folder icon in the bulked reports menu (at the top bar) in order to move the selected reports to a folder.

Important information

Report Folders are saved per project, and therefore, all of those options are available from the Project’s Reports tabs.

Click on “All Folders Content” in order to display all the project reports without the folders filters.

All the reports that aren’t related to any folder will be saved in the “Root” folder.

Please note that folders will be saved only if a report related to them,
therefore, please make sure you’ve added at least one report to your new folder.
Also, duplicated reports or reports which “saved as” will keep being related to the folder given to them in the original report.

Report Parameters

Parameters are variables, that allow you to define key and value, and by that inject any value you want to the query.

In order to use the parameters you need to define the key -value on the parameter popup and add the keyword to the report query.
You will be able to do so either from a CQL report or either from a dashboard:

Parameters through the CQL report

1. Open a CQL report, click on the parameter icon and define the keys and values you want. then add the keywords to the query with initial strudel (for example @tableName).

2. Open a CQL report, add the keywords to the query with initial strudel. When you’ll open the parameter popup, you could see the keywords you’ve entered to the query already there, just add value to each key and apply.

add the keywords to the query and add a value on the Parameters popup

Parameters through the dashboard

Adding a report with parameters to a dashboard will automatically add the report parameters to the dashboard. If you’de like to apply some parameters only for the dashboard, you could add them to the dashboard directly through the parameters icon (right next to dashboard filters).
You could also change the report parameters value on the dashboard, but if you would like to delete a report parameter you’ll need to delete it from the report itself.

Important information

  • Parameters will apply on CQL reports only (through the report builder and through the linked dashboard)
  • Two identical keys, in the same report, with different values, will inject the first set value (in both report and dashboard).
  • Two identical keys, with different values, in two separate reports, on the same dashboard, will inject the first set value.
Print Friendly, PDF & Email