퍼널 분석에 들어가기 전, 퍼널이란 무엇인가?
퍼널은 유저가 앱/웹에 진입해서 활동하는 이벤트를 모은 로그이고
나는 지금 SQL을 통해 퍼널 분석을 하고 있다.
퍼널 분석을 통해 얻고자 하는 것은
유저가 어느 구간에서 많이 이탈했는지 파악하고 개선점을 도출해서, 결과적으로는 비즈니스를 개선하는 것이다.
그렇다면 분석 기준점이 되는 것은 '유저'이므로
가장 중요한 것은 '어떻게 유저를 구별해서 집계할 것인지'이다.
유저를 식별하는 방법
유저가 회원 가입을 하면 당연히 user_id가 생성되지만
비로그인으로 앱/웹을 사용할 수 있는 경우가 애매하다.
이러한 경우 앱에서는 device_id를 기록하기 때문에 이것을 기준점으로 삼기도 하지만
앱을 설치한 후 명시적으로 해당 정보를 수집할 것인지에 대한 동의를 받아야 하기 때문에
집계가 안 되는 경우(NULL)도 있다.
또한 기기를 교체하거나 앱을 재설치하면 1명의 유저가 여러 개의 device_id를 가지게 될 수도 있다.
GA/Firebase에서는 user_pseudo_id가 존재하고, device_id 처럼 값이 바뀔 수는 있지만
반드시 값이 로깅 되어 있기 때문에 해당 값을 기준으로 유저를 식별한다고 한다.
CONCAT 함수 사용
두 개의 열을 합쳐서 고유한 ID를 만들어 내거나
정제한 데이터를 알맞게 살펴보기 위해서 사용할 수 있을 것 같다.
위와 같은 데이터가 있을 때
name과 screen을 합쳐서 새로운 행을 만들고, 데이터를 집계해서 가져와야 할 수 있다.
아래와 같이 작성해주면, name_screen으로 합쳐진 것을 볼 수 있다.
* 다음데 쉼표(,) 있는 것 잘 보고 빼먹지 않기 (오류가 난다)
여기서 name, screen은 지우고 조회하고 싶다면 EXCEPT를 쓸 수 있다.
여기서 만약에 login 시점만 보고 싶다면 WHERE를 쓸 수 있다.
만약에 원래 데이터에서 name_screen 쪽에 순서를 매기고
evendate, name_screen 우선순위로 정렬하고 싶다면 어떻게 해야 할까?
첫 시도.. 에러가 났습니다.
AI에게 물어보니까 CONCAT 생성하는 곳에서 바로 CASE 문을 사용할 수 없다고 한다.
바로 쿼리 수정
name_screen 순서를 붙이려면 CASE WHEN ~ ELSE ~ END AS 사용
이제 원하는 우선 순위로 순서를 정렬해 보자.
ORDER BY를 사용하면 된다.
계속 눈에 띄는 NULL 값이 있다.
일부러 만들었는데, HAVING 함수를 쓰기 위함이다.
일단 GROUP BY를 통해 내가 원하는 데이터를 만들어 본다.
로그인해서 Home 확인 > 플레이하면서는 menu, bag 확인 > 로그아웃은 Home 통해서 진행하는 퍼널이므로
각 퍼널별로 통과한 유저수를 알기 위해 COUNT(DISTINCT user_id) AS cnt
하고
GROUP BY ALL
해서 마무리
여기서 이제 null 값을 제외하려면 GROUP BY ALL 밑에다가 HAVING 써주면 된다.
WHERE vs HAVING 차이점
WHERE: FROM 절에서 필터링을 하고 싶은 칼럼
HAVING: GROUP BY 집계 이후 나오는 결과에서 필터링을 하고 싶은 칼럼
| WHERE 사용 예시 | HAVING 사용 예시 |
|
SELECT
* EXCEPT (name, screen),
CONCAT(name, "-", screen) AS name_screen
FROM test.concat
WHERE
name IN ("login")
|
SELECT * EXCEPT (eventdate, user_id, name, screen),
CASE
WHEN name_screen = "login-home" THEN 1
WHEN name_screen = "play_01-menu" THEN 2
WHEN name_screen = "play_02-bag" THEN 3
WHEN name_screen = "logout-home" THEN 4
ELSE NULL
END AS screen_number,
COUNT(DISTINCT user_id) AS cnt
FROM (
SELECT *,
CONCAT(name, "-", screen) AS name_screen
FROM test.concat
) AS subquery
GROUP BY ALL
HAVING screen_number IS NOT NULL
ORDER BY screen_number
|
오늘도 한 번 더 배우는 것이지만
데이터가 어떻게 쌓이는지에 대한 이해도가 없으면 쿼리를 작성하기 너무 어렵다.
퍼널 강의에서 '왜 특정 열만 선택하는지' 혹은 '왜 그렇게 쿼리를 짰는지' 자세히 설명을 안 해주다 보니
왜라는 의문이 계속 들어서 강의를 따라가기가 어렵다.
물론 강의에서 알려주는 것과 똑같이 쿼리를 작성해서 조회하면 기댓값은 나오지만
내가 배우려는 것은, 강의에서 나오는 데이터를 조회하는 방법이 아니라
내가 지금 실무에서 사용하는 데이터를 조회하려고 할 때 SQL 쿼리를 작성하는 방법이다.
시간이 오래 걸리더라도 나만의 데이터를 나의 쿼리로 조회해서 데이터를 분석한다
그래서 강의를 듣고 난 이후
내가 이해한 기준으로 나만의 데이터 테이블을 만들고 거기에 쿼리를 사용해 보고 있다.
이렇게 해야지 내가 오늘 사용한 코드에 대해 100% 이해하고
이후 회사에서 실무적으로 데이터를 활용할 때 실제로 사용도 할 수 있다.
'SQL 코딩 입문' 카테고리의 다른 글
| SQL 중급: 7일차 #2 (2) | 2024.12.14 |
|---|---|
| SQL 중급: 7일차 (2) | 2024.12.14 |
| SQL 중급: 5일차 (1) | 2024.11.30 |
| SQL 중급: 4일차 (1) | 2024.11.23 |
| SQL 중급: 3일차 (0) | 2024.11.17 |
