Google Data Studio vs. Power BI

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 than 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.

Power BI

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.

Power BI

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:

  1. 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. 🙂
  2. 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.
  3. 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).
  4. 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.

Visualization

As I mentioned at the beginning of this articles, I created 2 dashboards – 1 in Power BI, 1 in Google Data Studio:

  1. Google Data Studio dashboard is here.
  2. Power BI dashboard is here.

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.

Reliability

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.

Power BI

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

Power BI

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

Summary

So which option to choose?

If you:

  • 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

If you:

  • 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.

Footnotes

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:

  1. In Power BI for Desktop, you prepare your dataset in the Power Query
  2. Then you prepare your “dashboard pages”
  3. After you are done with your dashboard, you publish it to Power BI Online
  4. In Power BI Online, you need to schedule refreshing of the dashboard (up to 8 times a day)
  5. 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.

13 thoughts on “Google Data Studio vs. Power BI

    1. Hi Gilbert, great tip…
      I will update the article. One question still remains: how do you get Week-over-Week calculation?

      I already created MoM, hoping that I will just adjust the DAX formula to create WoW (Week starts on Mon or can rolling 7 days vs 7 days) but the generated DAX formula cannot be easily edited since there is not WEEK parameter available…

      For MoM, I have:

      Impr MoM% =
      IF(
      ISFILTERED(‘Dates'[Date]),
      ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy.”),
      VAR __PREV_MONTH = CALCULATE(SUM(‘Dates'[Impr]), DATEADD(‘Dates'[Date].[Date], -1, MONTH))
      RETURN
      DIVIDE(SUM(‘Dates'[Impr]) – __PREV_MONTH, __PREV_MONTH)
      )

    2. Is there any chance where i can import GA analytics data with out getting biased with any coloumn category. now whenni select gender and age the overall data is getting biased and decreasing total number of users.

      1. Hi Adam,
        I had to test first to see what’s happening when importing audiences data like gender. I think the API only returns users for whom GA knows gender and does not show something like “unknown” or “other” category when gender is not known.

        I tried to import gender data with PowerBI and I got same result like you – less users. My only suggestion is to run 2 queries. 1 with your gender split, 1 for “overall user” count. Then merge them together and assign total-known genders difference to “other gender”.

        This is not a super easy task in case you are just starting with PowerBI.

    1. Hi Agencia SEO Madrid,
      for agency level reports across multiple clients, Power BI is much more comprehensive tool allowing you to plot multiple clients into chart…

      DZ

  1. Hi Dan,

    First of all thank you for wonderful comprehensive article on comparing both Visualization tools.

    I’ve just started using the Google DataStudio, however, I couldn’t find the connector for Bing Ads within Studio. Is this something you’ve pulled through using Supermetrics for sheets?

    Best,
    Kushal

    1. Hi Kushal,
      glad to hear you liked the article!

      Yes, you are right. You have to pull BingAds data via SuperMetrics or e.g. Funnel can do it as well. However, I don’t really have personal experience with Funnel since they are a bit expensive IMO.

      DZ

      1. Thanks a lot, Dan. I really took a good pace in creating DataStudio dashboards, and enjoy working with it.

        I wish Google could come up with more connectors. Something I desperately need is in-house Social as well as CRM data connectors.

        Best,
        Kushal

        1. You can now use SuperMetrics connectors within DataStudio (https://supermetrics.idevaffiliate.com/idevaffiliate.php?id=1060). They will get you social data, however, it’s a paid service.

          One thing about PowerBI I have not mentioned in the article is that you can use DAX formulas to create super customized metrics such as “clicks last month” or “clicks this month” and put them on to a same line a table. This is something, you cannot do in DataStudio. I miay write an article about this in future.

          DZ

  2. My partner and I are developing a Joomla website for a certain project. We are using separate computer and separate hosts (localhosts). My partner has made changes to the graphical design and database structure so I have copied the directories (folders) to my computer. In doing such, I have encountered this error message “Database Error: Unable to connect to the database:Could not connect to MySQL”. Please help. Thanks :)).

    1. Sorry, but this is hard to troubleshoot without having the exactly same setup like you have on your pc. DZ

  3. Currently Data Studio has proven much faster and easier to design in and creates High Quality graphics with filters and charts that all update together, if thats what you want. Or keep them independent.

    Trying a new Microsoft platform Power BI was just too frustrating, and I am a programmer and expert at Excel. I really think Data Studio is much preferred to dash boards or Power Pivot / Querys in Excel. Its on the web, but much nicer and more appealing to users ! Im sure if yuo are an expert in Power BI you can make it sing… data studio is my pick hands down for design appeal and data manipulation.

Leave a Reply

Your email address will not be published. Required fields are marked *