Our zip code and location analysis add-in for Microsoft Excel, CDXZipStream, can be combined with the computational power of Excel to do a variety of interesting things. For instance, we’ve just recently introduced an easy-to-use geographic access analysis template for Excel that can take a matrix of X and Y locations (these could be stores, customers, warehouses, delivery points, etc.) and create a comprehensive report showing which X locations are closest to all Y locations, or vice versa.
Let’s say we have a list of customer addresses and would like to determine which store locations are within a 10 miles radius of each customer. This could be used to support, for example, a direct mail campaign to alert customers to the closest stores in their area. But in cases where there may thousands of customers and thousands of stores, determining the closest stores for every single customer is not a trivial calculation.
So how does the template work? First we copy and paste all store and customer addresses into the Excel template where they can be verified using the address verification function of CDXZipStream (Bing version). Just click a button in the template, and CDXZipStream automatically determines if each address can be matched to a location in either Bing Maps or Microsoft MapPoint; you can select which mapping source to use. If a match can be found, CDXZipStream geocodes the location to find its latitude and longitude. These points of latitude and longitude are used to calculate straight-line distances between all combinations of stores and customers. So to obtain a report showing the closest stores within radius of each customer, all we need to do is specify the radius distance (in this case, 10 miles) , click one more button, and a listing of all customers, along with the store locations within 10 miles, is automatically created in a worksheet.
The template can work with either full addresses (street, city, state, zip code) or zip codes alone if full addresses are not available. The user can also bypass the address verification step by inputting latitude and longitude data directly, if it is available from a gps or other source. And if you need to do the reverse calculation, i.e. find the closest customers for all store locations, just reverse the order of the data input.
For a short tutorial about the template, please watch the following video:
This video is also available on YouTube: Geographic Access Analysis in Microsoft Excel.
For more information, contact us at firstname.lastname@example.org or 1-877-CDX-TEC1 (1-877-239-8321). For current CDXZipStream clients, you can login to your on-line account and select "Buy Data Updates" from the Product Management Area. Select the Geographic Access Analysis Template from the drop-down list and proceed through the purchasing process. You must own the CDXZipStream Bing, Basic, or one of the premium demographic versions to see this as an option.