{
"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.
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.
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