Connect Zoho Analytics to GCP Cloud SQL

Zoho Analytics is created by Indian-based Zoho, which generated some estimated 500 million in revenue in 2019. Using Zoho Analytics provides the largest synergies when your company uses other products from the Zoho product suite (such as Zoho CRM or SalesIQ). However, Zoho Analytics’ target audience clearly goes beyond its existing user base. In this blog post I explain how you can connect it to a PostgreSQL database in Google Cloud Platform.

Connecting Zoho Analytics to a PostgreSQL database in Google Cloud Platform is fairly easy. Go to your Cloud SQL instance in Google Cloud console. In the menu, go to Connections. Under authorized networks, add the Zoho Analytics IP addresses as follows (by the way, use CIDR notation):

In the screenshot below, you can see how I added all the European IP addresses. To find them out for your regions, please refer to this overview.

It’s good practice to make a new user that only has reading rights. In the following query, I grant my user zohoanalytics reading rights to all existing and future tables in the database covid.

CREATE USER zohoanalytics WITH PASSWORD 'thisIsAPassword';
GRANT CONNECT ON DATABASE covid TO zohoanalytics;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO zohoanalytics;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO zohoanalytics;

Once the IP addresses are whitelisted and the user is created, you can connect to the Cloud SQL instance with Zoho Analytics.

The first step is fairly easy, you copy the IP address from the SQL instance overview. Normally you don’t need to change the port. Next, add the username and the password. Finally, enter the database you want to connect to.

Step 2. You can import one specific table, multiple tables and even write your own custom query. I appreciate the possibility to write your own queries. This way, you can clean the data before it is loaded into Zoho.

In the third step, you can choose the names of the tables you are importing. As you can see, Zoho does not necessarily assign the same datatype to the columns of your database. There’s a wide range of datatypes to choose from that go beyond what databases have to offer: continents, countries, states, provinces, counties, districts, ZIP codes, airports, emails and urls.

In Step 4, you set the frequency of refreshing the data. In the free and basic version you’ll have access to a daily sync. In the standard plan and up, you can choose to sync your data on an hourly basis.

Syncing can take a while. If the data is not showing up and the table appears to be empty, wait a little bit longer. In the data sources menu, you can see the status of your table.

Once syncing is done, you can go ahead and start making dashboards.