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 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
            }
        }
    ]                
}

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.

Card's JSON

{
    "x": 0,
    "charts": [
        {
            "title": "UserPoint",
            "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 UserPoint"
                    },
                    {
                        "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 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.

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