Developer
User guidesDeveloper websiteHelp centerLog in
  • Welcome!
  • Organisations structure
    • Datamart
    • Users and roles
  • User points
    • User identifiers
      • Networks IDs
        • Device-based Network IDs
          • Custom Device ID integration
          • ID5
          • First ID
        • User-based Network IDs
          • Custom User ID integration
          • UTIQ martechpass
      • Accounts
      • Emails
      • Device identifiers
    • User activities and events
    • Compartments
    • User profiles
    • User segments
    • Hyper point & Quarantine
  • Data model
    • Defining your schema
    • Computed fields
      • Concepts
      • Setup
      • Development
      • Examples
  • Data ingestion
    • Real time user tracking
      • Website tracking
      • Mobile apps tracking
      • Ads exposure tracking
      • AMP tracking
      • Conversions tracking
      • Email views and clicks
      • Tracking API
      • Event rules
      • Activity analyzers
    • Bulk processing
      • Imports
        • User activities import
        • User profiles import
        • User choices import
        • Segments import
      • Deletions
        • User identifiers deletion
        • Device points deletion
        • User points deletion
      • User identifiers association
      • Integration batch
    • Activities analytics
    • Data warehouse
      • Preliminary setup
        • BigQuery
      • Create data warehouse
  • Querying your data
    • OTQL queries
    • OTQL examples
    • GraphQL queries
    • UserPoint API
    • User activities
    • Activities analytics queries
      • API Quickstart
      • Dimensions and metrics
      • Use cases
    • Funnel API
  • Alerting
    • Alert configurations
  • Data visualisation
    • Quickstart
    • Dashboards
    • Sections and cards
    • Charts
    • Datasets and data sources
      • Using a data file data source
    • Transformations
    • Filters
    • Cookbook
    • Reference
  • Advanced usages
    • Audience segmentation
      • Audience features
      • Segment builders
      • Audience segment metrics
      • Audience segment feed
        • Building new feeds
        • Monitoring a feed
        • Curated Audiences (SDA)
      • Edge segments
      • Cohort-based Lookalike
    • Contextual targeting
      • Setup
      • Activation
        • Google Ad Manager
        • Xandr (through prebid.js)
      • API documentation
    • Exporting your data
      • Query Exports
      • Datamart replication
    • Data privacy compliance
      • User choices
      • Cleaning rules
      • Exercise of user rights
      • Cookies
    • Campaigns
    • Automations
      • Email routers
      • Email renderers
      • Opt-in provider
      • Custom action plugins
      • Usage limits for automations
    • Plugins
      • Concepts
      • Creation & Deployment
      • Coding your plugin
      • Manage existing plugins
      • Layouts
      • Presets
      • Monitoring
      • Throttling
      • Batching (for external feeds)
    • Platform monitoring
      • Resources usage
        • Dimensions and metrics
      • Collection volumes
        • Dimensions and metrics
      • Events ingestion monitoring
        • Dimensions and metrics
    • Data Clean Room
      • Bunker
      • Clean room
  • Resources
    • Tutorial: Data Ingestion
      • Your first events
        • Add the mediarithmics tag
          • Getting the tag
          • Adding the tag
        • Send events using the tag
          • Adding event properties
          • Finding the UserEvent type in your schema
          • Matching your schema
          • Standard events
      • Your first bulk imports
        • API basics
          • Authentication
          • Your first API call
        • Send documents using the API
          • Requirements
          • Sending documents
    • Using our API
      • Authentication
    • Tools & libraries
      • mics CLI
      • JS Tag
      • Plugin SDK
    • Data cubes
      • Creating a report
      • Reference
Powered by GitBook
On this page
  • Working with dates
  • Single series
  • Multiple series
  • Combining different data sources
  • Collection volumes
  • Comparing audiences
  • Working with channel, compartments and segments
  • Using the reduce transformation to display a @cardinality OTQL query as a metric

Was this helpful?

Export as PDF
  1. Data visualisation

Cookbook

This page references recipes you can use to speed up your data visualization learning curve.

PreviousFiltersNextReference

Last updated 2 years ago

Was this helpful?

Working with dates

Use the 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.

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
}

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

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

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

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

This can be achieved with two in the same :

A chart using an OTQL query in its , such as SELECT @count{} FROM UserPoint

A chart using a Collection volumes query in its .

calculation to visualize which viewing modes are more/less used in your audience

For this, use the transformation to replace IDs with names.

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 to put the dataset in the correct format.

Charts
Card
dataset
dataset
Metric
Bars
Index
get-decorators
the reduce transformation
format-dates transformation
In the following example, people in the audience are more likely to get touched in LIVE events that in replays.
Display channel names instead of channel IDs
@cardinality OTQL queries