Google Data Studio has been public for a while. It has already changed PPC reporting routines of many people. But how does Google Data studio compare with Microsoft’s Power BI which has been on the market for few years? Read more to find out.
This article will NOT tell you which tool is better or the best because this answer simply does not exist. The article is looking at the data manipulation/visualization issue from multiple perspectives.
The paragraphs logically follow the common reporting stages step by step.
This article was written based on personal experience while trying to setup the exactly same dashboard in both tools.
NOTE: Power Query (which is the main focus of this website) can be also find inside Power BI for Desktop under “Edit Queries” button. Yep, that’s right. The same Power Query you have been using in Excel. So when you see term “Power BI” in this article, you can almost swap if for “Power Query”.
Easy to Use Out of the Box?
No doubts. Google Data Studio is easier to use out-of-the box than Power BI. Data Studio is simply more intuitive, it shares UI elements with other Google services like AdWords, Google Analytics or Google Tag Manager so you are not really looking at completely new thing when playing with Data Studio for the first time. It also has less features then Power BI which means that you can break less things.
Basically, if you score yourself as below average Excel user, you will have more difficulties with Power BI than with Google Data Studio. If Excel or Power Query is already your daily bread, you should be fine with Power BI.
Data Sources & Integration
Google Data Studio
If you you are completely hooked up to Google’s services, you will not probably have a single issue with Google Data Studio. Most of Google Services are fully integrated into Google Data Studio such as AdWords and Google Analytics. At first sight, everything looks alright with AdWords, the issue starts when you want to add few accounts from MCC structure to one chart or table. Not possible. Only 1 account can be used as 1 data source. 2 accounts = 2 separate data sources = 2 separate charts. Oops (at least for now). I am pretty sure Google will eventually add support for adding multiple accounts from 1 MCC as 1 data source.
Other sources include BigQuery, SQL, file upload, Google Sheets. DCM, DFP and some more. What’s still not supported is DoubleClick Search but that’s a problem only for minority of users.
Update on 2017-09-30: Starting from September 2017, Google Data studio supports external connectors such as SuperMetrics or Funnel which allows you to directly import data from systems OUTSIDE the Google environment. This is a BIG DEAL for Google Data Studio. On the top of that, SuperMetrics connectors allow you to blend data into 1 table so you can e. g. see AdWords cost and Bing cost in 1 table or in 1 chart. However, all of this comes for a price.
You will not find much support for Google’s service in Microsoft world. That’s no surprise. Forget native support for AdWords or Google Sheets. What’s a bit surprising is support for Google Analytics (though, the connector is in beta).
So how do you get around the missing support for AdWords? The answer is SuperMetrics. With SuperMetrics, you download data from nearly all advertising systems (AdWords, Bing Ads, Yahoo, Twitter Ads…) to Google Sheets as flat tables and then connect the Google Sheet files to Power Query which residing inside Power BI. Full list of supported platforms by SuperMetrics is here.
Yes, using another service as intermediary is an extra step which may not be for everyone, especially when you need to pay around 50 EUR/per month for SuperMetrics but you can get really creative later in the “reporting funnel”.
Now speaking about natively supported data sources in Power BI. The full list is here. It’s rather long list. The paradox is that most of PPC marketers will be just fine with GA & Google Sheets with AdWords Scripts/SuperMetrics. Among other sources, you will find Microsoft Analysis Services (in-house data cubes created by your BI team), SAP, SQL databases, Facebook, Salesforce…
Power BI also let’s you to use files stored on your PC (TXTs, CSVs, XLSX…) or even a folder of files. You don’t have to upload anything anywhere, you just need to install Power BI Personal Gateway and it will pull the data from your PC during report refresh fully automatically. The assumption is that your PC is online at the time of refresh which may not be always the case.
Data Cleanup & Transformation
Google Data Studio
This is where Google Data Studio is behind Power BI. Basically, Google Data Studio is expecting to get nice & clean data which just needs to be visualized. You are not able to do any cleaning before loading to Google Data Studio. By cleaning I mean, e.g. converting campaign names to lowercase, filtering just for some data, doing some find & replace and so on.
If you are freelancer who is just feeding AdWords data to Google Data Studio, you will be probably fine until you need to e. g. add some internal CRM data which needs some cleaning before adding to your report.
You will find pretty much same Power Query inside Power BI as in standard Excel. In fact, Power Query inside Power BI seems to be slightly ahead in terms of updates. New functions seems to be added to Power Query inside Power BI first, then they are cascaded to Excel in about a month.
Having Power Query available means that you can do standard operations such:
- Filtering & cleaning raw data
- Converting special characters
- Changing letter cases
- Renaming columns
- Deduplicating the dataset
- Combining 2 tables into 1
- Looking up values from reference tables
- Splitting text value into separate columns (e. g. campaign name)
- and so much more…
Complexity & Customization
Generally speaking, Power BI let’s you do more complex operations. There are not many things you cannot do in Power BI. You can append tables on the top of each other, effectively meaning that you are able to see e. g. Bing & AdWords data in 1 charts (unlike in Google Data Studio) OR you look up values from reference tables via Merge function (basically VLOOKUP). You can even create your own data model including relationship between tables based on a common key. Furthermore, Power BI supports extremely long list of functions while Google’s list is way shorter. You can use the functions to e. g. create custom columns for segmentation purposes. What Data Studio supports and Power BI does not is RegEx.
The fact that it’s very difficult to plot (=append) data from 2 advertising platforms to 1 chart in Google Data Studio, is the deal breaker for me at the moment. I am not saying it is not possible, it’s just too difficult & cumbersome for an average user to achieve. If you are still interested, here is a quick draft of solving the problem. You can get away by using AdWords Scripts, Google Sheets & QUERY function in Google Sheets:
- You would start with pulling data to Google Sheets via AdWords Scripts (or via SuperMetrics). If you are unsure how to write AdWords Scripts, head to PPC-Scripts.eu which can help you with getting the right script. 🙂
- You will probably end up with having performance on multiple sheets, 1 sheet = 1 account. Of course, depending on the script you use at the end.
- Then you would use QUERY function inside Google Sheets (more info here and here) to stack the data onto each other. (If you are able to get all the performance data to 1 sheet via scripts, you can skip this step).
- Then you would select Google Sheets as data source instead of AdWords and used the sheet with stacked data.
Note that 1 Google Sheet file can only hold 2 million cells. It sounds like a lot but it is not. I have reached the limit many times. Especially when downloading KW level data day by day.
As I mentioned at the beginning of this articles, I created 2 dashboards – 1 in Power BI, 1 in Google Data Studio:
You can see that I was not able to easily create the same view in Google Data Studio in Power BI due to lack of Append functionality. The raw source data is here.
Both Google Data Studio and Power BI offer similar set of charts and tables, including custom colors, font sizes etc. You won’t be disappointed with neither of the tool. I have to mention, the custom colors in Power BI do not always work. So when you setup X colors and then publish your dashboard online, your colors will be set to default ones anyway.
One feature where Power BI excels is the dynamic relationship between all charts and tables on the page you are currently at. You interact in real time with your dashboard by selecting values (e. g. “Bing”) in your dimensions (e. g. “Network”). In real life, it looks something like this:
This is something you won’t find in Google Data Studio. This interactive feature allows you to easily see relationships between metrics (e. g. Impressions Share vs Average Position vs Clicks across multiple charts). At first, this feature does not like a big deal but start using it for few days and then try to live without it. Then you will realize how important it’s for you day to day analysis. You can try yourself, just go here and select Bing.
Another thing I noticed is that default Power BI charts/tables tend to look visually nicer than defaults in Google Data Studio.
Where Google Data Studio is already ahead is the “time comparison” feature. You can easily compare current to previous period – something which is only semi-friendly in Power BI at the moment. To an limited extent, you can use “Quick Measures” wizard to create custom comparison metrics for Year-over-Year or Month-over-Month calculations but e. g. Week-over-Week is missing – that’s where DAX needs to be used. Also, Quick Measures currently work only with Date hierarchy created by Power BI. That’s a BIG minus for Microsoft.
Google Data Studio
Despite the fact, it’s still in beta, I consider Google Data Studio as more reliable since the important sources (AdWords + GA) are directly integrated, there should be no issues with refreshing the data.
Since Power BI kind of needs Google Sheets as intermediary, there is higher likelihood of something going wrong. More things in puzzle means more things which can eventually break. Also, when you create complex queries, they are likely to fail one day despite the fact all the data is ready to go.
My real live experience is that Power BI dashboards sometimes fail to refresh. When I say sometimes, I mean once every 2 weeks. The common issue is that data pulled via SuperMetrics to Google Sheets does not refresh properly for various reasons. I found AdWords scripts more reliable for pulling the data but it’s just 1 data source of many so I still need to use SuperMetrics e. g. for getting Bing Ads data…
Pricing & Sharing
Google Data Studio
It’s simple: FREE for everyone & forever. You can share your dashboards similarly as with Google Docs & Sheets, you can setup view or edits rights on user level. The shared report can be shared via a link. Sample is link is here: https://datastudio.google.com/open/0BxCBbV34rkcEOUJJZDNaNG5PT2M
Another weak point of Power BI. Generally speaking, you need to pay if you want to manage access rights and sharing properly. The price is currently 9.99 USD per user per month.
However, there is also a free limited version which lets you do 99% of operations (I am using the free version and I am completly fine). The only caveat is the fact that you cannot manage users. The only way to share the data is to go fully public – anyone with the link can see and access the report. As long as you share your dashboards with right people, you should be fine. If anyone wants to steal your data, they will always find a way to do it regardless how well you dashboards are shared.
The Power BI link for sharing looks like this: https://app.powerbi.com/view?r=eyJrIjoiOWE0N2ZkZjAtMzA2ZS00MjJlLWI3NTAtZDA1OTg5YTMzMDhjIiwidCI6ImUyNGQ0N2FmLTVjNjMtNDIxNC04MTdhLTE4ZmUwN2ZiMWU2NiIsImMiOjh9
So which option to choose?
- Don’t want to pay for anything
- Have nice & clean data already
- Don’t need to do any data cleanup, combining & merging before vizualising
- Don’t need to see data from different sources in one chart
… then stick with Google Data Studio
- Need to do complex data transformations
- Want to see e. g. CRM & PPC data in one chart
- Are willing to pay
- Need to visualize data from advertising systems outside Google’s world
… then stick to Power BI & SuperMetrics combo.
Everyone knows Google Data Studio but not everyone knows Power BI so I am attaching a “preview” of how Power BI environment works.
Workflow in Power BI
Power BI has 2 “versions” – Power BI for Desktop (a Windows application you need to install) and Power BI Online (something like Google Data Studio).
The basic workflow is:
- In Power BI for Desktop, you prepare your dataset in the Power Query
- Then you prepare your “dashboard pages”
- After you are done with your dashboard, you publish it to Power BI Online
- In Power BI Online, you need to schedule refreshing of the dashboard (up to 8 times a day)
- Then you setup sharing (or generate the public link)
I am also attaching few screen shots from Power BI for Desktop
Adding data sources is similar to Power Query in Excel:
“Edit Queries” aka Power Query window. Different colors but nearly same functionality as Power Query in Excel:
There is also a section where you can define relationships between tables:
And finally, the dashboarding section:
You can get Power BI here.