Help Center

By creating timeline event queries, you can choose to roll up the data stored in timeline events and save the results to a profile property. Currently, you may be using the Timeline Query Connection to achieve these data rollups. With the new Timeline event rollups page, you use a simple three-step UI to create a rollup. 

Before you begin: Get user permissions for this feature

In order to begin using this feature, make sure your user role has the appropriate BlueConic role permissions to use this feature. Your BlueConic Application Manager must assign the 'Timeline event rollups' permission to the appropriate user roles on the Roles page. By default, only Application Managers and BlueConic Support get this permission.

Create timeline event rollups

Start by going to the Roles page to make sure your user role has permissions for timeline event rollups. 

1. Navigate to More > Timeline events from the main BlueConic navigation bar, and then select the Timeline event rollups tab.
   
2. Click the Add timeline event rollup button.
3. Select the Timeline event type whose data you'd like to store in a rollup.
   
4. Next to Conditions, you can filter the events you're interested in. For example, for the Timeline event type 'Order' you might only be interested in orders after April 10, with a minimum revenue of 200. The section below shows which properties and condition operators you can select. Other available date conditions are shown in the table below. 
   
   If you need details on how the timeline event properties are structured, in a different window, create a segment to find an eligible profile and inspect the profile's timeline events to see how the data is structured.
   
   • Note: If you select multiple conditions, all of the conditions must be met in order for a timeline event's data to be rolled up.
5. Next, you map the timeline event properties that meet the conditions you set above, in order to save them in profile properties. Under Mappings, for each timeline event property in the rollup, you select a timeline event property, a mapping operator, and a profile property. 
   • Note: The mapping operators available for each mapping depend on the data type you're selecting. After selecting a timeline event property in the first mapping box, the operator selector dropdown will only display valid and available options. Review the list of Mapping operators in the table below.
     
6. When you have completed the rollup, switch the toggle button to On and click Save to run the rollup. 

•  

When does a timeline event rollup get run?

Timeline rollup functionality doesn't work like connections. You don't need to schedule or press the Run now button. After you switch the rollup on, the rollup is run only when needed, making it more real time and accurate. A rollup for a profile is run when:

1. The rollup is created or updated.
2. A timeline event is added, updated, or deleted for a given profile.
3. The timeline event retention period has passed.
4. The recalculation date has passed.

Rollups using relative date conditions

If a timeline rollup uses a relative date condition - such as within last X days - it will automatically recalculate when the result of the query would change. For example, if you create a rollup that counts the number of orders in the last 7 days, BlueConic evaluates relevant profiles and tracks when the recalculation should happen, counting 7 days in the future (UTC, rounding up until midnight). When a timeline event is added, BlueConic will calculate the rollup and then plan to recalculate in 7 days. See an example of recalculating relative date conditions for timeline event rollups.

Refreshing a rollup

You can click Refresh in the Statistics widget in the right-hand panel of the page. You can refresh it once every 5 minutes. See Timing and refreshing the rollup for details.

Elements of the Basic Query Builder

Timeline event types

BlueConic stores time-based event data in timeline events. There are multiple timeline event types available out of the box, and you can extend the existing timeline event types and create custom timeline event types to suit your use cases. Learn more about timeline event types, their data structure, and priority levels.

Conditions

After you have selected timeline event data to be rolled up via the query builder, you can filter for specific event property values. Click the Add condition button to build up conditions that timeline events must meet before saving data into profile properties.

For example, you can choose to limit the query to only the 'Email delivered' events for a certain campaign. 

Note: If there are multiple conditions, all of the conditions must be met in order for a timeline event's data to be rolled up.

The condition operator that's available here depends on the data type of the event property.

Condition operators

The following condition operators are available in the basic timeline query builder.

Note: A timeline event property can contain multiple values, but timeline rollups use only the first value.

Condition operator	Data type	Description
Minimum	Number, Decimal	Value is equal to or larger than the given value (>=). Examples: (event value) (operator) (condition value) → Event matches condition 10 >= 5 → True 5 >= 20 → False 10 >= 10 → True EMPTY >= 5 → False Properties can contain multiple values; timeline rollups use only the first value. 10, 9 >= 10 → True 9, 10 >= 10 → False
Maximum	Number, Decimal	Value is equal to or smaller than the given value (<=). Examples: (event value) (operator) (condition value) → Event matches condition 10 <= 5 → False 5 <= 20 → True 10 <= 10 → True EMPTY <= 10 → False Properties can contain multiple values; timeline rollups use only the first value. 10, 11 <= 10 → True 11, 10 <= 10 → False
Is empty	Number, Decimal, Text, Date	The property doesn’t have a value or is an empty String in case of a Text property (IS NULL OR = ""). Examples: (event value) (operator) (condition value) → Event matches condition Empty → True ““ → True “Value” → False
Has a value	Number, Decimal, Text, Date	The property has a value and is not an empty string in case of a Text property (IS NOT NULL AND != ""). Examples: (event value) (operator) (condition value) → Event matches condition Empty → False ““ → False “Value” → True
Contains any (exact match)	Text	Contains any of the given values (exact matching. Case-insensitive) (IN (value1, value2, ...)). Example: Country IN ('Mexico', 'Netherlands') Will be shown as Contains any “Mexico” or “Netherlands” in the UI. Examples: Netherlands → True Mexico → True Empty → False France → False Netherlandsx → False Properties can contain multiple values; timeline rollups use only the first value. Netherlands, Mexico → True France, Netherlands → False Netherlands, France → True
Does not contain any (exact match)	Text	Does not contain any of the given values (exact matching. Case-insensitive) (NOT IN (value1, value2, ...)). Example: Country NOT IN ('Mexico', 'Netherlands') Will be shown as Does not contain any “Mexico” OR “Netherlands” in the UI. Netherlands → False Mexico → False Empty → True France → True Francex → True Properties can contain multiple values; timeline rollups use only the first value. Netherlands, Mexico → False France, Netherlands → True Netherlands, France → False
Between	Date	The date is between two fixed dates (>= and <=). It is inclusive. The second date is interpreted as midnight when the day ends. Examples: For example, >= 2024-03-28 AND <= 2024-03-28 will match for all events that occurred on March 28. Properties can contain multiple values; timeline rollups use only the first value. March 28 23:59:59 999, March 29 23:59:59 999 → True March 29 23:59:59 999, March 28 23:59:59 999 → False
Before	Date	The date is before or at a fixed date in time (<=). The date is interpreted as midnight when the day ends. Examples: (event value) (operator) (condition value) → Event matches condition 2024-03-28 23:59:59 999 <= 2024-03-27 → False 2024-03-28 23:59:59 999 <= 2024-03-28 → True 2024-03-28 23:59:59 999 <= 2024-03-29 → True Properties can contain multiple values; timeline rollups use only the first value. 2024-03-28 23:59:59 999, 2024-03-27 23:59:59 999<= 2024-03-27 → False 2024-03-27 23:59:59 999, 2024-03-28 23:59:59 999 <= 2024-03-27 → True
After	Date	The date is after a fixed date in time (>=). The date is interpreted as midnight when the day starts. Examples: (event value) (operator) (condition value) → Event matches condition 2024-03-28 00:00:00 000 >= 2024-03-27 → True 2024-03-28 00:00:00 000 >= 2024-03-28 → True 2024-03-28 00:00:00 000>= 2024-03-29 → False Properties can contain multiple values; timeline rollups use only the first value.
Within last	Date	The date is within the last x days, including today. Start: Go back to midnight (UTC) and then subtract the given number of days. End: Now For example, if it is March 28 2024 4pm when the condition is evaluated, and the condition is “Last 3 days”, it includes values between March 25 00:00 and March 28 4PM (UTC).  If you select the operator “Within last”, a number input field and the text "calendar days (UTC)" will appear. Here you must enter a number of 1 or more calendar days (UTC). See an example showing how BlueConic handles relative dates for processing a rollup.  About UTC dates: For details on UTC dates, see the UTC days section of the Hourly segment filters article.

 

Mappings

After you select a timeline event type in the query builder, you can identify the timeline data you'd like to store in a profile property. Start by selecting a property of the timeline event type from which you'd like to retrieve values. Depending on the data type, you can select a mapping operator (e.g. count, sum, etc.)

Next you choose where to store the value. You can choose to create a new profile property or select an existing one. 

Why certain profile properties are not shown and cannot be mapped

Note that certain profile properties are not shown because they cannot be mapped here, including profile properties that:

• Are already being used by other rollups.
• Have an incompatible data type (because integers can't be mapped to email fields, for example).
• Have an incompatible level of data sensitivity (because PII data cannot be mapped to a non-PII profile property).

Why certain operators are not allowed with certain properties

You can select one or more mappings based on sub-properties of the same nested property (in the timeline event type property selector). A nested property contains individual sub-properties, while a normal property has no sub-properties.

To prevent unexpected results, BlueConic does not allow:

• The operators Count/Sum/Average on a normal property in combination with ANY sub-property mapping.
• The operators Count/Sum/Average on a sub-property in combination with ANY sub-property of another nested property.

For example, you have a timeline event type with these properties:

• Normal property (A)
• Nested property (B)
  • Sub-property (B.1)
  • Sub-property (B.2)
• Nested property (C)
  • Sub-property (C.1)

Allowed: A mapping of SUM(B.1) with a mapping of SUM(B.2), since they are sub-properties of the same nested property.

Not Allowed: A mapping of SUM(A) with a mapping of SUM(B.1), since you have a normal property combined with a sub-property.

Allowed: A mapping of MIN(A) with a mapping of MIN(B.1), since you're using the Minimum operator and not Count, Sum, or Average.

Not Allowed: A mapping of SUM(B.1) with a mapping of SUM(C.1), since they are sub-properties of different nested properties.

Mapping operators

Some values have mapping operators available for constructing queries. When you select "All values" you cannot select any of the other operators in the Mapping section of the same rollup and vice versa.

Note: A timeline event property can contain multiple values, but timeline rollups use only the first value.

Mapping operator	Data type	Description
Count	Event type, Number, Decimal, Text, Date	The number of values for the given property of the matching events (non-distinct) (COUNT).  For example: When two event properties have the value "A", it is counted as 2 When a multi-valued text event property has two values (e.g. “A”, “B”), it is counted as 2. When there are no events of the selected event type (any more), the query won’t run and the result is EMPTY. When there are events of the selected event type, but no events match the conditions, the result is 0.
Sum	Number, Decimal	The sum of the values of the selected property of the matching events (SUM). Examples: Empty → 0 1 → 1 1, 2 → 3 3, 1, 2 → 6 When there are no events of the selected event type (any more), the query won’t run and the result is EMPTY.  When there are events, but they do not have a value for this property, the result is 0. This is different from some other languages, such as MySQL, that return NULL instead of 0.
Minimum	Number, Decimal	The minimum value of any of the matching events (MIN). Examples: Empty → Empty 1 → 1 1, 2 → 1 3, 1, 2 → 1
Maximum	Number, Decimal	The maximum value of any of the matching events (MAX). Examples: Empty → Empty 1 → 1 1, 2 → 2 3, 1, 2 → 3
Last	Date	The most recent date (MAX). Examples: Empty → Empty 2023-01-01 → 2023-01-01 2023-01-01, 2023-01-02 → 2023-01-02 2023-01-01, 2023-01-01 → 2023-01-01
First	Date	The oldest date (MIN). Examples: Empty → Empty 2023-01-01 → 2023-01-01 2023-01-01, 2023-01-02 → 2023-01-01 2023-01-01, 2023-01-01 → 2023-01-01
Average	Number, Decimal	The average value of all the matching events (AVG). NULL (undefined) is not taken into account. When storing the average value into a Number property, it will be rounded. Examples: Empty → Empty 1 → 1 1, 2 → 1.5 (will be rounded to 2 when stored as Number) 3, 1, 2 → 2 3,1,NULL → 2
All values	Number, Decimal, Text, Date	The distinct values of all events that match the conditions column name(s). Examples: Empty → Empty “Value 1” (2023-01-01) → “Value 1” “Value 1” (2023-01-01), “Value 2” (2023-01-02) → “Value 1”, “Value 2” “Value 1” (2023-01-01), “Value 2” (2023-01-01) → “Value 1”, “Value 2” “Value 1” (2023-01-01), “Value 2” (2023-01-01), “Value 2” (2023-01-01) → “Value 1”, “Value 2”

Statistics 

In the right-hand panel, you can find statistics about your timeline event rollup, including:

• Profiles to update: This shows the number of unique profiles that are marked for update or processing in a rollup.
• Profiles updated: This shows the number of unique profiles that have a value for one of the rolled-up properties.
• Last values set: This shows the last date and time this rollup set a value for one of the profile properties being updated.
  

Timing and refreshing the rollup

Timeline event rollup statistics are updated hourly automatically. You can refresh it manually for a single rollup in the Rollup detail screen in the right-hand panel. You can refresh it once every 5 minutes. If you have the screen open, and the 5 minutes have passed, you can refresh the page and the refresh button becomes active.

Click Refresh to update the rollup statistics. Profiles are marked for rollup when the rollup is created/updated, or when a timeline event is added, updated, or removed from a profile, including when the retention period has passed. This is done asynchronously as part of the existing profile index process after a profile is inactive for 5 minutes.

Timeline events in BlueConic are retained for a set period of time, depending on their priority setting (HIGH or LOW). When the retention period for timeline events ends, a related timeline rollup for the event that was removed will be recalculated.

Related items

The Related items box shows the BlueConic objects that are related to this rollup, including timeline events that provide data to the rollup as well as profile properties that will store the rolled-up values. Clicking on any of these related items opens that object in the current window. This is useful for inspecting the object's structure and contents.

Validating your results

Once you've created a rollup, you can validate your results by creating a segment of profiles based on the resulting rollup property. In the Segments page, check to see if it's being populated with profiles as expected. 

Another way to validate a rollup is to open the Profiles tab in BlueConic, and view profiles by Segment, choosing a Segment populated with profiles from your rollup. Inspect individual profiles and their timelines to see if the values of the rollup match the timeline events that make up the rollup.