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 Journey Analytics write permissions on your database).Another option is sending the results of the query as events to your projects. Choose to save to "Cooladata (events)". Make sure to review our documentation on sending batch events to Journey Analytics before you do this.
     • 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.
5. 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:

FROM users_table

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

Journey Analytics provides you direct access to your tables.

Note to use this with care, as overwritten/deleted data cannot be restored.

Internal Journey Analytics 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):

insert into my_table(
  select ip_country 
  from cooladata
  where date_range(last 7 days) 
  group by ip_country
  )

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:

truncate 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:

drop table my_table