Skip to main content

DataStudio Integration for Visualizations & Reports

3 minute read                                                                                                                 Intermediate

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 . Masked Phone Numbers

Screenshot 2024-09-16 at 3 21 29 PM

If you want to reveal the phone numbers in your dashboard

  1. In your dashboard, navigate to the table where you want to unmask the phone numbers.
  2. Click on the edit button, then select the table.
  3. On the right-hand side, under Dimension, click on Add Dimension.
  4. Search for contact_phone (in some cases, it may be listed as Phone) under the default group. Add it, then remove the previous Phone from the dimension.

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

Image 5

21 . 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