Repetition Rate of products

Mariotalo 0
Viewed 62

I would like to do an analisis about the products and to know if after the sell of the product, the costumer go back and buy again the same or another product/s. Please find bellow an example, the result I would like to have and the fields I have in order to achieve my objetive: enter image description here

Some rows of my table: enter image description here

  id    customer_id order_number    created_at               product_name   quantity    price   total_order_price   product_category    product_subcategory    product_grouped      orders_count    has_user_account    order_occurrence    recurrent_customer  customer_creation_date
  4546  23655       5657            2022-01-02 13:15:23 UTC  water      .   1           2€      2€                  drink               cold drink      .      waters               1               1                   1               .   0                   2022-01-02 13:15:23 UTC

Some clarifications: For coke, 2/3 of repetition rate means: 2 out of 3 customers bought cokes and then they bought again. For Orange juice, only one customer made another purchase after that.

product repeat data-analysis google-bigquery sql
1 Answers

I do not understand the repitation rate. You give 0/3 for coke, which has no follow up order. For orange juice you give a 1/1, but there are three follow up orders.

Please update your question and give more details.

  • First generate some example data.
  • tmp-table: Use windows function to get product of next_order
  • combine_tbl: count of each combination between product_nameand next_order
  • find the top entries and do some sum ups.
WITH
  tbl AS (
  SELECT
    100+cast(rand()*10 as int64) AS customer_id,
    product_name,
    date_sub(current_date,interval cast(rand()*50 as int64) day) as created_at,
    1 as order_counts
  FROM
    UNNEST(['coke',"water","coffee","ice"]) AS product_name , unnest(generate_array(1,50))
  ), tmp as
(SELECT
  *, last_value(product_name) over (partition by customer_id order by created_at asc rows  between unbounded preceding and 1  preceding ) as last_order,
  last_value(product_name) over (partition by customer_id order by created_at desc rows  between unbounded preceding and 1  preceding ) as next_order
FROM
  tbl
  order by 3 desc
),
combine_tbl as 
(Select 
product_name,
next_order,
count(1) as count_combination,
sum(order_counts) as sold_units,


from tmp
group by 1,2
order by 1
)

Select 
product_name,
sum(sold_units) as sold_units,
sum(if(next_order is not null,sold_units,0)) as sold_units_for_next_orders,
string_agg(next_order order by count_combination desc limit 3) as Top3_next_orders

from combine_tbl
group by 1
order by 1
userG7m710
Related