퍼널 분석과 ARRAY 및 STRUCT 다루기

앱 로그 데이터, ARRAY, STRUCT, UNNEST, PIVOT, EXCEPT

앱 로그 데이터를 보는 관점에서 핵심 포인트

EVENT 기준

• 사용자가 하는 행동 (클릭, 페이지 VIEW 등)
• 시스템 이벤트,오류
• 이벤트 파라미터[ID], 이벤트 파라이터 값[VALUE]

Array(배열)

같은 타입(자료형)의 값을 저장한다 — Python의 List와 유사하다

예를 들어, 음식 메뉴판이나 음악플레이리스트 등

생성하기

대괄호 사용

/*nums 컬럼이 생성되고, 각 3개의 행에 각 배열이 적재됨*/
SELECT [0, 1, 1, 2, 3, 5] AS nums
UNION ALL
SELECT [2, 4, 8, 16, 32]
UNION ALL
SELECT [5, 10]

ARRAY <자료형> 사용

/*nums 컬럼이 생성되고, 1개의 행에 정수형 배열이 생성됨*/
SELECT
ARRAY<INT64>[0, 1, 3] AS nums

배열 생성 함수 사용

SELECT
/*output1 컬럼에 [2024-01-01, 2024-01-08, 2024-01-15, 2024-01-22, 2024-01-29] 배열이 생성됨*/
GENERATE_DATE_ARRAY('2024-01-01', '2024-02-01', INTERVAL 1 WEEK) AS
output1,
/*output2 컬럼에 [1, 3, 5] 배열이 생성됨*/
GENERATE_ARRAY(1, 5, 2) AS output2

ARRAY_AGG 함수 사용 (여러 결과를 마지막에 배열로 저장하고 싶은 경우)

WITH programming_languages AS (
  SELECT "python" AS programming_language 
  UNION ALL
  SELECT "go"
  UNION ALL
  SELECT "scala"
)
SELECT ARRAY_AGG(programming_language) AS output 
FROM programming_languages

ARRAY_AGG 활용한 결과

데이터 접근하기 (OFFSET, ORDINAL)

• OFFSET : 0부터 시작
• ORDINAL : 1부터 시작

각 배열에 몇 번째 요소에 접근할지 위 함수로 지정이 가능하다

배열의 길이보다 큰 값을 참조하면 오류가 발생함으로, SAFE_OFFSET, SAFE_ORDINAL과 같이 SAFE_를 추가하면 NULL로 출력된다

Array 구조 풀기 [Flatten] (with. Cross Join)

: Array의 요소를 독립적인 행으로 평면화할 때 UNNEST를 사용한다

SELECT a.column, alias_name
FROM Table_A AS a
CROSS JOIN UNNEST(ARRAY_Column) AS alias_name

/*CROSS JOIN을 생략하고, ','를 쓸 수도 있음*/
SELECT a.column, {$alias_name}
FROM Table_A AS a, UNNEST(ARRAY_Column) AS {$alias_name}

UNNEST(ARRAY_Column)에서 ARRAY_Column을 잘 선택해야 한다.

*펼치는 기준이 되는 컬럼이라고 이해하면 가장 직관적이려나..?

조회하는 테이블 구조

쿼리와 결과 테이블

STRUCT(구조체)

서로 다른 타입(자료형)의 값을 저장한다 — Python의 딕셔너리와 유사하다

예를 들어, 영화 정보나 매장 정보 등

생성하기

소괄호 사용 (컬럼의 이름을 지정할 수 없음)

SELECT
(1,2,3) AS struct_test

STRUCT<자료형>(데이터) 사용

SELECT
STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test

데이터 접근하기

SELECT 
  struct_test.hi, 
  struct_test.hello
FROM ( 
  SELECT
  STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test 

Array와 Struct는 스스로와 서로를 구성요소로 활용할 수 있다

연습문제

쿼리 실행 순서 : FROM > JOIN > SELECT

SELECT title, actor, character, genres
FROM `inflearn-bigquery-451416.advanced.array_exercises`
/*확장하고 싶은 기준점 1*/
CROSS JOIN UNNEST(genres) AS genres
/*확장하고 싶은 기준점 2*/
CROSS JOIN UNNEST(actors) AS actors

/*CROSS JOIN에서 지정한 값(genres, actors)를 SELECT 절에서 호출해야 함 (단순 * 안됨) */

동일한 값의 변수 일괄 수정

맥북 [CMD + D] & 윈도우 [Ctrl + D]

PIVOT

집계함수(MAX, SUM, COUNT, ANY_VALUE 등), IF, GROUP BY 사용

기본 구조

SELECT 
  기준컬럼명, 
  집계함수(IF(조건, TRUE일 때의 값, False일 때의 값(주로 NULL, 0)) as {$column_name}
FROM {$table_name}
group by 기준컬럼명

*PIVOT하는 테이블에 대한 이해를 높이려면, 집계함수를 뺀 아래 구조의 테이블을 보는 것이 도움이 된다.

SELECT 
  기준컬럼명, 
  IF(조건, TRUE일 때의 값, False일 때의 값(주로 NULL, 0) as {$column_name}
FROM {$table_name}
group by 기준컬럼명

예시 문제

* EXCEPT({$컬럼명}) : {$컬럼명}을 제외하고 모두 조회

/*
WITH CTE AS(
  SELECT 
    user_id,
    event_date, 
    event_name,
    event_timestamp,
    user_pseudo_id,
    event_params.key as id,
    event_params.value.string_value as value
FROM `inflearn-bigquery-451416.advanced.app_logs`
CROSS JOIN UNNEST(event_params) as event_params
)
*/

WITH CTE AS(
  SELECT 
    user_id,
    event_date, 
    event_name,
    event_timestamp,
    user_pseudo_id,
    event_params.key as id,
    event_params.value.string_value as s_value,
    event_params.value.int_value as i_value
FROM `inflearn-bigquery-451416.advanced.app_logs`
CROSS JOIN UNNEST(event_params) as event_params
)
SELECT
  user_id,
  event_date, 
  event_name,
  event_timestamp,
  user_pseudo_id,
  MAX(IF(id = "firebase_screen", s_value, null)) as firebase_screen,
  MAX(IF(id = "session_id", s_value, null)) as session_id,
  MAX(IF(id = "food_id", i_value, null)) as food_id,
FROM CTE
group by   user_id, event_date, event_name, event_timestamp, user_pseudo_id

퍼널 분석

제품 분석의 도식화 (출처 : 카일스쿨 빅쿼리 활용편)

1. 서비스 및 비즈니스 파악 : 서비스의 목표와 기획안
2. 문제 정의 : 핵심 문제 및 목표 정의
3. 퍼널 정의 : 퍼널 매핑 및 퍼널 별 핵심 이벤트 정의
4. 퍼널 분석 : SQL 쿼리 작성 및 개선 우선순위 의사결정
5. 팀 공유

• 가설 도출 및 아이디어 공유
• 이탈 원인 파악
• 개선 사항 우선순위 선정

6. 기능 개발 및 배포

• 성과 트래킹 : AB Test, 지표 모니터링

하나의 퍼널에서 다음 퍼널로 얼마나 전환되는가를 파악

• 이벤트가 명시적으로 존재하는 경우 예를 들어) 메인 화면 진입, 구매 완료 등
• 이벤트가 명시적으로 존재하지 않지만, 모든 페이지의 view가 존재하는 경우 예를 들어) 페이지 view를 기준으로 퍼널 정의

퍼널의 종류

Open 퍼널

• 유저의 이벤트 행동 순서에 상관없이 집계
• 유연한 유저의 여정 : 모든 단계에서 시작될 수 있음(다양한 진입이 가능함)
• 원클릭 결제 등이 있다면 특정 순서를 건너 뛸 수 있음
• 다음 단계가 이전 단계보다 큰 경우도 있을 수 있음
• SQL : 해당 이벤트의 존재 여부만 파악

Closed 퍼널

• 유저가 정해진 단계를 순서대로 거쳐야 집계
• 엄격한 순서를 거쳐야 함(모든 단계가 순서대로 완료됨)
• 다음 단계가 항상 이전 단계보다 작거나 같음
• SQL : 유저 로그 데이터를 기반으로 앞 퍼널의 이벤트가 먼저 진행되었는지 확인

하나의 퍼널에서 다음 퍼널로 얼마나 전환되는가를 파악

전환율이 낮은 곳부터 개선하는 것이 추천되지만, 고객이 제품에 아하모멘트를 느낀 후 (습관을 형성한 후) 전환율을 올리는 것이 좋을 수 있다

제품의 근본적인 부분을 손보지 않고, 특정 전환율만 올리는 것은 해결이 아닐 수 있다

지표 집계 단위

• User 별로 집계할 것인가?
• Page View 별로 집계할 것인가?
• Session 별로 집계할 것인가?

+ 여러 차원(Dimension)으로 집계

• 시간 축 : 일자별 / 주차별 / 월별 등
• 지역 정보
• 사용자 인구 통계

Session이란?

• 일정 시간 동안 유저의 활동이 없으면, 세션이 종료된 것으로 간주 (e.g. 30분)
• 유저가 서비스를 명시적으로 종료 (e.g. 로그아웃)하면 세션이 종료된 것으로 간주

Session 별 집계

Session은 어떻게 구성되는가?

• 사용자의 로그를 기반으로 Session을 직접 생성함

일자가 바뀌는 시점에는 Session이 어떻게 처리되는가?

• 기준을 정의하기 나름
• e.g. 자정에 모든 세션 종료, 새벽 3시 기준으로 일자 구분

Session을 꼭 사용해야 하는가?

한 명의 유저가 하루에도 여러 번 접속하는 서비스라면 Session을 사용하는 것이 좋음

이탈이라는 이벤트가 명시적으로 발생하는가?

로그아웃 등은 명확하지만, 앱이나 웹 서비스에서 로그아웃을 하지 않은 경우가 많음

따라서 [유저의 행동이 일정 시간 이상 동안 없는 경우]를 이탈로 정의하는 경우가 많음

앱 활성화 여부, 사용자 인터렉션 등을 종합적으로 고려하여 Session 기준을 만들어야 함

유저를 식별하는 방법

로그인을 하지 않을 경우, user_id가 없음

user_id가 NULL인 경우 사용할 수 있는 방법

• device_id : 사용자의 기기를 고유하게 식별 앱 : 고유 식별자(IDFA, GAID) 수집 & 웹 : 쿠키 등 device_id는 기기를 변경하거나 앱을 재설치하면 새로운 ID가 할당되며, 수집 동의를 받아야 함

수집 동의는 어느 시점에 유저에게 받는거지..?

• user_pseudo_id : GA/Firebase에서 유저를 식별할 때 사용하는 기준 user_id가 NULL일 경우 user_pseudo_id로 보완해서 사용하기도 함

연습문제

일자별 각 화면으로 진입한 유저의 수 나타내기

--ARRAY UNNEST & PIVOT
WITH CTE1 AS(
  SELECT 
    user_id,
    event_date, 
    event_name,
    event_timestamp,
    user_pseudo_id,
    MAX(IF(event_params.key = "firebase_screen", event_params.value.string_value, NULL)) AS firebase_screen,
    MAX(IF(event_params.key = "session_id", event_params.value.string_value, NULL)) AS session_id,
    MAX(IF(event_params.key = "food_id", event_params.value.int_value, NULL)) AS food_id
FROM `inflearn-bigquery-451416.advanced.app_logs`
CROSS JOIN UNNEST(event_params) as event_params
group by all
--SET event_name_with_screen
), CTE2 AS (
  SELECT
    *, 
    concat(concat(event_name, "-"), firebase_screen) as event_name_with_screen,
    datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM CTE1
--SET STEP_NUMBER
), CTE3 AS (
  SELECT 
    event_date,
    event_name_with_screen,
    CASE
      WHEN event_name_with_screen = "screen_view-welcome" THEN 1
      WHEN event_name_with_screen = "screen_view-home" THEN 2
      WHEN event_name_with_screen = "screen_view-food_category" THEN 3
      WHEN event_name_with_screen = "screen_view-restaurant" THEN 4
      WHEN event_name_with_screen = "screen_view-cart" THEN 5
      WHEN event_name_with_screen = "click_payment-cart" THEN 6
    ELSE NULL
    END AS step_number,
    COUNT(DISTINCT user_pseudo_id) as cnt
  FROM CTE2
  GROUP BY ALL
  HAVING step_number is not null
  ORDER BY event_date, step_number
)
--PIVOT BY 'event_name_with_screen'
SELECT
  event_date,
  SUM(IF(event_name_with_screen = "screen_view-welcome", cnt, NULL)) AS `screen_view-welcome`,
  SUM(IF(event_name_with_screen = "screen_view-home", cnt, NULL)) AS `screen_view-home`,
  SUM(IF(event_name_with_screen = "screen_view-food_category", cnt, NULL)) AS `screen_view-food_category`,
  SUM(IF(event_name_with_screen = "screen_view-restaurant", cnt, NULL)) AS `screen_view-restaurant`,
  SUM(IF(event_name_with_screen = "screen_view-cart", cnt, NULL)) AS `screen_view-cart`,
  SUM(IF(event_name_with_screen = "click_payment-cart", cnt, NULL)) AS `click_payment-cart`,
FROM CTE3
GROUP BY event_date
order by event_date

쿼리 결과

연결된 시트

매개변수 값을 시트에서 입력받아서 쿼리 호출 시 활용할 수 있음