Create A Google Cloud PostgreSQL Database For Google Sheets & Google Analytics Data Reporting


Hi folks, so in this video, I’m gonna show
you how you can quickly set up a Google Cloud PostgreSQL database. So you can start ingesting data sources such
as Google Sheets, Google Analytics, and use your database for business intelligence reporting
and analytics on a platform such as Holistics. So let’s do this! Sign up or log in to the Google Cloud website
and go to your console. Select the SQL product from the drop-down
menu. You are now ready to set up your PostgreSQL
database instance. Select PostgreSQL as your database engine. The instance will take awhile to initialize
so don’t leave the screen and let it run for a few minutes. You can now finish setting up your PostgreSQL
instance. Give your instance a name and set up a password. Select a Google Cloud region with a datacenter
closest to your location and choose a zone if you like. Under configuration options, you can choose
a small machine type in order to save on costs or you can leave it to be set automatically. Those are the basic steps you will need to
create a PostgreSQL instance. Hit create and it will take awhile for your
instance to get fully set up, so just let it run for a few minutes. Our next step is to now create a PostgreSQL
database and connect it to the Holistics data platform to begin our business intelligence
reporting. Select edit where you can adjust the previous
instance settings. We now need to whitelist the Holistics IP
addresses to allow the Holistics network access to the PostgreSQL database. Select add network and you can now copy and
past over it the Holistics IP addresses for whitelisting. Add a new data source to Holistics. You can select PostgreSQL from the database
type drop-down menu, for a preview of the fields you are going to need. In Holistics IP addresses you need to whitelist
are listed on the right. So copy and paste each of this into Google
Cloud and add them as a new network. Save these new settings for your instance
and wait for these changes to be updated. The final step is to create a user account
and the PostgreSQL database itself. Select your instance, click on the user tab
and create a user account. Select a user and password and remember these
details connect to your PostgreSQL data source for Holistics later. Select the databases, click create database
and give your new database a name. You are going to need this to connect to Holistics. We now have all the information we need to
connect your PostgreSQL database to Holistics. Let’s give this data source a display name
that we can easily remember, like GCP-Postgres. The PostgreSQL IP address that we need to
connect to this instance is to be found in the overview tab. Copy this public IP address across and paste
it into the Holistics form. The database name is to be found under the
Database tab, from the PostgreSQL database that we created earlier. And finally the username and password are
taken from user account that we created earlier. We can test the connection and once you have
checked that it’s working correctly, we can save this data source. We can check the status of the PostgreSQL
database is working correctly from a full list of data sources. We can now run our data reporting and data
preparation jobs such as ingesting from Google Analytics and Google Spreadsheet data and
automating our reports for export and delivery. Now we have many other videos that show how
to perform these tasks in a lot more detail. So I’m just gonna give you a quick version
here. Holistics lets you easily import data into
your relational database such as PostgreSQL and you can import sources such as Google
analytics, Google Spreadsheets, CSV files from Google Drive folders, other database
tables and more. So to automate imports from Google Spreadsheet
data, copy the URL link of your Google Spreadsheet. Select Google Spreadsheet as your import source. Paste the URL and click validate source. If this is your first time connecting Google
Spreadsheets to Holistics, you will need to do a Google authentication. The Google Sheet is being read correctly,
so now select your Google Cloud PostgreSQL database, select schema name and give a table
name. You can now configure the import mode and
data types for your import job. And most helpfully, you can set a schedule
for how often you want data to be imported from your Google Spreadsheet into your PostgreSQL
database. Every few mins, every few hours as you require. You now validate this configuration, I can
save and execute this job now or on schedule. The details of our import job as it runs will
be saved in logs so we can refer to for troubleshooting if any errors were to occur. With the data we have imported into our PostgreSQL
database, we can now build reports with just a few steps. We can analyze this table by sending query
and build a simple chart by just selecting columns and fields for experimenting in our
editor. There are many other functions you can perform
inside Holistics such as automatically importing Google analytics data into your PostgreSQL
database to be combined with data from your other sources. So hop on over to our Youtube channel to find
out what else you can do on the Holistics data platform. From the Holistics team, I will see you soon.

Leave a Reply

Your email address will not be published. Required fields are marked *