How to connect to your databases in Google Data Studio 360

Google Data Studio 360 is a great new way for data professionals to visualize, collaborate and share data at scale.

The biggest challenge that enterprises are facing when considering transitioning to this new product is accessing, transforming and managing the data that is going to be visualized and shared in Google Data Studio 360.

Tools included in the Google Analytics 360 Suite are robust and powerful – yet don’t allow you to easily acquire, prepare, transform, and blend all the enterprise data that exists outside Google’s set of products.

Clearly, Google’s 360 suite will hold only a part of your enterprise’s data, and to tell the full story you would need to combine your data with other data sources outside of Google’s products. For example, it is not possible, with Google Data Studio 360, to bring in data from a company’s database. Apart from that, there are many other data sources that might be part of the analysis. However, unless your company already fully utilizes Google BigQuery and Google Sheets, it will not be possible to bring most of your enterprise’s data into Google Data Studio 360.

In this guide, I will discuss how to transition to Google Data Studio 360, and go on to show you how to access all your data in it. Specifically, I will focus on how to get data from different data sources into Data Studio, and how to transform and manage your data so that it can easily be accessed from within Data Studio 360 by analysts in your organization.

How to bring internal enterprise data into Google Data Studio 360

There are now 6 ways to bring your data into Google Data Studio, and be sure to check out our article on the challenges of connecting your database directly to Data Studio.

Google Data Studio 360 is part of the Google Analytics 360 Suite, a platform that helps you evaluate the full customer journey. Other tools that are included in the suite are: Tag Manager 360, Google Analytics 360, Attribution 360, Optimize 360, and Audience Center 360. Combined together, these tools allow you to collect, access, analyze, and act on your data.

Google Data Studio 360 allows you to visualize and tell your story with the data. It connects to the following data sources: AdWords API, Attribution 360, BigQuery, Google Analytics, Google Sheets, and YouTube Analytics.

If you want to blend in any of your enterprise’s data that sits outside of these data connectors, your choice is one of the following:

1. Load your data into Google Sheets
Google Sheets is a free product offered by Google. It is a great tool but it has its limitations. For starters, it limits the number of cells to 400 000, and the total number of cells that contain formulas can’t be more than 40 000. So, only a small amount of raw data can fit into a single Google Sheet.

2. Load your data into BigQuery
Google BigQuery streams data in, or loads data from Google Cloud Storage or Google Data Store. It is Google’s big data product, so it allows access to vast amounts of data. It is not free, so if you have a lot of data that goes into BigQuery daily, it will cost you.

3. Use Data Import in Google Analytics
Data Import lets you upload data from external sources and combine it with data you collect via Google Analytics. Typically, you would use it to upload data that is related to Google Analytics, such as website content data, pay per click campaign data, cost data, product data, and refund data. A set list of dimensions and metrics defines the key limits of what you can do. Simply put, you can only add data that joins nicely with Google Analytics data.

Most of us are left with Google Sheets. To work around its space limitations, you will have to take data transformation outside of Data Studio 360.

Analytics Canvas has an excellent solution for enabling access to an enterprise’s internal data in Google Data Studio 360. It extracts data from its source, then transforms and loads it into Google Sheets and/or Google BigQuery, where it can easily be accessed by Data Studio.

Transforming data for Data Studio 360

In Analytics Canvas, connect to a file type or a database of your choice: CSV, Excel, Microsoft SQL Server, Access, MySQL, Oracle, PostgreSQL, or Teradata, for instance. Navigate to the database and the table that you would like to import, transform the data the way you want it, and load/use the Google Sheets export block to export data into Google Drive. Click Run to publish.

As an example, let’s take Google Analytics data and customer profile data that comes from an internal database, transform it, and export it into Google Sheets for analysis in Data Studio 360.

Let’s say that I sent an email to our existing clients, asking them to visit our website and sign up for an offer. This offer was also mentioned in a blog post, so anybody who read it could potentially sign up for the offer too. I would like to analyze existing customers who received the offer, and also new customers who signed up after reading the blog post.

In Analytics Canvas, I connect to the database that contains detailed customer profile information. I use a Filter Block to filter out those who did not receive the offer, and leave those who have received it. Out of 42 000 customers, 5580 received the offer.

I am interested in specific information about the company. I would like to know the company’s industry. I create a formula that uses the company name to identify the industry, using a Calculation Block. In some companies, I have emailed multiple people. Since I want to do analysis at the company level, I use a Summarize Block to leave only 1 record per company. I end up with a list of 930 companies who took up the offer.

Next I connect to Google Analytics. I include the following dimensions in my query: source, medium, and page path. I add a filter on the page path to view all the people who have accepted the offer, by signing up and visiting the Thank You page. Use of the filter allows me to view just a few rows of necessary data, instead of getting hundreds of thousands of rows.

Now I am ready to export the data into Google Sheets. I add the Google Sheets Export Block by right-clicking on the canvas and selecting Add Export Block > Google Sheets Export.

I then click on the Pick Google Sheets button, to select a sheet to write the data to. I navigate to the sheet, select where to place the data within the sheet, and click ok.

If necessary, I can use multiple export blocks to break the data into multiple tabs and sheets. Data Studio allows for multiple data sources in a report. You can set a specific data source for each page, and even each individual chart, in your report.

In this case, I want to write the data into two different sheets in the same file. I add another Google Sheets export block and set it to write to Sheet2 of the same Google Sheet.

All I have to do now is click on the Run button to write to the Google Sheets.

I am all set. Now I can access the data I just published to Google Sheets from Data Studio.

Loading enterprise data into Data Studio 360

Now to Data Studio 360. Create a new data source by clicking on the + icon in the lower right corner.

Select Google Sheets from the list of connectors. You might be prompted to grant access to Google Sheets, if you haven’t done so already. Navigate to the spreadsheet and worksheet that contains the data, and click the Connect button. Data Studio creates a new data source.

Repeat the same action to create a data source that contains another worksheet. Now we have 2 data sources under the Data Sources tab.

Click on the Reports tab and add a new report by clicking on the + icon. Select the data source that we’ve just created from the menu and click the Add to Report button.

Note: Use the slider on the left if you are unable to edit the report.

Select one or more charts to create the report. You can also add filters and a date range. Every time you add a chart, you have the option of selecting a data source for it, if you use multiple data sources for your report.

Here is an example of a report that I was able to create. Click to view it in Google Data Studio.

Wondering why I didn’t use Data Studio’s native Google Analytics Connector? In order to get the data in the right form, I needed the following:

• To access multiple Google Analytics accounts and combine all data in 1 table.
• To ensure that we eliminated Google Analytics sampling.
• To filter and prepare our imperfect data.

Access your enterprise data in Google Data Studio. Sign up to download Analytics Canvas and add your internal data to Google Data Studio 360 data sources.

With Analytics Canvas’s ability to connect to Google BigQuery and Google Sheets, you now have a flexible way to connect to your enterprise’s data.