Blogging all things data

What can SEO reporting in Google Analytics gain from Power BI?

What can SEO reporting in Google Analytics gain from Power BI?

As I walked into the boardroom for BizData’s Power BI Desktop training session I couldn’t shake the feeling that, compared to the other attendees, I was the odd one out. I did not consider myself involved with the types of data heavy projects which I imagined Power BI being geared towards. Unlike many of the other attendees, I was not a client facing manager tasked with presenting project-critical data and did not fully grasp how I could implement Power BI day-to-day. I was there to understand the basic contours of Power BI, but was about to learn some skills that I could directly implement in my own marketing work. 

The Google Analytics Situation

As the morning rolled on, I was gradually familiarising myself with the workings of Power BI. Starting out with connecting data sources, I noticed an in-built Power BI Google Analytics connector. I had assumed that I already had all the visualisations that I would ever need in Google Analytics without the need for Power BI.  As anyone familiar with GA can attest, it offers many pages of graphs, numbers and tables covering all kinds of user stats underpinned by a customisable dashboard layout. 

It has so many pages of data that even those familiar with it can often get lost trying to find the exact reference they need. 

Dashboards and Custom Reports offer a way to cut through the data to some extent but feel limited in the analysis and data blending options they offer. Additionally, there is the added factor of Google Search Console and Google Ads being separate tools, with spotty integration into Google Analytics at best.

"I had become so accustomed to this state of affairs in SEO reporting that it seemed natural to me to get lost in GA trying to find a particular number or waste time switching between the Google tools. At this point, I started to suspect that Power BI could play a hand in breaking this status quo. "

As the training session moved on to creating relationships between datasets, I had another realisation. With the ability to pull in multiple sources into Power BI, what if the siloing of the Google tools was no longer a problem and I didn’t have to rely on the limited pre-built integration? 

Power BI for SEO Reporting?

The question for me now became, could I use the techniques I was learning in this Power BI training session to create something which could clarify or simplify my data situation in any way? As I familiarised myself with the Power BI visuals which come pre-packaged, as well as looking at the Power BI custom visuals which are available in the Microsoft Marketplace, ideas started to percolate.

The variety of available Power BI visuals was certainly an improvement compared to the standard slim pickings of Google Analytics.

the selection of visualisations offered in Google Analytics

The slim pickings of Google Analytics visuals

The Power BI training session concluded with a group exercise testing us on what we had learned throughout the day. Working with a sample dataset, we worked at trying to extract insights which would help answer a number of business questions which were also presented. It was a satisfying end to an informative day and I was ready to take what I’d learned and go to town on my Google data. 

I started off by using the built-in Google Analytics connector, but also pulled in data from Search Console and Google Ads. After cleaning up the data a little and setting relationships I was ready to put what I’d learnt at the power BI training session to use.

Baby steps with using Power BI for SEO

By exporting data for individual important keyword groups from Search Console, cleaning it up a little, creating the appropriate relationships and performing a little calculation, I was able to create a stacked column chart plotting the percentage of each page’s traffic which came through organically for each keyword. 

a stacked column chart in power bi used to present seo data

Through deliberate dashboard design and the use of the filter options, clicking into each bar would bring up further information about that particular page. This allowed me to quickly and easily compare the details of pages, as they were performing in relation to certain keyword sets.

The speed with which I was navigating multiple dimensions of data was simply not achievable with Google Analytics. Furthermore, the slicer function became a smooth and fast way to filter the graph based on whatever metric and whatever range I wished. Say I wanted to bring up all the pages for a certain range of bounce rates while also showing the average pageview time for that range. Easily done! 

a slicer function in power bi used to filter seo data

Continuing to experiment with Power BI visuals, I used the line and stacked column chart to overlay potentially useful data sets. For example, average bounce rate represented by column and average page load time represented by line with each page listed on the axis. Adding a date slicer into the mix created a way to zero in on the times when page load times were highest and see if there was any correlation with bounce rate on any particular pages. 

a line and stacked column chart visual in power bi used to present seo data

Another set of data points that I juxtaposed were landing pages and average pages per session. The idea was to see if the specific page people entered from dictated how much they’d be inclined to click into other pages. By adding in my historical Search Console data and connecting it in, I was able to create two gauges which would give me search Impressions and CTR data.

Slicing the whole lot by date allowed me to explore which pages had the most capture, accounting for change over time as well as understanding how it related to Google SERP performance. A shortcoming of this visualisation was the fact that the two gauges could not be sliced by time because Search Console does not export date data. All of these would have been cumbersome to do without Power BI and wouldn’t have offered the benefits of swift filtering and slicing. 

gauges and a line chart in power bi used to visualise and compare seo data

Future SEO Visualisation Prospects

To finish off I amused myself with the Enlighten Aquarium Power BI custom visual to see what my pages would look like as fish, with their sizes determined by page traffic volume. 

I am clearly only scratching the surface of what is possible to for SEO using Power BI. As I become more familiar with it, I fully expect to come up with more ways to get insights by blending different data sources and putting it together in ways Google Analytics simply cannot manage. 

The next steps for me will be to bring in on-page keyword analysis, combining it with user and traffic stats to see what it can teach me about optimising content and creating effective Calls to Action. This would be achieved by exporting the keyword data from a tool such as SEMRush and building new relationships with the data I already have going.  

Whether you're new to Power BI like I am, or already use it in your day-to-day work, BizData offers fantastic, 1-day instructor led Power BI training all year round to get you up to scratch on the fundamentals. Additionally, we offer Power BI Online Mentoring for that personal touch.