Google DataStudio: Connection to PostgreSQL and Facebook Ads

In my previous post, I discussed how to integrate Grafana with Google Analytics and Facebook Ads.

For the integration process, we had created a PostgreSQL database to store the data and used the Stitch service for data transfer. In this post, instead of using Grafana, I’d like to use the Looker Studio tool. Our workflow will be partially the same, but this time I’ll create the database via DigitalOcean.

Looker Studio PostgreSQL Connection

First, we’ll need a connection to DigitalOcean. I’ve previously discussed these procedures in many articles. For getting started, you can refer to the article titled What is DigitalOcean?. Once you connect to the service, you’ll be able to see the Databases option on the left side panel. Through this section, you can access the databases you’ve created. To create a database, click the Create button, then select the Databases option. The database creation screen consists of several sections, including database options (PostgreSQL, MySQL, Redis), billing plan, data center locations, and cluster naming. You may fill in the remaining fields as desired, while keeping the PostgreSQL selection fixed. After clicking the Create a Database Cluster button, the database creation process will begin. This process typically takes between 1 to 2 minutes. However, before the installation is complete, we will share your database access details with you. The Secure this database cluster section will provide access credentials for the resources we’re authorizing. If your database is behind a firewall (security firewall), you’ll need to add the following IP addresses to the Add trusted sources field so that Data Studio can connect to your database¹.

64.18.0.0/20	64.233.160.0/19	66.102.0.0/20
66.249.80.0/20	72.14.192.0/18	74.125.0.0/16
108.177.8.0/21	173.194.0.0/16	207.126.144.0/20
209.85.128.0/17	216.58.192.0/19	216.239.32.0/19

The Connection details section provides access to your database connection information, where you can view details such as username, host, and database name.

username = doadmin
password = \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
host = asasd-do-user-1234567-0.a.db.ondigitalocean.com
port = 25060
database = defaultdb
sslmode = require

Instead of using the above details, you can enter a JDBC URL in the format jdbc:postgresql://[:]/. Based on the above information, your JDBC URL will look like this.

jdbc:postgresql://asasd-do-user-1234567-0.a.db.ondigitalocean.com:25060/defaultdb

In addition to the above steps, we can also establish a secure connection (SSL) ². After acquiring the above information and completing the installation, we can proceed to the integration process for the services we will access via Stitch.

Stitch Facebook Ads Integration

As mentioned in the article titled Grafana: Google Analytics and Facebook Integration, we can access the Stitch panel, navigate to the Integrations section, and connect to numerous services from there, allowing us to retrieve data from these services. Since Data Studio provides direct access to Google Analytics, we will proceed with Facebook Ads for now. We will click the Add Integration button in the Integrations section to view the list of services and select the Facebook Ads service. We can then fill in the fields in the Configure your Facebook Ads Integration section as needed. Upon clicking the Authorize button, the authorization process will begin. After selecting the user and Facebook Ads tables and completing the authorization, our access to the services via Stitch will be fully established.

Stitch PostgreSQL Target Configuration

Now we can proceed to the Destination setup phase. This time, we’ll define Stitch as the destination for the database information we created with DigitalOcean. Select PostgreSQL from the destinations list and enter the database access details into the corresponding fields, then initiate the registration process. The Destination success message will confirm that the connection has been approved. Now, our Facebook Ads tables are ready to be sent to the designated database. The IP addresses you can define for access to DigitalOcean are as follows:

52.23.137.21/32	52.204.223.208/32	52.204.228.32/32	52.204.230.227/32

Data Studio: PostgreSQL as a Source

In addition to pre-defined services, options such as PostgreSQL, MySQL, Cloud SQL for MySQL, and BigQuery are available. When selecting PostgreSQL in the above-mentioned steps, you’ll see the database linking steps appear. I provided detailed information about these steps in the introduction paragraph. After completing these steps, you can proceed to the Tables and Custom Query options.

Under Tables, you can view the tables we’ve imported via Stitch, along with their metrics and dimensions. That’s all for the process. For further information about Data Studio charts and other details, please refer to the following articles:

• Looker Studio: Getting Started
• Looker Studio: Charts
• Looker Studio: Calculated Fields (Calculated Fields)
• Looker Studio: Pivot Table Usage

Footnotes

1. Connect to PostgreSQL, Data Studio Help ↩
2. PostgreSQL SSL Support ↩