Drill Deeper: Mapping Geolocation in Tableau
Where are my customers?
Tableau can visualize locations of customer data based on ZIP out of the box. When dealing in detailed regional areas, this may not be enough. Having a more accurate street based location to show clusters provides powerful insight to sales and marketing.
To take Tableau up a notch, you'll need to leverage the power of Google Maps.
Use Google API to gather latitude and longitude for address information for mapping in Tableau.
The use case in my production environment required a few instances of gathering the location data from 10,000 to 50,000 records at a time. With this data size it was easier for me to use Excel, VBA, and Google Geocoding API to get the data. I'm a pretty big fan of VBA and custom functions in Excel, which is not a popular opinion. Using VBA to create a custom function reduces the formula bloat in more complex operations. Sure, it's fancy you can nest 14 functions together, but that just obfuscates the logic and makes your workbook hellish to maintain. Using a custom function can do unique operations, or combine operations to solve a problem. Self-documenting and comment filled code: amazing!
The logic and ideas here could be modified into a different language and tool set appropriate for your organization. I'd imagine once you get to around 100,000 records in a single go, this process may justify some other development methods and deployment strategies.
Using the code or workbook below, you'll be able to use the function =GetCoordinates() to gather your geolocation data. Going from zero to implementation is relatively easy, and starts with Google.
Get Your Own Geocoding API Key from Google
I love developer documentation that's easy to understand and access. I'm not a seasoned developer so accessible API documentation is fantastically useful. Google provides some top-notch documentation for using their APIs. To use the VBA Excel solution provided below, you'll need to review the developer documentation from the Google Maps API page and attain your own API Geolocation Key and provide a credit card if you plan on using more than the free allotment.
Your API Key is the key to making this process work. To use more than 2500 calls, you'll need a credit card for the charges, so don't pass this out unless you've cash to burn.
Great, now that you have your key, the fun can begin!
Download your code
Okay. VBA in Office files can be scary. For the sake of transparency, here is the simple .bas involved to make the magic happen:
Pretty simple right? If you'd like, you can copy paste this yourself, or insert the .bas file by clicking the download button.
The Preferred Method: Download Example.xlsm
Download the Excel file which contains the macro and some formatting to help get you started. Either method you choose will get you to the same place.
Add Your Key to the GoogleAPIKey Variable
You'll need to replace the portions of the provided VBA with your key provided from Google. If you don't, the =GetCoordinates() function will kick back an error.
Watch the Magic Happen
Once you have the key saved in the VBA, add your address data and pass the string to =GetCoordinates() to call the information from Googles serves.
This process asks for an XML return, grabs the location, and saves as Coordinates. I then use Excel to strip the latitude and longitude. This reduces the number of calls I have to make and saves a few bucks.
You can monitor the process from the Google Developer Console.
Add The Coordinates to Tableau
There are two options here:
Connect to Excel
Connect to a database
Connecting Tableau Directly to Excel
If you choose to leave the coordinate information in Excel, I suggest that you copy the data and paste as values into another clean workbook. Excel has a habit of running functions when opening and making modifications to the workbook, which can charge you more than expected, or cause other issues. Once you have the data, copy it as values to another safe location for consumption in Tableau.
Copy to Your RDBMS of Choice
I like the long-term solution of adding this information into a table on the server related to the address ifnroamtion. This makes the creation of views and extracts more manageable and provides a safe long-term storage solution for your data.
With a little VBA, Google-Fu, and a bit of spare time, you can be a data viz hero in your organization.