So something I’ve been interested in doing for a while is mapping Canadian provinces and US states in Google Data Studio. New features added to Google Data Studio have allowed me to do that and I’ll give you a quick overview of how you can build something similar in Data Studio.

Below is a video showing how you can use the Data Studio report.

Here is the actual Data Studio report that you can interact with. Please note that in order to view and interact with the Google Map you’ll need to open the report in Google Data Studio. You can click this link to view the report; https://datastudio.google.com/s/iBHyMtzn8g8

Building the Report

So there are a couple of things I’ll show you how to do in order to build a similar report.

The first is to create a parameter with the names of the various metrics we’ll want to use. The data is mainly from Wikipedia.

The data I’ve included is;

• Population
• Homicide Rate
• GDP
• GDP per capita
• Human Development Index
• Total Area

Below is what the data set looks like in Google Sheets.

To be able to switch between metrics on our Google Map, we need to create a parameter showing the names of each of the metrics we want to use.

I wrote a previously blog post showing in more detail how you can create a parameter to be able to switch between metrics on a Google Map in Google Data Studio; How to change Google Map metrics using a Parameter in Data Studio.

The first thing is to create a parameter. We will name this map_parameter.

Next, create a calculated field in which the formula is simply the name of the parameter as shown below.

Next, create another calculated field using a CASE statement. Below is the CASE statement that you can copy and paste.

CASE map_field
WHEN 'Homicide Rate (2018)' THEN Homicide Rate (2018)
WHEN 'Population (2019)' THEN Population (2019)
WHEN 'GDP (million USD)' THEN GDP (million USD)
WHEN 'GDP per Capita (2018)' THEN GDP per Capita (2018)
WHEN 'Human Development Index' THEN Human Development Index
WHEN 'Total Area (km2)' THEN Total Area (km2)
END 

Here is what it should look like in Data Studio.

Using these three custom fields we can have a map in which we can change the displayed metric.

Create a CASE statement to separate North America into Regions

The next thing to do is to create a CASE statemen that breaks up North America into regions.

Here is the CASE statement below.

CASE
WHEN Country IN ('United States') AND State / Province  IN ("Alaska", "Hawaii") THEN "Alaska and Hawaii"
WHEN Country IN ('Canada') AND State / Province  IN ("Ontario", "Quebec","Nova Scotia","New Brunswick","Manitoba","British Columbia","Prince Edward Island","Saskatchewan","Alberta","Newfoundland and Labrador") THEN "Canadian Provinces"
WHEN Country IN ('Canada') AND State / Province  IN ("Nunavut", "Northwest Territories","Yukon",) THEN "Canadian Territories"
ELSE "Contiguous United States"
END 

It separates out Canadian Provinces from Territories and Alaska and Hawaii from the Contiguous United States.

Shown below are the outlying states and territories excluded from our map.

We can use the metric slider to filter by whatever metric we’ve chosen. In the screenshot below the chosen metric is Population, so I’ve used the metric slider to filter out up to a certain value. Only the top four most populous states remain.

You can also make the Google Map full screen to get a better look at the data. Below is a map showing the Human Development Index scores of US states and Canadian Provinces.

My Other Blog Posts

If you liked this blog post you might enjoy these other articles I’ve written.