Home Google Analytics Merging Google Search Console and Google Analytics knowledge in Knowledge Studio

Merging Google Search Console and Google Analytics knowledge in Knowledge Studio

I’m a data freak, and I really enjoy exploring ways to analyze numbers to understand performance. I think it’s pretty impossible to love digital marketing if you’re not passionate about data.

I am also a subscriber to the saying “A picture is worth a thousand words” and I appreciate the challenge of distilling complex data to present a meaningful story. We used to use Tableau as a dashboard tool to visualize data, but over the past few years I’ve become a huge fan of Google Data Studio. The performance on offer is extremely impressive considering that it is (currently) free. If you haven’t tried it yet and love data visualization, you should definitely spend some time doing it.

It is easy to use and particularly easy to link with other Google properties. For example, it’s amazingly easy to connect to Search Console, Google Analytics, and Google Ads, and you can quickly create dashboards that pull data from all three sources to give you a holistic picture of your website’s performance through paid and organic channels.

However, there are some limitations to pulling data from multiple sources into a single chart (a “chart” is any of the reports available in Data Studio that contain tables and pivot tables). Fortunately, the very powerful Data Blending feature overcomes these limitations.

What is data blending?

By default, Google Data Studio Charts can only use one data source. This isn’t always a problem because you can have multiple charts on a single slide so you can see a lot of data in one simple view, but the ability to create a “mixed data source” allows you to have records in one Connect data source to the records of up to four other data sources. A single chart can then use the mixed data source to display data from all five data sources, which allows you to see much more information on that chart.

* Aside *, while most data blending consists of using multiple data sources on a chart, you can actually blend a data source into itself. A good example of this would be if you wanted to have a graph that shows the data of active users for 1 day, active users for 7 days, and active users for 28 days from Google Analytics. Annoyingly, analytics doesn’t allow you to have all of these metrics on one chart at the same time. Then, by merging the Google Analytics data source with itself, you can add all of the metrics to a merged data source and compare the metrics on a chart.

A good example of merging data sources is page performance.

Merging Google Analytics and Google Search Console data to analyze page performance

If I had to choose a tool to measure the performance of individual pages, I would go with Google Analytics. It’s fantastic to tell you pretty much everything you need to know about how users interact with a given page, including things like scrolling depth that I looked at before. However, it doesn’t tell you anything about the performance in the SERPs that Search Console data can help with. If you’re not using Search Console, sign up today – it’s another free and great offer from Google.

A powerful trio of Google Data Studio, Google Analytics, and Google Search Console can provide great insight into how content is performing on your website. I wanted to give an example of how by bringing this data together you can get a great report that otherwise wouldn’t be possible on a chart. For this post, I’ll assume a basic level of familiarity with Google Data Studio, but it’s very easy and even early Data Studio newbies should be able to follow the directions.

Frustratingly, Google Analytics and Search Console don’t play together as well as you’d hope, and that process starts with a quick fix …

1]Create custom field

To create a mixed data source, you need to “join” two fields. The frustration in this case is that by default, Google Analytics treats URLs differently than the Search Console. Analytics does not use the full URL while it does in Search Console. ie https://browsermedia.agency/blog/ecosia-versus-google/ (one of the most frequently viewed blog posts on our website) is reported in Google Analytics as “/ blog / ecosia-versus-google /”, but “https: //browsermedia.agency/blog/ecosia-versus-google/ “in the Search Console.

Although they both refer to the same page, the fact that they’re spelled differently means you can’t use them as a link key, making the holistic page analysis I’m looking for impossible. The solution is to either add “https://browsermedia.agency” to the Google Analytics data or remove it from the Search Console data.

Since I want the abbreviated form in my reports (for reasons of space), I usually choose to shorten the URLs in the Search Console data. To do this, you need to create a custom field I named ‘Short URL’ and use the following formula:

You’ll need to adjust the ’28’ to suit your particular domain – 28 will work for our site (although by my calculation only 27 characters need to be removed?). You can test that it is doing what it is supposed to by creating a quick table in Data Studio using the Search Console data source with Short URL as the dimension and Impressions as the metric.

You should see the URLs in the same format as a Google Analytics chart. Assuming this is the case, you are ready to create a mixed data source …

2]Merge data

As mentioned above, any mixed data source requires that at least two fields be joined. There are several ways to create a blended data source, but I usually go to Manage Related Data from the Resource menu:

On this page, all you need to do is select “Add Data View” and then select your Google Analytics and Search Console data to start the merge process. Since we want to study the performance of individual pages, we will use the “Landing Page” from the Google Analytics data and the custom “Short URL” field that we created above as a join key.

Then you need to add all of the metrics that you want to use for this mixed data source. For the report I’m trying to generate, the following works fine:

Data mix of Google Analytics and Search Console

You can edit the mixed data source at any time. So don’t worry if you forget to add certain metrics the first time you set it up – I almost always have to go back and add metrics when I start creating charts using the mixed data source. This will often break the chart, but a quick browser refresh will fix this and you will be ready to go again.

3]Create diagrams

You now have a very large data set that contains information from both Google Analytics and the Search Console. So it’s time to get creative and make diagrams that make the most of the combination.

You can use the mixed data source in any chart type, but I want to create a table to show the performance of pages both in the SERPs and after the users arrive on the page.

This can be achieved by creating a simple table chart with Landing Page as a dimension and adding the following metrics (columns):

Google Analytics Search Console Mixture

As shown above (a June 2021 summary), the first three columns contain all of the Search Console data, while the rest of the information comes from Google Analytics. I added some heat map colors to identify peaks and valleys, but sorted the table by pageviews because I wanted to focus on the most popular pages on the site.

I hope this could be a useful report for analyzing pages on a website.

It’s interesting to see how the data differs between Search Console and Google Analytics. This is not a flaw of the mixed data source and simply reflects the inherent data differences between the two platforms, which are complex and warrant a blog post of their own. In this particular example, the Google Analytics data shows the performance for each page for all traffic sources, so you would expect the pageviews to be higher than the clicks in Search Console. You can of course apply segments to the Google Analytics data if you want to investigate specific traffic sources.

experiment

The above example is a simple example of a fairly straightforward mix of data. As with most platforms, the way to learn the most is by generating reports yourself. Google Data Studio is definitely worth exploring, and the potential that mixed data sources offer is remarkable and is often the answer when you’re struggling to find the perfect chart to display the information you want to display.

Play along and see what you can achieve with data blending.

Latest articles

web optimization encourages oblique investments | Monetary tribune

With the flood of new investors on the stock market in a relatively short space of time, the regulator has stepped up its efforts...

6 causes Maxime Bernier needs you to vote for PPC

This article is part of a Narcity series that highlights the key points you need to know about where Canada's political party leaders stand...

PPC candidate apologizes for being requested to kill Trudeau – in some way

It could be the most insincere excuse in political history. Popular Party candidate and pot-backer Mark Emery was beaten up on Twitter Monday night after...

The state of search engine optimisation 2021 [New Search Engine Journal Research]

Do you love dates? Are you looking for help to successfully steer your SEO strategy and career in the right direction for 2022?Then...