『SQL로 시작하는 데이터 분석』 Chapter 9. 결론

캐시 타니무라, 『SQL로 시작하는 데이터 분석』, 박상근, 한빛미디어-OREILLY(2022), p415-428.

 

퍼널 분석

• 퍼널: 특정 목적을 달성하기 위해 거쳐야 하는 여러 단계
• 단계별로 전환율을 분석

SELECT count(a.user_id) as all_users
,count(b.user_id) as step_one_users
,count(b.user_id) / count(a.user_id) as pct_step_one
,count(c.user_id) as step_two_users
,count(c.user_id) / count(b.user_id) as pct_one_to_two
FROM users a
LEFT JOIN step_one b on a.user_id = b.user_id
LEFT JOIN step_two c on b.user_id = c.user_id
;


SELECT count(a.user_id) as all_users
,count(b.user_id) as step_one_users
,count(b.user_id) / count(a.user_id) as pct_step_one
,count(c.user_id) as step_two_users
,count(c.user_id) / count(b.user_id) as pct_step_two
FROM users a
LEFT JOIN step_one b on a.user_id = b.user_id
LEFT JOIN step_two c on a.user_id = c.user_id
;

 

이탈, 휴면의 정의

이탈

• 서비스의 특성을 고려해 이탈의 개념 정의 및 적절한 기간을 설정
• 시간 기반의 이탈 지표: 특정 기간 동안 상품을 구매하지 않거나 인터랙션이 없는 고객의 수 (주로 30일~1년)
• 갭 분석: 사용자가 상품을 재구매하거나, 기능을 재사용하기까지의 기간을 계산
  • 정확한 이탈 기준을 정의할 때 활용
  • 특정 액션이나 이벤트를 기준으로 한 시계열 데이터가 필요 (lag 활용)
• time since last 분석으로 고객을 모니터링
  • 마지막 구매, 마지막 결제, 마지막 앱 실행 시간 등 시간 기반 지표를 설정하고, 그 뒤의 변화를 분석하는 일

-- gap 분석
SELECT gap_months, count(*)
FROM
(
    SELECT id_bioguide, term_start
    ,lag(term_start) over (partition by id_bioguide order by term_start) as prev
    ,age(term_start,lag(term_start) over (partition by id_bioguide order by term_start)) as gap_interval
    ,date_part('year',age(term_start,lag(term_start) over (partition by id_bioguide order by term_start))) * 12
          + date_part('month',age(term_start,lag(term_start) over (partition by id_bioguide order by term_start))) as gap_months
    FROM legislators_terms
    WHERE term_type = 'rep'
) a
WHERE gap_months is not null
GROUP BY 1
ORDER BY 1
;


-- 마지막 시간 이후(time since last) 분석
SELECT date_part('year',interval_since_last) as years_since_last
,count(*) as reps
FROM
(
    SELECT id_bioguide
    ,max(term_start) as max_date
    ,age('2020-05-19',max(term_start)) as interval_since_last
    FROM legislators_terms
    WHERE term_type = 'rep'
    GROUP BY 1
) a
GROUP BY 1
ORDER BY 1
;

 

휴면 (lapesed or dormant)

• 아주 활동적인 고객과 이탈해 버린 고객의 중간 단계 (이탈 고객보다 중요함)
• 오랫동안 접속이 없지만 고객의 과거 이력으로 보아 돌아올 가능성이 있음
  • 마케팅 email or 별도 홍보활동으로 휴면이 돌아올 수 있는 전략이 필요
• 기준 (고객 서비스)
  • 7일 이상 서비스를 사용하지 않은 고객 : 휴면
  • 30일 이상 서비스를 사용하지 않은 고객 : 이탈

SELECT 
case when months_since_last <= 23 then 'Current'
     when months_since_last <= 48 then 'Lapsed'
     else 'Churned' 
     end as status
,sum(reps) as total_reps     
FROM
(
    SELECT 
    date_part('year',interval_since_last) * 12 
     + date_part('year',interval_since_last)
     as months_since_last
    ,count(*) as reps
    FROM
    (
        SELECT id_bioguide
        ,max(term_start) as max_date
        ,age('2020-05-19',max(term_start)) as interval_since_last
        FROM legislators_terms
        WHERE term_type = 'rep'
        GROUP BY 1
    ) a
    GROUP BY 1
) a
GROUP BY 1
;

 

장바구니 분석

• 고객이 함께 구매하는 상품 목록에서 구매 패턴을 찾아내는 분석
• 주목적은 함께 구매하는 상품 종류를 찾는 일이지만, 개념을 확장해서 어떤 부류의 사람이 아이스크림을 구매하며,
  아이스크림과 함꼐 구매하는 제품은 무엇인지 등 특정 상품을 기반으로 한 구매 패턴을 찾기도 함
• 주의 사항
  • 분석 쿼리의 성능 : 3개 이상의 제품으로 구성된 그룹을 찾다 보면 쿼리 실행 속도가 느려짐
  • 몇몇 제품만 구매가 자주 일어나, 제품 조합을 망치는 경우 (너무 자주 구매하는 제품을 제외하고 수행하는 것을 추천)
  • 자기실현적 예언(self-fulfilling prophecy)이 될 수 있음
    • 자기실현적 예언: 미래에 예측한 일이 우연히 발생하는 것이 아니라, 그렇게 이뤄질 것이라고 믿고서 무의식적으로
      그에 맞게 행동한 결과로 발생하는 현상

SELECT product1, product2
,count(customer_id) as customers
FROM
(
    SELECT a.customer_id
    ,a.product as product1
    ,b.product as product2
    FROM purchases a
    JOIN purchases b on a.customer_id = b.customer_id and b.product > a.product
) a
GROUP BY 1,2
ORDER BY 3 desc
;

SELECT products
,count(customer_id) as customers
FROM
(
    SELECT customer_id
    ,string_agg(product,', ') as products
    FROM purchases
    GROUP BY 1
) a
GROUP BY 1
ORDER BY 2 desc
;

 

Reference

• SQL Style
  • Modern: https://gist.github.com/mattmc3/38a85e6a4ca1093816c08d4815fbebfb
  • Base: https://www.sqlstyle.guide/
• Blog: https://towardsdatascience.com/