Making Custom Reports on DataStudio

10 minute read                                                                                                             Intermediate

Prerequisites

• Capturing data for reports & analytics
• Data Studio Integration

Video Tutorial

Overview

This document is meant to guide organisations to build custom dashboards either entirely from scratch or build on top of the existing reports provided by Glific.

In order to keep things simple, all steps that will be described below will be in reference to the existing reports but they can be extended to build reports or dashboards from scratch as well.

The existing reports are built keeping in mind some of the general monitoring and evaluation metrics that organisations would like to keep a track of either on a weekly or daily basis.

These reports include information on daily activity status, engagement metrics, frequency of interactions and user information all within different pages of the reports.

The reports can be used as is or customised to the organisation’s liking by adding more data sources, blending different data views/tables together in existing or new pages of the report.

What's being reported in the current reports?

In the current version of the report (v3.0) you will find the following pages -

Daily Status : Gives an overall view of the total number of active contacts and messages sent by the users each day. It also gives a breakdown of the number of active contacts and messages by hour in order to evaluate time-based factors that might be impacting engagement

Flow Reports : This page gives a breakdown of the user engagement per flow. The charts included within this report give an overview of the number of times a user has requested a particular activity or flow or provided feedback on an activity.

Sessions : The sessions page gives total and average number of sessions (flows) initiated by the user. It also gives a breakdown of the frequency of interactions per user, average time spent on flow per user and the average number of messages sent by the user for each flow.

Contact Details : All contact details collected by the organisation can be visualised in this view of the report.

Field Views (User Details) : Additional page to visualise contact details that makes use of a stored procedure in Bigquery meant to unnest all the fields level information that the organisation is collecting in their flows and mapping them back to the contact details Capturing data for reports & analytics

All the reports above can be filtered as per date, time, flow name, flow label (activity/feedback etc.) and contact details.

Getting Started :

Adding template to new report page

Once you have made a copy of the existing reports and made connections to your data sources by following the steps in this document Integrate DataStudio to create Visualisations & Reports, you are now all set to customise existing pages or add new pages.

In order to add a new page,

1. Navigate to Pages on the header
2. Click on New Page (you can even duplicate an earlier page by clicking on Duplicate Page and customise it as per your requirements)

1. Rename the report by clicking on the Page (Number) from the header panel and selecting the 3 dots beside the current report name

An empty page will be created without the source template. In order to add the source template -

1. Multi-select all the rectangles, texts and filters from the template that you would like to be copied from a previous page
2. Right click on the selection and click the Make report-level

You are now ready to add data sources and charts to your new report page.

Connecting to new data sources from report page

Every report page has a pre-selected data source which can be changed. If you want to select a source from already connected data sources or if you want to include a new custom query or data source from your BigQuery Console -

1. Click on Add Data option available on the header panel

1. Select the Bigquery connector

1. Select the project that contains your datasets
2. Select the Glific dataset
3. Choose from the list of available data sources

Or you could also -

1. Add a dummy chart and then click on Edit Data Source (pen icon) in the Data Source section of the right data pane
2. Select the data source from the list of connected data sources after the BigQuery connector automatically opens to your project

Working with fields, metrics, dimensions and filters

When working with charts, the right data pane gives you options to change dimension, metrics, define custom fields and add filters to organise data in a certain way. Here is a complete breakdown of all the available options -

Fields : Fields are the columns that appear in your connected dataset

Date Range Dimension : As the name suggests, this option lets you specify the datetime field within your dataset and can be used to plot time-series charts and graphs. Specifying the date dimension also lets the user further filter as per month, week, day, hour and so on

Dimension : Dimensions describe or categorize your data. Adding dimensions to a chart groups the data by those dimensions. Time, flow name, city, student’s age are all examples of dimensions you could use to group the information in your chart.  Note that any type of data can be a dimension, including a column of unaggregated numbers

Metrics : Metrics measure your dimensions. A metric is the result of applying an aggregation to a set of values. That aggregation could come from the underlying data set, or be the result of implicitly or explicitly applying an aggregation function, such as COUNT(), SUM(), or AVG()

Note about Dimensions and metrics in your data source : When you connect to your data in Data Studio, you'll see the list of fields along with their default properties, as shown below. Dimensions (unaggregated columns that you can group by) appear as green fields. Metrics (aggregated data) appear as blue fields

Parameters : Parameters store user-defined data. Unlike dimensions and metrics, which get their information from the underlying data set, parameters get their information from users. Parameters can then be used to customise or personalised your reports and data sources

Calculated Fields : Calculated fields let you create new fields derived from your data. Calculated fields appear in the field list with fx at the end

Filters : There are two types of filters that can be added to the report, one at a page level, also called as a control and the other at a chart level which is available as an option in the right data panel after a chart is added. Both filters work essentially the same way and can be used to visualise only a portion of the data

Creating calculated fields

In order to create a calculated field -

1. Click on either dimension or metrics in the right data pane and select Create Field at the very bottom of the drop-down list

1. In the formula text box, specify the condition using which you want the new field to be created. The formula takes in all SQL functions. Some examples in which this could be used include HOUR(inserted_at) to extract only the HOUR information from the datetime column or doing a CASE WHEN to specify “Male” or “Female” instead of a “1” or “2” from the original data source.

1. Provide the calculated field with a name and data type and then select the Apply button.

Creating Filters

Creating page-level filters :

1. Select on Add a control option from the top header panel

1. Select the type of filter you want to add from the page. The available filters include options like sliders to filter numeric data types, checkboxes to select only boolean values or drop down list to filter as per dimensions available in a specific column of the dataset.

1. Adjust the shape and position of the filter within your report
2. Select the column using which the data needs to be filtered from the Control Field option available in the right data pane.

1. Make adjustments to the metric, order, range etc. as necessary based on the type of filter selected

Creating chart-level filters

1. After adding a chart, scroll to the Filter section of the right data pane.

1. Click on Add a filter
2. Filters are usually of two types - Include or Exclude. Select whether you want to include or exclude certain dimensions or types of values (NULLs) from your data

1. Select the field which has the dimension or value that needs to be filtered
2. Select a filtering condition. This can include filtering based on partial or full matching with the column value or filtering null values or values that start with a certain value.

1. Type in the keywords for which the filtering condition needs to be applied

Working with grouped data

In certain cases, you might be interested in working with averages such as per user per flow average times or with grouped data such as combining contacts and messages tables together and grouping them to visualise the number of messages sent by users.

These manipulations are not directly available in the backend tables and might get a little tricky to work with if you are building custom fields since Data Studio does not support group by functions in the formula box for calculated fields.

In such cases, it is advisable to either select the table visualisations where you can add multiple grouped dimensions and visualise the number of records for such groupings. For example, if you wish to get the number of flows requested by a user per day, you could -

1. Select the Table with Bars chart from the Add A Chart option in the top header panel

1. Add dimensions for Date and Phone in the right data pane to create the groupings

1. Select the flow_label metric and choose the SUM aggregation to get the total number of activities requested per user per day

Creating Views

The other way to deal with averages or with grouped data is to create views from backend tables and add them as data sources within your reports. You can create views directly from Data Studio in the following way

1. Click on Add A Data option in the top header panel
2. Select the Bigquery connector
3. Click on the Custom Query option and then select your project and dataset name
4. Type or paste your view query in the Enter Custom Query text box
5. Select Add after you are done

The views currently in use include Flat Fields available in your Bigquery Console along with UserSessions and ContactMessages available within Datastudio (Click on Resource > Manage added data sources in your Datastudio report to view them)

Managing views/queries - changing names and data types

The default name given to a query or a view when it's created is Bigquery. You might want to give this particular view/query a name in order to manage it across multiple pages of the report.

You can also make data type changes after you have created the view/query if you haven't already done so while creating it. In order to make name or data type changes -

1. Select the Resource option from the header panel of Datastudio
2. Select the Manage Added Data Sourcesoption

1. Navigate to the data source that has the name “Bigquery” and click on the Edit option beside it

1. Double click on the current view/query name at the top of the edit box and change the name

1. In order to change the data type, navigate to the field to be changed and select the data type from the drop-down list from either Number, Text, Geolocation, Boolean etc

1. Click on the Done button in the top right corner of the editing box after you have made all the changes

Styling the graphs

After you are done adding data sources, creating new views, adding graphs etc. it is time to style them as per your report.

Each graph has its own styling criteria. A bar graph may have an option to change the orientation, i.e, vertical or horizontal orientation whereas a pie chart gives you an option to select the number of total slices (if the data was numeric).

The styling depends on your use-case and there is no hard and fast rule as to how you would like to present the data.

You may however want to adjust the colors of the graphs/charts based on the template colors you are using. This can be done through the Color and Background and Border sections within the Styling tab.

You can also add labels to the chart and adjust the font size and color of the labels. The other thing you might want to try is making text/orientation changes to the legend of the graph. The changes are automatically saved.

Once you are done styling the pages and the graphs, you have your organisation level reports ready to be viewed or shared.

Reference Article

• Configure Dimensions and Metrics

• About Calculated Fields

• Working with Filters

• Configure Chart Components