[MODE] Understanding Search Functional 리뷰 - 1

Understanding Search Functionality | SQL Analytics Training - Mode

우리 다음 분기에 검색 기능 개선을 하려고 하는데,
지금 사람들이 어떻게 쓰고 있는지, 어떻게 개선되면 좋을지 데이터 분석 자료가 나오면 참고하려고 합니다.
분석 부탁드려요

 

 

1. 가설 세우기

데이터를 살펴보기 전에 사용자가 검색을 할 때의 몇 가지 가설을 세워보자.

• 검색의 목적은 무엇인가?
• 어떻게 검색의 목적을 만족했는지 알 수 있는가?
• 사용자 별 검색에 대한 퀄리티를 어떻게 정량적으로 이해할 수 있는가?
• 사용자의 검색 경험은 좋은가, 나쁜가?
• 검색해볼 가치가 있는가?
• 검색해볼 가치가 있다면, 구체적으로 무엇을 개선해야 하는가?

 

2. 데이터 살펴보기

Users, Events 총 2개의 테이블이 있다.

 

1) Users 테이블

 

2) Events 테이블

 

Events 테이블에서 알고자 하는 특정 이벤트가 있다.

• search_autocomplete : 유저가 자동 완성 목록 중 하나를 선택
• search_run : 유저가 검색을 실행하고 검색 결과 페이지를 확인
• search_click_X : 1 ~ 10까지의 범위의 검색 결과 리스트 중 x번째 검색 결과 선택

---

Understanding Search Functional : Answers

 

1. Developing Hypotheses

좋은 검색 기능은 유저들이 원하는 것을 쉽게 찾을 수 있도록 도와주고, 최소한의 프로세스로 빨리 찾을 수 있도록 도와준다.

 

📌 검색 기능이 위의 목적을 충족하는지 이해하기 위해 고려해야 할 것

1) Search use : 누가 검색 기능을 사용하는지

2) Search frequency

    - 유저가 검색을 많이 한다면, 검색 기능에 대해 만족을 느낄 가능성이 높음

    - 유저가 짧은 시간 내에 반복적으로 수정하면서 검색을 한다면, 처음에 원하는 결과를 찾지 못하여 검색 단어를 수정하고 있을 가능성이 높음

3) Repeated terms

    - 위의 문제를 해결하기 위한 가장 좋은 방법은 실제 검색 단어의 유사성을 비교해보는 것

    - 그러나 이 작업은 짧은 시간 내에 수행하는 검색 수를 계산하는 것보다 훨씬 느리고 어렵기에 생략

4) Clickthroughs

    - 유저가 검색 결과 중 많은 링크를 클릭한다면, 좋은 결과를 얻지 못할 가능성이 높음

    - 유저가 하나의 결과를 클릭한 후 검색을 수정한다는 것도 원하는 결과를 얻지 못했을 가능성이 높음

       → 이를 통해 클릭한 결과의 수로는 유저의 검색 결과에 대한 만족을 판단하기 어려움

    - 그러나 검색 시 노출되는 순위가 좋은지를 결정할 때는 매우 유용함

       → 유저가 노출 순위가 낮은 결과를 자주 클릭하거나 추가 페이지로 스크롤할 경우, 순위를 조정해야 함

5) Autocomplete Clickthroughs : 자동 완성 기능의 성공 여부는 별도로 측정되어야 함

 

 

2. The state of search

세션 별로 검색에 대해 이해하는 것이 중요하기에 검색 기능이 좋은지 좋지 않은지를 확인하기 전에 문제의 목적을 위해 세션을 정의하는 것이 좋다.

세션은 2개의 이벤트 사이에 10분동안 쉬지 않고 기록된 이벤트의 문자열로 정의된다.

만약 10분동안 이벤트가 기록되지 않는다면 그 세션은 종료되며 다음 이벤트는 새로운 세션으로 간주된다.

 

1) 자동완성 기능 vs 페이지 이동 검색 세션 수 확인

사람들이 얼마나 자주 검색하는지를 시간의 흐름에 맞춰 확인해보자.

▶ 유저들은 검색 결과 페이지로 이동하는 검색보다는 자동완성기능을 더 자주 사용하는 것으로 알 수 있다.

 

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

 

[+] 위의 쿼리문의 테이블들이 서브쿼리로 되어있어서 보기 어려워서 WITH문을 활용하여 쿼리문 수정

WITH bounds AS (
  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
  ), final AS (
  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 bounds
  WHERE last_event >= INTERVAL '10 MINUTE'
      OR next_event >= INTERVAL '10 MINUTE'
   	  OR last_event IS NULL
  	 	OR next_event IS NULL
  	 	), session AS (
  SELECT user_id,
         session,
         MIN(occurred_at) AS session_start,
         MAX(occurred_at) AS session_end
  FROM final
  GROUP BY 1,2
  ), x AS (
  SELECT e.*,
         session.session,
         session.session_start
  FROM tutorial.yammer_events e
  LEFT JOIN 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'
  ), z AS (
  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 x
   GROUP BY 1,2,3
   )

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 z
GROUP BY 1
ORDER BY 1

 

테이블을 하나씩 확인해보자.

 

① tutorial.yammer_events 테이블

 

② bounds 테이블

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

▶ 이전 이벤트와의 차이를 last_event, 다음 이벤트와의 차이를 next_event 컬럼에 추가하였다.

 

③ final 테이블

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 bounds
WHERE last_event >= INTERVAL '10 MINUTE'
    OR next_event >= INTERVAL '10 MINUTE'
 	OR last_event IS NULL
	OR next_event IS NULL

▶ last_event가 10분 이상이라면 이전에 세션이 끝난 것이기 때문에 현 이벤트에서 새롭게 세션이 시작되기에 가지고 있는 id를 출력한다.

▶ last_event가 null 값이면 id를 출력한다.

 

④ session 테이블

SELECT user_id,
       session,
       MIN(occurred_at) AS session_start,
       MAX(occurred_at) AS session_end
FROM final
GROUP BY 1,2

▶ final 테이블에서 user_id와 session으로 GROUP BY를 하여 한 세션당 시작 시간과 종료 시간을 출력한다.

 

⑤ x 테이블

SELECT e.*,
       session.session,
       session.session_start
FROM tutorial.yammer_events e
LEFT JOIN 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'

▶ tutorial.yammer_events 테이블과 session 테이블을 LEFT JOIN한다.

     (발생 시각이 세션 시작 시간보다 크고 세션 종료 시간보다 작은 조건으로 조인 진행)

▶ event_type은 'engagement'로 WHERE 조건절을 걸어서 출력한다.

 

⑥ z 테이블

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 x
 GROUP BY 1,2,3

▶ x 테이블에서 session_start, session, user_id로 GROUP BY한다.

• autocompletes : event_name이 'search_autocomplete'이면 user_id 수 출력
• runs :  event_name이 'search_run'이면 user_id 수 출력
• clicks : event_name이 'search_click_'으로 시작하면 user_id 수 출력

 

⑦ 최종 테이블

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 z
GROUP BY 1
ORDER BY 1

▶ 세션 시작일(week)로 GROUP BY, ORDER BY 한다.

▶ 세션 시작일을 기준으로 각 날짜 별 세션의 수(sessions), 그 중 자동 완성 검색 세션 수(with_autocompletes), 그 중 검색 결과 페이지로 이동하는 검색 세션 수(with_runs)를 출력한다.

 

그래프가 아닌 수치로만 비교해보아도 자동완성검색을 한 세션의 수가 월등히 많은 것을 확인할 수 있다.

 

🔎 DATE_TRUNC 함수
- 시간, 일, 월 등 지정하는 날짜 부분을 기준으로 타임스탬프 표현식을 자름
- DATE_TRUNC('datepart', timestamp)
https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_DATE_TRUNC.html

 

자동 완성 기능은 세션의 약 25%, 검색 결과 페이지로 이동하는 검색의 세션은 8% 정도에서만 사용된다.

▶ 자동완성, 이동 검색 세션 수를 전체로 나누어준 후, 실수형태로 변경해준다.

 

🔎 ::FLOAT
(더 알아보기)
https://stackoverflow.com/questions/10482827/calculations-with-count

 

이후의 내용은 다음 포스팅에서 :)