Power BI and Google Analytics

One of the more underused features of Power BI is the Google Analytics data source.  Working closely with marketers and agencies I continue to see them struggle with getting and reporting on their data from Google Analytics and it should not be that difficult.   With Power BI or even with Power Query in Excel you can easily get, model and visualize this data with only a few clicks.

Let’s walk through how easy this is, first step is to get the data from Google Analytics which is done by selecting get data and choosing Google Analytics and logging in with your account.


Once logged in you will see a list of sites that are being managed under your account and from here you drill down into your site and select the data you need.  For this example lets say I want look at the number of hits on my site and be able to separate new users from existing users.  For this I will need to get hits from session, User Type from User and Date under time.



Now that we have our data elements click load and the data will be loaded into Power BI.  To add a little depth to my report I did two things.  First I imported a date table, and if you don’t already have one here is a great blog to create one in power BI.  Next I created a custom column in my date table to specify records in three categories, “Last 30 days”, “Greater than 30 days” and “Future.

DAX : Last 30 Days = if(Now()>=[Date], if(DATEDIFF([Date],NOW(),DAY) <=30,”Last 30″, “Greater than 30″),”Future”)

Last I created a measure to show goal of hits by using hits from the previous year with 20% increase.

DAX: Hits Goal = CALCULATE(SUMX(‘I Predictus (2)’,[Hits]),SAMEPERIODLASTYEAR(‘G_Calendar'[Date]),all(G_Calendar)) * 1.2

Now I can create a couple simple visuals one showing overall hits in the last 30 days to goal and the other daily hits by user type against overall goal.


This is just a small example of the data available via the Google Analytics data source.  If you are using Google Analytics then I suggest you start using this data source and start developing your reports and dashboards.  Oh and how could I forget to mention that once you have these developed you can schedule the refreshes to be automatically done.

Power BI Export Data

If you missed it last week, Microsoft released a Service Update for Power BI , and a long awaited feature is now here.  You can now export the data that is behind a visual and this will close a huge gap for Power BI when compared to many of it’s competitors.  For myself this is beyond huge as all I have been hearing from clients for the past 5 months is, “When are exports going to be available?”. The answer is finally here and it is very simplistic to use.

For any visual all you have to do is select the menu in the top right and choose export data.


It is that easy and you will get a download the data in excel for that visual.  One note is that it does not drill down into the data.  The export will have at the same level the visualization is at.  For example in the above visual the following are the results.


However if you choose a visual with more detailed data such as a table the below would be the visual and result pair.

image   image

Or in a column chart

image image

Happy exporting everyone!

Is Data Visualization still just a buzzword?

If asked this question two years ago, my answer would have been a resounding “Yes.” At that time, the focus was on displaying data using visualizations that were pleasing to the eye to help make a sale, impress the “C” levels, and to put a check mark in the box of capabilities. But times, and needs, have rapidly changed.

The tools available just two years ago were cumbersome to work with and the landscape only had a few major players. Fast forward to today, and the landscape is full of choices in both on premise and cloud based tools. It’s not just the toolsets that have changed however. It is also the maturation of best practices that have brought data visualization from a buzzword to a necessity for all companies, large and small.

So what are some of the best practices? How do I make data visualization succeed for my organization?

Here are a few keys to ensuring success.

1. Use the right visualization.

It’s a lot like when the newest iPhone comes out, and everyone races to be the first to get it. Only after the purchase however, you realize that there wasn’t that much of a difference from the previous version and you gain no productivity. Don’t be in a rush to use a new visualization just to say you can use it. Make sure that it tells the user something and is organized in a way that they can learn quickly. Nothing will kill a dashboard or report more quickly than ineffective visualizations.

2. 10 seconds to learn

The key to any dashboard or report is that the consumer must be able to have at least one takeaway in the first 10 seconds. This will lead to a higher adaption rate and make your audience come back begging for more data.

3. More isn’t better

Don’t overwhelm your audience with a dashboard or report that has too many visualizations. We have all seen demo dashboards with 10, 15 or even more visualizations. Although it looks impressive, it’s best to ask yourself questions such as: ‘Did I learn anything?’ and / or ‘Did it tell me a story?’ Most likely your answer will be no.

4. Tell a Story

Make sure that the data is always telling a story and not just being displayed for the sake of displaying. This is often found in situations where an attempt has been made to move an operational report into a visualization tool. Operational reports have their place in the story but it should be 2 or 3 levels deep with supporting visualizations first that have a drill path to the transaction level details.

5. Use Files/Slicers

Don’t create multiple copies of the same report just to show a different product, category or client. Use filters and/or slicers to help slice and dice your report and give your audience one place to go to get all their data. This gives one other benefit that individual reports don’t give and that is aggregate values across multiple products, categories and clients.

Take a look at the two reports below and try to apply the keys to data visualization above.

Report 1.


Report 2.


Did you notice that each report has the same data? Were you able to learn more from Report 2? While both reports have the same data, the right visualizations makes a difference in immediate comprehension. Using column charts to show trended data over time verses a bar chart allows the eyes to easily move from left to right and see the patterns. Next, tree maps are a great replacement for the age old pie chart, as not only do they show large amounts of data in a readable format, they also take your eyes in a progression from left to right as the size decreases. Additionally, funnel charts tell a much more concise story compared to a bar or line chart and draw you to not only see the values decrease, but also creates a visualization that quickly shows the disparity between values.

What’s next? If you haven’t already begun to adapt the concepts of data visualization within your organization, now is one of the best times to start. There is a right tool out there for everyone. If you find yourself wondering where to begin, reach out to a provider already offering data visualizations in your area of expertise. You don’t always have to recreate the wheel to take advantage of the areas of efficiency that they offer.