In this blog post I’ll give an example of how you can combine Data from Google Analytics and Wikipedia in Google Data Studio using Google Sheets.

Below is a dashboard showing Google Analytics Users, Population data from Wikipedia, and a custom metric showing the number of Users per Million Population.

Why would you want to combine Google Analytics and Wikipedia Data?

You might want to combine data to understand some of your Google Analytics in context to the real world. In the example I use I wanted to see which people from which countries are more likely to visit my website.

So to do that I’d want to get population data into my dashboard.

Step 1: Pulling data from Wikipedia into Google Sheets

So for this example, I want to pull in data from this Wikipedia page; List of countries by population (United Nations).

To get it into Google Sheets, we use a simple formula.

I give another example of this in my previous blog post Use Google Sheets to pull data from Wikipedia into Google Data Studio.

=ImportHtml(“https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)”, “table”, 4)

Placing this formula in the cell A1 pulls in the data from the table in Wikipedia.

I also use the formula: =SPLIT(A2,”*[(“)

This separates any additional characters from the names of countries. For example China[a] becomes simply China.

Step 2: Adding our Population Data to Google Data Studio

So we can connect our Google Sheet to Google Data Studio using the standard Google Sheets connector.

Once we’ve added it we can see all the data fields.

However for this example, we will only be using Population (1 July 2019).

Step 3: Adding our Google Analytics data using the Extract Data connector

So for this example I only want the Country and the number of Users for the country. Therefore it makes sense to just use the Extract Data connector.

So I select Country as the only Dimension and Users as the only Metric.

I then set the date range to be the past 180 days and set it to refresh the extract daily.

I then add it to my Google Data Studio report.

Step 4: Blending the two Data sources together

So the next step is to blend these two data sources together. Using the Extract Data connector ( and not the regular Google Analytics connector ) makes the data more compliant and easier to blend together.

I simply join Country onto Country.

Now that we’ve blended the data we can build our dashboard.

Step 5: Creating a calculated field to determine Users per Million people

So what I’ve interested in finding out is which country has the highest number of Users per million people in their populations.

To do that I create a table with Users, Population and the following calculated field;

=SUM(Users)/(SUM(Population)/1000000)

It should look something like this when you create it in your report.

Now we have our table showing Users, Population and the number of Users per million inhabitants of that country.

So for example Sweden has 10 million people living there and 1145 website users. Therefore for every 1 million people there are roughly 114 users.

But of course, when we are dealing with countries we must follow the sacred commandments of Google Data studio and add in a map.

Step 6: Creating a calculated field to determine Users per Million people

The final step is to create a geo map showing Users per Million. But we’re going to use the brand new Metric Slider feature to make it a bit more interesting.

So with the metric slider we unfortunately can’t have optional metrics, but it’s fine for what we want to show.

For example, let’s see we want to see the cream of the crop of countries who visit my website.

We can use the metric slider to only show countries that have 50 users per million inhabitants and above.

We have a select group of elite countries. Shout out to Singapore (the cream of the crop), Sweden and the Netherlands for being the top three. And a shout out for Canada for representing the Americas. And a shout out to Northern Europe and Scandinavia!

Anyone, hope you found this blog post informative and maybe it might spark some ideas.

My Other Blog Posts

If you enjoyed this blog post you might like some of my previous posts.