조금 더 현실적인 문제 해결을 위한 SQL 쿼리짜기

부제 : 8월에 한 번 구매한 고객 중 9월 달에 구매한 이력이 없는 고객은?

 

 

0. 들어가며

 

현실적인 비즈니스 문제를 풀기위한 쿼리는 복잡한 편이다.

그 이유 중 하나는 고객을 '정밀하게' 타겟팅하기 위함이다.

이는 비즈니스의 '비용의 최소화, 수익의 극대화'를 위한 방법 중 하나다.

일상에서 접하는 광고유형 중 하나도 부제와 같은 맥락의 고민이 시작했다.

이전에 구매한 이력이 있지만, 더 이상 우리의 제품과 서비스를 이용하지 않는 고객을 타겟팅 한다.

고객님 돌아와주세요!

 

고객이 우리의 제품/서비스를 잘 이용하다가, 제품/서비스 사용을 중단한 까닭을 여기선 차치하자.
왜냐면 그 이유가 너무 많기 때문에. 이 글에서는 다 못다룬다.

 

 

이유는 차치하고,

이전에 구매한 이력이 있고 더 이상 제품/서비스를 이용하지 않은 고객의 주문을 유도한다 는

목표를 세웠다고 가정하자.

 

영영 떠나보내면 안된다. 빠르게 다시 이용하게 만들자.

이와 비슷한 수법을 쓰는 곳은 인터넷통신사(SKB, KT, LGU+의 해지방어팀), 음악 스트리밍 서비스사 등이 있다.

고객이 해지할라 뉘양스 툭 던져주면 할인해택을 제시한다.

 

자 이제 내가

8월에 한 번 구매한 고객 중 9월 달에 구매한 이력이 없는 고객

 

 

을 골라내는 작업의 담당자라고 가정하자.

(앞으로 나올 쿼리 문법은 postgresql을 따른다)

 

1. 구매 데이터를 살펴보자

아래와 같은 예제 데이터가 있다고 보자.

 

CREATE TABLE mysql_test ( 
id INT PRIMARY KEY, 
customer_id VARCHAR(30) NOT NULL, 
pay_date DATE
)


INSERT INTO mysql_test values (1,'11','2021-08-01')
INSERT INTO mysql_test values (2,'22','2021-08-02')
INSERT INTO mysql_test values (3,'33','2021-08-02')
INSERT INTO mysql_test values (4,'44','2021-08-03')
INSERT INTO mysql_test values (5,'55','2021-08-03')
INSERT INTO mysql_test values (6,'66','2021-08-03')
INSERT INTO mysql_test values (7,'77','2021-08-04')
INSERT INTO mysql_test values (8,'11','2021-08-05')
INSERT INTO mysql_test values (9,'33','2021-08-06')
INSERT INTO mysql_test values (10,'55','2021-08-08')
INSERT INTO mysql_test values (11,'11','2021-09-01')
INSERT INTO mysql_test values (12,'11','2021-09-02')
INSERT INTO mysql_test values (13,'33','2021-09-03')
INSERT INTO mysql_test values (14,'55','2021-09-04')
INSERT INTO mysql_test values (15,'77','2021-09-05')
INSERT INTO mysql_test values (16,'13','2021-09-06')
INSERT INTO mysql_test values (17,'15','2021-09-06')

 

mysql_test라는 테이블은 주문id, 고객id, 결제일이 담겨져있다.

이 테이블을 통해 8월에 구매한 기록이 1건 있고, 9월 달에는 구매한 이력이 없는 고객ID를 찾아

이들에게 10% 할인쿠폰 SMS메시지를 보내려고 한다.

 

 

2. 문제를 구조화 시키기

이 문제를 풀기위해 머릿 속에 벤다이어그램을 그려보자.

 

머릿 속에 위와 같은 그림을 그려보자

우리가 원하는 부분은 '8월달엔 구매를 했으나 9월달엔 구매를 하지 않은 유저'다.

집합으로 표시하면 {8월달 구매한 고객} - {9월달 구매고객}이다.

A - (A ∩ B)

{8월달 구매고객}- ({8월달 구매고객}∩ {9월달 구매고객}) 으로도 표현할 수 있다.

결국 그림에서의 보라색 부분만을 추출하는 것이다.

 

 

3. A-B 접근법

 

내가 떠올린 방법은 A-B 접근법이다.
우선 8월에 구매를 했고(조건1), 구매건수는 1건(조건2) 의 데이터를 뽑는다.

위 데이터는 완벽하지 않다.

왜냐하면 위 데이터가 9월에 구매한 기록이 없는 회원이 제외(조건3) 되었는지 보장할 수 없기 때문이다.

 

따라서 총 3개 조건의 필터링이 필요하다.

이를 위해 서브쿼리를 사용했다.

 

우선 (조건1)과 (조건2)를 만족하는 데이터를 뽑아 해당 데이터를 임시로 테이블화 한다.

그렇다면 쿼리를 아래와 같이 짤 수 있을 것이다.

 

select customer_id, (extract(MONTH from pay_date)::bigint) as mon ,
count(extract(MONTH from pay_date)) as cnt
	from mysql_test
	group by customer_id, mon
	having (extract(MONTH from pay_date)::bigint) = 8 and count(extract(MONTH from pay_date)) = 1

customer_id,와 월, 구매횟수를 컬럼값으로 삼았다.

 

이를 위해

having절에 (조건1)과 (조건2)를 넣었다.
조건 1 = 8월 달에 구매한 내역이 있을 것

동시에

조건 2 = 8월 달 구매한 내역이 단 1건일 것

 

 

 

조건1, 조건2를 동시에 만족하는 위의 커리를 실행하면 아래와 같은 데이터가 추출된다.

 

위 데이터를 인라인뷰(FROM절 서브쿼리)로 하여 다시 구매기록(mysql_test 테이블)과 조인을 시킨다.

데이터를 다 보기 위해 아래와 같이 쿼리를 짰다.

select *
from (
	select customer_id, (extract(MONTH from pay_date)::bigint) as mon , count(extract(MONTH from pay_date)) as cnt
	from mysql_test
	group by customer_id, mon
	having (extract(MONTH from pay_date)::bigint) = 8 and count(extract(MONTH from pay_date)) = 1
) as data
left join mysql_test as b
on data.customer_id = b.customer_id

 

데이터를 위와 같이 짜고 돌려보면 아래와 같은 결과가 나온다.

 

 

위 데이터를 통해 customer_id 77이 8월 달에 한 번, 9월 달에 한 번 구매한 것을 발견할 수 있다.

 

따라서 customer_id 77은 9월달에 구매한 기록이 있으므로, (조건3)를 만족시키지 않는다.

 

 

4. 조건3를 만족시키는 방법

 

customer_id 77이 8월 4일에 1 건, 9월 5일에 1 건 주문기록이 있다.

즉 customer_id 77이 두 번(중복되어) 들어가 있다는 뜻이다.

 

중복된 값을 제외하기 위해서는

customer_id를 기준으로하여 id가 몇번 나왔는지 카운팅을 하는 것이다.

 

곧 같은 id가 2번이상 나왔다는 것은 9월달에 구매한 기록이 있다는 것을 의미한다.

 

2번이상 나온 id를 제외하는 쿼리를 추가하면 결국  아래와 같이 짜볼 수 있겠다.

 

 

select b.customer_id
from (
	select customer_id, (extract(MONTH from pay_date)::bigint) as mon , count(extract(MONTH from pay_date)) as cnt
	from mysql_test
	group by customer_id, mon
	having (extract(MONTH from pay_date)::bigint) = 8 and count(extract(MONTH from pay_date)) = 1
) as data
left join mysql_test as b
on data.customer_id = b.customer_id
group by b.customer_id
having count(b.customer_id) <2

 

having count가 핵심이다.

2건 미만인, 곧 1건인 것만 필터링한다.

 

 

최종 customer_id

 

 

마침내 8월 달에 1건 구매하고, 9월 달엔 구매한 이력이 없는 고객의 위 3명이다.

id가 22,44,66인 3명에게 10%할인쿠폰 SMS를 보내면 된다.

5. 마무리하며

 

현실 이커머스 업계에서는 위와 같은 문제를 접한다고 한다.

고객의 재구매를 유도하기 위해(목표) 고객의 주문데이터에 접근해

여러 조건을 걸어, 데이터를 뽑아내는 과정이다.

 

나도 처음 이 문제를 마주했을 때, 어떠한 방식으로 접근해야 하는지 모르겠어서 헤맸다.

보다 적확히 표현하자면, 논리적인 글로는 표현을 할 수 있겠는데, 쿼리를 어떻게 짜야할지 막막했다.

 

결국 효과적인 솔루션은 여러번 고민하고 쿼리를 짜면서

접근법을 생각해 내는 것이다.

해당 id를 뽑아내기 위한 쿼리는 다양할 것이고, 내가 쓴 방법은 그 중 하나다.

 

이 문제를 접하며, 문제 자체는 간단한데 쿼리는 간단하지 않음을 느꼈다.

결국 여러번 해봐야한다.

 

 

문제를 풀기위해 많은 도움을 준 쭈니, 하니에게 참 고맙다!