‘SQL’ 분석 트레이닝 - (2) 검색 기능 분석 (*협업 툴 Yammer)
강의 시나리오를 따라하고 실제로 sql 을 활용하여 문제를 해결하는 과정을 기록하기
프로젝트는 3 가지이다.
프로젝트 1 - 유저 인게이지먼트 하락 원인 분석
프로젝트 2 - 검색 기능 분석 <<<
프로젝트 3 - A/B 테스트
(출처: [백문이불여일타] 데이터 분석을 위한 SQL 실전편)
- 프로젝트 진행 순서 (원문)
- The problem
- Getting oriented
- The data
- Making a recommendation
- Answers
프로젝트 2 - 검색 기능 분석
Understanding Search Functionality
https://www.notion.so/2-767ead6ee63f4f08931f30cd42223296#ae814a16250c42a8892582946415a04c
프로젝트 2 - 검색 기능 분석
강의 시나리오를 따라하고 실제로 sql 을 활용하여 문제를 해결하는 과정을 기록하기
www.notion.so
튜토리얼 페이지 : 프로젝트를 진행하기 위한 배경정보 페이지
개요
검색 기능에 텍스트를 입력하고 엔터키를 치거나, 자동완성 기능으로 연관검색어가 드랍다운 되어 나타난다.
문제상황
엔지니어링 팀의 리소스 낭비 하는게 아닐까? 더 좋은 기능을 개발하는게 좋을까? 수정할게 있을까? (ui, 레이아웃 등)
방향제시
검색기능의 목적은? 목적은 달성하고 있는지? 유저 경험들을 수치화 시킬 수 있는지?
데이터
여러가지 데이터(테이블) 중에서 중점적으로 봐야할 데이터를 골라야 한다.
- search_autocomplete : 검색 + 자동완성 페이지 (자동완성 페이지 추천)
- search_run : 검색 + 엔터 (전체검색)
- search_click_X : 검색 + 클릭 (일부 검색 후 추천단어)
Table 1 : Users
user_id create_at state activated_at company_id laguage
Table 2 : Events
user_id
occurred_at
event_type : signup_flow, engagement
event_name : (주요 이벤트) search_autocomplete, search_run, search_click_X location device
추천 방법
가설들을 다시 한번 추려보자.
- 검색기능의 경험이 좋은지? 나쁜지?
- 검색기능이 잘 작동하고 있는지?
- 검색기능의 개선점이 있는지?
yammer의 데이터를 시각화하고 구체적인 개선안을 찾아보자. 우리의 데이터가 충분한지도 확인하자.
(추가적인 데이터 테이블이나 설문 등을 통한 조사가 필요한지) 개선된 기능에 대한 목표나 (선행)지표는 무엇인지 결정하자.
가설 전개
검색기능의 궁극적인 목적은 수많은 정보들 중에서 원하는 것을 쉽게 찾게 하는 것이다.
다음 몇가지 예로 검색기능이 잘 수행되고 있는지 확인할 수 있다.
- 사용여부: 사용하는지? 얼마나 자주 사용하는지?
- 사용빈도 : 주요 기능인지? 검색이 잘 되는지?
- 반복 검색어 : 어떤 단어가 잘 안찾아지는지? << 어려운 작업
- 클릭 : 원하는 정보를 검색하지 못해서 반복검색을 하고 있는건 아닌지?
- 자동완성 클릭: 검색 후 자동완성 페이지 클릭하는지?
검색 환경
비지니스 마다 세션의 정의는 달라져야 하는데
yammer의 경우, 세션당 10분의 듀레이션 동안의 인터렉션만 이벤트로 가정한다.
아래 그래프는 하나의 세션에서 검색기능을 사용한 이벤트를 나타내고 있다.
유저들이 검색 결과보다 자동완성 기능을 더 많이 사용하고 있는데,
8.4 기준 전체검색 8%에 비해 자동완성이 25% 이다.
> > 검색창의 기능 사용여부... 일단 유저들이 사용은 하고 있는 모습니다.
자동 완성의 경우 방문당 1-2회 이내로 검색이 끝나는 경우가 많았지만,
검색 실행의 경우 방문당 1-5회 이내로 검색량이 다소 많게 나타났다.
전체 검색을 여러번 하는 경우는 원하는 검색이 잘 되지 않기 때문일까?
: 2번 그래프를 보았을때 한 섹션당 검색 실행의 횟수만 보아서는 검색결과에 문제가 있는것으로 판단할 수 있지만,
1번에서 검색 실행의 비율이 8%대에 그치는 것으로 보아 UX에 문제가 있다는 것은 성급하다.
오히려 복잡한 검색을 하는 자연스러운 플로우 볼 수 있다.
첫 세션에서 검색 실행 후 클릭이 거의 발생하지 않는다.
세션수가 많아져도 클릭수가 동시에 증가하지 않는 경우가 발생한다.
하지만 데이터로 봤을때 예외적으로 발생한 구간은 표본수가 적기 때문에 아웃라이어 처리를 해야한다. (평균의 오류를 조심하자!)
검색 실행 횟수 분포를 보면 clicks가 0인 세션이 54%에 달하지만, 비교군이 필요하다.
사용자들이 검색 클릭에 있어서 추천순서와 상관없이 클릭하는 행동을 보이고 있다. 검색결과 순서의 로직이 좋았다면 1-3 번째까지에서 월등한 클릭이 나왔을 것이다.
앞선 파트의 ‘세션 내 검색 횟수 별 평균 클릭 (상세)’ 테이블을 보더라도 클릭수가 6회 까지 길어지고 있다.
아니면 검색 결과의 컨텐츠 순서(랭킹) 문제보다, 결과물들 자체가 만족스럽지 못한것일 수도 있다.
검색 실행의 효용을 경험한 사용자는 1달 이내에 재사용 할 것이다?!
자동 완성을 사용하는 사람들이 재사용 빈도가 더 높다.
하지만 검색창 사용중에서 자동 완성이 25%, 검색 실행이 8%인 것에 비해
자동 완성 재사용 횟수가 1회가 1,239이고 검색 실행 재사용 횟수가 1,177인 것은
검색 실행의 feature retention이 높다는 것을 알 수 있다.
(정확히는 같은 유저의 1달 이후의 재방문율 수치를 구해야함!)
결론적으로
기능적으로 자동완성에 비해 검색 실행은 약간의 문제가 있는 것으로 보인다.
검색 결과 순서의 로직도 계속 살펴 봐야한다.
검색 실행이 자동 완성에서 찾기 힘든 복잡한 검색어를 다룬다고 봤을때,
자동 완성과 다른 로직으로 결과 순서를 만들어 보는것도 고려해볼 수 있겠다.
가장 명확한 점은 검색 실행 부분을 집중적으로 보완해야 한다는 것이다.
쿼리를 만들기에 앞서 주요 조건들을 정리해보자
- 세션당 10분의 듀레이션 동안의 인터렉션만 이벤트로 가정한다.
- user_id = 20 을 예시로 우선 작성한다.
- 주석 -> 강조표시로 사용
서브쿼리1
SELECT user_id,
event_type,
event_name,
occurred_at,
--LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS lag_event,
--LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS lead_event,
occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
ROW_NUMBER() OVER () AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
AND user_id = 20
ORDER BY user_id,occurred_at;
LAG,LEAD 함수 : 해당 컬럼에 대하여 앞뒤 n번째 값을 출력
특정 세션과 직전 & 직후 이벤트 사이의 시간을 출력하였다.
조건절에 의해 10분내에 해당하는 세션들은 삭제시킨다.
서브쿼리2
SELECT bounds.*,
--CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
--WHEN last_event IS NULL THEN id
--ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id,
event_type,
event_name,
occurred_at,
occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
ROW_NUMBER() OVER () AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
AND user_id = 20
ORDER BY user_id,occurred_at
) bounds
/* WHERE last_event >= INTERVAL '10 MINUTE'
OR next_event >= INTERVAL '10 MINUTE'
OR last_event IS NULL
OR next_event IS NULL;
*/CASE WHEN : last_event << 10분 이상 or NULL 값인 세션에 ‘id’ 입력
WHERE 조건절 : next_event, last_event << 10분 이내 세션은 삭제
ELSE LAG() : last_event << 10분 이내는 앞세션과 같은 세션
last_event가 10분 이상인 row는 이전 세션이 종료되고 첫 세션이 시작된 이벤트로 본다.
따라서 10분 이내의 이벤트는 앞 세션에서 이어지는 세션으로 본다.
서브쿼리3
SELECT user_id,
session,
--MIN(occurred_at) AS session_start,
--MAX(occurred_at) AS session_end
FROM (
SELECT bounds.*,
CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
WHEN last_event IS NULL THEN id
ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id,
event_type,
event_name,
occurred_at,
occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
ROW_NUMBER() OVER () AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
AND user_id = 20
ORDER BY user_id,occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE'
OR next_event >= INTERVAL '10 MINUTE'
OR last_event IS NULL
OR next_event IS NULL
) final
GROUP BY 1,2;
MIN, MAX 함수 : 세션이 인정되는 10분내의 이벤트들
10분내 시작과 끝 세션에 발생하는 이벤트를 잡아내기 위한 쿼리문 출력까지 완료..
전체쿼리
SELECT DATE_TRUNC('week',z.session_start) AS week,
COUNT(*) AS sessions,
COUNT(CASE WHEN z.autocompletes > 0 THEN z.session ELSE NULL END) AS with_autocompletes,
COUNT(CASE WHEN z.runs > 0 THEN z.session ELSE NULL END) AS with_runs
FROM (
SELECT x.session_start,
x.session,
x.user_id,
COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes,
COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs,
COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks
FROM (
SELECT e.*,
session.session,
session.session_start
FROM tutorial.yammer_events e
LEFT JOIN (
SELECT user_id,
session,
MIN(occurred_at) AS session_start,
MAX(occurred_at) AS session_end
FROM (
SELECT bounds.*,
CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
WHEN last_event IS NULL THEN id
ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id,
event_type,
event_name,
occurred_at,
occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
ROW_NUMBER() OVER () AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id,occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE'
OR next_event >= INTERVAL '10 MINUTE'
OR last_event IS NULL
OR next_event IS NULL
) final
GROUP BY 1,2
) session
ON e.user_id = session.user_id
AND e.occurred_at >= session.session_start
AND e.occurred_at <= session.session_end
WHERE e.event_type = 'engagement'
) x
GROUP BY 1,2,3
) z
GROUP BY 1
ORDER BY 1
위 서브쿼리의 조건 내 'search_autocomplete', 'search_run', 'search_click_%' 값 집계
특정 세션에서 자동완성 횟수가 1회 이상인 세션이 370개, 전체검색 횟수가 1회 이상인 세션이 131개
다음과 같은 그래프를 작성 할 준비가 완료된다.