With this technique, you can also combine data from different data sources where the date would be returned in different formats.
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.
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 UserPoint.
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 UserPoint visiting through each viewing mode for your audience
The number of UserPoint 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.
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
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.
{
"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
}
}
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
}
}
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 UserPoint 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 UserPoint
"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
}
}
]
}
