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
- Connect
contacts
,messages
, andmessage conversations
through 'My Projects' - Connect
flat fields
,monthly active users
,Contact collections
,success messages
andflow context
by replacing your Dataset ID into the preset 'Custom Query'. - Connect
flow Results
andContact 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.
- Template v5.0: https://lookerstudio.google.com/u/0/reporting/6f6d2bd7-7bd0-45d0-afad-1e9620f266d8/page/p_eq3uecja2c
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:
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:
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 sources
from the Resources Menu. Refer to the image below:
Connect contacts
, messages
, and message conversations
through 'My Projects'
5 . Click on edit
one by one for each data source. Start with contacts
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.
e . You might get a warning "Configuration has changed. Do you want to apply the changes?
”. That’s okay, click on APPLY
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:
- All fields with 'id' in their name should be of the type 'text'
- 'phone' should be of the type 'text'
- 'language' should be of the type 'text'
- All fields with 'at'/'time' in their name should be of the type 'Date and Time'
- Latitude/Longitude should be of the type latitude, longitude.
g. Click on DONE
in fields listing page
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.
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.
9 . Click on Reconnect
to update.
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
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
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)
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
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)
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
If you want to reveal the phone numbers in your dashboard
- In your dashboard, navigate to the table where you want to unmask the phone numbers.
- Click on the edit button, then select the table.
- On the right-hand side, under
Dimension
, click onAdd Dimension
. - Search for
contact_phone
(in some cases, it may be listed asPhone
) under the default group. Add it, then remove the previousPhone
from the dimension.
20 . Click CLOSE
on the top right, and your reports will be ready
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