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;
- Homicide Rate
- 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.
My Other Blog Posts
If you liked this blog post you might enjoy these other articles I’ve written.