[BigQuery] 데이터 마이닝

<데이터 분석을 위한 SQL 레시피> 8장 22강의 내용을 실습했습니다.

 

샘플 데이터

purchase_detail_log (구매 상세 로그 테이블)

 

---

어소시에이션 분석 (연관성 분석)

 

어소시에이션 분석

: 데이터마이닝 분야 중 하나인 '상관 규칙 추출'의 대표적 방법으로 상관 규칙을 대량의 데이터에서 찾아내는 것을 의미합니다. (상관 규칙이란 '상품 A를 구매했다면 상품B도 구매한다.'와 같이 시간적 차이와 인과관계를 갖는 규칙을 의미합니다.)

 

즉, 어소시에이션 분석이란 데이터 내부의 연관성, 즉 상품과 상품간의 상호 관계 또는 종속 관계를 찾아내는 분석법입니다. 

이러한 연관성을 파악하면 ‘어떤 상품을 누구에게 팔아야 할 것인지’ 혹은 ‘누가 어떤 상품을 구매할 것인지’를 예측하여 매출을 극대화시킬 수 있습니다.

 

 

 

어소시에이션 분석에 사용되는 지표

 

• 지지도    상관 규칙이 어느 정도의 확률로 발생하는지를 나타내는 값

 

• 확신도 (신뢰도)   어떤 결과가 어느 정도의 확률로 발생하는지를 의미하는 값, 어떤 조건을 만족하는 경우의 확률 

 

• 리프트    '어떤 조건을 만족하는 경우의 확률(= 확신도)'을 '사전 조건 없이 해당 결과가 일어날 확률'로 나눈 값 

보통 리프트 값이 1.0 이상이면 좋은 규칙이라 판단합니다.

 

 

 

---

두 상품의 연관성을 어소시에이선 분석으로 찾기

WITH
purchase_id_count AS (
  --구매 상세 로그에서 유니크한 구매 로그 수 계산하기
  SELECT COUNT(DISTINCT purchase_id) AS purchase_count
  FROM stately-forest-350015.ch22.purchase_detail_log
)
, purchase_detail_log_with_counts AS (
  SELECT
    d.purchase_id
    ,p.purchase_count
    ,d.product_id
    --상품별 구매 수 계산하기
    , COUNT(1) OVER(PARTITION BY d.product_id) AS product_count
  FROM stately-forest-350015.ch22.purchase_detail_log AS d
    CROSS JOIN
    --구매로그 수를 모든 레코드 수와 결합하기
    purchase_id_count AS p
)
SELECT *
FROM purchase_detail_log_with_counts
ORDER BY product_id, purchase_id
;

 

위의 쿼리를 통해 각 상품 ID를 사용해 purchase_count와 product_count를 셀 수 있습니다.  

---

WITH
purchase_id_count AS (
  SELECT COUNT(DISTINCT purchase_id) AS purchase_count
  FROM stately-forest-350015.ch22.purchase_detail_log
)
, purchase_detail_log_with_counts AS (
  SELECT
    d.purchase_id
    ,p.purchase_count
    ,d.product_id
    , COUNT(1) OVER(PARTITION BY d.product_id) AS product_count
  FROM stately-forest-350015.ch22.purchase_detail_log AS d
    CROSS JOIN
    purchase_id_count AS p
)
, product_pair_with_stat AS (
  SELECT
    l1.product_id AS p1
    ,l2.product_id AS p2
    ,l1.product_count AS p1_count
    ,l2.product_count AS P2_count
    ,COUNT(1) AS p1_p2_count
    ,l1.purchase_count AS purchase_count
  FROM purchase_detail_log_with_counts AS l1
    JOIN purchase_detail_log_with_counts AS l2
    ON l1.purchase_id = l2.purchase_id
  WHERE 
    --같은 상품 조합 제외하기
    l1.product_id <> l2.product_id
  GROUP BY
    l1.product_id
    ,l2.product_id
    ,l1.product_count
    ,l2.product_count
    ,l1.purchase_count
)
SELECT *
FROM product_pair_with_stat
ORDER BY p1, p2 
;

 

동시에 구매한 상품 조합을 생성하고, 조합 별로 구매 수를 세는 쿼리입니다. (이때, 자기 결합을 한 뒤 같은 상품 페어는 제외하였습니다. )

 

위의 쿼리를 통해 어소시에이션 분석에 필요한 '구매 로그 총 수', '상품  A의 구매 수', '상품 B의 구매 수', '상품 A와 상품 B 동시 구매 수' 라는 4개의 키 페어를 구하였습니다. 

---

4개의 키페어를 이용해 어소시에이션의 지표인 지지도, 확신도, 리프트를 계산합니다.

 

WITH
purchase_id_count AS (
  SELECT COUNT(DISTINCT purchase_id) AS purchase_count
  FROM stately-forest-350015.ch22.purchase_detail_log
)
, purchase_detail_log_with_counts AS (
  SELECT
    d.purchase_id
    ,p.purchase_count
    ,d.product_id
    , COUNT(1) OVER(PARTITION BY d.product_id) AS product_count
  FROM stately-forest-350015.ch22.purchase_detail_log AS d
    CROSS JOIN
    purchase_id_count AS p
)
, product_pair_with_stat AS (
  SELECT
    l1.product_id AS p1
    ,l2.product_id AS p2
    ,l1.product_count AS p1_count
    ,l2.product_count AS P2_count
    ,COUNT(1) AS p1_p2_count
    ,l1.purchase_count AS purchase_count
  FROM purchase_detail_log_with_counts AS l1
    JOIN purchase_detail_log_with_counts AS l2
    ON l1.purchase_id = l2.purchase_id
  WHERE 
    --같은 상품 조합 제외하기
    l1.product_id <> l2.product_id
  GROUP BY
    l1.product_id
    ,l2.product_id
    ,l1.product_count
    ,l2.product_count
    ,l1.purchase_count
)
SELECT
  p1 
  ,p2 
  ,100.0 * p1_p2_count / purchase_count AS support
  ,100.0 * p1_p2_count / p1_count AS confidece
  , (100.0 * p1_p2_count / p1_count)
  /(100.0 * p2_count / purchase_count) AS lift
FROM product_pair_with_stat
ORDER BY p1, p2 
;

상품 p1을 구매한 사람이 상품 p2를 구매한다라는  두 상품의 상관 규칙에 관한 지지도, 확신도, 리프트를 확인할 수 있습니다.