Google Data Studio Pivot Tables – Fully Explained

Pivot Tabes in Google Data Studio give you the ability to display your data in a table with multiple dimension at the same time.

This gives you the ability to use Data Studio for Data Exploration but also it gives you a new ability to display your data in this Dashboarding Tools.

In this video we’re gonna take a first look at the pivot table functionality within Google Data Studio. Hey there and welcome back to another video of Measure School teaching you the tech tools and tactics of today’s digital marketing world. My name is Julian and we are live right now to take a first look at the pivot table functionality within Google Data studio. 

Now this was just recently announced really just yesterday and I had little time to play around with it, nonetheless, we want to take a quick look at what we can do within Google Data Studio.

What are Pivot Tables in GDS?

Now if you’re not familiar with pivot tables, they are basically another way to look and slice and dice your data within, normally a spreadsheet tool so if you are working with Google sheets or Excel you might be familiar with pivot tables. They are very powerful when it comes to really quickly exploring your data because you can input multiple dimensions and multiple columns really and slice and dice the data within one table. Now within Google Data studio we didn’t have this functionality beforehand we actually had to make use of the tables within Google Data Studio and if you wanted to slice and dice the data a bit further then we would need to do this in a spreadsheet tool like Google sheets. So today we have that functionality built into the Data studio, how cool? 

So let’s take a look at this new functionality and jump over here to our demo. I have a little Data Studio sheet that I’ve prepared here and this is connected just to the demo data that we have available from Google Analytics. We obviously have a new Data field on the new visualization field which is the pivot table. We’ll take a look at this in a second. Now the first thing that I wanna do is actually draw a table here. And this table is actually what it says it’s a table, so you have on the one hand your dimension here and you have your metrics inside of this table with your sessions.

Now you could put in multiple metrics so we could put in whatever, users for example, and expand this. But what if I wanted to actually, dig deeper into these dimensions? What if I wanted to see which countries these people are actually from? And this is not easily done with a table here because we would need to filter the data or have multiple tables in order to find out who is actually here from the USA, who is from the UK for example and therefore a pivot table would be something that I would use within Google sheets. 

The Features

Now we have that capability within the interface so if we draw this here, I’m just gonna make this smaller, it will pull in the data it takes a while, I noticed, so now we have this data available as well.

Row Dimensions

Now you obviously would need to choose our dimensions first so, if we would compare this to a medium here, let’s say we don’t want the source up here we want to additionally actually have our country we would type this in as well, now this would hopefully be pulled correctly. Then we have that data available here spliced by country, as well as by the medium, so if you go to the view here it actually makes more sense and you can see it better. So here we have our new table and once we scroll around we can actually see that we have this nice visualization which is not possible here in the normal table and we can find really quickly which source, of which medium in this regarde, brings traffic. 

So for example, we have a lot of traffic from CPM hospital, whatever that means in this demo report, but there is no traffic from India for example, or we have here Chinese traffic. Ok well anyways, what you can do and this is a bit tricky I guess because you can’t really see the scroll table here but, you can go left and right and also up and down, because this table is actually pretty big, and then spot trends so for example we have organic traffic from different countries but, for example, here we can find an outlier from Ethiopia we don’t have any organic traffic. This is really cool because we can now also explore our data a bit more in-depth with these different dimensions without building too many tables, because going into a new dashboard experience within Google Data studio you oftentimes need to already know your data very well. 

So it hasn’t just been a good tool for data exploration but I can see a pivot table being something really cool to use for data exploration outside of something like Google Analytics for example. because I can now pull the data in dynamically through the data sources. I wouldn’t have to use the spreadsheet software anymore, if I’m very familiar with what data is actually going in so I don’t have to clean it in that sense. Then we have that available in this report as well and we can actually pivot different dimensions here but only two so we can choose different rows and columns.

Source

We can actually add a dimension to this row column for example as well. So let’s say we would also like to have the source, not only the medium, so here we go and that will then be pulled in.

Column Dimensions

Oh this always takes a while and, now we have the medium and the source and all the countries and we could obviously also put in a column dimension as well. So let’s say we would actually like to know this by City. Now it gets really complicated, well the table gets really big, but then we would be able to see who comes from the United States and which city so we can dig deeper into the data. This is really the case when you want to, okay there’s an arrow here we need to have a smaller data set now. 

How could we make this a bit smaller? Country, city, let’s say is there something like states that we could choose, no? Hmm, continent is there, like yeah continent, that should be good. Let’s see if that works. Okay and yeah and then you can look into the data, so for example here, we can see our continent. Let’s actually drag this up. Okay now this is all calculated in the cloud. So, here we have America so let’s do this data again. We have the Americas and then we have all the different countries and then we should get to Europe at some point. 

Here’s Europe now and most of the people coming from Germany to this data account that we have in here. Now the other thing this behaves like a table normally you can up here choose the control filters that you might have implemented so if you only wanted to look at organic medium you could do this as well. Then it will all be filtered accordingly. Now again this is pulling in a lot of data and correlating a lot of data so it might take a while to actually put in.

Now we are only looking at the organic source, as we can see here, and the different search engines in this case pivoted by the continent and also the country. So I hope this gives you a few ideas on how you could be using this in a sense of taking your data, putting it together, it just gives you a little bit more control and a little bit more creativity maybe once you are looking at this data. 

Now not a lot of people like to have actual tables on their dashboard, which I can understand, but if there are specific questions that you want to answer via a table and you can’t do it with a normal table then look into pivot tables and what they exactly can do for you. I think it’s a very powerful feature that has been added to the Data Studio. Now let’s take a quick look at the system error that happens. 

Okay, unable to this is a cloud software in the end so we see that we have errors still happening. This is still in beta, so I guess we are facing some stability issues. I hope we can get this resolved in the few coming months. I know that they are frequent updates and I know that Google is pushing this product really forward because they know that the suite of analyzing data for measurement, which is done by Google tag manager for example or the new tracking codes that just came out up to gathering the data in Google Analytics, but then also analyzing it and making it useful to people can be real well done with Google Data studio. 

They’re investing a lot of their energy into a dashboard in software just like this one so they’re bringing out new features and they’re bringing out fixing the bugs hopefully and that’s what I would expect as well from this product. 

Now just to show you a bit more about this pivot table, although we can’t see it right now, and we will just reload and see if we can refresh this. We see that we can also sort this table obviously, so for our first row and our second row and our columns we can sort them and we can also choose a custom date range.

Adding Filters

This is possible with every of these visualizations but, we can also add a filter. Now, one thing that I saw in the doc in the post here that was published by Google, in the Help section. There are some drawbacks to this pivot table, so you can’t do anything that you would do in Excel or in Google sheets. You can actually only have one pivot table per page, so obviously it’s resource-intensive. 

The second thing is that it doesn’t put over in the pages. So normal pages would have different tables and you could filter and then they update automatically in these different pages. This is also not possible and you can also not use this metric filter. So if we would put in a filter here and say only include, I don’t know, sessions that were, let’s try this out, sessions that are over than 1000. Let’s save this. We should get an error here, that is correct, because we can actually not use these filters that are filtered by metric within our pivot table. So if you get rid of this, they should upload.

Summary

Okay, I hope this gives you a little bit of an overview of how you could be using pivot tables. I just wanted to show this new functionality. Obviously, this is a very controlled context and we are not building out a dashboard here that actually is something that we would use for clients but the functionality is now built into Google Data Studio and I hope you are able to use it for your implementations of a dashboard as well. That’s already it with this little overview first look at Data Studio pivot tables. Now if you liked this video, please give us a thumbs up and also subscribe to the channel right over there because we’re bringing you new videos every week. Now my name is Julian, till next time.

JOIN US!

Master Data & Analytics with Measuremasters

Exclusive Courses & Workshops | Ongoing Troubleshooting | Support Resources, Tools & much more
guest
0 Comments
Inline Feedbacks
View all comments

Blog Categories

Join 30,000+ marketers getting exclusive resources and our latest content!

now it's time to

Start measuring like a master

Itching to jump into the world of MeasureMasters? This is what you have to look forward to.