< Return to Feed
Haris Cajic - 01.18.2018

Utilizing the Power of Google Sheets in Data Studio

Google Data Studio is an excellent tool for creating professional, dynamic dashboards and reports. Its modular approach allows for the separation of visual components and their underlying data. Data connectors such as Google Sheets allow for you to organize much of your data in one place and use this data to power your dashboards. While the end results look fantastic, it may be difficult to understand exactly how to format your initial data so that it displays properly in the various types of graphs Data Studio provides for you. I will be going through various types of charts and graphs in Data Studio, showing how to set up and configure each to display correctly.

 

Chart Explanations
I want to plot the amount of keywords a website is ranking for over time. The screenshot below shows how the data should be placed into Google Sheets. Note that the date is formatted in a specific way. This will become apparent when we set up the connector in Data Studio.

 

Time Series & Google Sheets

Example of how data should be placed in Google Sheets.

 

Data Studio

Put down a Time Series chart. Click on “Select Data Source” in the right-hand menu. Click the “CREATE NEW DATA SOURCE” button. Select “Google Sheets” and find the sheet that you have just created. You can either enter a URL, or browse for it within your Google Drive.

Note: Sometimes you may have to refresh Data Studio for the Sheet that you just created to show up in the list. If this is the case, click “CANCEL” in the top right and refresh your page. Follow the steps again and you should see the sheet show up. If you do not, then you may need to check if your sheet is visible to you. Check the sharing options for this.

Now that I have selected my sheet, I need to format the data. I will change the “DATE” field to a YEARMONTH type (YYYYMM). If you wish to format your data in another way, such as by the days in a month, then you will have to select YYYYMMDD and format your Sheet appropriately.

Properly formatted data example in Google Sheets.

 Now just select your Dimension and Metric.

Example of dimension and metric selections in Google Sheets.

 

The result I got looked like this:

Result of Google Sheet keyword ranking by time and dimension.

 

Time Series with Multiple Lines

This one is a bit trickier. The key is in formatting the data correctly.

Google Sheets

Example of data formatted in Google Sheets for time series with multiple lines.

 In this example, I created a line chart that displays a client’s visibility compared to a few competitors. The data will make sense once we configure the connector and the visual chart.

Configure the connector like the first example. Once the connector is ready, we will need to set up the dimensions and metrics.

The key with this chart is to select a breakdown metric, which in this example, is done by the Name field.

Example of breakdown in Google Sheets using the Name field.

The final chart looks like this:

Example of final chart using breakdown metric in Google Sheets.

Combo Chart

In this example I wanted to showcase how the amount of high-quality photos posted on a Google My Business page correlated with the amount of photos viewed by potential customers on that page.

Google Sheets

Example of combo shart in Google Sheets.
 

Data Studio

For this example, you will dump all of your relevant metrics down, and then configure the style section to display bars and lines for the appropriate metrics.

Example of configuration for formatting style in Data Studio.

 Example of formatting with graphs and lines in Data Studio.

Notice how I have selected my series to be graphs and lines based on how I want them to be displayed on the chart.

Example of chart with graphs and lines in Data Studio.

This visual tells the story perfectly, and in a compact format. Businesses whose Google My Business profiles have remained static (in this case, the average amount of posted photos is 6 and has not increased) do not receive as much interaction by people browsing their pages.

Bullet Chart

Bullet charts give you a lot of data in a small package. However, they can be tricky to configure. This example should clarify how to properly set one up.

Google Sheets

In this example, I list the amount of reviews a client has obtained on their Google My Business page over the course of a few months. We will use the bullet chart to display whether or not we have hit our goal for the campaign.

Using a bullet chart to illustrate campaigns in Google Sheets.

Note that there is also additional data in this table. This data was used other charts. However, for this example, I only needed the first two columns. Google Data Studio will of course allow you to pick and chose which metrics you wish to include in your report.

Data Studio

In order for the chart to display correctly, we must configure the range limits as well as the target.

Example of confuration of range limits to make chart display correctly.

 

In this example, the goal was to obtain 40 reviews. Any amount above 40 was considered excellent. Any amount below 20 was considered bad, and would have been indicative of a failed review acquisition strategy.

The final chart looked like this:


Example of final chart in Google Sheets.