[BigQuery] 입력 양식 최적화하기

<데이터 분석을 위한 SQL 레시피> 6장 16강을 실습한 내용입니다.

 

 

 

샘플데이터

form_log (입력 양식에서의 출력 로그와 오류 로그)

---

 

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
;

eks

 

---

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만으로는 발생한 오류가 어떤 원인으로 발생했는지 알 수 없다.

입력 양식에서 오류가 발생한 경우에는 아래 데이터처럼 적절한 오류로그를 출력하도록 하는 것이 좋다.

(이 데이터는 입력 양식을 입력할 때 무엇을 입력했는지 리포트하고자 입력된 문자열을 로그 그대로 출력)

form_error_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 필드: 폼 단위로 비율을 집계한 것)