Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Location Data Knowledge Base

Data methodologies, and Release schedules.

Using the Power of Alteryx Data Packs. Part Three

BrandonKO
Alteryx
Alteryx
Created

Using the Power of Alteryx Data Packs. Part Three: Retail Cannibalization

 

Welcome back! I hope this series around utilizing Alteryx’s Business/Location Insights packs has proved valuable to you. In Part One we learned the basics of what the Alteryx Insights packs do and provide. In Part Two we explored some practical applications of the Insights pack by delving into advanced trade area creation (based on capture rate). In the same vein, we will be covering another practical application of the Insights packs, Cannibalization (not the type where you eat other people 😑).

 

Background

 

If you’re unfamiliar with the concept of Cannibalization, in the world of retail analytics, it refers to the study of measuring the impact of opening a new store location on existing locations. How many customers/sales will our new hardware store steal from our original location? That can be a tough metric to quantify, but there are a lot of clever methodologies developed to assist in our understanding. Cannibalization is an unavoidable consequence of growth, there’s no way around it. But utilizing advanced analytical techniques within Alteryx can minimize the impact so you create the optimal situation for net-new growth.

To properly do this kind of analysis, you need understanding of your current store network, accurate trade area definition (see Part Two), and customer behavior i.e., willingness to drive, concentrations of customer sales etc.

With all that being said, let’s dive into the provided example app!

 

Use Case

 

We begin with an existing store location in the heart of Chicago. This is obviously densely populated market so it will be interesting to see what level of impact opening a new store nearby will have. We also need a defined trade area for our existing store as well as point of sale data. The POS data is critical for understanding the distribution of store sales across geographies. In this case we will be using Block Groups, the most granular geography types.

Existing Store:

BrandonKO_0-1672351222016.png

Next, we will create 5-minute drivetime trade area around our new, potential store location(s). Using the Allocate Input tool and Spatial Match tool, we query from our Block Group file and obtain the geographies that intersect our new store trade area:

 

BrandonKO_1-1672351222095.png

 

We then join in our POS data from the existing store, which has the Block Group key that we can match to the Block Groups in from our new store. This generates a list of shared spatial objects to see where overlap exists. Once we combine these datasets together via Append Tool, we can begin to calculate the distance using the Distance Tool, of shared point of sale data to existing and potential locations. We then create an “Interaction Ratio” which is the existing store drivetime divided by the potential store drivetime. In our next formula, we create a “Proximity Range” for scaling the sales based on distance. This will be how we thematically shade our map and determine cannibalized sales.

BrandonKO_2-1672351222111.png

 

IF [Interaction Ratio] <.85 THEN .25 ELSEIF [Interaction Ratio]>=.85 and [Interaction Ratio] <=1.25 THEN .5 ELSE .75 ENDIF

 

Our final formula is our “Cannibalized Sales” which is “Sales_Per_BG” (sales in shared area) multiplied by “Proximity Range”.        

The next step in this app involves creating a nice, formatted output. Once again, we rely on the Alteryx Reporting Tools to create our thematic map. The bulk of this work takes place in the Map Tool which accepts multiple inputs at once, and you can layer in your different spatial objects. You also can import custom images like logos!

                                                                                      

BrandonKO_3-1672351222127.png

 

 

Since this is an analytical app, we created it with a Numeric Up Down tool to easily adjust the size of the trade area for our potential site. When we set it to create a 5-minute trade area, this is our result:

   

BrandonKO_4-1672351222471.png

 

Based on our analysis, our existing store will be hit hard by this potential location, $14.9 million. A little more context around the map, the blue line and point represent our potential location, while the black is our existing site. The red, green, and blue colored block groups represent the point of sale ratio where we have overlap.

 

Wrap Up

 

Hope you found this third part interesting. This app is completely customizable. If you aren't currently licensing any of the Insights data packs, you can download the free Census Data from licenses.alteryx.com to get your Block Groups. You can also switch the Distance Tools to "DistanceMiles/Kilometers" instead of "Drivetime". If you have any questions, feel free to reach out, or contact your Alteryx account representative!

 

Additional Resources

 

Attachments
Comments
BrandonKO
Alteryx
Alteryx

Check out Part Four!