BlueConic now enables you to write custom SQL queries within the timeline event rollup feature. This new functionality allows you to query timeline events and store the results directly in profile properties for activation or export.
Unlock advanced use cases and gain deeper insights from your timeline data with custom queries. Previously, such capabilities were only possible through the Timeline Query Connection. With the new SQL editor, you have greater control and flexibility to tailor your data analysis to your specific needs and calculate this as data flows into the CDP.
Key features
Direct SQL Querying powered by AlaSQL: Write queries using AlaSQL (version 4.5.1) directly in BlueConic. Benefit from features like autocompletion and data validation to streamline your workflow.
Note: AlaSQL uses slightly different syntax from traditional SQL. For example, column names containing spaces are enclosed in square brackets
[]or backquotes`, rather than double quotes"". For more details, review this AlaSQL page.
Switch between Basic and SQL modes: Start with the user-friendly Basic mode for common use cases, and toggle to SQL mode when you're ready to dive into more complex queries.
Enhanced autocomplete: As you type, the editor suggests functions, tables, and columns, making it easier to construct accurate queries.
Easy mapping of results: Seamlessly map your query results to new or existing profile properties, ensuring your data is ready for activation or export.
Getting started
Navigate to timeline event rollups
Go to More > Timeline events > Timeline event rollups in BlueConic.Toggle between Basic mode and SQL mode
Use the new toggle switch to choose between:Basic Mode: A point-and-click interface ideal for configuring common, straightforward use cases. Review the article Create timeline event rollups for more on how this works.
SQL Mode: An advanced SQL editor designed for more complex queries and use cases.
Notes:
In SQL mode, the SQL editor takes the place of the event type selector and conditions found in Basic mode.
If you save a rollup and then open it at a later time, it is opened in the mode in which you saved it.
Best Practice: Start in Basic mode
We recommend starting in Basic mode to set up your initial configuration. Once you've saved your rollup, switch to SQL mode to customize and refine your query further. BlueConic will automatically translate your configuration into an AlaSQL query, giving you a solid foundation to build upon.
Important notes:
Save before switching: Always save your rollup before switching modes to prevent losing any configurations.
Mode switching behavior:
From Basic to SQL: Unsaved profile properties in Basic mode will be lost if you switch to SQL mode without saving.
From SQL to Basic: Your custom SQL query won't be converted back to the Basic configuration due to potential complexity. However, your previous Basic mode settings are remembered.
Saving in SQL mode: Saving a rollup in SQL mode removes any prior Basic mode configurations.
Relative date conditions: During the beta phase, SQL mode doesn't support relative date conditions (e.g., "Date within the last 2 days"). These are available in Basic mode.
Using the SQL editor to create timeline queries
When you click SQL from the toggle to open SQL mode, the SQL editor opens with a large input field for you to write SQL queries to run against timeline events for each profile directly in AlaSQL. 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.
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 recognisability, 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, we don’t need a primary key. | <<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 |
SQL example
Here is an example of how the SQL editor works based on the Order event plugin:
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:
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.
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. |