The timeline event rollups feature in BlueConic enables developers to query timeline events and store the results in profile properties for activation or export.
When you add or edit a rollup via More > Timeline events > Timeline event rollups, you can build or adjust your query using two different modes:
Basic mode includes a point-and-click query builder that is ideal for configuring common, straightforward use cases. This mode allows you to select your timeline event type and conditions and then map the timeline data to profile properties.
SQL mode includes a built-in SQL editor for developers to write more complex queries directly in AlaSQL. This mode allows you to pursue more advanced use cases that were previously only possible through the Timeline Query Connection.
The following sections outline how to use SQL mode specifically to tailor your data analysis to your specific needs and calculate this as data flows into the CDP.
Note: You can switch between Basic and SQL modes using the toggle at the top of your rollup page.
Before you begin: Best practices for using SQL mode
If you plan to use SQL mode to build out a new rollup or update an existing one, consider these best practices:
Start your query in Basic mode
Configure and save a new rollup in Basic mode first and then switch to SQL mode to customize and refine the query further. BlueConic will automatically translate that Basic configuration into an AlaSQL query when switching, providing a ready-to-use query upon which to build.
Save before switching to SQL
If you configure your rollup in Basic mode first, save it in that mode before you switch to SQL. If you switch from Basic to SQL without saving, any Basic mode configurations will be lost.
Note: Once you save a rollup in SQL mode, any prior Basic mode configurations are automatically removed.
Preview the query
As you are developing your rollup, preview your configuration to validate that it produces the expected outcomes, and make changes as necessary. To run a preview, enter a unique profile identifier at the bottom.
Creating timeline queries in SQL mode
When you’re ready to start using SQL mode, click SQL from the toggle. The SQL editor opens at the top, and you can begin writing queries to run against timeline events for each profile directly in AlaSQL (version 4.5.2).
One prominent feature of the SQL editor is its autocomplete feature; as you type, the editor will suggest AlaSQL functions, tables, and columns. The autocomplete structure is as follows:
[primary key icon] <table name>.<property id> <alaSQL datatype>
<Display Name>
<Description>
For example:
For more detailed examples, review the article SQL query examples for timeline event rollups.
Notes:
You can resize the height of the editor by dragging the bottom right corner up or down.
If you type S in an empty query, the editor will suggest the SELECT statement:
Understanding table and column naming conventions
Take note of the table names and default columns used in the SQL editor:
Table names
Table type | Naming convention | Example |
Timeline event type | <<event type ID>>_event | order_event |
Nested property | <<event type ID>>_<<nested property ID>> | order_product |
Multi-value property | <<event type ID>>_<<Property ID>> | order_tag |
Multi-value sub-property | <<event type ID>>_<<nested property ID>>_<<Property ID>> | order_product_category
order_product_tag |
Default columns
Column | Naming convention | Example |
Event ID | id | order_event.id |
Date | date | order_event.date |
Row ID | row_id | order_event.row_id |
Foreign key row id (for nested / multi-value properties)
The timeline event type ID isn’t guaranteed to be unique; only the combination of id+date is unique. For nested properties, setting an identifier isn’t required and the value can be empty. Therefore, we introduce new keys row_id for the rollup, which are generated numbers starting with 1. For recognizability, we also show the configured identifier, or when no identifier is configured but there is a sub-property with property ID “id” we show that. For multi-value properties, a primary key is not needed. | <<table>>_row_id
We also have <<table>>_id for recognisability | order_product.order_event_row_id
order_product.order_event_id
order_tag.order_event_id |
Value (for multi-value properties) | value | order_tag.value
order_product_tag.value |
AlaSQL data structure example
Here is an example of how the SQL editor works based on the Order timeline event type:
order_event
Primary key: row_id
Required columns:
id
date
order_product
Primary key: row_id
Configured identifier: id (could be empty)
Foreign key*: order_event_row_id. We also have order_event_id for recognizability
order_product_category
Column name for the value: value
Foreign key*: order_product_row_id. We also have order_product_id for recognizability
order_product_tag
Column name for the value: value
Foreign key*: order_product_row_id. We also have order_product_id for recognizability
order_tag
Column name for the value: value
Foreign key* order_event_row_id. We also have order_event_id for recognizability
First, you configure this example in a new timeline event rollup using Basic mode.
Once you switch to SQL mode, the following will be automatically generated in the editor:
SELECT
COUNT(`order_event`.`row_id`) AS `number_shirt_orders`
FROM
`order_event`
WHERE
`order_event`.`row_id` IN (
SELECT DISTINCT
`order_product`.`order_event_row_id`
FROM
`order_product`
LEFT JOIN `order_product_category` ON `order_product`.`order_event_row_id` = `order_product_category`.`order_product_row_id`
WHERE
LOWER(`order_product_category`.`value`) IN ('shirt')
)
Here, the number of shirt orders is counted. A few notes to take into account:
We make the WHERE clause on the product category case insensitive by using LOWER().
We join the tables on the row_id, since these are always unique identifiers.
We use a subquery to prevent duplicate counts. (We want the number of orders that have at least one order_product of category shirt; we don’t want an order with two shirts to count twice.)
The automatic translation to SQL always uses backticks so that it allows for spaces in table/column names and doesn’t conflict with AlaSQL functions/keywords like order.
To count the number of events, we count the number of row_id, since this column always has a value for each event.
Using the timeline event type data model
To help you better understand the data scheme and develop queries more quickly, SQL mode features a panel on the left with the timeline event type data model. Using this panel, you can view all available timeline event types and event type properties created under your tenant and add them directly to your query.
To add an item:
Place your cursor at the appropriate spot in the editor.
Navigate to the data model panel and drill down to the item you want to add.
Click the item’s name in the panel and then click its name from the tooltip that pops out.
Tip: Search for any event type or event type property using the search field at the top in the panel.
Adding relative date conditions
Relative data conditions (e.g., “within the last 7 calendar days”) are supported in both Basic mode and SQL mode, helping to optimize performance and reduce runtime.
To apply a relative date condition to your SQL query, use the event data filter above the editor in SQL mode to designate whether the rollup should load all events or only events that fall within a specified number of days.
Note: If you set a relative data condition in Basic mode - e.g., “Event date/time…Within last…10 calendar days (UTC)” - that exact condition will be reflected in the event date filter in SQL mode. The number of days can then be modified through that filter, if necessary.
Incorporating date conditions in AlaSQL code
In SQL mode, BlueConic detects if there is a relative date condition in your AlaSQL code if the query contains a comparison (e.g., =, <>, >, >=, <=, <) of a date column with the current date (e.g., NOW()).
Supported comparisons in SQL mode: =, <>, >, >=, <=, <, BETWEEN
Supported current date functions in SQL mode: NOW(), GETDATE(), CURRENT_DATE(), CURDATE()
Notes:
Date columns/current date functions can be combined with DATEADD and DATEDIFF. (The examples below use DATEADD but can also be used with DATEDIFF.)
BlueConic supports hour, day, week, month, quarter, and year as interval periods. AlaSQL defines:
“Month” as 30 days.
“Quarter” as 91.25 days.
“Year” as 365 days
Example queries:
Query | Description |
| Count the number of orders in the last 90 days |
| Count the number of orders in 2024.
Note: This query doesn’t utilize a sliding window, since it only contains a static date. It doesn’t require recalculations. |
| Subscription end date is soon, within 7 to 90 days |
Mapping query results to profile properties
Once you've created a valid SQL query, you can select where to store the results using the Mappings section underneath the SQL editor.
The system will create a mapping for each SELECT statement. In a mapping, in the profile property selector to the right of the arrow, you have the option to create a new profile property or use an existing one. Like the Basic query builder, the profile properties you are allowed to enter are based on the data type and data sensitivity.
Notes:
Profile properties that are used by other rollups, have incompatible data types, or have incompatible data sensitivity will not be displayed.
The field on the left of the arrow shows the SELECT statement as read-only. It shows either the alias, or the function (if applicable) and table and column name.
If you make changes to the query and the query is still valid, mappings are added or removed as needed. If you make a change that doesn’t result in a valid query, the mappings will not be updated.
If a SELECT statement changes (including the alias/function), the selected profile property is cleared from the mapping.
If the order of SELECT statements changes, the order in the mapping changes accordingly (and the selected profile properties are cleared).
An alias is required when using multiple functions, a static value, or a case statement.
Understanding rules of property creation
When it comes to creating new profile properties through SQL mode, here are some rules to keep in mind:
SELECT Statement | Rules |
| The data type and data sensitivity of the event type property must be followed. |
| For existing profile properties, properties of all data types and data sensitivities can be selected. (Note: Storing text in a Number property, for example, might result in errors.)
New profile properties will be of data type Text and follow the data sensitivity of the event type property. |