[sql] cross selling

고객들은 하나의 제품만을 구매하기도 하지만, 여러 상품을 한번에 구매하기도 한다. 치약만 구매한 고객이 있는 반면 칫솔을 함께 구매하는 고객이 있고, 아이폰만 구매한 고객이 있는 반면 애플워치도 함께 구매한 고객이 있을 것이다.

 

이러한 주문 데이터는 DB에 기록되기 마련이며, 이를 잘 활용한다면 다른 고객들이 제품을 구매할때 어떤 다른 제품을 함께 구매하면 좋은지 추천해줄 수 있다. 그리고 이렇게 다른 제품을 추가로 구매하게 하는 것을 교차 판매(cross selling) 이라고 한다.

 

우리는 교차판매로 매출을 증대시켜 성과금을 받기 위해, cross selling 연관 상품을 추출하는 sql 쿼리를 알아보고자 한다.

---

이번쿼리는 4가지 단계를 거친다.

 

① 유저별 주문상품 테이블 생성

② 유저가 주문한 상품 조합 생성 (self join)

③ 상품조합별 빈도수 계산

④ 상품별로 가장 빈도가 높은 조합 필터

 

① 먼저 주문 테이블(ga.orders)와 주문상품 테이블(ga.order_items) 테이블을 조인해서 유저정보(user_id)와 상품정보(product_id)를 각각 가져와서 subquery를 생성해준다. 

with
tmp1 as(
select o.user_id, oi.product_id
from ga.orders o
	left join ga.order_items oi 
	on o.order_id = oi.order_id 
)

tmp1 테이블

 

② 그리고 위에서 생성된 임시 테이블 형태에서 상품의 조합을 파악하고자 우측에 상품정보(함께 구매한 상품) 칼럼을 하나 더 추가해주고자 한다. 이 user_id가 같으며 product_id가 동일하지 않는 조건으로 self join을 해주면 된다. 우리는 구매 상품의 조합을 보길 원하기에, product_id 미일치 조건을 넣어준 것이다 (교차판매를 희망한다면 같은 상품을 구매한 정보는 궁금하지 않다).

tmp2 as(
select a.user_id,
	a.product_id as prod_id_01,
	b.product_id as prod_id_02
from tmp1 a
	join tmp1 b 
	on a.user_id = b.user_id and a.product_id != b.product_id
)

tmp2 테이블

③ 다음으로 위 tmp2 테이블에서 'prod_id_01', 'prod_id_02' 기준으로 group by 하여 조합의 빈도를 헤아려준다 (count). 이 단계에서부터 어떤 두 제품의 조합이 몇건 발생했는지 확인 할수있다.

tmp3 as(
select prod_id_01, prod_id_02, count(*) cnt
from tmp2
group by 1, 2
)

 

▼ cnt: 조합 발생 빈도

tmp3 테이블

 

④ 마지막으로 각 제품별로 가장 많이 함께 구매한 제품을 필터링 해준다. row_number()로 빈도가 가장 높은 제품 조합에 '1'을 부여해서 subquery를 생성후, 본 쿼리의 조건절에서 '1'만 필터링해줬다.

--1. 유저별 주문 상품 목록
with
tmp1 as(
select o.user_id, oi.product_id
from ga.orders o
	left join ga.order_items oi 
		on o.order_id = oi.order_id 
),
--2. 주문상품 self join
tmp2 as(
select a.user_id,
	a.product_id as prod_id_01,
	b.product_id as prod_id_02
from tmp1 a
	join tmp1 b 
		on a.user_id = b.user_id and a.product_id != b.product_id
),
--3. 주문상품으로 group by 
tmp3 as(
select prod_id_01, prod_id_02, count(*) cnt
from tmp2
group by 1, 2
),
--4. 가장 많은 조합 순으로 정렬
tmp4 as(
select *,
	row_number() over(partition by prod_id_01 order by cnt desc) rnum
from tmp3
)
select *
from tmp4
where rnum = 1
order by cnt desc
;

 

이렇게 해서 결과적으로 각 제품별(prod_id_01)로 어떤 제품(prod_id_02)을 가장 많이 함께 구매하는지 추출할수 있다. 우리는 이제 교차판매를 성공시킬 준비를 마쳤다.