[Big Query] 벤 다이어그램 분석 준비하기

 Today's Topic

• 벤 다이어그램 분석의 개요
• 벤 다이어그램 분석, 어디에 쓰일 수 있나요?
• 벤 다이어그램을 활용하여 분석하기

- Used Function

• SIGN함수
• CASE구문
• UNION ALL

 

---

1.  벤 다이어그램 분석의 개요

벤다이어그램이라는 말은 많이 들어 보셨을 것입니다.

출처: Wolfram

위와 같이 서로 다른 집합들의 관계를 표현하는 것이 바로 벤 다이어그램입니다.

A와 B와 C의 관계가 있다면 A와 B 공통적으로 나타나는 관계도 존재할 수 있고, A와 B각각 나타나는 관계도 존재할 수 있습니다. 또한, A와 C 간의 관계도 마찬가지일 것이고, B와 C 심지어는 ABC모두 동시에 관계 있는 경우도 있습니다.

이러한 관계를 파악하고자 사용할 수 있는 분석이 바로 벤 다이어그램 분석입니다.

 

---

2.  벤 다이어그램 분석, 어디에 쓰일 수 있나요?

 

그러면 이 벤 다이어그램을 어떻게 분석에 활용할 수 있을까요?

대표적인 사례가 e커머스 사이트라고 볼 수 있습니다.

 

e커머스 사이트란, 옥션, 11번가, 쿠팡과 같이 B2C형태로 상품을 판매하는 온라인 사이트 입니다.
쿠팡과 같은 경우에는 아예 판매자가 e커머스 사이트의 주체가 되기도 하지만,
11번가, 옥션과 같은 경우에는 판매자와 구매자를 중개해주는 역할을 담당하기도 합니다.

 

E커머스 사이트에서는 어떤 사용자 액션이 있을까요?

로그인, 좋아요, 구매라는 액션이 있다고 가정해 보겠습니다. 

 

E커머스사이트의 고객들은 회원 로그인을 하고, 괜찮아 보이는 상품이 있으면 해당 상품에 좋아요를 누르고, 마음에 든다면 구매까지 하는 일련의 과정을 거칩니다.

 

하지만 고객들 중에는 좋아요(찜하기)를 누르지 않고 바로 구매하는 고객들도 있을 것이고, 

로그인을 하지 않고 비회원주문을 하는 경우도 있을 수 있습니다.

또한, 비회원이고 좋아요만 누르고 구매를 하지 않는 경우도 있습니다.

 

이러한 상황에서 마케터라면 회원여부와 좋아요를 누른 사람의 관계 그리고 구매까지 한 고객들의 관계가 어떻게 되는지 알아보고 싶을 것입니다.

 

또한, 최근 중고거래플랫폼으로 인기를 끌고 있는 당근마켓도 벤다이어그램 분석을 할 수 있는데

그 사이에서 나오는 사용자 액션이 좋아요, 채팅, 구매입니다.

그렇다면 이들 사이에서는 어떤 관계가 나올 수 있을지 알아보고자 할 경우에도

벤 다이어그램 분석을 사용할 수 있습니다.

 

---

3.  벤 다이어그램을 활용하여 분석하기

 

그러면 이제부터 벤다이어그램을 활용한 분석을 진행해 보겠습니다.

아래의 테이블을 활용한 실습이 진행되며,

테이블 이름: user_action

 

진행 순서는 다음과 같습니다.

 

1. 액션플래그 집계

2. 모든 액션 조합에 대한 사용자 수 계산

3. 벤 다이어그램을 만들기 위해 데이터 가공하기

 

 

---

step 1. 액션플래그 집계

현재 위 표와 같은 데이터는 가공되지 않은 상태이고, 이것으로 분석을 바로 들어가기는 어렵습니다.

그러므로 분석목표에 맞게끔 수정해 주어야 합니다.

 

우리의 분석 목표는 '액션'에 따른 관계입니다. 

하트만 누르고 구매를 하지 않은 경우는 얼마나 되는지와 같은 케이스를 확인해보는 것이겠지요.

그렇기에 제일 먼저 해야할 것은 위의 action칼럼을 활용하여 액션플래그를 집계하는 것이라 볼 수 있습니다.

 

액션플래그는 0과 1로 집계할 수 있습니다.

해당 액션을 했다면 1, 하지 않았다면 0으로 할 수 있습니다.

 

쿼리를 보시겠습니다.

 

---2-1) 액션 플래그 만들기 // 해당 액션을 했으면 1, 아니면 0을 부여하기
with
user_action_flag as(
  select user_id,
  sign(sum(case when action = 'heart' then 1 else 0 end)) as heart,
  sign(sum(case when action = 'chat' then 1 else 0 end)) as chat,
  sign(sum(case when action = 'buy' then 1 else 0 end)) as buy
  from inlaid-lane-373607.user_action
  group by user_id
)
select * from user_action_flag;

 

1) with구문을 사용해 임시 테이블을 생성합니다. 물론 물리 테이블로 생성해도 되지만, 편의상 임시테이블로 만들겠습니다.

2) user_action테이블에서 데이터를 추출하는데, user_id별로 데이터를 묶습니다.

3) case구문을 사용해 action 칼럼이 각각 heart, chat, buy라면 1을 부여하고, 아닐 경우 0으로 부여하는 칼럼을 만듭니다.

4) sum함수를 사용하여 유저별 heart, chat, buy개수를 각각 합칩니다.

5) sign함수를 사용하여 해당 값이 양수이면 1, 0이면 0, 음수이면 -1을 반환하도록 합니다.

 

※ 우리들은 각 유저들이 heart, chat, buy기능을 이용한 횟수가 아닌 이용했는지 여부를 확인하는 것이므로, 

sign함수를 사용해 그 값을 1로 만들어 준 것입니다.

※ 3번 단계에서 마무리하게 된다면 각각의 이벤트에 대한 이용 여부를 확인하게 되므로, 유저가 그 기능을 이용했는지 파악하기가 직관적이지 않습니다.

 

그 결과는 다음과 같습니다.

 

user_id별로 각 액션의 이용 여부를 0과 1로 확인할 수 있게 됩니다.

 

---

step 2. 모든 액션 조합에 따른 사용자 수 계산하기

step1 단계인 액션플래그를 집계까지 마무리 되었습니다.

이대로 벤다이어그램을 만들기는 아직 어렵습니다.

아직 사용자의 액션의 이용 여부만 있지, 각각의 액션에 대한 사용자 이용 여부는 집계가 되지 않았기 때문입니다.

그렇기에 벤 다이어그램을 그리기 위해서는 좋아요, 채팅, 구매 이 각각의 액션들을 상황별로 모두 잘게 쪼개어 이를 액션 플래그와 합쳐주는 작업이 필요합니다.

 

사실, 이 작업은 cube()함수가 지원되는 sql이라면 매우 간단하게 처리할 수 있습니다.

애초에 cube함수는 계산할 수 있는 모든 행을 계산하여 반환하는 것이므로

모든 액션 조합 (좋아요, 채팅, 구매)별로 사용자 수를 계산하여 결과를 편리하게 내어 줍니다.

 

하지만, 표준 sql을 지향하는 google big query에서는 cube함수가 지원되지 않는 관계로, (실행 결과 syntax error반환)

다른 방법을 사용해야 합니다.

 

그래서 cross join을 활용해 null을 포함하는 레코드를 추가하여 모든 계산 결과를 반환할 수 있도록 만듭니다.

(null값도 계산을 해야 하기 때문입니다.)

 

--2-2) 모든 액션 조합에 따른 사용자 수 계산
----참고) 계산가능한 모든 조합을 반환하는 cube()를 쓰면 훨씬 간편하나, 빅쿼리에서는 cube함수 사용이 불가하므로 다른 방법을 사용합니다.
---- 결과 테이블에서 각 칼럼이 1/1/1일 경우 구매 시 모든 과정을 거쳤다는 의미입니다.
with
user_action_flag as(
  select user_id,
  sign(sum(case when action = 'heart' then 1 else 0 end)) as heart,
  sign(sum(case when action = 'chat' then 1 else 0 end)) as chat,
  sign(sum(case when action = 'buy' then 1 else 0 end)) as buy
  from inlaid-lane-373607.sqlr202301.lec11_5
  group by user_id
)
, action_diagram as (
  select act_heart, act_chat, act_buy, count(*) as users 
  from user_action_flag
  cross join unnest(array[heart, null]) as act_heart 
  cross join unnest(array[chat, null]) as act_chat
  cross join unnest(array[buy, null]) as act_buy
  group by act_heart, act_chat, act_buy
)
select * from action_diagram
order by act_heart, act_chat, act_buy;

 

1) with구문을 사용해 action_diagram이라는 임시 테이블을 생성합니다. 물론 물리 테이블로 생성해도 되지만, 편의상 임시테이블로 만들겠습니다.

2) from절을 사용, 앞서 만들었던 임시 테이블인 user_action_flag에서 데이터를 추출합니다.

3) cross join unnest(array)를 사용하여, heart칼럼에다가 null로만 이루어진 칼럼을 생성하여 이를 행별로 합칩니다. 즉, heart칼럼에는 1(좋아요를 누른 고객)와 null로만 데이터가 이루어질 것입니다. 

4) 좋아요, 채팅, 구매별로 그룹화합니다.

5) 각 액션을 선택하고, null값과 상관 없이 해당하는 전체 유저의 수를 카운팅합니다.

5) 앞서 만들었던 action_diagram테이블을 act_heart, act_chat, act_buy별로 정렬합니다.

 

그 결과는 다음과 같습니다.

각 액션별로 사용자의 행동을 보여주고 있습니다.

위 테이블에서 null로 나온 것은 해당 액션을 했는지 모르는 경우이며,

0은 하지 않은 액션, 1은 했던 액션으로 보시면 됩니다.

즉, 7행의 경우 좋아요를 눌리지 않은 고객의 수(단, 채팅이나 구매여부는 모름), 10행의 경우 좋아요, 채팅을 이용하지 않고 구매한 경우에 해당됩니다.

모든 칼럼이 null값인 1행의 경우, 모든 사용자의 수를 나타냅니다. (앞서 count(*)를 사용하여 유저를 카운팅 했으므로 이러한 결과가 나올 수 있습니다.)

 

이렇게 보면 점점 벤다이어그램에 가까워지는 것 같지만 아직은 뭔가 부족한 것 같습니다.

이제는 편의상 붙였던 0과 1의 플래그를 언어로 바꿔주고, 맨 마지막 칼럼에 모든 사용자 대비, 해당 액션의 비율을 구해보면서, 벤 다이어그램을 그릴 수 있도록 완성시켜보겠습니다.

 

---

step 3. 벤 다이어그램을 그리기 위한 마지막 단계

이제는 마지막 단계로서

0과 1 플래그를 이해할 수 있는 문자열로 바꿔주고

전체 사용자 대비 해당 액션을 이용한 사용자의 비율을 구함으로써 어떤 것이 많은 관계가 있는지 살펴보겠습니다.

 

--2-3) 벤 다이어그램을 만들기
with
user_action_flag as(
  select user_id,
  sign(sum(case when action = 'heart' then 1 else 0 end)) as heart, 
  sign(sum(case when action = 'chat' then 1 else 0 end)) as chat,
  sign(sum(case when action = 'buy' then 1 else 0 end)) as buy
  from inlaid-lane-373607.sqlr202301.lec11_5
  group by user_id
)
, action_diagram as (
  select act_heart, act_chat, act_buy, count(*) as users 
  from user_action_flag
  cross join unnest(array[heart, null]) as act_heart
  cross join unnest(array[chat, null]) as act_chat
  cross join unnest(array[buy, null]) as act_buy
  group by act_heart, act_chat, act_buy
)
--- 0과 1로 된 플래그를 문자로 변환하기.
select
case act_heart when 1 then 'heart' when 0 then 'not heart' else 'any' end as act_heart,
case act_chat when 1 then 'chat' when 0 then 'not chat' else 'any' end as act_chat,
case act_buy when 1 then 'buy' when 0 then 'not buy' else 'any' end as act_buy
, users
-- 전체 유저 대비 액션 이용의 비율 구하기
, 100*users / nullif(sum(case when act_heart is null and act_chat is null and act_buy is null then users else 0 end) over(),0) as ratio
from action_diagram
order by act_heart, act_chat, act_buy;

 

1) action_diagroam 테이블을 활용하여 0과 1로 된 플래그를 case구문을 사용해 변환합니다.

2) 액션 이용 비율 구하기의 경우, 전체 사용자의 수는 전체가 null인 경우였으므로, 해당 행의 유저 수와 전체 유저 수를 나눠주면 됩니다.

 

그 결과는 다음과 같습니다.

 

10행에서 좋아요, 채팅까지 하고 구매하지 않은 경우는 3건의 케이스로서, 전체 5명의 유저 중 60%가량을 차지했네요.

16행에서 좋아요, 채팅을 하지 않고 구매만 한 고객도 1건의 케이스가 있었습니다. 

 

 

이렇게 오늘은 벤다이어그램 분석을 해 보았습니다.

users칼럼을 활용해 해당 테이블을 기반으로 벤 다이어그램을 작성할 수 있으며

비율까지 구해봄으로써 어떻게 구매 데이터를 활용할 수 있는지 확인할 수 있었네요.

 

e커머스 말고도 벤 다이어그램을 활용할 수 있는 경우는 무궁무진합니다.

예를 들어 도서관의 경우에도 적용할 수 있을 것입니다.

책을 보러 오는 사람, 책을 빌리러 오는 사람, 공부하러 오는 사람이 있겠네요.

단순히 도서관의 책을 보러 오는 사람이 있을 것이고, 빌리고 그 자리에서 어느 정도 읽다가 가는 사람도 있을 것이고,

책을 빌리고 공부하는 케이스도 있을 것입니다. (전문 서적의 경우 그럴 확률이 높겠지요.)

 

이처럼 많은 활용도를 가지는 분석이기에 좀 더 확실히 알아 둘 필요가 있을 것 같네요.

 

이것으로 벤 다이어그램 분석을 준비하기 내용을 마치겠습니다.

 

감사합니다.

 

---

본 내용은 "데이터 분석을 위한 SQL레시피" 교재를 참고하여 작성되었습니다.