Developer
User guidesDeveloper website
Search…
Welcome!
Organisations structure
User points
Data model
Data ingestion
Querying your data
Data visualisation
Quickstart
Dashboards
Sections and cards
Charts
Datasets and data sources
Transformations
Cookbook
Reference
Advanced usages
Audience segmentation
Exporting your data
Data privacy compliance
Campaigns
Automations
Plugins
Platform monitoring
Resources
Tutorial: Data Ingestion
Using our API
Tools & libraries
Data cubes
Powered By GitBook
Cookbook
This page references recipes you can use to speed up your data visualization learning curve.

Working with dates

Use the format-dates transformation to display dates in a user-friendly way.

Single series

Chart's JSON
{
"title": "Application events (last 6 months)",
"type": "Bars",
"dataset": {
"type": "format-dates",
"sources": [
{
// @date_histogram query
"type": "OTQL",
"query_id": "666"
}
],
"date_options": {
"format": "YYYY-MM-DD"
}
}
}

Multiple series

You can do the same with the result of a join
Chart's JSON
{
"title": "Montly events per channel or type-",
"type": "Bars",
"dataset": {
"type": "format-dates",
"sources": [
{
// This works with a join but this can also work from a single source
// without the join
"type": "join",
"sources": [
{
"type": "OTQL",
// Select {date @date_histogram } FROM UserEvent
// WHERE channel_id = XXX
"query_id": "666",
"series_title": "Group 1"
},
{
"type": "OTQL",
// Select {date @date_histogram } FROM UserEvent
// WHERE channel_id = YYY
"query_id": "777",
"series_title": "Group 2"
},
{
"type": "OTQL",
// Select {date @date_histogram } FROM UserEvent
// WHERE channel_id = ZZZ
"query_id": "888",
"series_title": "Group 3"
}
]
}
],
"date_options": {
"format": "YYYY-MM-DD" // The date format we want to return
}
},
// Show the legend for a better event display
"options": {
"legend": {
"enabled": true,
"position": "bottom"
},
"big_bars": false // Allow space between dates
}
}

Combining different data sources

With this technique, you can also combine data from different data sources where the date would be returned in different formats.
Chart's JSON
{
"title": "Events",
"type": "Bars",
"dataset": {
"type": "join",
"sources": [
// Get some counts from activities analytics by month
{
"type": "activities_analytics",
"query_json": {
"dimensions": [
{
"name": "date_YYYYMMDD"
}
],
"metrics": [
{
"expression": "number_of_user_events"
}
]
},
"series_title": "activities_analytics"
},
// Get other counts from OTQL by month with @date_histogram
// and format the result in the same format as activities analytics
{
"type": "format-dates",
"sources": [
{
"type": "OTQL",
"query_id": "666"
}
],
"series_title": "OTQL",
"date_options": {
"format": "YYYYMMDD"
}
}
]
},
"options": {
"hide_x_axis": true // We hide the x axis as there are a lot of values
}
}

Collection volumes

A nice way to display collection volumes is by showing the actual number of elements in the collection with a quick history of the volumes.
​
​
This can be achieved with two Charts in the same Card :
  • A Metric chart using an OTQL query in its dataset, such as SELECT @count{} FROM UserPoint
  • A Bars chart using a Collection volumes query in its dataset.
Card's JSON
{
// Card display options.
// Here we show a small card with a vertical layout
"x": 0,
"y": 0,
"h": 2,
"w": 3,
"layout": "vertical",
// The two charts in the card
"charts": [
{
// The number of user points as a metric
"title": "UserPoint",
"type": "Metric",
"dataset": {
"type": "OTQL",
"query_id": "666" // SELECT @count FROM UserPoint
}
},
{
// Bars showing a history of the number of user points
"title": "",
"type": "Bars",
"dataset": {
// We do use the format-dates transformation to display
// friendly dates instead of timestamps
"type": "format-dates",
"sources": [
{
"type": "collection_volumes",
"query_json": {
"dimensions": [
{
"name": "date_time"
},
{
"name": "collection"
}
],
"dimension_filter_clauses": {
"operator": "AND",
"filters": [
{
"dimension_name": "datamart_id",
"operator": "EXACT",
"expressions": [
YOUR_DATAMART_ID
]
},
{
"dimension_name": "collection",
"operator": "EXACT",
"expressions": [
"UserPoint"
]
}
]
},
"metrics": [
{
"expression": "count"
}
]
}
}
],
"date_options": {
"format": "YYYY-MM-DD HH:mm"
}
},
// We hide axis to have a nice little chart only showing trends
// with the ability for the user to get values by hovering the bars
"options": {
"hide_x_axis": true,
"hide_y_axis": true
}
}
]
}
Another tip when showing collection volumes is to replace lists of metrics with bar charts. This makes it easier to visualize proportions, especially if you have a reference number like the total number of user points.
​
​
​
Card's JSON
{
"x": 0,
"charts": [
{
"title": "User points",
"type": "Metric",
"dataset": {
"type": "OTQL",
"query_id": "666"
}
},
{
"title": "Activability",
"type": "Bars",
"dataset": {
"type": "to-list",
"sources": [
{
"type": "OTQL",
"query_id": "111",
"series_title": "Total user points"
},
{
"type": "OTQL",
"query_id": "222",
"series_title": "With accounts"
},
{
"type": "OTQL",
"query_id": "333",
"series_title": "With emails"
},
{
"type": "OTQL",
"query_id": "444",
"series_title": "With web cookies"
},
{
"type": "OTQL",
"query_id": "555",
"series_title": "With apple Mobile ID"
},
{
"type": "OTQL",
"query_id": "666",
"series_title": "With google Mobile ID"
}
]
},
"options": {
"type": "bar",
"hide_y_axis": true,
"colors": [
"#333333"
]
}
}
],
"y": 0,
"h": 5,
"layout": "vertical",
"w": 4
}

Comparing audiences

You may want to compare a particular audience you are building or that's been built to the whole datamart or to a specific reference audience.
For example to answer the question Do users in this audience have different viewing modes than all users ? you can build a dashboard at the builders and/or the segments scope with :
  • The number of user points visiting through each viewing mode for your audience
  • The number of user points visiting through each viewing mode for all users
  • ​Index calculation to visualize which viewing modes are more/less used in your audience
In the following example, people in the audience are more likely to get touched in LIVE events that in replays.
{
"title": "Viewing modes",
"type": "Bars",
"dataset": {
"type": "index",
"sources": [
{
"type": "OTQL",
"query_id": "666", // SELECT {events {session_mode @map}} FROM UserPoint
"series_title": "Segment"
},
{
"type": "OTQL",
"query_id": "666", // SELECT {events {session_mode @map}} FROM UserPoint
"series_title": "Datamart",
"adapt_to_scope": false
}
],
"options": {
"limit": 10,
"minimum_percentage": 1,
"sort": "Descending"
}
},
"options": {
"type": "bar",
"plot_line_value": 100,
"format": "index"
}
}

Working with channel, compartments and segments

When doing any chart that returns channels, compartments or segments, you will usually want to display names instead of IDs in the UI.
For this, use the get-decorators transformation to replace IDs with names.
Display channel names instead of channel IDs
{
"title": "Data by channels",
"type": "Bars",
"dataset": {
"type": "get-decorators",
"sources": [
{
"type": "to-percentages",
"sources": [
{
"type": "OTQL",
"query_id": "666" // SELECT { channel_id @map} FROM UserEvent WHERE ...
}
]
}
],
"decorators_options": {
"model_type": "CHANNELS"
}
},
"options": {
"format": "percentage"
}
}
​

Using the reduce transformation to display a @cardinality OTQL query as a metric

​@cardinality OTQL queries return a key / value dataset. In lots of cases, this dataset only has one value but can't be displayed as a metric as it is not in the correct format.
We can use the reduce transformation to put the dataset in the correct format.
​
​
{
"title": "Number of different event names retrieved",
"type": "Metric",
"dataset": {
"type": "reduce",
"sources": [
{
"type": "OTQL",
"query_id": "666" // SELECT {nature @cardinality} FROM ActivityEvent
}
],
"reduce_options": {
"type": "first"
}
}
}
Previous
Transformations
Next
Reference
Last modified 2mo ago
Copy link
Outline
Working with dates
Single series
Multiple series
Combining different data sources
Collection volumes
Comparing audiences
Working with channel, compartments and segments
Using the reduce transformation to display a @cardinality OTQL query as a metric