Drill Deeper: Mapping Geolocation in Tableau
The Problem
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.
The Solution
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:
Option Explicit Function GetCoordinates(Address As String) As String '----------------------------------------------------------------------------------------------------- 'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500 'requests per day, so be careful not to exceed this limit. 'For more info check: https://developers.google.com/maps/documentation/geocoding 'In order to use this function: the XML, v3.0 library from VBA editor: 'Go to Tools -> References -> check the Microsoft XML, v3.0. '----------------------------------------------------------------------------------------------------- 'Created by: Jared Duquette at DigitalDuquette.com 'Create Date: 11-11-2016 'Modified by: Jared Duquette 'Modified Date: 11-14-2016 '----------------------------------------------------------------------------------------------------- 'Declaring the necessary variables. Using 30 at the first two variables because it 'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll). Dim Request As New XMLHTTP30 Dim Results As New DOMDocument30 Dim StatusNode As IXMLDOMNode Dim LatitudeNode As IXMLDOMNode Dim LongitudeNode As IXMLDOMNode Dim strQuery As String Dim GoogleAPIKey As String On Error GoTo errorHandler 'Create the request based on Google Geocoding API. Parameters (from Google page): '- Address: The address that you want to geocode. '- Sensor: Indicates whether your application used a sensor to determine the user's location. 'This parameter is no longer required. 'API Requested by [ENTER YOUR NAME] at [enter_your_email@email.com] 'Google API for Project: GoogleAPIKey = "YourKeyHere" 'Billing enabled via Google Developer portal for project 'Request must be sent with API over SSL 'Example: 'https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&key=YOUR_API_KEY 'Actual: 'https://maps.googleapis.com/maps/api/geocode/xml?&address=1419+W+LAKEWOOD+BLVD&key=YourKeyHere 'Basic example provided: 'Request.Open "GET", "http://maps.googleapis.com/maps/api/geocode/xml?" & "&address=" & Address & "&sensor=false", False 'Build the query string: strQuery = "https://maps.googleapis.com/maps/api/geocode/xml?" strQuery = strQuery & "address=" & Address strQuery = strQuery & "&key=" & GoogleAPIKey strQuery = strQuery & "&sensor=false" Request.Open "GET", strQuery, False 'Send the request to the Google server. Request.send 'Read the results from the request. Results.LoadXML Request.responseText 'Get the status node value. Set StatusNode = Results.SelectSingleNode("//status") 'Based on the status node result, proceed accordingly. Select Case UCase(StatusNode.Text) Case "OK" 'The API request was successful. At least one geocode was returned. 'Get the latitdue and longitude node values of the first geocode. Set LatitudeNode = Results.SelectSingleNode("//result/geometry/location/lat") Set LongitudeNode = Results.SelectSingleNode("//result/geometry/location/lng") 'Return the coordinates as string (latitude, longitude). GetCoordinates = LatitudeNode.Text & ", " & LongitudeNode.Text Case "ZERO_RESULTS" 'The geocode was successful but returned no results. GetCoordinates = "The address probably does not exist" Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day. GetCoordinates = "Requestor has exceeded the server limit" Case "REQUEST_DENIED" 'The API did not complete the request. GetCoordinates = "Server denied the request" Case "INVALID_REQUEST" 'The API request is empty or is malformed. GetCoordinates = "Request was empty or malformed" Case "UNKNOWN_ERROR" 'Indicates that the request could not be processed due to a server error. GetCoordinates = "Unknown error" Case Else 'Just in case... GetCoordinates = "Error" End Select 'In case of error, release the objects. errorHandler: Set StatusNode = Nothing Set LatitudeNode = Nothing Set LongitudeNode = Nothing Set Results = Nothing Set Request = Nothing End Function '-------------------------------------------------------------------------- '--------------------------------------------------------------------------
Pretty simple right? If you'd like, you can copy paste this yourself, or insert the .bas file by clicking the download button.
The VBA in Visual Studio Code.
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.
Example view of the API console manager. Here you can monitor the process.
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.
Wrap-Up
With a little VBA, Google-Fu, and a bit of spare time, you can be a data viz hero in your organization.
Happy dashboarding!
