Combine Data from Google Analytics and Wikipedia in Google Data Studio

Combine Data from Google Analytics and Wikipedia in Google Data Studio

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

list of countries by population table

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.

adding data from wikipedia to google sheets

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.

Adding Google Sheets as a data source

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

data fields from Wikipedia in google data studio

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.

google analytics data using the extract data connector

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

the extract data connector

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.

blending data in google data studio

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.

a calculated field in google data studio

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.

a table showing data from wikipedia and google analytics

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.

How to setup a map with a metric slider in google data studio

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!

A map showing how you can use a metric slider in google data studio

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

Download Google Data Studio Templates

If you’d like to download one of my Google Data Studio reports as a template to use for your own data, you can visit Data Studio Templates and purchase one.

My Other Blog Posts

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