(1) Table
기본 테이블 형태의 쿼리결과에 기능추가 가능
-
컬럼 숨기기
- 눈모양클릭
-
검색기능, 타입변경기능, 포맷기능
-
url의 경우 이미로 변환 가능
(2) COUNTER
- 디스플레이에 노출할 컬럼선택
- 몇번째 행을 counter 할지 정수입력
- 비교할 컬럼 선택
- 비교할 행 정수 입력
- Format으로 숫자형 변경가능
- Format Target Value 클릭 시 counter와 target 포맷 동일하게 설정
(3) Bar & Line
- X축 : 시간컬럼
- Y축 : 측정값 컬럼
- Normalize values to percentage 클릭 시 %로 변경 가능
- X Axis, Y Axis 에서 각각 x축 y축 범위 및 라벨입력 가능
- Color에서 색상변경
- Data Label에서 데이터 표기 및 포맷
(4) Area, Pie
전체에대한 기준값별 비율 확인
groupby에 기준값 입력
(5) Box Plot
데이터 분포 및 아웃라이어 확인
(6) Cohort
- 일 단위 재방문 유저 수
-- [Table Info]
-- query_1 : user
-- query_2 : product
-- query_3 : order
-- query_4 : category
-- query_5 : event
-- 1-1. 일 단위 재방문 유저 수
WITH raw AS (
SELECT user_id, strftime("%Y-%m-%d",event_timestamp) as dt
FROM query_5
WHERE event_timestamp BETWEEN "{{ DATE.start }}" AND "{{ DATE.end }}"
)
, first_visit as (
SELECT user_id
, min(dt) as min_dt
FROM raw
GROUP BY 1
)
SELECT fv.min_dt
, julianday(raw.dt) - julianday(fv.min_dt) as diff
, count(distinct fv.user_id) as users
FROM first_visit as fv
LEFT JOIN raw
ON raw.user_id = fv.user_id
GROUP BY 1,2
ORDER BY 1,2
🌟 시간기준 컬럼이 꼭 있어야 한다 !
- 주 단위 재방문 유저 수 (Pivot)
-- [Table Info]
-- query_1 : user
-- query_2 : product
-- query_3 : order
-- query_4 : category
-- query_5 : event
-- 1-2. 주 단위 재방문 유저 수
WITH raw AS (
SELECT user_id, strftime("%W",event_timestamp) as dt
FROM query_5
WHERE event_timestamp BETWEEN "{{ DATE.start }}" AND "{{ DATE.end }}"
)
, first_visit as (
SELECT user_id
, min(dt) as min_dt
FROM raw
GROUP BY 1
)
SELECT fv.min_dt
, raw.dt - fv.min_dt as diff
, count(distinct fv.user_id) as users
FROM first_visit as fv
LEFT JOIN raw
ON raw.user_id = fv.user_id
GROUP BY 1,2
ORDER BY 1,2
쿼리문에 해당하는 컬럼들로 피봇을 그린 후 heatmap을 설정할 수도 있음
- 월 단위 재방문 유저 수 (쿼리로 Pivot)
-- [Table Info]
-- query_1 : user
-- query_2 : product
-- query_3 : order
-- query_4 : category
-- query_5 : event
-- 1-2. 주 단위 재방문 유저 수
WITH raw AS (
SELECT user_id, strftime("%m",event_timestamp) as dt -- 현재 데이터는 2022년도 데이터이므로 월만 뽑아서 사용용
FROM query_5
WHERE event_timestamp BETWEEN "{{ DATE.start }}" AND "{{ DATE.end }}"
)
, first_visit as (
SELECT user_id
, min(dt) as min_dt
FROM raw
GROUP BY 1
)
, agg as (
SELECT "2022 - " || cast(fv.min_dt as string) as month_dt
, raw.dt - fv.min_dt as diff
, count(distinct fv.user_id) as users
FROM first_visit as fv
LEFT JOIN raw
ON raw.user_id = fv.user_id
GROUP BY 1,2
ORDER BY 1,2
)
SELECT *
, (diff_1_users / diff_0_users) * 100 as diff_1_retention
, (diff_2_users / diff_0_users) * 100 as diff_2_retention
, (diff_3_users / diff_0_users) * 100 as diff_3_retention
, (diff_4_users / diff_0_users) * 100 as diff_4_retention
FROM (
SELECT month_dt
, sum(case when diff = 0 then users end) diff_0_users
, sum(case when diff = 1 then users end) diff_1_users
, sum(case when diff = 2 then users end) diff_2_users
, sum(case when diff = 3 then users end) diff_3_users
, sum(case when diff = 4 then users end) diff_4_users
FROM agg
GROUP BY 1
)
쿼리 자체로 cohort를 생성 할 수도 있음
0으로 나타나는 이유는 diff컬럼들이 string으로 인식되어서 인데..
SQLite 처음 써봐서 이유를 모르겠다..
(7) Funnel
-- [Table Info]
-- query_1 : user
-- query_2 : product
-- query_3 : order
-- query_4 : category
-- query_5 : event
-- 2. 구매까지 퍼널 단계 별 유저 수
-- 전환이벤트 : 화면 방문 단위 전환 (visit, page)
-- 퍼널 : checkout > cart > PDP > PLP >Home
SELECT
case when screen = "home" then "1_home"
when screen = "plp" then "2_plp"
when screen = "pdp" then "3_pdp"
when screen = "cart" then "4_cart"
when screen = "checkout" then "5_checkout"
end as screen_stage
, count(*) as event_cnt
, count(distinct user_id) as user_cnt
FROM query_5
WHERE event_timestamp between "{{date.start}}" and "{{date.end}}"
AND event_type = "visit" and object_type = 'page'
GROUP BY 1
ORDER BY 1
패스트캠퍼스 Data Mater Class
Study Note
Study Log