[BigQuery] 입력 양식 최적화하기
<데이터 분석을 위한 SQL 레시피> 6장 16강을 실습한 내용입니다.
샘플데이터
1. 확인 화면에서의 오류율을 집계하는 쿼리
확인 화면에서의 오류율을 집계하는 쿼리
/confirm 페이지에서 오류가 발생해 재입력 화면을 출력하는 경우를 집계한다.
SELECT
COUNT(*) AS confirm_count
,SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) AS error_count
,AVG(CASE WHEN status = 'error' THEN 1.0 ELSE 0.0 END) AS error_rate
,SUM(CASE WHEN status = 'error' THEN 1.0 ELSE 0.0 END) / COUNT(DISTINCT session)
AS error_per_user
from stately-forest-350015.sql_training.form_log
where
--확인 화면 페이지 판정하기, 분모로 URL이 '/regist/confirm'인 경우를 출력
path = '/regist/confirm'
;
2. 입력 ~ 확인 ~ 완료까지의 이동률 집계하기
로그를 이용하여 '입력 ~ 확인 ~ 완료'까지의 폴아웃 리포트를 만드는 쿼리
(※ 해당 쿼리에서는 입력화면에서 사용자의 의사가 있었는지 없었는지 판별할 수 없음)
WITH
mst_fallout_step AS (
--/regist 입력 양식의 폴아웃 단계와 경로 마스터
select 1 AS step, '/regist/input' AS path
union all select 2 AS step, '/regist/confirm' AS path
union all select 3 AS step, '.regist/complete' AS path
)
, form_log_with_fallout_step AS (
select
l.session
,m.step
,m.path
--특정 단계 경로의 처음/마지막 접근시간 구하기
, MAX(l.stamp) AS max_stamp
, MIN(l.stamp) AS min_stamp
from
mst_fallout_step AS m
JOIN
stately-forest-350015.sql_training.form_log AS l
ON m.path = l.path
-- 확인 화면의 상태가 오류인것만 추출하기
where status = ''
--세션별로 단계 순서와 경로 집약하기
group by l.session, m.step, m.path
)
,form_log_with_mod_fallout_step AS (
SELECT
session
,step
,path
,max_stamp
--직전 단계 경로의 첫 접근시간
,LAG(min_stamp) --처음 접근시간 직전
OVER(PARTITION BY session ORDER BY step)
AS lag_min_stamp
--세션 내부의 최솟값
,MIN(step) OVER(PARTITION BY session) AS min_step
--해당 단계에 도달할 때까지의 누계 단계수
,COUNT(1)
OVER(PARTITION BY session ORDER BY step
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cum_count
from form_log_with_fallout_step
)
, fallout_log AS (
SELECT
session
,step
,path
from
form_log_with_mod_fallout_step
where
--세션 내부에서 단계순서가 1인 url에 접근하는 경우
min_step = 1
--현재 단계 순서가 해당 단계의 도착할 때까지의 누계 단계 수와 같은 경우
AND step = cum_count
--직전단계의 첫 접근시간이 NULL 또는 현재단계의 최종시간보다 앞인 경우
AND (lag_min_stamp IS NULL OR max_stamp >= lag_min_stamp)
)
SELECT
step
,path
,COUNT(1) AS count
-- <단계순서> = 1인 URL로부터의 이동률
,100.0 * count(1)
/FIRST_VALUE(COUNT(1))
OVER(ORDER BY step ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS first_trans_rate
--직전 단계로부터의 이동률
,100.0*COUNT(1)
/ LAG(COUNT(1)) OVER(ORDER BY step ASC)
AS step_trans_rate
from fallout_log
group by step, path
order by step
;
3. 입력 양식 직귀율 집계하기
입력 양식 직귀율 : 입력화면으로 이동한 후 입력시작, 확인화면, 오류화면으로 이동한 로그가 없는 상태의 레코드 수를 센 것
입력 양식 직귀율은 입력화면 방문 횟수를 분모에 두고, 입력양식 직귀 수의 비율을 집계하면 계산할 수 있다.
( ※ 해당 쿼리는 페이지 열람로그를 사용해서 앞의 리포트를 작성해도, 입력 시작 후에 이탈했는지 또는 입력도 안 하고 이탈했는지 판별할 수 없다.)
WITH
form_with_progress_flag AS (
SELECT
substr(stamp, 1, 10) AS dt
,session
--입력 화면으로의 방문플래그 계산하기
,SIGN(
SUM(CASE WHEN path IN ('/regist/input') THEN 1 ELSE 0 END)
) AS has_input
--입력 확인 화면 또는 완료 화면으로의 방문 플래그 계산하기
,SIGN(
SUM(CASE WHEN path IN ('/regist/confirm', '/regist/complete') THEN 1 ELSE 0 END)
) AS has_progress
FROM stately-forest-350015.sql_training.form_log
GROUP BY dt, session
)
SELECT
dt
,COUNT(1) AS input_count
,SUM (CASE WHEN has_progress = 0 THEN 1 ELSE 0 END) AS bounce_count
,100.0*AVG(CASE WHEN has_progress = 0 THEN 1 ELSE 0 END) AS bounce_rate
FROM
form_with_progress_flag
WHERE
--입력 화면에 방문했던 세션만 추출하기
has_input = 1
GROUP BY dt
;
4. 오류가 발생하는 항목과 내용 집계하기
샘플데이터
form_log만으로는 발생한 오류가 어떤 원인으로 발생했는지 알 수 없다.
입력 양식에서 오류가 발생한 경우에는 아래 데이터처럼 적절한 오류로그를 출력하도록 하는 것이 좋다.
(이 데이터는 입력 양식을 입력할 때 무엇을 입력했는지 리포트하고자 입력된 문자열을 로그 그대로 출력)
각 입력 양식의 오류 발생 장소와 원인을 집계하는 쿼리
입력 양식의 종류, 입력항목, 오류종류로 집약하고 오류 수와 전체에서 차지하는 비율을 계산한다.
SELECT
form
,field
,error_type
,COUNT(1) AS count
--전체에서 차지하는 비율을 구하고자 sum윈도 함수로 전체 오류수의 모수를 구함.
,100.0*COUNT(1)/SUM(COUNT(1)) OVER(PARTITION BY form) AS share
FROM
`sql_training.form_error_log`
GROUP BY form, field, error_type
ORDER BY form, count DESC
;
(share 필드: 폼 단위로 비율을 집계한 것)