Skip to main content

DataStudio Integration for Visualizations & Reports

3 minute read                                                                                                                 Intermediate​

Steps to integrate DataStudio for newly onboarded organization​

1 . Copy the Glific Report template.

2 . Add data sources.

3 . Connect all tables one by one to get the data from BigQuery tables.

4 . Get data in Flat Fields (Refer : Populate data in Field Views (User info) report.

5 . Your reports will be ready.

Below are the detailed steps with screenshots to integrate Data studio.

Note: The screenshots may be different, as google may update the screens with their updates

1 . Click on the link of the reports template (shared publicly with view access). Use the latest version 4.0 for your reports.

2 . Click on the Make a copy icon at the top right corner, from the 3 dots dropdown next to the Share button.

image

3 . Click on Copy Report.

Note: Use the original data source, for now, don’t change it for any data source, it will break a few charts which have calculated or renamed fields.

image

  1. In the new tab of the created copy of the report, select Manage added data sourcesfrom the Resources Menu

image

  1. Click on edit one by one for each tables. Start with contact and messages tables

image

  1. Select BigQuery data source

a. Select MY PROJECTS

b. Select the Project (If the project is not listed you might need to enter the project ID manually)

c. Select the Dataset

d. Select the Table. Click on the Reconnect button.

image

f . You might get a warning "Configuration has changed. Do you want to apply the changes?”. That’s ok, click on APPLY

image

g . Click on DONE in fields listing page

image

Repeat steps 5 and 6 for the following tables. Contacts, Messages

To get the project ID click on the phone number on BigQuery.

image

6 . For Flat fields data source update the custom query with project ID (from

the Glific settings interface for big query) and phone number

image

and click on Reconnectto update.

image

  1. For Flow context data source update the custom query with project ID (from

the Glific settings interface for big query) and phone number. There will be four place where you need to update the project ID

image

  1. Follow the same steps as above for Monthly active users the data source. Replace the project ID and phone number as per your setup.

9 . For Flow results. Go to your big Query account. Open a new Tab and run below query. Replace tides-saas-309509.917302307943 with your Project ID

image

CREATE OR REPLACE FUNCTION `tides-saas-309509.917302307943.jsonObjectKeys`(input STRING) RETURNS ARRAY<STRING> LANGUAGE js AS R"""
const inputJson=JSON.parse(input)
 return Object.keys(inputJson).map((key) => {
 return (JSON.stringify([key, inputJson[key].input]));
});
""";

10 . Click on Run button. After successful execution a new Routine will be created with name JsonObjectKeys

image

11 . Go back to DataStudio and follow the same steps as we did for Flat fields (7 & 8) above for Flow Results data source. Replace the project ID and phone number as per your setup.

12 . Click CLOSE on the top right, and your reports will be ready

image

13 . Your reports are ready for use.

image

  1. Follow the steps in the below document to create reports.

https://support.google.com/datastudio/answer/6292570?hl=en

Connection to V5 Data Studio Report​

Before you start, ensure that you have set up and connected your BigQuery Account

Overview

A . Make a copy of Glific report template v5.0

B . Connect 10 data sources to your BigQuery Account

  1. Connect contacts, messages, and message conversations through 'My Projects'
  2. Connect flat fields, monthly active users, Contact collections, success messages and flow context by replacing your Dataset ID into the preset 'Custom Query'.
  3. Connect flow Results and Contact Collection by running a query for each in BigQuery before connecting the data sources.

3 . Your reports will be ready.

Implementation Time: 60 minutes

Below are the detailed steps with screenshots to integrate Data studio.

Note: The screenshots may be different, as google may update the screens with their updates. Message us on Discord to let us know.

Detailed Steps:

Make a copy of Glific report template v5.0​

1 . Click on the link of the reports template (shared publicly with view access). Use the latest version 5.0 for your reports.

2 . Hover your cursor near the top of the report, then click on the 3 dots in the top right corner, then click on the Make a copy icon. Refer to the image below:

Image1

Note: If you have never used Data Studio before then you will be prompted to provide some basic information and agree to their terms and conditins. Do so and then repeat step 2.

3 . Click on Copy Report. Refer to the image below:

Image2

Note: Do not click on any of the drop downs for New Data Source. Directly click on 'Copy Report'

Connect data sources to your BigQuery Account​

4 . A new tab will open with the copy of the report and some visuals not functioning, select Manage added data sourcesfrom the Resources Menu. Refer to the image below:

Image3

Connect contacts, messages, and message conversations through 'My Projects'​

5 . Click on edit one by one for each data source. Start with contacts

image

6 . Connect to the BigQuery data source:

a. Select MY PROJECTS from the menu on the far left.

b. Select the Project (If the project is not listed you might need to enter the Dataset ID manually. Refer to the note after step 8)

c. Select the Dataset, this is your WA bot number

d. Select the Table with the matching name. Click on the blue Reconnect button at the top right corner.

image

e . You might get a warning "Configuration has changed. Do you want to apply the changes?”. That’s okay, click on APPLY

image

f . The fields listing page will open up. Verify that the fields are set to the correct type. Most will be correct by default.

What to check for:

  1. All fields with 'id' in their name should be of the type 'text'
  2. 'phone' should be of the type 'text'
  3. 'language' should be of the type 'text'
  4. All fields with 'at'/'time' in their name should be of the type 'Date and Time'
  5. Latitude/Longitude should be of the type latitude, longitude.

g. Click on DONE in fields listing page

image

7 . Repeat steps 5 and 6 for the following data sources; Messages, Message Conversations

Note: To get the Dataset ID click on the phone number on BigQuery.

image

Connect flat fields, monthly active users, Contact collections, success messages and flow context by replacing your Dataset ID into the preset 'Custom Query'.​

8 . For the Flat Fields data source, select Edit and then update the custom query with Dataset ID (from the Glific settings interface for big query as shown in the note above). Replace tides-saas-309509.917302307943 with your Dataset ID (Refer to the image below)

Note: Ensure that you only replace the Dataset ID, that is, ".contacts" should not be deleted. This applies for all steps where you replace the Dataset ID, ensure you do not select and paste over the remaining text.

image

9 . Click on Reconnect to update.

image

10 . Verify the field types. (Refer Step 6.f.) And then click Done

11 . Follow steps 8 to 10 as above for; Monthly active users, Contact Collections and Success Messages, Replace the Dataset ID as per your setup.

12 . For Flow context data source update the custom query with Dataset ID (from the Glific settings interface for big query). There will be four places where you need to update the Dataset ID

image

Connect flow Results and Contact Collection by running a query in BigQuery before connecting the data sources.​

13 . For Flow results. First go to your BigQuery account. Open a new tab and run below query. Replace tides-saas-309509.917302307943 with your Dataset ID

image

CREATE OR REPLACE FUNCTION `tides-saas-309509.917302307943.jsonObjectKeys`(input STRING) RETURNS ARRAY<STRING> LANGUAGE js AS R"""
const inputJson=JSON.parse(input)
 return Object.keys(inputJson).map((key) => {
 return (JSON.stringify([key, inputJson[key].input]));
});
""";

14 . Click on the Run button. After successful execution a new Routine will be created with name JsonObjectKeys (refer image below)

image

15 . Go back to DataStudio and follow the same steps as we did for Flat fields (9 to 11) above for Flow Results data source. (Replace the Dataset ID as per your setup in 2 lines.)

16 . For Contact Collection. Again go to your BigQuery account. Open a new tab and run below query. Replace tides-saas-309509.917302307943 with your Dataset ID

image

BEGIN
CREATE OR REPLACE VIEW
  `tides-saas-309509.917302307943.contact_collection` AS (
  SELECT
    name,
    phone,
    group_name
  FROM
    `tides-saas-309509.917302307943.contacts`,
    UNNEST(SPLIT(group_labels, ',')) group_name);
END

17 . Click on the Run button. After successful execution a new Routine will be created with name contact_collection (refer image below)

Image 4

18 . Go back to DataStudio and follow the same steps as we did for contacts (6 and 7) above to connect the Contact Collection data source.

19 . Click CLOSE on the top right, and your reports will be ready

Image 5

20 . Your reports are ready for use.

Note: To create your own custom reports follow the steps in the below document .

https://support.google.com/datastudio/answer/6292570?hl=en