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

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FUx7fNjcfw9KvGNf1JTaW%2Fadvanced%20banner.png?alt=media&#x26;token=ea2ec56e-2c6a-4c54-bae4-561b2fa33b7b" alt="Advanced difficulty banner" width="175"><figcaption></figcaption></figure></div>

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](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/retail) in CARTO Analytics Toolbox.

For this analysis we will use [Iowa liquor sales](https://data.iowa.gov/Sales-Distribution/Iowa-Liquor-Sales/m3tr-qhgy) 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 <a href="#step-1-get-the-data-ready" id="step-1-get-the-data-ready"></a>

First, we need to prepare the data for the analysis. To do this, we’ll use the [BUILD\_CANNIBALIZATION\_DATA](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/retail#build_cannibalization_data) procedure that computes and enriches the catchment area of existing stores. The following information is required as input.

1. The type of grid and resolution to be used. Note this is a [spatial index](https://docs.carto.com/analytics-toolbox-bigquery/overview/spatial-indexes/)-based analysis.
2. The existing store locations.
3. The type of catchment area to be computed (buffer, kring, or isoline).
4. Variables from the [CARTO’s Data Observatory](https://carto.com/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](https://docs.carto.com/analytics-toolbox-bigquery/guides/data-enrichment-using-the-data-observatory/) or on your workspace (see image below).
5. The size of the catchment area by urbanity type. This is passed through the options argument. See the [procedure documentation](https://docs.carto.com/analytics-toolbox-bigquery/sql-reference/retail/#build_cannibalization_data) for details on types of urbanity.\
   **Note** this uses [CARTO global urbanity categories](https://carto.com/blog/building-spatial-model-classify-global-urbanity-levels/) available through the [Spatial Features](https://carto.com/spatial-data-catalog/browser/?category=derived\&provider=carto) datasets for which a [subscription](https://docs.carto.com/analytics-toolbox-bigquery/guides/data-enrichment-using-the-data-observatory/) is required.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/iWiQIa4pXNuxeu68yy1s/store-cannibalization-1.png" alt=""><figcaption></figcaption></figure>

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:

{% tabs %}
{% tab title="carto-un" %}

```sql
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]}'
);
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
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]}'
);
```

{% endtab %}

{% tab title="manual" %}

```sql
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]}'
);
```

{% endtab %}
{% endtabs %}

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.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/q8iZK873t1orHHae7jcP/store-cannibalization-2.png" alt=""><figcaption></figcaption></figure>

## Step 2. Cannibalization overlap computation <a href="#step-2-cannibalization-overlap-computation" id="step-2-cannibalization-overlap-computation"></a>

Next, we compute the cannibalization impact of two potential new stores using the [CANNIBALIZATION\_OVERLAP](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/retail#cannibalization_overlap) procedure.

{% tabs %}
{% tab title="carto-un" %}

```sql
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]}'
);
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
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]}'
);
```

{% endtab %}

{% tab title="manual" %}

```sql
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]}'
);
```

{% endtab %}
{% endtabs %}

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.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/Gib52FIUXsJHKCdIJpi9/store-cannibalization-3.png" alt=""><figcaption></figcaption></figure>

The map below summarizes all the steps of the analysis:

{% embed url="<https://clausa.app.carto.com/map/87c1f87a-9e7b-454c-a55e-c7fac90d8e93>" %}

<img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/GWtJUWkKFcCzxcPMkgpe/eu-flag-website.png" alt="EU flag" data-size="line"> This project has received funding from the [European Union’s Horizon 2020](https://ec.europa.eu/programmes/horizon2020/en) research and innovation programme under grant agreement No 960401.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/store-cannibalization-quantifying-the-effect-of-opening-new-stores-on-your-existing-network.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.