# Using Visual Crossing’s Location Analysis Plugin in Excel

| |

Sean McCafferty provides an overview of Location Analysis, a new Excel Add-in from Visual Crossing, which helps store owners place locations and analyze retail competition.

The ability to place business locations for optimal performance has been a staple of retailers for decades. Business owners simply want to know what the optimal location for their business is to attract customers, increase revenue and if possible, diminish the capacity of their competition. Different models and varying demographic data are used for this analysis and the models have gotten more sophisticated over time. With this ability has come complications and expense. In this article we will use the Location Analysis plugin for Excel as an affordable method to complete a location analysis using some basic demographics that are available to everyone.

For this example, we will use two simple data sheets of My Locations and Competitor Locations to highlight the value that can be gained by virtually anyone.

## Distance–Attraction Modelling

The model we are discussing today will measure business locations and put them on a “battlefield” with competitors and attempt to predict customer potential using two variables: Distance and Attraction. The model is based upon the Huff model which has long been a standard for determining retail store attraction. We will need information from the spreadsheet in order to complete this analysis: ‘Location’ in the form of lat/long or address as well as an ‘Attraction’ metric that describes the ability of a location to draw in customers. It will then use the calculated probability based on those metrics to capture data values from the areas it covers.
One of the great advantages to doing this exercise in Excel is the accessibility of the data. Users can add/remove/modify data as-needed.

### Distance

Customers are inherently attracted to stores based upon how easily they can acquire the product that they need. The more accessible a store is to a collection of potential customers, the better it will perform. Typically, the business will be asked to include a max distance setting to tell the algorithm when to stop and at which point the probability of acquiring customers becomes zero. All areas outside of the max range are considered to have zero attraction capability. Locations that have overlapping ranges will compete and share the area based upon proximity ratios and attraction.

### Attractiveness

Determining how attractive a store is can be a challenging concept, but many retailers have a variety of go-to metrics that they can use to determine overall attractiveness. Many of these variables are based upon personal evaluation rather than hard metrics but every owner can choose to weight these as they deem appropriate. Some of these are: square footage of the location, time since remodeled, neighborhood location by income level and even a subjective score by scouts/reviewers. The weighting on these and the final score is always best done by the business themselves. Simply setting all stores to equal attraction or putting in best guesses on a range of 1-10 can be invaluable.

## Value Capture vs Standard Demographic Analysis

The key to location analysis is the capture of valuable information around locations so that they can be evaluated. How many potential customers are around these business locations? This is where a demographic layer can be helpful. The most basic is simply ‘Population’ at the Census Block Group level. Many of these basic demographic layers come with the tool and can provide a good estimate of how well a store is placed. Businesses that focus on a specific demographic may choose to target more specific demographics such as: ‘Family Households’, ‘Households with children’, or ‘Household Income’.
Once the user has chosen a demographic layer, the tool can now overlay a probability grid created from distance/attraction metrics and determine the percentage of demographic population that is available to go to its locations. A final capture of population is produced so that users can compare their presence vs the competition. Here we can see a basic radius capture of population:

In a simple system with no competition the probability of store attendance is 100%. But we can see the area the store influences and how many customers are available.

IMPORTANT NOTE: We have all seen detailed demographic reports from research firms that often show the demographics of an area. The difference here is that Location Analysis is doing an actual capture and aggregate count of people that meet the demographic criteria. If you are doing an analysis of an area that is 80% Hispanic, you would think this would be valuable if you are targeting Hispanics… But if an area that is 10% Hispanic can produce 2x the actual number of Hispanic people, the lower percentage here would be the more valuable area. It is critical that the tool you use can give concrete values that are aggregated across your geographic area rather than a basic color theming of demographic percentages.

A basic capture can give you an invaluable baseline statistic for your working area:

## Estimations to Revenue

Users may additionally choose to apply this population value to various other metrics to make it more comprehensible to business users. First, you may apply an acquisition or penetration ratio as a global value to determine what the expected population will come to your location. Secondly, the user can apply a ‘revenue per customer’ estimate to come to a final conclusion of ‘Expected Revenue’. This final value can also help the user tweak values to make the capture ratios or attractiveness metrics more accurate based on historical performance.

There are several standard tasks that all location owners want to accomplish to help them understand their business better. It can start with a simple analysis to see how well they are doing, comparison to competitors, cannibalization and the dreaded task of finding new locations. We will discuss how each of these can be made easier once you have the right tools.

### Location Analysis

When we are speaking about spatial analytics we want to know how we are performing around us. Using standard mapping you can overlay customer locations to see how far your stores are able to pull in customers. You can also see specific areas of concentration. One problem… for smaller businesses the task of collecting and managing customer locations is not always easy.

For this analysis we are focused on Distance and Attraction by demographics. The key here is to understand the key metric baseline. How many customers are available to us? What makes this metric key is that you can then apply your actual penetration in an area and make a direct calculation to revenue and translate your expected performance in other areas. Not all geographic areas are the same in density or demographic. Using an area’s potential and applying your penetration can you give a much more detailed expectation of ROI for investing in a remodel, advertising or a new location.

### Battleground Analysis

One of the most challenging aspects to Location Analysis is to track competitors. Most companies have very little data about their competitors and are unsure how to approach the battle. Location Analysis doesn’t require much information as we are working primarily with 2 variables and only one is required: Location. If you have a list in Excel, can manually enter addresses or a do a quick point of interest search from within Location Analysis you can quickly acquire their locations. The second variable, Attraction, can be estimated, averaged or manually entered by a reviewer.

Once the locations are in place, simply let Location Analysis do its work. It will tell you by location where customers will prefer to go and aggregate the population available to every location and tell you how you stack up against the competition.

### Cannibalization

It is not always customers that are hurting your store’s performance, sometimes it is your own stores. Too much area overlap can diminish a store to a more attractive location. With Location Analysis you can get a full picture of where the locations are conflicting with each other. We can see in the map below where the red areas highlight our strongest cannibalization.

### Site Scouting

The most basic Location Analysis that you typically find from the report-style analysis is simply to show me what areas have the demographic I need. However, seeing your strong demographic areas speckled across the area isn’t helpful. And again, seeing percentages of my demographics are not useful. I need to understand full capture possibilities. This is where the capture mechanism of Location Analysis is put to work. For every pixel on the screen do an analysis of my core demographic and show me where I can place a store such that I will get maximum customer access.

As the person responsible for finding the ideal location you need options! I don’t want to propose locations until I know the area is rich in my demographic. In the map below, a distance-calculated heatmap shows blue areas where we can capture the largest volumes of households with children:

Retailers can confidently place a location in the blue hotspot and know that they are close enough to bring in the customers they expect. This greatly increases our chance of producing the ROI we expect.

## Location Changes and Simulations

A slightly more challenging problem is to determine what would happen if a new site was chosen by the business, or even if a new site was added and another was closed or moved. This model can also help with this calculation. Simply create a new ‘scenario’ by adding a location and let the system recalculate all the probabilities based on your same metrics.

In the map above we can see the direct impact a new location would have before investing heavily. Adding multiple sites will quickly produce a ranked list that will help you in your property evaluations.

## Wrap Up

Location Analysis functionality should be a key part of every business owner’s toolbox. With the advent of inexpensive plugins for Excel, this is now available to everyone. Not a lot of data processing or cleanup is required, and the skill set to do the analysis is minimal. As your demands and skills grow, slowly you can enhance your requirements.