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
{
"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
{
"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.
{
"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 :
{
// 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.
{
"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
{
"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.
{
"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"
}
}
}Last updated
Was this helpful?