Cookbook
This page references recipes you can use to speed up your data visualization learning curve.
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"
}
}
}
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
}
}
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
}
}
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.
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
}
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
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"
}
}
When doing any chart that returns channels, compartments or segments, you will usually want to display names instead of IDs in the UI.
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"
}
}
@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.
{
"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 modified 9mo ago