Integrate Doppler with BigQuery and visualize personalized reports
Thanks to this integration, information about Campaigns sent from Doppler goes to BigQuery (a Google cloud storage service). It travels from there to a Data Analytics tool to visualize personalized reports.
The main advantages of this integration are:
- The chance to obtain personalized reports about your Campaigns in a simple way.
- The data crossing, not only data about Doppler Campaigns but also information from other data sources.
- The identification of action items to optimize your Email Marketing strategy and digital strategy in general.
To complete the integration, you must take action both in Doppler Control Panel and in Google Cloud Platform.
- Give access permissions to BigQuery from Doppler
- Create your project and pin Doppler’s Project from Google Cloud Platform
- See Campaign reports in your Data Analytics tool
- Connect Google Data Studio with BigQuery
- Connect Power BI with BigQuery
- Connect Tableau with BigQuery
How to give access permissions to BigQuery from Doppler
1- Log in to your Doppler account. From the Control Panel you’ll see the BigQuery icon.
2- Clicking there you can give access permissions to one or more Google accounts (Gmail domain or corporate accounts), so those accounts will be able to see information at BigQuery. If you want, you can also remove permissions.
Each time you give permission, the user will be notified by email. Take into account that permissions could delay until 10 minutes, so we suggest waiting this time before doing any action at BigQuery.
Well done! Those were the steps you have to do from your Doppler account.
How to create a project and pin Doppler Project from Google Cloud Platform
1- Click the following link: https://console.cloud.google.com/home/dashboard
2- If you are not logged in, you have to write your Google email address and password.
3- Now you are in the Google Cloud Platform dashboard! Click “Select project”, on the left side of the screen, to create your own project and link up with Doppler’s Project.
4- At the right of the modal, click “New project”, choose its name and configure the extra information.
5- Done! Your recent project will be shown on the left side of the screen.
Before going ahead, you have to configure a credit card in the Billing Information section of Google Cloud Platform. This is because Google charges every information request that exceeds the daily maximum included.
If you don’t do it, you cannot visualize information saved at BigQuery. Take a look at this Google article to configure the Billing section step by step.
6- Now, click here and pin it up to link up your project with Doppler’s BigQuery Project. In case you don’t see your project (at the left side of the screen), select it again so you’ll see Doppler project doppler-291113 pinned to your project.
You’ve just completed all the steps at those platforms, and now they’re correctly matched. Information about Campaigns sent with Doppler will be automatically updated in BigQuery every one hour.
Let’s go ahead! We’ll see how to use this information to generate personalized reports at three of the most popular Data Analytics tools.
How to visualize Campaigns reports at your Data Analytics tool
Now it’s time to show your Campaigns information in a dashboard or Data Visualization tool, so you can take advantage of it. There are many Data Analytics tools to do it, here we focus on three because they are popular and also easy to configure: Google Data Studio, Power BI and Tableau.
Connect BigQuery with Data Studio
Data Studio is a Google-free tool. We provide you with a predesigned dashboard with some fields and metrics shown by default. You can edit it as you prefer.
To visualize data with Data Studio, you need to make a copy of Doppler Data Source, and then synchronize the copy with the dashboard, so the information can be shown correctly.
To make a copy of Doppler Data Source:
1- Enter to this link. You’ll see the Doppler Data Source.
2- Click the icon to make a copy of the Data Source. You’ll see a pop-up to confirm the action. Click “Make a copy of the Data Source”.
4- It’s done! The copy has been made correctly. You can find it in the Data Source section of your Data Studio account datastudio.google.com. We suggest you to edit the copy name, so you can recognize it simply.
5- From your Data Source copy, follow the route: Shared projects > Your project > doppler-291113 > doppler_reports > CampaignSummary. Then click “Reconnect”, on the right side of the screen.
6- Click here. It’s the predesigned Google Data Studio Template (field names are in Spanish, you can edit them). Then click “Use Template” on the right side of your screen. A pop-up will appear to match it with the copy of the Data Source. Once you find and select it, click “Copy inform”.
7- Hold on a minute to visualize data in your dashboard. You’ll find your personalized report at the Reports section of your Data Studio account datastudio.google.com.
Connect BigQuery with Power BI
1- To start, download and install ODBC Driver for BigQuery from this link. Choose 32 or 64 bits version.
2- Open the ODBC Data Sources admin and go to SYSTEM DSN, identify Google BigQuery option and click Configure.
3- In the Authentication section, choose User Authentication. Then click Sign in, you’ll be redirected to the browser. Enter your Google user and password. A confirmation code will be shown: copy and paste it in ODBC admin.
4- Click Refresh token, so it will be autocompleted based on the code you’ve pasted in the last step.
5- In the Catalog (Project) option, display it to see the project you’ve created in Google Cloud Platform (How to create a project and pin Doppler Project from Google Cloud Platform – step 4). Select it and click Test to be sure that the connection is done.
You are ready to complete the process from Power BI.
6- Open Power BI, go to Get Data and look for ODCB.
7- Select Google BigQuery and paste the following query at the Advanced Options section:
SELECT * FROM `doppler-291113.doppler_reports.CampaignSummary`
To finish, click OK. Next you’ll see your Campaign data and start designing your dashboard.
Connect BigQuery with Tableau
1- Open Tableau. At the Connect section, go to To a Server and choose Google BigQuery.
2- You’ll be redirected to log in with the Google account that has permission at BigQuery. Once you enter your Email address and click Continue, you will be able to return to Tableau if everything is OK.
3- Choose the project you’ve created at Google Cloud Platform (How to create a project and pin Doppler Project from Google Cloud Platform – step 4).
4- In the Project section, display and choose doppler-291113. If it doesn’t appear, click + and write its name.
5- At the Data Set section, choose doppler-reports. To finish, drag the table you want to use to prepare your report.
That was all. Enjoy the integration!