재구매율과 충성고객 분석

재구매율=(2회 이상 구매한 고객 수/전체 구매 고객 수) * 100
충성고객 분석에는 2가지 방법이 있다
구매횟수 기준, 지출금액 기준

articles_hm → 상품 정보
customer_hm → 고객 정보
transactions_hm → 거래 내역

percent → 전체 고객 중 재구매 고객 비율(%)
repeat_customers → 재구매한 고객 수
total_customers → 총 구매 고객 수

SELECT COUNT(DISTINCT CASE WHEN purchase_count > 1 THEN customer_id END) repeat_customers, 
       COUNT(DISTINCT customer_id) total_customers,
       ROUND((COUNT(DISTINCT CASE WHEN purchase_count > 1 THEN customer_id END) / COUNT(DISTINCT customer_id)) * 100, 2) percent
FROM (SELECT customer_id, COUNT(DISTINCT t_dat) purchase_count
    FROM basic.transactions_hm
    GROUP BY customer_id) a

2회이상 구매한 고객수는 구매한 날짜가 다른날을 count하였다

SELECT customer_id, 
       COUNT(article_id) purchase_count
FROM basic.transactions_hm
GROUP BY customer_id
HAVING purchase_count >= 25
ORDER BY purchase_count DESC

총 구매횟수가 25번을 넘는 고객의 아이디

SELECT 
    COUNT(DISTINCT CASE WHEN purchase_count >= 25 THEN customer_id END) loyal_customers, 
    COUNT(DISTINCT customer_id) total_customers,
    ROUND((COUNT(DISTINCT CASE WHEN purchase_count >= 25 THEN customer_id END) / COUNT(DISTINCT customer_id)) * 100, 2) loyal_customer_rate
FROM (
    SELECT customer_id, COUNT(article_id) purchase_count
    FROM basic.transactions_hm
    GROUP BY customer_id
) a

위의 쿼리를 서브쿼리 a로 만들어 총 구매횟수가 25를 넘는 충성 고객수, 총 고객수, 충성고객 비율을 알수있다

SELECT customer_id, 
       SUM(price) total_spent
FROM basic.transactions_hm
GROUP BY customer_id
HAVING total_spent >= 1
ORDER BY total_spent DESC

총 구매금액이 1 이상인 고객의 아이디

SELECT COUNT(DISTINCT CASE WHEN total_spent >= 1 THEN customer_id END) loyal_customers, 
       COUNT(DISTINCT customer_id) total_customers,
       ROUND((COUNT(DISTINCT CASE WHEN total_spent >= 1 THEN customer_id END) / COUNT(DISTINCT customer_id)) * 100, 2) loyal_customer_rate
FROM (
    SELECT customer_id, SUM(price) AS total_spent
    FROM basic.transactions_hm
    GROUP BY customer_id
) a

위 쿼리를 서브쿼리로 만들어 총 구매금액이 1 이상인 고객의 수, 총 고객의 수, 충성고객 비율

이제 재구매율과 충성고객을 찾았으니 목표를 설정하자
1.재구매율을 높이고, 고객이 지속적으로 브랜드를 이용하도록 유도
2.충성고객을 유지하면서 추가적인 구매를 유도
3.충성고객을 더욱 강화하여 브랜드 애착도를 상승

1

WITH customer_index_purchases AS (
    SELECT t.customer_id, 
           a.index_group_name, 
           COUNT(DISTINCT t.article_id) AS unique_products, 
           COUNT(t.article_id) AS total_purchases
    FROM basic.transactions_hm t
    JOIN basic.articles_hm a ON t.article_id = a.article_id
    GROUP BY t.customer_id, a.index_group_name)
SELECT customer_id, 
       index_group_name, 
       unique_products, 
       total_purchases
FROM customer_index_purchases
WHERE total_purchases > 1
ORDER BY customer_id, total_purchases DESC

비슷한 상품을 반복적으로 구매하는 고객을 위의 쿼리로 찾고 이전에 구매한 상품과 유사한 제품 추천

2
충성고객들은 지출 금액, 구매 빈도 별로 등급제를 만들어 혜택을 제공한다

3
신상품 사전 예약, 특별 초대 등을 충성 고객 한정으로 발송하여 충성고객을 잡아둔다