Store cannibalization: quantifying the effect of opening new stores on your existing network

Cannibalization is a very common analysis in retail that consists in quantifying the impact of new store openings on existing stores. Depending on the business, the metric/s driving this impact can be different, e.g. population, footfall, or simply the overlapping area covered by the catchment area of two stores.

The key to quantifying cannibalization is to measure potential losses in the overlapping areas between the catchment area of existing and new stores.

In this example, we’ll show how to run a cannibalization analysis in two simple steps using the retail module in CARTO Analytics Toolbox.

For this analysis we will use Iowa liquor sales open data available in bigquery-public-data.iowa_liquor_sales.sales. In particular, we’ll focus on the area around Waterloo taking a buffer of 30 km around Waterloo city center considering Hy-Vee stores as our customer’s stores (see map at the end of the example). For practical reasons, a table with these stores has been made available at cartobq.docs.cannibalization_hyvee_waterloo_ia.

Step 1. Get the data ready

First, we need to prepare the data for the analysis. To do this, we’ll use the BUILD_CANNIBALIZATION_DATA procedure that computes and enriches the catchment area of existing stores. The following information is required as input.

The type of grid and resolution to be used. Note this is a spatial index-based analysis.
The existing store locations.
The type of catchment area to be computed (buffer, kring, or isoline).
Variables from the CARTO’s Data Observatory subscriptions we’d like to use to quantify cannibalization. In this case, population. Note the variable slug is required which can be obtained using the Analytics Toolbox or on your workspace (see image below).
The size of the catchment area by urbanity type. This is passed through the options argument. See the procedure documentation for details on types of urbanity. Note this uses CARTO global urbanity categories available through the Spatial Features datasets for which a subscription is required.

In our example, we’d like to quantify cannibalization through the population overlap between the new store’s catchment area and the existing ones. Therefore, we run the following query to get the data ready:

CALL `carto-un`.carto.BUILD_CANNIBALIZATION_DATA(
--grid_type
'h3',
--store_query: must include unique identifier and geometry
R'''
 SELECT store_id, sales, geom
 FROM `cartobq.docs.cannibalization_hyvee_waterloo_ia`
''',
--resolution
9,
--method
'buffer',
--do_variables
[('population_f5b8d177','sum')],
--do_urbanity_index: urbanity variable slug
'urbanity_e1a58891',
--do_source
'<my-dataobs-project>.<my-dataobs-dataset>',
--output_destination
'<my-project>.<my-dataset>',
--output_prefix
'hyvee_waterloo_cannib',
--options: ditsnaces(buffer radii by type of urbanity in kilometers)
'{"distances":[1.3,0.7,0.3]}'
);

CALL `carto-un-eu`.carto.BUILD_CANNIBALIZATION_DATA(
--grid_type
'h3',
--store_query: must include unique identifier and geometry
R'''
 SELECT store_id, sales, geom
 FROM `cartobq.docs.cannibalization_hyvee_waterloo_ia`
''',
--resolution
9,
--method
'buffer',
--do_variables
[('population_f5b8d177','sum')],
--do_urbanity_index: urbanity variable slug
'urbanity_e1a58891',
--do_source
'<my-dataobs-project>.<my-dataobs-dataset>',
--output_destination
'<my-project>.<my-dataset>',
--output_prefix
'hyvee_waterloo_cannib',
--options: ditsnaces(buffer radii by type of urbanity in kilometers)
'{"distances":[1.3,0.7,0.3]}'
);

CALL carto.BUILD_CANNIBALIZATION_DATA(
--grid_type
'h3',
--store_query: must include unique identifier and geometry
R'''
 SELECT store_id, sales, geom
 FROM `cartobq.docs.cannibalization_hyvee_waterloo_ia`
''',
--resolution
9,
--method
'buffer',
--do_variables
[('population_f5b8d177','sum')],
--do_urbanity_index: urbanity variable slug
'urbanity_e1a58891',
--do_source
'<my-dataobs-project>.<my-dataobs-dataset>',
--output_destination
'<my-project>.<my-dataset>',
--output_prefix
'hyvee_waterloo_cannib',
--options: ditsnaces(buffer radii by type of urbanity in kilometers)
'{"distances":[1.3,0.7,0.3]}'
);

As a result, the following table contains the information of every cell (spatial index) within each store’s catchment area. This table is later used in step 2.

Step 2. Cannibalization overlap computation

Next, we compute the cannibalization impact of two potential new stores using the CANNIBALIZATION_OVERLAP procedure.

CALL `carto-un`.carto.CANNIBALIZATION_OVERLAP(
--data_table: this is the output table of step 1
'<my-project>.<my-dataset>.hyvee_waterloo_cannib_output',
--new_locations_query
 R'''
   SELECT store_id, geom
   FROM UNNEST([STRUCT("new_store_1" AS store_id,
                       ST_GEOGPOINT(-92.337150, 42.504767) AS geom),
                STRUCT("new_store_2" AS store_id,
                       ST_GEOGPOINT(-92.340272, 42.493134) AS geom)])
 ''',
--method
'buffer',
--do_urbanity_index
'urbanity_e1a58891',
--do_source
'<my-dataobs-project>.<my-dataobs-dataset>',
--output_destination
'<my-project>.<my-dataset>',
--output_prefix
'hyvee_waterloo_cannib',
--options: distances(buffer radii by type of urbanity in kilometers)
'{"distances":[1.3,0.7,0.3]}'
);

CALL `carto-un-eu`.carto.CANNIBALIZATION_OVERLAP(
--data_table: this is the output table of step 1
'<my-project>.<my-dataset>.hyvee_waterloo_cannib_output',
--new_locations_query
 R'''
   SELECT store_id, geom
   FROM UNNEST([STRUCT("new_store_1" AS store_id,
                       ST_GEOGPOINT(-92.337150, 42.504767) AS geom),
                STRUCT("new_store_2" AS store_id,
                       ST_GEOGPOINT(-92.340272, 42.493134) AS geom)])
 ''',
--method
'buffer',
--do_urbanity_index
'urbanity_e1a58891',
--do_source
'<my-dataobs-project>.<my-dataobs-dataset>',
--output_destination
'<my-project>.<my-dataset>',
--output_prefix
'hyvee_waterloo_cannib',
--options: ditsnaces(buffer radii by type of urbanity in kilometers)
'{"distances":[1.3,0.7,0.3]}'
);

CALL carto.CANNIBALIZATION_OVERLAP(
--data_table: this is the output table of step 1
'<my-project>.<my-dataset>.hyvee_waterloo_cannib_output',
--new_locations_query
 R'''
   SELECT store_id, geom
   FROM UNNEST([STRUCT("new_store_1" AS store_id,
                       ST_GEOGPOINT(-92.337150, 42.504767) AS geom),
                STRUCT("new_store_2" AS store_id,
                       ST_GEOGPOINT(-92.340272, 42.493134) AS geom)])
 ''',
--method
'buffer',
--do_urbanity_index
'urbanity_e1a58891',
--do_source
'<my-dataobs-project>.<my-dataobs-dataset>',
--output_destination
'<my-project>.<my-dataset>',
--output_prefix
'hyvee_waterloo_cannib',
--options: ditsnaces(buffer radii by type of urbanity in kilometers)
'{"distances":[1.3,0.7,0.3]}'
);

From the table of results and the map below, we can see that only the first potential new store would have any cannibalization effect on Hy-Vee Food Store #2. In particular, the catchment area of the new store shares 4.7% of its area with the existing store, and this translates into 19.3% of the existing store’s population coverage. Based on these results, the second candidate for a potential new store seems to be a better option since it would not have any cannibalization effect on the existing stores.

The map below summarizes all the steps of the analysis:

Last updated 5 months ago