[Yammer/Mode] 유저 인게이지먼트 하락의 원인 분석(Investigating a Drop in User Engagement), Retention chart

 

Investigating a Drop in User Engagement
(유저 인게이지먼트 하락의 원인 분석)

https://mode.com/sql-tutorial/a-drop-in-user-engagement/

https://inf.run/T4EM

 

 

문제 상황 :

주간활성유저(WAU) 감소에 대한 원인 파악이 필요하다. (+가능하다면 해결책 제시)

 

활성유저 정의 :

Yammer defines engagement as having made some type of server call by interacting with the product (shown in the data as events of type "engagement"). Any point in this chart can be interpreted as "the number of users who logged at least one engagement event during the week starting on that date."

(engagement 정의는 로그인 여부, 결제 여부 등 회사마다 다를 수 있다.)

 

WAU(weekly active users) 쿼리 :

SELECT DATE_TRUNC('week', occurred_at) AS week,
        COUNT(DISTINCT user_id) AS weekly_active_users
FROM tutorial.yammer_events
WHERE occurred_at BETWEEN '2014-04-28 00:00:00' AND '2014-08-25 23:59:59'
      AND event_type = 'engagement'
      AND event_name = 'login'
GROUP BY week
ORDER BY week

* 실제 데이터는 매우 방대하므로 날짜 지정 잊지 말기!

 

■  DATE_TRUNC( ‘기준’, 타임스탬프가 있는 컬럼명) - PostgreSQL
필요한 만큼 타임스탬프를 취하고 나머지는 디폴트 처리.
https://mode.com/blog/date-trunc-sql-timestamp-function-count-on/
예를 들어 month 기준으로 수행하면  2000-07-17 10:10:10 ▶ 2000-07-17 00:00:00

 

 

---

 

 

어떤 이유가 있을 수 있을까. 생각나는 대로 마구 상상해보기.

우선 Users 테이블에서 유저 상태가 active / pending 으로 나뉘는데, 시스템상의 오류로 pending -> active 전환이 안되고 있을 수도.

혹은 액티브 유저의 상태가 다시 보류 상태로 돌아가서 이용을 못하고 있거나.

회사ID를 통해 이용하는 서비스인데 특정 회사 계정에 문제가 있다든지..

이메일 이벤트 테이블이 따로 있는 걸로 봐서 이용자와 닿는 주요 채널인 거 같은데,

메일링 서비스가 제 역할(유저의 접속 유도)을 제대로 못하고 있거나, 이메일 발송에 문제가 있어서 발송이 되고 있지 않을 수도.

 

▼

 

모드 홈페이지에서 제시한 가설들 & 고려해볼 만한 점

1. 업무용 툴이니까 주말, 공휴일에 engagement 하락할 것

2. 야머 앱 내에 문제가 있어서 사람들이 제대로 이용하지 못할 때 engagement 하락 -> 디바이스 타입별로 체크하면 좋을 듯

3. 유저들이 이용하는 것과 별개로 로그 수집하는 과정에 문제가 있으면 데이터가 누락되면서 하락한 것처럼 보일 수 있음

4. 봇에 의해 이뤄지는 액션에 문제가 생기는 경우 (ex. product, infrastructure 변경 등으로 봇이 사이트와 상호작용하는 데에 문제가 생김)

5. 외부로부터 트래픽 유입이 막힌 상황

6. 마케팅 이벤트 때문에 유입된 유저에 의해 engagement가 훅 늘었다가 다시 줄고 있는 과정이 아닌지

7. QA 등으로 사내에서 발생하는 트래픽과 외부 유입 계정에 의한 활동을 구분하지 않아서 생기는 변화폭 (일반 유저의 이용 하락이 아닐 수 있다)

8. 검색엔진 로직에 따른 노출도 변화

 

 

■  QA (Quality Assurance)

제품이나 서비스의 품질 및 프로세스를 개선하기 위한 활동. 테스트 및 시나리오 관리를 통한 환경 안정화

 

 

원인에 대한 가설을 세운다면 어떤 데이터에 접근해야 하는지 명확하게 보인다.

또한 어떤 것부터 파악해볼지 우선순위를 세우고 분석에 임하도록.

 

 

---

 

 

1. 신규 이용자 하락에 따른 결과 아닐까? 추이를 체크해보자 = 가장 빠르고 쉽게 체크할 수 있는 지표

https://app.mode.com/modeanalytics/reports/9406529cf9a9

SELECT DATE_TRUNC('day',created_at) AS signup_date,
        COUNT(distinct user_id) AS signup_users,
        COUNT(CASE WHEN activated_at IS NOT NULL THEN user_id ELSE NULL END) AS activated_users
FROM tutorial.yammer_users
WHERE created_at BETWEEN '2014-06-01 00:00:00' AND '2014-08-31 23:59:59'
GROUP BY signup_date
ORDER BY signup_date

+)

평일/주말 차이가 있으므로 주/월 단위로 그래프를 그려 파악하기 = 패턴 파악이 용이함

전일대비 증가율 보기.

 

 

 

2. 신규 이용자에 문제가 없어 보이므로 기존 이용자 체크.

가입시기를 기준으로 그룹 나눠서(cohort) 살펴보자

https://app.mode.com/modeanalytics/reports/b87dc5c175a7/runs/8c9cb7705e1b/embed

SELECT DATE_TRUNC('week', x.occurred_at) AS week,
      COUNT(DISTINCT CASE WHEN x.user_age >=70 THEN x.user_id ELSE NULL END) AS "10+ weeks",
      COUNT(DISTINCT CASE WHEN x.user_age < 70 AND x.user_age >= 63 THEN x.user_id ELSE NULL END) AS "9 weeks"
	  -- 이하생략
FROM (SELECT u.user_id, e.occurred_at,
             EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) as user_age
      FROM tutorial.yammer_users u
            INNER JOIN tutorial.yammer_events e
            ON u.user_id = e.user_id
            AND e.event_type = 'engagement'
            AND e.event_name = 'login'
      WHERE u.activated_at IS NOT NULL) x
GROUP BY 1
ORDER BY 1

-- +날짜지정

가입한지 10주가 넘은 기존 유저 코호트의 WAU 감소를 확인할 수 있다.

= 마케팅에 인한 일회성 등락이나, 검색엔진 등 외부 유입에 의한 트래픽 변화가 아님.

 

■  retention chart

사용자의 engagement가 유지되는지 체크

리텐션데이터 더 공부해보기 https://kmarketing7.co.kr/blogPost/untitled-57

 

 

 

3. 유저의 디바이스 타입별로 나눠서 살펴보자.

https://app.mode.com/modeanalytics/reports/32d25b4a8b00/runs/89d67e419adb/embed

SELECT DATE_TRUNC('week', occurred_at) AS week,
      COUNT(DISTINCT CASE WHEN e.device IN ('macbook pro','lenovo thinkpad','macbook air','dell inspiron notebook',
      'asus chromebook','dell inspiron desktop','acer aspire notebook','hp pavilion desktop','acer aspire desktop','mac mini')
                      THEN e.user_id ELSE NULL END) AS computer,
      COUNT(DISTINCT CASE WHEN e.device IN ('iphone 5','samsung galaxy s4','nexus 5','iphone 5s','iphone 4s','nokia lumia 635',
      'htc one','samsung galaxy note','amazon fire phone')
                      THEN e.user_id ELSE NULL END) AS phone,
      COUNT(DISTINCT CASE WHEN e.device IN ('ipad air','nexus 7','ipad mini','nexus 10','kindle fire','windows surface',
        'samsumg galaxy tablet')
                      THEN e.user_id ELSE NULL END) AS tablet
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
      AND e.event_name = 'login'
GROUP BY 1
ORDER BY 1

* 모든 디바이스를 한 그래프 내에 보면 y축 설정값 때문에 변동폭이 잘 보이지 않는다. 개별 그래프로 체크할 것!

컴퓨터를 이용하는 케이스에 비해서 휴대폰, 태블릿 유저 감소폭이 크다.

= 모바일 앱에 관련된 문제가 있을 수 있으니 최근 업데이트 내용 등을 체크.

체크) computer WAU가 가지는 계절성

 

 

 

4. 기존 이용자를 끌어들이는 digest email 체크

https://app.mode.com/modeanalytics/reports/8d9722caeccb/runs/692925581af1/embed

SELECT DATE_TRUNC('week', occurred_at) AS week,
      COUNT(CASE WHEN e.action = 'sent_weekly_digest' THEN e.user_id ELSE NULL END) AS weekly_emails,
      COUNT(CASE WHEN e.action = 'email_open' THEN e.user_id ELSE NULL END) AS email_opens,
      COUNT(CASE WHEN e.action = 'email_clickthrough' THEN e.user_id ELSE NULL END) AS email_clickthroughs
FROM tutorial.yammer_emails e
GROUP BY 1
ORDER BY 1

위 코드처럼 피봇팅

or 액션별 카운트 + 다른 툴로 피봇테이블 작성

확인해보니 이메일을 열어보는 것 까지는 문제없지만, 수신 후 email_clickthroughs (이메일 내 링크 클릭) 유저가 줄고 있음을 확인할 수 있다.

 

 

 

결론.

WAU  감소의 원인은 ①모바일 사용 환경, ②digest email 과 관련이 있을 것으로 보인다.

 

 

---

 

[참고]

 

https://team.postype.com/post/9405656

기획자와 마케터를 위한 활성 사용자 총정리

https://url.kr/lcis49

파워유저 커브(스마일 커브)

파워유저커브

= 한 번만 방문한 사람에 비해 2번 방문한 사람은 적을 테고, 3번 방문자는 더 적고...

하지만 충성고객이 늘어나면 매일 방문하는 유저가 늘어남으로써 그래프 모양이 스마일처럼 됨.