[B&S 유저들의 이탈 시점 예측] 문파(길드) 데이터 엔지니어링

1. 문파 데이터의 문제점

길드 아이디에 속한 아이디의 리스트가 저장되어 있어, 각 유저의 길드 정보를 바로 파악하기 어려웠다.

 

2. MySQL Workbench

guild_member_acc_id에 저장되어 있던 아이디들을 콤마(,)를 기준으로 나눠 각 다른 열(m1, m2... m78)에 구분하여 저장되도록 데이터를 수정했다.

 

MySQL Workbench에서 bns DB를 만들고, train_guild 데이터와 train_label 데이터를 삽입해 테이블을 생성했다.

 

길드원 아이디 열들을 UNION으로 결합하고, GROUP BY 문으로 길드 아이디를 기준으로 그룹화한 뒤 COUNT 함수로 각 길드 아이디별로 행의 수를 계산해서 길드원의 수를 member_count 열에 저장했다.

SELECT guild_id, COUNT(*) AS member_count
FROM (
select guild_id, m1 as guild_member_id from bns.train_guild UNION
select guild_id, m2 FROM bns.train_guild union
select guild_id, m3 from bns.train_guild union
select guild_id, m4 from bns.train_guild union
-- 중간 생략
select guild_id, m74 from bns.train_guild union
select guild_id, m75 from bns.train_guild union
select guild_id, m76 from bns.train_guild union
select guild_id, m77 from bns.train_guild union
select guild_id, m78 from bns.train_guild 
) AS guild_members
GROUP BY guild_id;

 

그리고 각 길드원이 속한 길드 아이디의 리스트를 뽑기 위해 UNION 결합을 하고 GROUP BY와 GROUP_CONCAT 함수를 사용했고, 길드 아이디를 COUNT한 값인 가입한 길드의 개수를 guild_count 열에 저장했다.

SELECT guild_member_id, COUNT(DISTINCT guild_id) AS guild_count, GROUP_CONCAT(DISTINCT guild_id ORDER BY guild_id) AS guild_id_list
FROM (
select guild_id, m1 as guild_member_id from bns.train_guild union
select guild_id, m2 from bns.train_guild union
select guild_id, m3 from bns.train_guild union
select guild_id, m4 from bns.train_guild union
select guild_id, m5 from bns.train_guild union
-- 중간 생략
select guild_id, m74 from bns.train_guild union
select guild_id, m75 from bns.train_guild union
select guild_id, m76 from bns.train_guild union
select guild_id, m77 from bns.train_guild union
select guild_id, m78 from bns.train_guild
) AS guild_members
GROUP BY guild_member_id;

 

 

3. 시각화

(1) 레이블별 유저들이 가입한 길드의 개수 분포

이탈 유저들은 길드 활동을 열심히 하지 않는 것으로 보인다. 게임 콘텐츠를 즐기기 위해 길드 활동을 열심히 하는 유저들은 잔존하는 가능성이 높다는 가설이 맞는 것으로 확인됐다.

 

(2) 레이블별로 유저들이 가입한 길드의 규모(길드원 수) 비교