Ask or search…
K
Links

retail

ADVANCED BETA
This module contains procedures to perform analysis to solve specific retail analytics use cases, such as revenue prediction.

COMMERCIAL_HOTSPOTS

COMMERCIAL_HOTSPOTS(input, index_column, variable_columns, output, options)
Description
This procedure is used to locate hotspot areas by calculating a combined Getis-Ord Gi* statistic over several variables. The input data should be in either an H3 or Quadbin grid. The individual Gi* statistics are combined using Stouffer's Z-score method, which also allows to introduce individual weights, with the combined statistics following a standard normal distribution. The hotspots are identified as those cells with a positive combined the Gi* statistics which is significant at the specified significance level, i.e. whose p-value is below the p-value threshold (pvalue_thresh) set by the user.
Input parameters
  • input: VARCHAR name of the table or query containing the input data. If the * input it's a table, it should include database and schema, i.e., follow the format database.schema.tablename.
  • index_column: VARCHAR name of the column containing the H3 or Quadbin indexes.
  • variable_columns: ARRAY names of the columns containing the variables to take into account when computing the combined Gi* statistic.
  • output: VARCHAR name of the table where the output data will be stored. It should include database and schema, i.e., follow the format database.schema.tablename.
  • options: VARCHAR containing a valid JSON with the different options. Valid options are described in the table below.
    Option
    Description
    variable_weights
    ARRAY An array containing the weights associated with each of the variables. These weights can take any value but will be normalized to sum up to 1. If NULL, uniform weights will be considered.
    kring
    BIGINT The size of the k-ring (distance from the origin). This defines the area around each cell that will be taken into account to compute its Gi* statistic. The default value is 1 (i.e. only the first-order neighbouring cells are taken into account).
    kernel
    STRING The kernel type used to smooth the individual Gi* statistic over the the area around each cell defined by the kring parameter. If not specified, a 'uniform' kernel will be used by default.
    pvalue_thresh
    FLOAT Threshold for the Gi* value significance, ranging from 0 (most significant) to 1 (least significant). It defaults to 0.05. Cells with a p-value above this threshold won't be returned.
Output The output will contain the following columns:
  • a column named either H3 (STRING) OR QUADBIN (BIGINT) depending on the grid type storing the unique geographic identifier of each grid cell.
  • combined_gi: FLOAT64 with the resulting combined Gi*.
  • p_value: FLOAT64 with the p-value associated with the combined Gi* statistic.
Examples
CALL CARTO.CARTO.COMMERCIAL_HOTSPOTS(
'<mydatabase-id>.<myschema-id>.<mytable-name>',
'H3',
ARRAY_CONSTRUCT('feature_1','feature_2'),
'<mydatabase-id>.<myschema-id>.<mytable-name>_OUTPUT',
NULL
);
-- Table `<mydatabase-id>.<myschema-id>.<mytable-name>_OUTPUT` will be created
-- with columns: h3, combined_gi, p_value
CALL CARTO.CARTO.COMMERCIAL_HOTSPOTS(
'<mydatabase-id>.<myschema-id>.<mytable-name>',
'H3',
ARRAY_CONSTRUCT('feature_1','feature_2','feature_3'),
'<mydatabase-id>.<myschema-id>.<table-name>_OUTPUT',
'{
"variable_weights":[0.4,0.3,0.3],
"kring":1,
"kernel":"gaussian",
"pvalue_thresh":0.01
}'
);
-- Table `<mydatabase-id>.<myschema-id>.<mytable-name>_OUTPUT` will be created
-- with columns: h3, combined_gi, p_value
Last modified 1mo ago