UK-ecommerce Retention Analysis

SELECT MEDIAN(customer_avg_buying_cycle) AS avg_buying_cycle
FROM (
    SELECT customer_id
        , CASE WHEN order_count - 1 == 0 THEN 0
                ELSE order_date_diff / (order_count - 1)
        END AS customer_avg_buying_cycle
    FROM (
        SELECT customer_id
            , EXTRACT('epoch' FROM DATE_TRUNC('day', last_order_date - first_order_date))/3600/24 AS order_date_diff
            , order_count
        FROM (
            SELECT customer_id
                , MIN(order_date) AS first_order_date
                , MAX(order_date) AS last_order_date
                , COUNT(DISTINCT order_id) AS order_count
            FROM retail_cleaned
            GROUP BY 1
        ) AS step1
    ) AS step2
) AS step3

WITH retail_cleaned AS (
    SELECT *
         , MIN(order_month) OVER(PARTITION BY customer_id) AS first_order_month
    FROM retail
    WHERE country = 'United Kingdom'
    AND order_date NOT LIKE '2011-12%'
    AND category != '삭제'
    AND customer_id IS NOT NULL
    AND unit_price >= 0.01
)
SELECT first_order_month
      ,month0
      ,ROUND(IF(month1 = 0, 0, month1 / month0 * 100) ,2) AS month1_pct
      ,ROUND(IF(month2 = 0, 0, month2 / month0 * 100) ,2) AS month2_pct
      ,ROUND(IF(month3 = 0, 0, month3 / month0 * 100) ,2) AS month3_pct
      ,ROUND(IF(month4 = 0, 0, month4 / month0 * 100) ,2) AS month4_pct
      ,ROUND(IF(month5 = 0, 0, month5 / month0 * 100) ,2) AS month5_pct
      ,ROUND(IF(month6 = 0, 0, month6 / month0 * 100) ,2) AS month6_pct
      ,ROUND(IF(month7 = 0, 0, month7 / month0 * 100) ,2) AS month7_pct
      ,ROUND(IF(month8 = 0, 0, month8 / month0 * 100) ,2) AS month8_pct
      ,ROUND(IF(month9 = 0, 0, month9 / month0 * 100) ,2) AS month9_pct
      ,ROUND(IF(month10 = 0, 0, month10 / month0 * 100) ,2) AS month10_pct
      ,ROUND(IF(month11 = 0, 0, month11 / month0 * 100) ,2) AS month11_pct
FROM (
        SELECT LEFT(first_order_month,10) AS first_order_month
                ,COUNT(DISTINCT customer_id) AS month0
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 1 MONTH) = order_month THEN customer_id END) AS month1
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 2 MONTH) = order_month THEN customer_id END) AS month2
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 3 MONTH) = order_month THEN customer_id END) AS month3
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 4 MONTH) = order_month THEN customer_id END) AS month4
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 5 MONTH) = order_month THEN customer_id END) AS month5
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 6 MONTH) = order_month THEN customer_id END) AS month6
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 7 MONTH) = order_month THEN customer_id END) AS month7
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 8 MONTH) = order_month THEN customer_id END) AS month8
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 9 MONTH) = order_month THEN customer_id END) AS month9
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 10 MONTH) = order_month THEN customer_id END) AS month10
                ,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 11 MONTH) = order_month THEN customer_id END) AS month11
        FROM retail_cleaned
        GROUP BY first_order_month
        ORDER BY first_order_month 
     )retention_pct

library(dplyr)
library(data.table)
library(lubridate)
library(stringr)
library(clipr)
library(usethis)
library(readxl)
library(writexl)
library(dlookr)

retail <- read_xlsx("Online Retail.xlsx")
retail

retail <- retail %>%
   rename(order_id = "InvoiceNo", product_id = "StockCode",
          description = "Description", subcategory = "Subcategory",
          category = "Category", order_date = "InvoiceDate",
          unit_price = "UnitPrice", customer_id = "CustomerID",
          country = "Country", quantity = "Quantity")

retail

# drop abnormal description values
desc_drop <- c("add stock to allocate online orders",
               ...
               "20713")

retail_df <- retail %>%
   filter(description != desc_drop)

# drop cancelled order values
retail_df <- retail_df %>%
   filter(!str_detect(order_id, '^C'))

# drop rows where quantity & unit_price < 0
retail_df <- retail_df %>%
   filter(unit_price > 0,
         quantity > 0)

# chec
retail_df <- retail_df %>%
   mutate(order_month = format(as.Date(order_date), "%Y-%m")) %>%
   mutate(total_amount = quantity * unit_price)retail_df %>%
   filter(str_detect(order_id, '^C'))

# Save to csv file
write.csv(retail_df, "retail.csv", row.names = F)