Are you looking for ways to build better dashboards?
Google Data Studio (now called Looker Studio) allows us to build dashboards and present data in a clear and informative way. While it boasts a lot of default charts and graphs we can utilize, there are times when the information we want to display isn’t readily available.
The key to building an effective dashboard isn’t just about putting data into a canvas, but to get people to view and use them.
Recently, our MeasureMasters members’ live training session involved teaching how to enhance Data Studio dashboards with a few simple steps. This training was so well received that we wanted to share it with the MeasureSchool audience, as well.
The complete session covered 6 enhancements. For this post, we’ll share with you our top 3 recommended Google Data Studio dashboard enhancements.
Here is an overview of what we’ll cover:
Let’s dive in!
Helping Your Clients
Before continuing, let’s discuss why you should implement these enhancements in the first place.
A dashboard is where you can display various types of visual data. It is intended to convey different but related information in an easy-to-digest manner.
It is not enough to just chuck in as much information into the page as possible when creating a dashboard. If your clients do not understand what you’re trying to present in the dashboard, then it is essentially useless.
As a dashboard creator, your aim with the creation should be to help your clients understand their data. You might ask, why? Why can’t you just present all their data and let them figure it out and draw conclusions themselves?
Let us share a quote by W.E.B. Du Bois:
“When you have mastered numbers, you will in fact no longer be reading numbers, any more than you read words when reading books. You will be reading meanings.”
Essentially, we want to help our clients understand their data so that they can go beyond just collecting it and see the meaning behind these data.
They will then see the value of their data in growing their business, and start applying what they’ve concluded from the data to make better business decisions, know where to invest, and determine which aspects of their business they should allocate their efforts to improve.
If you don’t overwhelm them with a cluttered dashboard that they’ll find extremely difficult to understand, then you will also save them a considerable amount of time, for which they will surely be grateful.
A clean and organized dashboard that gives them more insights than just pure data will surely lead your clients to see your services’ value and they will want to return for further help.
Time Series Annotations
The first in our list of the top recommended Google Data Studio dashboard enhancements is adding time series annotations.
For clients who have questions about the spikes or dips in their data, creating an annotation system for any data series that changes over time would help answer.
Adding time series annotations in Google Data Studio is not straightforward, unlike in Google Analytics. We need to use data blending to add notes about events that influenced the traffic.
Let’s look at an example. Here, we have a graph showing how page views vary with time and some notes at the bottom with highlights in the graph where notes are present.
Currently, we can see the annotations all at once, but we can also click on one of the dates to see exactly what event was recorded on that day.
Once we select this date, we can see that on June 1st, we started the International Children’s Day campaign where we ran paid advertising on Facebook and Instagram.
This explains the spike in page views for the coming days and possibly the dip that comes after pausing the campaign.
As mentioned earlier, we will use data blending. In our case, the main data source providing the page views is Google Analytics. For annotations, we used a Google Sheets spreadsheet to add all the notes we want.
For the spreadsheet, make sure there are at least two columns for the date and dashboard annotations.
We can then use the Date as the join keys for our data blend. Next, include the Pageviews metric from our GA data source and the Dashboard annotations dimension from GS.
What’s important is that at the chart level, we will use both the page views and dashboard annotations as metrics in a combo chart.
🚨 Note: If you’re unfamiliar with the different available chart types, check out our guide on the Google Data Studio Charts to create stunning reports.
You can then configure the Pageview (or any KPI you want to analyze in a time series) as a Line and the Notes as Bars.
When we hover over one of the dates, we can see that the notes, when used as a metric, will be automatically counted as an aggregation.
All that’s left to do is add a table containing the annotations and their corresponding dates somewhere in the dashboard and you’re all set! You now have a workaround for the time series annotations that are lacking in Google Data Studio.
Case When Formulas
The next technique is to group and simplify data with Case When formulas.
Out of all the Google Data Studio dashboard enhancements we will discuss, this is one of our favorites since it can be used practically anywhere.
Since the Case Function in Google Data Studio is extremely versatile, we’ll show you two examples of its possible use cases. One is for simplifying an organic position by customizing and putting it into ranges, and the other is for traffic channels.
Let’s start with simplifying the organic position range example.
At the left is a table showing the average position for the different keywords (which we covered) and the corresponding position range. We can see the Case formula we used on the right.
🚨 Note: Check out our guide on RegEx for Google Tag Manager for a refresher on regular expressions syntax. While this guide is catered for GTM use, the Case formula uses the same syntax in Google Data Studio.
Let’s say your client wants to focus on the keywords that are in the 11 to 20 position. With the use of Case when formulas, we can easily select a position range from a dropdown list to filter the table.
To illustrate, click on the dropdown selector then select the Between 11-20 option.
This saves any viewer of your dashboard the hassle of scrolling through hundreds or possibly thousands of rows of data if they want to reach, for example, keywords that are on position 30 or further.
If you look closely at the Case When formula, you have to first implement an intermediary step that rounds up the position, but the general idea of grouping data into ranges can already be seen in the formula.
💡 Top Tip: Check out our guide on the 5 Data Studio functions for calculated fields to learn more helpful functions to use alongside your Case formulas.
Next, we have the traffic channels grouping example.
If you’re using Google Analytics, then you already know that it has a traffic channel grouping there, so you may wonder why can’t you just use the grouping used there. Channel grouping in GA is strict and one can easily mess up with it.
If you make any changes, it can affect how you grouped all the traffic in unpredictable ways. Another one of its many disadvantages is that it doesn’t look at historical data.
In our opinion, Google Data Studio is much better for channel grouping because it’s instant. It rearranges and reshuffles all the traffic as you want and puts it in the correct channel.
Even if you make a mistake, you can rest assured that Data Studio will still return accurate data instantly, unlike Google Analytics where you cannot know if the data you’re looking at is correct if you make a mistake.
Let’s look at an example showing the difference in traffic channel grouping with Google Analytics and Google Data Studio.
The most noticeable difference is that in Data Studio, we can break down channel groups further so that we can gather more insight. Paid searches are further divided into branded and non-branded, social into paid and organic, and email into newsletters and offers.
Another difference we can observe is that the “Social – Paid” channel group is listed under Other in Google Analytics instead of in Social.
There is also a discrepancy in the number of organic search transactions. There are 55 in GA and 56 in GDS. One of the transactions was grouped under Organic Search in GDS; whereas in GA, it is under Referral.
Troubleshooting these differences can involve manually checking your data, which we won’t cover in this guide. However, these discrepancies illustrate how you can group traffic channels in Google Data Studio versus Google Analytics.
Let’s look at the formula used in this example.
Yes, the formula we reached seems complicated, but it is not as complex as it may seem. At its core, it is still a Case When formula. Just remember that after WHEN we have the conditions and after THEN is the traffic channel grouping we want.
Admittedly, setting this up can be extremely tedious and it can take longer depending on the data your client has. However, having the ability to group traffic channels or any other data yourself unlocks endless possibilities.
You can also be sure that the client has the correct data, and that any information they extract from your dashboards can be relied on in dictating how to grow their business, such as where to invest more money and where to put less effort and money.
Lastly, we have monthly forecasts.
For our example, we’ll show you how to build simple predictions to see how your budget will look at the end of the month and how much remaining budget we have per day. Then, we’ll calculate an end-of-month forecast comparing money spent to the allocated budget.
Currently, the text is highlighted in green whenever we are spending less than the budget. However, some clients think that underspending by 23% is not good.
They want to spend as close to their budget as possible to have a high return on investment, so spending less than 23% of the budget can place this forecast in the yellow or even red zone if they wanted.
Nevertheless, this change can be easily altered later. Let’s first discuss how to calculate the different values and forecasts.
First, we will blend a Google Sheets spreadsheet containing the monthly budget with any data source that we’re using to extract the amount spent. This could be from Google Ads or Facebook Ads if we’re looking at the money spent on ads.
Next, we’ll do a simple calculation for the budget remaining by subtracting the amount spent from the monthly budget.
The calculation for the budget remaining per day is a bit long, but we’ve made it easy to follow. Let’s break down each step:
- Extract the month from the date by using the MONTH function.
- Use the CASE function to return the number of days in a month.
- Determine the days passed this month by using the CAST function to change today’s date to text → Extract the day portion by using the SUBSTR function → Change the type from text to number using the CAST function → Enclose in MIN function.
- Calculate the days remaining this month by subtracting the days passed this month from the number of days in a month enclosed by the MIN function.
- Subtract the total monthly budget by the total amount spent this month → divide the difference by the days remaining this month to get the budget remaining per day.
Keep in mind that you don’t have to follow these steps exactly as we have presented here. Feel free to change and adapt these formulas to your needs, or try out different ways to arrive at the same value.
Finally, for the end-of-month forecast, we’ll only implement two additional steps since we have already calculated the needed values earlier. These steps are:
- Calculate the average amount spent per day by dividing the total amount spent by the days passed this month → Forecast this spending by multiplying the number of days in the month → Divide this by your monthly budget and subtract 1 to obtain the percent difference of money spent to the allocated budget.
- Use the CASE formula to determine if forecast spending is over or under budget. If the percent difference is negative, then it is under the budget and it is over the budget otherwise.
🚨 Note: Use the last step at the chart level only. An error message will show up, but the formula should still work as expected.
Realistically, there will be fluctuations in the amount spent per day, but remember that an update will be generated every day to calculate for more accurate forecasts.
You can end the process here, but we can also add conditional formatting to the values so that your clients can quickly understand what’s going on.
There are also additional charts we can use from the Google Data Studio community visualizations to better show insights at a glance. For this example, we could use a gauge chart for the end-of-month forecast.
Those are our top 3 recommended Google Data Studio dashboard enhancements. These include adding time series annotations, grouping and simplifying data with Case When formulas, and calculating monthly forecasts.
Hopefully, you have an understanding of the need to help your clients comprehend their data and that these dashboard enhancements could be a step in that direction.
Another way for clients to draw insights from the data is by giving them the ability to see and summarize the data want. Learn how to make dynamic reports with various controls and filters by making your Google Data Studio dashboards interactive.
Do you agree with our list of the top dashboard enhancements? Which ones will you use for your dashboards? Let us know in the comments below!