In my last blog entry, I created a tutorial on Google Data Studio; Google Analytics dashboard solution offered for free to the general public.
While I was impressed by the tool, I was saddened that they didn't include more powerful map capabilities. Don't get me wrong, their map features are generally in line with what dashboard solutions have to offer. I was just really hoping that they might include some of the magic of maps through Google Fusion tables. To demonstrate their awesome capabilities, I've created a tutorial which walks the user step by step through creating a basic map, a merged map and then a multi layered map. See image below for the beautiful completed visualization (not currently able to view interactive map on mobile).
The Tutorial Description
In this tutorial I have continued with the Zillow data set used from the Google Data Studio tutorial. In the previous tutorial we were able to see a map of average house prices by state. We were also able to see the average % of houses sold at a gain in the US. When looking at the data, one question that I had was:
I thought that a neat way to explore this further would be through a layered Google Fusion map. In the map above we are able to see a heat map of the average price of a home by state and overlay it with the relative performance of % of houses which sell at a gain.
While google fusion tables are incredibly powerful for the end consumer, they have a number of steps to set up the map. The tutorial steps below will guide you through this process in very granular detail. At a high level, the steps will include creating multiple customized maps based on data or shape files, merging them and layering them to create the final visualization.
The Tutorial Steps
1. Download the Fusion Tables Google Drive application
2. Download the sample data set needed to create the sample.
Note: if you have trouble downloading the file from github, go to the main page and select "Clone or Download" and then "Download Zip" as per the picture below.
4. Create a Fusion table based on the US Census shape file using the website Shape Escape. Click "Browse", select the zipped shape file from step 3 and select "Upload". Leave the web page open until it displays a "Status Complete" message and a link to your new Fusion table.
5. Click on the Fusion table link and save the file to your google drive.
6. Click on the"Map of Geography" tab and straight away you should see an overlay of the state boundaries on the google map like below. Save the map, and rename it to "Shape Map".
7. Create a table based on one of the files downloaded in the Zillow Data in step2 "Zillow_Summary_Data_2017-06_For_MAP.csv". Select "File" > "New Table" and select the data you downloaded from step 2. Keep all default settings and upload.
8. After the file is uploaded, a new Fusion table is created for you with the name of the source data file. Click on "Map of Geometry". This will kick off geocoding using "State Name" as the location. When it is complete, your map will look as below. This shows that the data has been properly mapped to each of the states. Save the map, and rename it to "Zillow Data".
9. Create a merged table of our US Census shape file and the Zillow data. We now want to create a merge of display the values of this data in the form of a heatmap going by the state boundaries from our map in step 6.
10. Select "File" > "Merge" and select the Fusion table called "Shape Map" from the map created in step 6.
11. Merge the US Census and the Zillow Fusion tables. Match the data based on the "State" column in the Zillow Fusion table and the "STUSPS" column in the US Census Fusion table.
12. Select the fill of the state shapes. Select "Feature Maps" > "Change Feature Styles". In the popup window select "Fill Color" > "Gradient" > "Show a gradient". Select the column "Avg Home Value" and then add the upper limit of 700,000 to the upper range.
13. See the resulting merged map with the heat map representation of average price. Save your Merged Zillow-US State Shape Map. Please call it "Zillow Data -Layer 1". Your resulting graph will look as below.
14. Share the map by clicking "Share" in the upper right corner of the map. Select "Public on the Web - Anyone on the internet can find and view". Go back to your map and select "Tools" > "Publish". Copy the embed code html, you will need it for layering this map at a later time.
15. Pull back up the map called "Zillow Data" saved in step 8.
16. Configure the markers to display the relative performance of the percentage of homes sold at a gain for each state. Select "Feature Maps" > "Change Feature Styles". In the popup window select "Marker Icon" > "Buckets" > "Divide into custom buckets". Select the column "% Homes Sold at Gain" and then add the three buckets: 0:85, 85 to 95 and 95 to 100. Save the file and call it "Zillow Data - Layer 2".
17. Share the map following the steps in #14. Take note of the link as you will use it when layering the maps.
18. Layer the maps using a Google Fusion Layer Wizard.
19. In the wizard under step 1 "Add Map Layers", copy your html from step 14 into the first layer. Select "Add to Map". Then select "Add Layer". Copy your html from step 17. The resulting map looks as below.
20. Copy your html and open in a standalone html file or embed in a website.
21. Celebrate! You've just made your first layered Google Fusion Map!
Some tips for future projects
You can now go wild and download a variety of shape files from USCIS cartographic boundary downloads including county, zip code etc. Additionally, USCIS has made available a lot of great data sets that have mappings to these geographies.
Just note that when you really go crazy with very high density geography zones (such as ZTAC, zip code, county data etc), the geocoding step can be long and error prone. I have had it fail, fail silently and take 5+ hours to run. However, usually if you try a few times it will eventually work like charm.
While the set up can be somewhat convoluted and error prone, I still really love Google Fusion Maps. They are an incredibly powerful tool which produces beautiful results at a zero dollar price point. How can I complain about that?
Written by Laura Ellis