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.
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:
We can access the data from all our Google services, including our Google Spreadsheet.
And we can easily pick and choose which dimensions and metrics we want to add to our visualisations.
Now that we have all our data in place, we can turn this:
Into this:
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:
And drag it onto your canvas
Select a dimension
And select a metric
Now we can see, that since his breakout hit The Sixth Sense, M Night was been trusted with more and more budget.
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.
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.
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:
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.
The default formatting for this chart isn’t exactly what we were after:
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
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).
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
And chose the above 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.
This brings up a view of all our database fields and a blue plus icon which allows us to create our own calculated fields.
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.
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
and we now have a lovely pie chart showing the breakdown of “good” films vs “bad” films.
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.
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.
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.
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
Click the link icon in the Text Properties panel
And add in your link text and the url for your report page
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.
