Superset에서 Redshift테이블을 시각화하기(KPI 대시보드)

들어가며

이번주 Redshift부터 Snowflake등을 배우고, 오늘 대시보드에 대해 학습하였다.

8주의 마지막의 날인 오늘은 앞서 배운 Redshift와 대시보드를 활용하여 데이터를 시각화해보는 실습을 진행한다.

실습 개요는 다음과 같다.

---

 "실습 소개 및 준비단계" 

1. 실습명

AWS Redshift의 analytics 스키마의 테이블 2개를 Superset에서 2개의 chart로 만들고 대시보드로 시각화하기
부제 : Superset에서 NPS 컬럼 차트를 KPI 대시보드에 추가하기

(1) 목표 결과물

1. 채널별 Monthly Active User차트
   • dataset은 S3의 analytics.user_session_summary 기반
2. Monthly Cohort 차트
   • dataset은 S3의 analytics.cohort_summary 기반
3. 1번, 2번로 하나의 대시보드 생성
   • Key Metrics 이름의 대시보드 생성

이를 위해서는 Redshift에 다음과 같은 테이블이 필요하다.

 

(2) 필요한 Redshift 테이블 정보

• raw_data.user_session_channel
• raw_data.session_timestamp
• raw_data.session_transaction
• analytics.user_session_summary
• analytics.cohort_summary

이미 있는 raw_data 스키마 아래의 3개의 테이블

 

raw_data 스키마아래의 3개의 테이블 같은 경우는, 이전 실습에서 테이블을 생성하여 S3로부터 COPY명령어로 Bulk Update해와서 이미 셋팅된 상황이다.

 

그리고 analytics 스키마 아래의 2개의 테이블은 아직 없는 상황이고, raw_data의 테이블을 기반으로 분석 테이블을 만들어줘야된다.

차례로, user_session_summary와 cohort_summary를 만들어보자.

생성은 AWS Redshift query editor를 이용하여 작업하였다.

 

 

 

 

 

 

 

테이블 스키마

 

---

2. 실습에 필요한 분석 테이블 만들기 

(1) cohort_summary 생성하기

(1).1 📶 코호트 분석이란 ?

코호트 (Cohort) :

• 같은 속성을 같은 사용자 그룹
• 보통 속성은 사용자의 서비스 등록일

코호트 분석이란?

• 코호트를 기반으로 ‘사용자의 이탈률, 잔존률, 총 소비금액’ 등을 계산함
• 코호트 기반 사용자 잔존률(Retension)은 보통 월 기반으로 시각화해서 보는것이 일반적

 

(1).2 테이블 생성하기

이번에 생성할 analytics.cohort_summary 테이블은 코호트 분석을 위한 테이블로, 

사용자별로 처음 방문한 달(cohort_month), 그리고 방문한 모든 달의 정보를 지닌 테이블이다. 

 

[ 필드명 ] 

cohort_month : 처음 방문한 달 (한 사용자에 대해서는 동일한 값)

visited_month : 방문한 달

cohort.userid : 사용자

 

사용자가 방문한 달 수마다 레코드가 생길 것이다.

 

[ 테이블 생성 SQL ]

-- cohort_summary 생성
CREATE TABLE analytics.cohort_summary as
SELECT cohort_month, visited_month, cohort.userid
FROM (
    SELECT userid, date_trunc('month', MIN(ts)) cohort_month
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
    GROUP BY 1
) cohort
JOIN (
    SELECT DISTINCT userid, date_trunc('month', ts) visited_month
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid
) visit ON cohort.cohort_month <= visit.visited_month and cohort.userid = visit.userid;

• 더 정확한건 Count(distinct(userid)) 임 -> 그러나 본 테이블은 중복이 없기 때문에 distinct 생략함 
• DATEDIFF 함수를 써서 cohort랑 visited 간의 개월수 차이를 계산함
  • 같으면 0이됨

[ 구글 스프레드 시트로 확인하기] => 실패

코호트 데이터를 대시보드화 하기 전에 미리 잘 분포되어 있고 이상치 없는지 확인해보고자 구글 스프레드 시트로 데이터를 로드하여 확인해보려 했지만, 아래에서 막혔다.......💥

 

Redshift query editor에서 cohort_summary 테이블을 전체를 조회하고 그 결과를 CSV로 저장한다.

CSV로 추출

export 눌렀지만, redshift 무료 계정이라 export는 못하나보다...💦

아쉽지만 이는 pass하고 넘어가겠다. 

 

 

(2) user_session_summary 생성하기

(2)-1. 테이블 생성하기

[ 테이블 생성 SQL ]

-- user_session_summary 생성
CREATE TABLE analytics.user_session_summary AS
SELECT usc.*, t.ts
FROM raw_data.user_session_channel usc
LEFT JOIN raw_data.session_timestamp t ON t.sessionid = usc.sessionid

결과물 

analytics아래 2개의 테이블

analytics 아래에 2개의 분석 테이블 생성 완료했다.

이제 Superset으로 Redshift에 연결해서 대시보드 작업을 해보자

 

---

" Superset으로 대시보드 작업 "

1. MAU 차트 만들기

(1) DB 연결

Host는 작업 그룹의 엔드포인트에서 뒤에 포트 번호 뺀 것이고,

USERNAME과 PW는 이전에 설정한 관리자 계정 입력한다.

 

이때 계정은 AWS Redshift의 관리자 권한이 있어야한다.

없으면 dataset 불러올때 권한 문제로 못 가져온다. (중요)

관리자 계정이 아니면 permission 오류 발생함

 

 

연결되면 Databases 목록에 Redshift가 뜬 것을 확인할 수 있다.

이후 Datasets 탭에서 가서 데이터를 불러오자.

 

(2) Dataset 추가 

주어진 순서대로 DATABASE > SCHEMA 선택 후 user_session_summary 테이블을 선택하고 불러온다. 

 

(3) Chart 만들기

그리고 Chart 목록에서는 Line 차트 선택한다.

 

제일 먼저 차트의 이름을 지정한다. 

 

그리고 다음과 같이 chart 속성을 설정해준다.

X축은 ts에서 month만 나오게 지정하고

y축은 user 수를 

그리고 데이터는 channel별로 나오게 설정한다. 

 

이때 y축은 따로 쿼리문을 지정해줘서 COUNT(DISTINCT userid)) 값이 되도록 한다.

CUSTOM SQL로 작성한 Metrics

 

(4) 저장

만들었으니 SAVE 버튼을 눌러 저장하자!

 

 

---

 

2. Cohort 차트 만들기

(1) Dataset 불러오기

위 과정과 똑같이 이번엔 cohort_summary테이블 데이터 정보를 불러온다.

 

(2) Chart 만들기

이번 chart는 Pivot 차트로 선택한다.

그리고 chart 이름 지정 후, 사진과 같이 chart 속성을 지정한다.

그러면 위와 같이 차트가 완성된다.

여기서 아쉬운 점은 Columns가 단순 월이라 정렬이 오른쪽으로 되어있어 가독성이 떨어진다.

이를 cohort_month와 visited_month의 차로 값을 바꾸고 다시 chart를 만들어보자.

 

Column 변경

 

그러면 아래와 같이 왼쪽부터 잘 정렬이 됐고, 한눈에 파악하기 편해졌다.

위의 chart를 잠깐 해석해보면
2019년 5월에 가입한 사용자가 281명인데, 그후 1달이 지났을 때는 그 중 262명의 사용자가 남아있다는 의미이다. 

 

만든 차트를 이제 저장하며 대시보드로 가져가보자.

 

(3) 저장하고 Dashboard 만들기

save as 로 저장하며 ADD TO DASHBOARD로 같이 가져간다. 

그리고 아까 1에서 만들어준 MAU 차트를 방금 만든 KPI 대시보드에 추가해준다.

 

최종: 대시보드 확인하기

Dashboard 탭으로 가서 KPI Dashboard를 클릭하면 MAU 차트와 Cohort차트가 있는 대시보드가 보일 것이다.

만약 좌우가 잘려있다면, EDIT DASHBOARD버튼을 누르고 차트 좌우로 사이즈를 조절하면 된다. 

완성된 대시보드

 

여기까지 하면 실습 목표까지 잘 완료했다 ! 

추가적으로 NPS 지표로 chart 2개를 만들어 위의 KPI 대시보드에 추가할 수 있는데 계속 이어서 만들어보자.

 

---

3. Nps 차트 만들기

(1)  NPS란 

기업은 고객들에게 '주변에 추천하겠는가?' 라는 질문에 대한 답변으로 0부터 10까지 고객 만족도를 계산한다.  

이때 0~6점은 비추천자(detractor), 7~8점은 passive(소극자), 9~10점은 promoter(홍보자)로 구분한다.

 

이때 NPS 지수는 promoter 퍼센트 - detractor 퍼센트를 한 값이 된다.

 

 

nps지수는 주로 월별로 진행되며, 본 실습에서는 raw_data.nps 테이블을 이용한다. 

그리고 만들 차트는 2개로 다음과 같다.

  - Nps Summary : 월별 고객 유형과 nps지수의 pivot 그래프 
  - Monthly NPS : 월별 NPS 지수의 line 그래프

 

바로 만들어보자

 

(2) dataset으로 raw_data.nps 불러오기

위에서 했으니 설명 생략

 

(3) NPS Summary 차트 만들기 

chart 종류는 pivot으로 선택한다.

이전엔 raw_data가지고 nps_summary 만들었는데, 이번엔 대시보드로 바로 가져오는 것이다.  

-- nps_summary 만들던 쿼리문
CREATE TABLE analytics.nps_summary AS
SELECT
  month,
  ROUND((promoters-detractors)::float/total_count*100, 2) AS overall_nps
FROM (
  SELECT LEFT(created_at, 7) AS month,
  COUNT(CASE WHEN score >= 9 THEN 1 END) AS promoters,
  COUNT(CASE WHEN score <= 6 THEN 1 END) AS detractors,
  COUNT(CASE WHEN score > 6 AND score < 9 THEN 1 END) As passives,
  COUNT(1) AS total_count
 FROM raw_data.nps
 GROUP BY 1
 ORDER BY 1
);

-- 또는 아래 방법도 됨
SELECT LEFT(created, 7) AS month,
 ROUND(SUM(CASE
 WHEN score >= 9 THEN 1
 WHEN score <= 6 THEN -1 END)::float*100/COUNT(1), 2)
FROM raw_data.nps
GROUP BY 1
ORDER BY 1;

 

바로 chart 속성을 지정해보자.

Rows와 Metrics를 직접 custom화한 것으로 지정해줘야된다.

custom화 한 rows와 metrics

• month : LEFT(created_at, 7)
• promoters : COUNT(CASE WHEN score >= 9 THEN 1 END) 
• detractors : COUNT(CASE WHEN score <= 6 THEN 1 END)
• passives : COUNT(CASE WHEN score > 6 AND score < 9 THEN 1 END)
• total_count : COUNT(1) 
• overall_nps : ROUND((promoters-detractors)::float/total_count*100, 2)

그리고 chart에 색깔을 주고 싶으면 CUSTOMIZE 탭으로 가서 설정하면 된다.

 

 

다 만들었으면 SAVE 누르고 KPI 대시보드에 추가해주자

 

 

(3) Monthly NPS 차트 만들기 

바로 다음 차트를 만들어보자.

이번 차트는 line chart이다.

 

이번에는 X축은 create_at필드에서 Month화하고, METRICS만 CUSTOM으로 지정해주면 된다.

• overall_nps : ROUND(SUM(CASE
   WHEN score >= 9 THEN 1
   WHEN score <= 6 THEN -1 END)::float*100/COUNT(1), 2)

자 마지막 SAVE를 하고 대시보드에 추가해주자

---

" 최종 대시보드 "

대시보드에서 설명을 추가하고 레이아웃 설정하는 등을 하고 완성한 대시보드이다.

NPS 지표와 MAU 지표 그리고 Cohort 지표를 보여주는 KPI 대시보드이다. 

 

 

 

---

" 마치며 "

이렇게 8주차 결과물을 최종 대시보드로 마무리하게 되었다.

 

데이터 웨어하우스인 Redshift를 이용해 S3의 데이터를 불러와보고, 이를 분석 테이블로 만드는 과정을 거쳐 시각화인 차트/대시보드까지 만들게되었다.

일련의 과정을 이어서 '대시보드'라는 결과물을 만들어내니 매우 뿌듯하며,
대시보드를 보며 작업한 과정들이 파노라마처럼 머리속을 지나간다. 🎞

 

이번 실습을 통해 ETL, ELT 과정을 확실히 이해할 수 있었고, 대시보드가 데이터 지표를 파악하기 쉽다는 것을 다시 한 번 느끼게 되었다. 

 

만약 추가 학습을 한다면 MAU외에도 DAU, WAU도 추가할 수 있을 것 같다.

 

우선은 다음주부터는 프로젝트 기간이니까, 주말동안 쉬면서 부족한 부분을 학습해야지

 

다음주에 배운 기술들을 잘 활용해서 좋은 프로젝트 결과가 나오길 바라며, 

다음주도 화이팅해보자고🏃‍♀️