Segments

Segmentation can be used to simplify queries, compare segments, analyze specific segments, and generate actionable lists quickly and easily.

Creating and using segments is open to all users, when non-admin users can only update the segments they created (like reports).

Creating Segments

Creating a segment using the Cohort Report

To create a segment from any cohort report:

  1. Open a cohort report, in the report editor or dashboard.
  2. Mark any section of the table by clicking on it: individual cells, whole rows or whole columns.
  3. From the top right of the preview area, click “Explore users” and select “Create segment”.
  4. In the popup that was opened, enter the desired segment name and click “Create segment”.
  5. A save confirmation message will appear. To edit the segment query or set an update schedule, click the link in the confirmation message to go to the segment editor page.

create segment from cohort

 

Creating a segment using the CQL Editor

You can use the CQL editor interface to create a segment. To create a new segment using the segment builder:

  1. Go to Project / Segments in the main menu.
  2. Click the “+” button, and select “CQL Editor”.

This can be used to:

  • Edit segments created from a report.
  • Create a segment based on a query you already use.
  • Create complex segment queries (not available in the segment builder).

Note to adhere to the following rules:

  • A segment must contain only user_id: no matter what query you use, the final response must include a single column containing “user_id” values to work as a user segment. If your query contains additional properties, precede it by “SELECT user_id FROM (…) ” to isolate the “user_id” column.
  • A segment should contain only unique values: to avoid getting duplicate results, end your query in “GROUP BY user_id”.

The default segment query template, available when opening a new CQL Editor segment, already includes the required syntax specified above. Edit it or replace the FROM clause to keep the correct syntax. Note that invalid syntax will not be possible to save.

Segment CQL editor

 

Creating a segment using the Segment Builder

The segment builder provides an easy to use interface for creating advanced behavioral segments. To create a new segment using the segment builder:

  1. Go to Project / Segments in the main menu.
  2. Click the “+” button, and select “Builder”.

The builder consists of conditions and filters.

Conditions include the following 3 options:

  • Users who did anything: select a segment of users who were active in the specified time period.
  • Users who did specific events: select a segment of users who did any of a list of specific event in the specified time period.
  • Users who had a comparative KPI value: select a segment of users whose KPIs in the specified time period were above/below a value. Allows you to select from preset behavioral KPI templates or define any calculation on any property using custom KPIs.

You can add up to 5 conditions in a single segment query, for example: to filter by consecutive behaviors – by choosing different time periods for each condition, or to drill down into your segment further by intersecting several condition types.

Filters:

  • You can filter any specific condition based on any property. For example, users whose purchases are below $10 by calculating the SUM of “cost” in event “buy”.
  • You can filter all users in the segment based on any user/session property. For example, only include users from the US by filtering “ip_country” to be in “United States”.

segment builder

 

Segments List

To see and manage all saved segments in your project go to Project – Segments in the main menu.

Segments list

From here you can:

  • See a list of saved segments
  • Edit saved segments
  • Delete saved segments
  • See the run (update) history of each segment
  • Run (update) a segment manually
  • Create new segments

 

Using segments in reports and dashboards

Segmentation can be used to simplify queries, compare segments, analyze specific segments, and generate actionable lists quickly and easily.

Filtering by segment

Narrow down your reports and dashboard by showing only users from specific segments.
To filter by segments:

  1. Open any filter: report, sheet or dashboard.
  2. From the properties list, choose “segment”.
  3. Select the segments you want the user to be part of. Enter multiple segments if the users must be part of all of them, i.e. the intersection of the segments. For example: include both “new_users” and “new_shoppers” to only show users who made their first purchase and are also new to the site.
  4. You can include several segment conditions in one filter using the AND operator. However, note that the OR operator is not allowed in conjunction with segment filters.

filter by segment

Cohort report by segment

In any Cohort report, group users into cohorts based on their segments and compare between them.
To cohort by segments:

  1. Open any Cohort report
  2. In the “Group cohorts by” section, choose “segment”.
  3. Select any segments you wish to include in report.
  4. Optional: add “Other” to the segments list to also include users who do not belong to any of the segments you specified.

cohort by segment

KPI breakdown by segment

In any KPI report, add a breakdown by segments to show which segment the user belongs to.
Note that users that belong to more than one segment will appear multiple times – once for each segment.
To break down a KPI by segments:

  1. Open any KPI report
  2. In the breakdown (bottom) section, choose “segment” from the first row options.
  3. Select any segments you wish to include in report.
  4. Optional: add “Other” to the segments list to also include users who do not belong to any of the segments you specified.

kpi breakdown by segment

Funnel breakdown by segment

In any Funnel report, add a breakdown by segments to split the funnel steps to the segments and compare between them.
Note that users that belong to more than one segment will appear in each segment.
To break down a funnel by segments:

  1. Open any Funnel report
  2. In the breakdown (bottom) section, choose “segment”.
  3. Select any segments you wish to include in report.
  4. Optional: add “Other” to the segments list to also include users who do not belong to any of the segments you specified.

funnel breakdown by segment

Print Friendly, PDF & Email

Aggregation Tables

Aggregation Tables automatically run scheduled calculations and data aggregations and save them in a separate, permanent table. This can be used to:

  1. Compute and store aggregated data in order to enhance the performance of queries running on large scales of data.
  2. Fetch data from external data bases into tables in your project for faster and easier querying.
  3. Send events data to your project from external data bases. Contact you Customer Success Manager or email us at support@cooladata.com for more information on this function.

Multiple tasks can be created on the same table. In this way you can make different manipulations to the same table, in multiple queries and/or schedules. For example, first fetch the data from your linked data source, then add an aggregation to it in the same table.

Table are saved until manually deleted. To delete a table, delete all tasks related to it.

 

Creating/Editing an Aggregation Table

  1. From the Project menu, go to Aggregation Tables.
  2. A list of your saved Aggregation Tables is displayed. Click a task to open it.
  3. To create a new Aggregation Table, click the + button at the top right, or the Add Aggregation button, in case your list is empty. The following opens:
  4. In the Aggregation Table editor page, enter the following details:
    • Task Name: The name of the task. Supports any text. Enter something descriptive to help you identify the task.
    • Query: enter any query, on any data source, including all tables and linked data sources in your project.
      Notes:

      • Do not use the “filters (context)” or “date_range (context)” features in queries run via Aggregation Tables. This is because there is no (report/dashboard) context for these kind of queries, and therefore the query will fail.  Instead write explicit date range and conditions.
        Tip: use the report options “Show CQL” to see the final query (without “context”) when copying a query from a report to an Aggregation Table.
      • The top query aliases are stored as the columns names in the table, and therefore cannot contain spaces, special characters or begin with a number.
      • When editing a saved query, only change the names or number of columns in the final results if using “Replace” in Write mode (see below), otherwise the the schema of the results on the next run will not match the saved table, and it will fail.
    • Run the query to see a preview of your results. By default the preview is set to the top 50 rows – change this figure for the right hand side of the Run button row.
    • Settings:
      • Save to: where the table will be saved. The default options is “Cooladata”, which means the results will be saved to a table in your project. Additionally, Linked Data Sources in your project can be used as the save to destination, which means the results will be saved to a table in your database (make sure to give Cooladata write permissions on your database).
      • Table name: the table to which the data will be saved. Table names are case sensitive, and cannot include spaces or special characters. Take care when using an existing table, as existing data might be overwritten.
      • Write mode:
        • Append: new rows are added to the table each time the query is computed.
        • Append and update: new rows are added to the table each time the query is computed, and existing rows that match the unique key you selected will be replaced with the updated data.
        • Replace: The entire table data is overwritten each time the query is computed.
    • Notify on Failure to: email/s to be notified if any scheduled run fail.
    • Schedule: when to run the query.
      • Active: turn this off to prevent any run of this task. Note that this will also block Jobs from running it.
      • Frequency:
        • Daily: At a specific hour of the day (UTC).
        • Weekly: On a specific day of the week, at a specific hour of the day (UTC).
        • Monthly: On a specific day of the month, at a specific hour of the day (UTC).
        • CRON: Set the frequency by specifying a CRON expression. The CRON expression should be in the format of – minutes (0 – 59) hour (0 – 23) day of month (1 – 31) month (1 – 12) day of week (0 – 6). The star symbol (*) should be placed as a wild card. For example, this CRON expression will run the Aggregation Table daily at 3:30 am UTC: 30 3 * * *. You may refer to www.cronmaker.com for a description of CRON expression syntax.
      • Run now: if the task has been saved, click to run it immediately and update the table.
  1. Click Save to save this task. If you entered a new table name, the table will be created on the next (successful) run. Click Save and Run to create/update the table with your new/updated query immediately.

 

Querying Aggregation Tables

To query an Aggregation Table, in a CQL query, as with any table in your project, state the table name in the FROM clause, instead of “cooladata”. For example:

You can also use these tables as the data source for various report builders, such as KPI. See the report documentation for more information.

To use a date picker on a query from a table, use the TABLE_DATE_RANGE function – see Date and Time Functions for more information.

Aggregation Tables saved to your project can be found in the Schema, under Aggregation Tables.

  • Expand any table to see the table’s columns and their data types.
  • Drag&drop a table/column name to use it in a query.

 

Manipulating Tables

Cooladata provides you direct access to your tables.
Note to use this with care, as overwritten/deleted data cannot be restored.
Internal Cooladata tables (events data) are protected from these methods.

Insert Into

With the “insert into” expressions you can append new data to any of your external tables. You can append data to any of the existing data columns in the table. Data columns you do not append to will be updated with NULL values.

Example – the following query will append data to the user_country column in my_table table (other columns in the table will be updated with NULL values):

Truncate Table

With the “truncate table” expressions you can clear any data stored in one of your external tables. This method will remove all lines from the table, leaving it empty, but retain the table’s data scheme, so that you can append new data to it later on.

Example – the following query will empty the table my_table:

Drop Table

With the “drop table” expressions you can delete any of your external tables. This will complete remove the table and all its data, and this action cannot be undone.

Example – the following query will delete the table my_table:

Print Friendly, PDF & Email

Alerts

Alerts enable you to receive notifications on changes or special business cases in your data.

Alerts can be used in two business cases:

  • To monitor the system (operational)
  • To indicate a business change

When you define an alert, you will receive an email containing a set of reports whenever a condition you define applies, according to the schedule you specify.

The email graphically displays the report charts and provides an attached CSV file containing the raw data of the reports.

 

Defining an alert

To define a new alert:

  1. From the main menu, choose Project projecticon – Alerts. This shows a list of all previously defined alerts in the project. From here you can also update and delete existing alerts.
  2. Click the Add + button at the top right corner.
  3. Define your alert:
    • Name: Give your alert a name – it will be included in the email subject and body.
    • CQL: add a CQL query that will trigger the alert. The query must return a single numeric value. For example, the default query returns the number of events received during the previous day:
      SELECT count(*)
      FROM Cooladata
      WHERE date_range (yesterday)
    • Condition: define a limit value for the query response, above/below which an alert will be sent. For example, the default condition will send an alert if the query returns a result of less than 1, i.e. if no events were received during the previous day.
    • Check Condition: you can check the query and condition by clicking Check Condition – the query response and trigger result will be shown (if the query is valid).
    • Frequency: specify the schedule for checking/sending the alert:
      • Daily: specify the hour at which the report should be sent (UTC)
      • Weekly: specify the day of the week and hour at which the report should be sent (UTC)
      • Monthly: specify the day of the month and hour at which the report should be sent (UTC)
      • CRON Expression: see www.cronmaker.com for a description of CRON expression syntax.
    • Recipients List: select one or more CoolaData users and/or enter any email address (can also add emails not registered to CoolaData). Add your own email as a recipient to get a copy of the alert when it is sent.
    • Text: you can add a rich HTML text to the email body. This is not mandatory.
    • Reports to include: Select one or more reports from the project to include in the email body. This is not mandatory.
    • Click Save.
    • Run Now: Once saved, you can also run the alert manually by clicking Run Now – if the condition is triggered, the alert email will be sent to the list of recipients you defined.

 

Alert History

To see when an alert was last run or sent, select History from the list item menu.

Here you can also see which reports were sent and the number of recipients. Click each row to see more details.

Print Friendly, PDF & Email

Publications

Publications enable you to send an email containing a set of reports to a list of recipients, either registered to CoolaData or not.
A publication email displays the report charts, and includes a CSV attachment of the data of each report.
Any user can send a one-time publication from a report page.
Project admins can also create and manage scheduled publications.

Publications can also be sent as white-label, with your branding and mailing addresses. Contact your Customer Success Manager, or email us at support@cooaldata.com for more information.

 

Sending a One-Time Publication

  1. From any saved report page, click Options and select Publish. The following displays:
    3-28
  2. Define your publication:
    • Title: the title shown in the email.
    • Reports to Publish: the reports to include in the email.
    • Recipients List: any (valid) email address, whether registered to Cooladata or not. Add yourself as a recipient to receive a copy of the publication email.
  3. Click Publish now to send the publication. The email should arrive within a few minutes.

 

Defining a Scheduled Publication

You can create a new scheduled publication either from a report, as explained above, or from the publications page.

From Report

  1. From any saved report page, click Options and select Publish. The following displays:
    3-28
  2. Define your publication:
    • Title: the title shown in the email.
    • Reports to Publish: the reports to include in the email.
    • Recipients List: any (valid) email address, whether registered to Cooladata or not. Add yourself as a recipient to receive a copy of the publication email.
    • Frequency: the schedule for sending this publication email:
      • Daily: the email will be sent every day, at the hour you select (UTC).
      • Weekly: the email will be sent on day of the week and at the hour you select (UTC).
      • Monthly: the email will be sent on the day of the month and at the hour you select (UTC).
      • CRON Expression: define any schedule valid by CRON expressions. See www.cronmaker.com for a description of CRON expression syntax.
  3. Click Save to save the publication. It will be sent on the schedule you defined.
  4. You can also click Publish now (before or after saving the publication) to send the email right away.

 

From Publications

  1. From the Project menu (cogwheel icon at the top right), and select Publications.
  2. Here you can create new publication, and manage your publications.
  3. Click the + button at the top right to create a new publication.
  4. Define your publication, same as From Report (see above).

Note: A project can contain up to 5 saved publications. To enable your project to send more publication, contact your Customer Success Manager or email us at support@cooaldata.com.

Print Friendly, PDF & Email

Jobs

Jobs allows you to schedule a sequence of tasks that will run consecutively, one after the other, once the previous task has been completed. This enables you to create more advanced processes on your data, without manual intervention, such as running aggregations over other aggregations, and conditioning alerts to only run once an integration has been updated.

This feature is available to project admins.

Settings up a new job

  1. From the project menu, select Jobs.
  2. Click the + icon to open a new job page.
  3. Enter a name (can use any name).
  4. One by one, select the tasks you want to run in the job. You can select from:
    • Segments
    • Aggregation Tables
    • Integrations
    • Alerts
    • Publications
  5. Once you’ve selected the type of task, choose one from the list of existing tasks in your project.
  6. You can re-arrange the order of the tasks by dragging the task icon up or down.
  7. Next, set the job schedule. Note that by adding a task to a job, you reset the tasks schedule to Manual. This means the task will only run on the job’s schedule. You can change the individual task’s schedule later from the task page.
  8. Lastly, if you’d like to be notified by email on any failed runs of this job, enter the emails to include in the list below. You can always check for run status in the job’s history, from the jobs list.
  9. Click Save to save the job, or Save and run now to also execute it. Once saved, click Run now to manually execute the job.

 

Managing saved jobs

The jobs list shows you all existing jobs.

From here you can:

  • Edit jobs: click any row to open and edit the job.
  • Delete, run now, and see the job history: from the row options (ellipses icon at the right end).

The job history shows all runs of the job. Click a row to see details of the tasks in the run. Successful runs will show all tasks it included. Failed runs stop at the first failed tasks, and will therefore show all tasks up to the one that failed.

 

Print Friendly, PDF & Email