...
| Expand | |||||
|---|---|---|---|---|---|
| |||||
|
First attempts
We did the first attempt for the case of accessories recommendations (cross-selling on the PDP) as follows:
| Code Block |
|---|
CREATE OR REPLACE TABLE `@reports.Data_AccessoriesPrediction_logistic_reg_v1_11M`
as
SELECT
sum(KPI_defined.mainImpressions) as mainImpressions
, sum(KPI_defined.clicks) as clicks
, sum(KPI_defined.clicks) / sum(KPI_defined.mainImpressions) as ctr
, case when source_exact_price > 0 and abs(exact_price / source_exact_price) between 0.8 and 1.2 then 1 else 0 end as same_price_20
, case when source_exact_price > 0 and abs(exact_price / source_exact_price) between 0.9 and 1.1 then 1 else 0 end as same_price_10
, case when source_exact_price > 0 and abs(exact_price / source_exact_price) between 0.7 and 1.3 then 1 else 0 end as same_price_30
, case when ifnull((select property_values[safe_offset(0)] from unnest(product_properties) where property_name = 'category_leaf_de' limit 1),'-') in unnest((select ifnull(source_property_values, []) from unnest(source_product_properties) where source_property_name = 'category_leaf_de' limit 1)) then 1 else 0 end as same_leaf_category
, case when ifnull((select property_values[safe_offset(0)] from unnest(product_properties) where property_name = 'products_brand' limit 1),'-') in unnest((select ifnull(source_property_values, []) from unnest(source_product_properties) where source_property_name = 'products_brand' limit 1)) then 1 else 0 end as same_brand
, case when ifnull((select property_values[safe_offset(0)] from unnest(product_properties) where property_label = 'Produktlinie' limit 1),'-') in unnest((select ifnull(source_property_values, []) from unnest(source_product_properties) where source_property_label = 'Produktlinie' limit 1)) then 1 else 0 end as same_product_line
FROM
`@reports.widget_context_item_product_analytics`
WHERE date(day) >= date_sub(current_date(), interval 6 month)
and widget = 'accessories'
group by same_brand, same_leaf_category, same_price_10, same_price_20, same_price_30, same_product_line
;
CREATE OR REPLACE MODEL `@reports.Model_AccessoriesPrediction_logistic_reg_v1_11M`
OPTIONS(
model_type='logistic_reg'
, CATEGORY_ENCODING_METHOD = 'DUMMY_ENCODING'
, CALCULATE_P_VALUES = TRUE,
ENABLE_GLOBAL_EXPLAIN = TRUE
) AS
SELECT
(IF(clicks >= it,1,0)) AS label
, same_price_10
, same_price_20
, same_price_30
, same_leaf_category
, same_brand
, same_product_line
FROM
`@reports.Data_AccessoriesPrediction_logistic_reg_v1_11M`
join unnest(GENERATE_ARRAY(1,mainImpressions)) as it
; |
As a result, the predictive power only based on these few similar parameters is quite low (as it was expected), but could grow with more parameters.
...
Also, the Explainable AI tab shows possible usage of the model as factor analysis, showing here that the attribution is highest on the same product line and brand and only after the category and price.
...