Skip to main content
Data Runs Deep Menu

Main navigation

  • Home
  • Who We Are
    • Careers
    • What We're Up To
    • What We Believe In
    • Ethical Policy
  • What We Do
  • Google Marketing Platform
    • Analytics 360
    • Tag Manager 360
    • Salesforce and GA360
    • Google Analytics App+Web
    • Data Studio
    • Optimize
  • Google Cloud Platform
    • Google BigQuery
      • Google BigQuery Training
      • Google Analytics Integration
  • Consulting
    • Strategy
    • Implementation
      • Google Analytics Audit
      • Google Tag Manager Audit
      • Enhanced Ecommerce Implementation
      • Google Tag Manager
    • Analysis
      • Google Data Studio
        • Google Data Studio Case Study - Mona
      • UX Analysis
  • Training
    • Five Day Google Analytics Online Workshop
    • Advanced Google Analytics Training
    • Google Tag Manager Training
    • Google Analytics 360 Training
    • Advanced Analytics Bootcamp
  • Partnerships
    • Conductrics
  • Blog
  • The Flying Beagle
  • Merch
  • Contact

Visualising M Night Shyamalan In Google Data Studio

Presenting Data Through Google Data Studio
By: Nick Hood
Posted in
  • Tips
  • Data Studio
Article Date
August 16, 2016

Back in June, we published a blog on How To Access Google Data Studio Outside The USA. That’s what we’ve been doing, and to explore the potential of Data Studio we decided to use the platform to answer the question:

What happened to M Night Shyamalan?

We had our suspicions, but as evangelists of the importance of analytics we were not about to go drawing conclusions without consulting the data. So we created a report in Google Data Studio to help tell the story.

Not only did we discover that things kind of went downhill for M Night after The Sixth Sense, we started to get an idea of just how cool Google Data Studio is. Here’s a quick overview of how we put this report together.

Data collection and data connection

One of the big advantages of using Data Studio is the ability to connect one report to multiple data sources (Adwords, BigQuery, Google Analytics, Youtube Analytics etc). For this piece we did only require Google Sheets, but as Sheets in itself does allow you to pull data from various channels we did scratch the surface of some “Data integration”.

We got the bulk of our data from the OMBd API (link) then paired it up with some supplementary info from Box Office Mojo and Wikipedia just using vlookups to match data the data by film title. We used a few simple calculations in our spreadsheet to add additional layers of information like profit/loss figures and award nomination to win ratios and flagged certain line items like films that were profitable, or had an above average IMDB rating.

Google Sheets Data for Data Studio

The data was great, but as is the case with most data, doesn’t tell much of a story in it’s raw format. Enter Google Data Studio. With a few button clicks in Data Studio:

Add Data Source Data Studio

We can access the data from all our Google services, including our Google Spreadsheet.

Add Google Sheets to Data Studio

And we can easily pick and choose which dimensions and metrics we want to add to our visualisations.

Dimension and Metric Picker Data Studio

Now that we have all our data in place, we can turn this:

Google Sheets Data for Data Studio

Into this:

Example Data Studio Report

Creating charts in Data Studio

Creating charts is Data Studio is super easy, the default styles are already really nice, and there are enough styling options to customise your reports to match the theme and tone you require. Once your data is connected you simply choose the type of chart you want:

Select Chart Options Data Studio

And drag it onto your canvas

Data Studio Canvas

Select a dimension

Select Dimension Data Studio

And select a metric

Select Metric Data Studio

Now we can see, that since his breakout hit The Sixth Sense, M Night was been trusted with more and more budget.

Data Studio Bar Chart

Gotta spend money to make money etc. So how much money did each of these films earn? We can now select a second metric to sit alongside Budget to compare the two. Just click the chart, and click add metric in the properties panel. We selected Gross Box Office to see if the money earnt followed the trend of the money spent.

Data Studio Budget Metric
Gross Box Office Metric Data Studio

For those familiar with the works of M Night you’ve probably already guessed, that It did not. As budget increased, Box Office revenue decreased. For those familiar with the works of the Notorious B.I.G, you already know that mo money mo problems. But this was a nice simple example of how a Data Studio chart can make it easy to interpret data and communicate a message.

Data Studio Multiple Bar Chart

Highlighting specific bars in a Data Studio bar chart

Another simple technique we used to make a bar chart a little more interesting is to stack two dimensions to highlight selected panels like in this example:

Data Studio Bar Chart Formatted

Money isn’t everything, so we wanted to see how M Night’s films faired over time in terms of public opinion.  We selected the dimension of Year + Title and the metric of IMDB rating to see how users perceived the quality of each release and we saw that ratings were in steady decline post The Sixth Sense.

However, to give some additional context we wanted to highlight if these films were good or bad in the greater scheme of things, not just in comparison to the Sixth Sense, so we compared their rankings with a global average of feature film ratings on IMDB (Identified by Quora https://www.quora.com/What-is-an-average-rating-on-IMDB-for-a-movie).

At the time of writing this Data Studio doesn’t currently provide an option for conditional formatting within a bar chart so we used a secondary dimension to highlight the films that were rated better than average.

To do this we included one of the calculated fields in our Google Sheet which categorised films as either above or below average based on their IMDB rating value as a secondary dimension.

Data Studio Year Dimension

The default formatting for this chart isn’t exactly what we were after:

Data Studio Stacked Chart Formatting Issues

But a couple of small style changes give us the desired outcome. Unchecking the Single color checkbox lets us define the colours we want to use for each of the groups

Data Studio Chart Formatting Options
Data Studio Bar Chart Formatting

And selecting Stacked Bars overlaps the two dimensions (this works for this example as our secondary dimension is a Boolean value with only true or false outcomes so we’re not overlapping two positive values).

Data Studio Stacked Bar Chart Formatting

We now have a chart that shows both the decline in ratings over time, and the proportion of films considered to be better than average.

As we were keen to explore some of the other chart types in Data Studio we added a pie/doughnut chart to further illustrate this.

To do this we select Pie chart from the insert menu

Insert Pie Chart Data Studio

And chose the above average rating dimension

Average Rating Dimension

We then required a metric for the chart, and the appropriate metric is just a count of how many films fall under each of the Above Average Rating options which wasn’t a field we had in our Google Sheet. We could have gone back and just added an additional column to our original data, or, we could use the calculated field functionality within Data Studio.

Calculated fields

The ability to add calculated fields is another of the most appealing features of Data Studio. More information on this can be found here:

https://support.google.com/360suite/datastudio/answer/6299685?hl=en&ref_topic=6370331

For this example, we simply wanted a count of titles to use as the baseline for our pie chart. Within the metric picker menu we select Create new metric.

Create New Metric Data Studio

This brings up a view of all our database fields and a blue plus icon which allows us to create our own calculated fields.

Create A Custom Field In Data Studio

We used the COUNT function to get a value for the number titles, which we can then use to see how many titles fall under our below and above average categories.

Count Calculated Field Data Studio

More information on functions can be found here:

https://support.google.com/360suite/datastudio/table/6379764?hl=en

We add our new metric to our pie chart properties 

Data Studio Pie Chart Dimensions

and we now have a lovely pie chart showing the breakdown of “good” films vs “bad” films. 

Example Data Studio Pie Chart

To finish the chart off, we add a little annotation using the text box function, summarising the chart and making that message just that little bit clearer.

While very simple when compared with calculated fields and the like, the ability to add these captions is really useful for adding additional information and observations.

To add these, you click the insert menu item and choose text.

Insert Captions Data Studio

Filters and interactive reports

The ability to add custom filters is perhaps the most exciting features of Data Studio reports. You can define the metrics you want to display, then can add a custom filter based on the dimensions in your data. So for a website performance report you might want to show your key metrics, then allow the user to filter by traffic source or device type to see how each of these performed. This could reduce a multiple page report into a one pager, where the user can define what information is relevant to them.

If you’re creating a report on the performance of M Night Shyamalan films, you might want to display all the key performance metrics like award wins and ROI and then allow the user to filter by film title to see how each film performed.

Example Interactive Data Studio Report

One issue we had with this page of the report is that by default, unless the filter is used, the values are going to be displayed as a sum for all the films, so on page load you would see an IMDB rating of 66 out of 10 which even from a fans perspective seemed excessive.

To change this, we adjusted the aggregation settings in the Edit data source options to average.

Data Studio Aggregation Settings

Now the values when displayed in isolation will show the actual value for the film, but when grouped together will be displayed as an average. This means you can select multiple films from the filter and get meaningful information back.

Inter page linking in Data Studio

Once we had all our charts and annotations in place, the last step was to tie them all together and we did this using a simple in page navigation utilising the newly implemented linking feature of Data Studio.

To create a link from one report page to another you grab the url for the report page you want to link to e.g. https://datastudio.google.com/u/0/#/org//reporting/0B6vuEpR8jgX2R29GVzJxWDlOak0/page/vbQ

Insert a text field

Insert Captions Data Studio

Click the link icon in the Text Properties panel

Add A Link To A Data Studio Report

And add in your link text and the url for your report page

Link Options Data Studio

While the linking in our example is straight forward and linear, there are a lot of interesting potential uses for this like expanding/drill down type links in which you provide an overview report then link through from individual charts to stand alone report pages which expand upon the summary chart.

So that is how we made our first presentation/report hybrid using Google Data Studio. We can already see so many possibilities for practical implementations of this tool so as we continue to delve deeper into its capabilities we will keep you posted on what we find.

Get in touch

To find out more, email us on hello@datarunsdeep.com.au or complete a quick contact form.

Get in touch

Talk to us: 1300 737 885

08:30 - 16:30, Monday to Friday. We love to chat

Visit us, we’re at:

Level 8, 313 Little Collins Street Melbourne, VIC 3000

Footer

  • Home
    • Who We Are
      • What We Do
      • What We're Up To
      • Careers
      • Contact
  • Training
    • Google Analytics Training Courses
    • Advanced Google Analytics Training
    • Google Tag Manager Training
  • Consulting
    • Analysis
    • Implementation
    • Strategy
    • Google Analytics Audit
    • Google Tag Manager Audit
  • Google Cloud Partner
  • Google Marketing Platform Sales Partner
  • Bitcoin accepted here
  • Registered Looker Consulting Partner Melbourne
  • Twitter
  • LinkedIn
  • Google Plus
  • Blog RSS feed
Copyright © 2009-2021 Data Runs Deep Pty Ltd. All Rights Reserved. Google Analytics is a trademark of Google Inc.
Website by Marin Sekesan