인프런 커뮤니티 질문&답변

민영 김님의 프로필 이미지
민영 김

작성한 질문수

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

1-11. 퍼널 분석 의사 결정과 섹션 정리

[빠짝스터디 1주차 과제] ARRAY, STRUCT 연습문제 / PIVOT 연습문제 / 퍼널별 전환율을 쉽게 구할 수 있도록 PIVOT해보기

작성

·

16

0

연습문제

CREATE OR REPLACE TABLE advanced.array_excercised AS #DDL
SELECT movie_id, title, actors, genres
FROM (
	SELECT
		1 AS movie_id,
		'Avengers: Endgame' AS title,
		ARRAY<STRUCT<actor STRING, character STRING>>[
			STRUCT('Robert Downey Jr.', 'Tony Stark'),
			STRUCT('Chris Evans', 'Steve Rogers')
		] AS actors,
		ARRAY<STRING>['Action', 'Adventure', 'Drama'] AS genres
	UNION ALL
	SELECT
		2, 
		'Inception',
		ARRAY<STRUCT<actor STRING,character STRING>>[
			STRUCT('leonardo DiCaprio', 'Cobb'),
			STRUCT('Joseph Gordon-Levitt', 'Arthur')
		],
		ARRAY<STRING>['Action', 'Adventure', 'Sci-Fi']
	UNION ALL
	SELECT
		3,
		'The Dark Knight',
		ARRAY<STRUCT<actor STRING, character STRING>>[
			STRUCT('Christian Bale', 'Bruce Wayne'),
			STRUCT('Heath Ledger', 'Joker')
		],
		ARRAY<STRING>['Action', 'Crime', 'Drama']
)

→ actors라는 STRUCT 구조체를 만들고 그 안에 2개의 필드 actor와 character를 지정하고, STRUCT 구조체 2개를 list처럼 ARRAY에 넣은 것.

위의 테이블을 가지고 연습문제 1~4번 진행

  1. array_excercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.

    SELECT
  title, 
  genre
FROM advanced.array_excercised
CROSS JOIN UNNEST(genres) AS genre

     

  2. array_excercised 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다.

    SELECT
  title, 
  actor.actor,
  actor.character
FROM advanced.array_excercised
CROSS JOIN UNNEST(actors) AS actor

    • actors ARRAY안에 2개의 STRUCT 구조체가 있는 구조이므로 ARRAY 데이터에 접근하는 방법으로 데이터에는 접근 가능

      actors[SAFE_OFFSET(0)].actor AS first_actor

  3. array_excercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다.

    SELECT
  title, 
  actor.actor,
  actor.character,
  genre
FROM advanced.array_excercised
CROSS JOIN UNNEST(actors) AS actor
CROSS JOIN UNNEST(genres) AS genre

    • *UNNEST를 2번 연속 사용할 수 있다.

    • *쿼리문의 실행순서는 FROM → JOIN → SELECT 이다. UNNEST를 통해 만들어진 actor는 현재 actor.actor가 아니라 actor라는 STRUCT 구조체이므로 구조체에 바로 접근할 수 없다는 에러가 뜰 수 있다.

  4. 앱 로그 데이터(app_logs)의 배열을 풀어주세요.

     

    SELECT
  event_date,
  event_timestamp,
  event_name,
  event_param.key AS key,
  event_param.value AS value,
  user_id, 
  user_pseudo_id,
  platform
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS event_param

    event_params는 RECORD라고 되어있는데 STRUCT이다. 중첩된 구조라는 의미.

PIVOT 연습문제

  1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(column)으로 만들어야 합니다.

SELECT
	order_date,
	SUM(IF(user_id = 1, amount, 0)) AS user_1,
	SUM(IF(user_id = 2, amount, 0)) AS user_2,
	SUM(IF(user_id = 3, amount, 0)) AS user_3
FROM advanced.orders
GROUP BY 
	order_date
ORDER BY
	order_date

2. orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다.

SELECT
	user_id,
	SUM(IF(order_date = "2023-05-01", amount, 0)) AS `2023-05-01`,
	SUM(IF(order_date = "2023-05-02", amount, 0)) AS `2023-05-02`,
	SUM(IF(order_date = "2023-05-03", amount, 0)) AS `2023-05-03`,
	SUM(IF(order_date = "2023-05-04", amount, 0)) AS `2023-05-04`,
	SUM(IF(order_date = "2023-05-05", amount, 0)) AS `2023-05-05`
FROM advanced.orders
GROUP BY
	user_id

  1. orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다.

SELECT
	user_id,
	MAX(IF(order_date = "2023-05-01", 1, 0)) AS `2023-05-01`,
	MAX(IF(order_date = "2023-05-02", 1, 0)) AS `2023-05-02`,
	MAX(IF(order_date = "2023-05-03", 1, 0)) AS `2023-05-03`,
	MAX(IF(order_date = "2023-05-04", 1, 0)) AS `2023-05-04`,
	MAX(IF(order_date = "2023-05-05", 1, 0)) AS `2023-05-05`
FROM advanced.orders
GROUP BY
	user_id

4. user_id = 32888이 카트 추가하기(click_cart)를 누를 때 어떤 음식(food_id)을 담았나요?

WITH base AS(
  SELECT
  #* EXCEPT(event_params),
	event_date,
  event_timestamp,
  event_name,
  event_param.key AS key,
  event_param.value.string_value AS string_value,
  event_param.value.int_value AS int_value,
  user_id, 
  user_pseudo_id,
  platform
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS event_param
)


SELECT
	user_id,
	event_date,
	event_name,
	MAX(IF(key = 'firebase_screen', string_value, NULL)) AS firebase_screen,
	MAX(IF(key = 'food_id', int_value, NULL)) AS food_id,
	MAX(IF(key = 'session_id', string_value, NULL)) AS session_id
FROM base	
WHERE
	user_id = 32888
	AND event_name = 'click_cart'
GROUP BY
	user_id,
  event_date,
	event_name

퍼널별 전환율을 쉽게 구할 수 있도록 PIVOT 해보기

 WITH base AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    user_id, 
    user_pseudo_id,
    platform,
    #event_param,
    MAX(IF(event_param.key = 'firebase_screen', event_param.value.string_value, NULL)) AS firebase_screen,
    #MAX(IF(event_param.key = 'food_id', event_param.value.int_value, NULL)) AS food_id,
    MAX(IF(event_param.key = 'session_id', event_param.value.string_value, NULL)) AS session_id
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE
    #event_date = "2022-08-01" #적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건
    event_date BETWEEN "2022-08-01" AND "2022-08-18"
  GROUP BY ALL
  

 ), filter_event_and_concat_event_and_screen AS (
  SELECT
    * EXCEPT(event_name, firebase_screen, event_timestamp),
    CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM base
  WHERE
    event_name IN ("screen_view", "click_payment")
 ), funnel_analysis 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 filter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date 
)

SELECT 
  event_date,
  MAX(IF (event_name_with_screen = "screen_view-welcome", cnt, NULL)) AS `screen_view-welcome`,
  MAX(IF (event_name_with_screen = "screen_view-home", cnt, NULL)) AS `screen_view-home`,
  MAX(IF (event_name_with_screen = "screen_view-food_category", cnt, NULL)) AS `screen_view-food_category`,
  MAX(IF (event_name_with_screen = "screen_view-restaurant", cnt, NULL)) AS `screen_view-restaurant`,
  MAX(IF (event_name_with_screen = "screen_view-cart", cnt, NULL)) AS `screen_view-cart`,
  MAX(IF (event_name_with_screen = "click_payment-cart", cnt, NULL)) AS `click_payment-cart`
FROM funnel_analysis
GROUP BY ALL
ORDER BY event_date

 

sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

인프런 AI 인턴님의 프로필 이미지
인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 민영 김님.

BigQuery에서 사용하는 ARRAY, STRUCT, 그리고 PIVOT 등의 기능을 통해 데이터를 시각화하고 분석하는 과정은 매우 흥미롭습니다. 이렇게 다양한 SQL 쿼리를 작성하면서 데이터 분석의 기본기를 탄탄히 다지는 것은 큰 강점이 될 것입니다.

질문 주신 array_excercised 테이블을 통해서 연습한 문제들은 영화/배우 데이터의 구조를 활용해 BigQuery의 UNNEST 기능을 익히는 좋은 기회가 됩니다. 또한 PIVOT 문제에서는 다양한 방식으로 데이터를 집계하는 연습을 하셨고, 이는 데이터 분석에 매우 유용한 기술입니다.

다음은 관련된 기존의 질문과 답변을 찾아 소개드립니다. 퍼널 분석 및 PIVOT을 통해 데이터를 시각화하고 전환율을 구하는 데 도움이 되길 바랍니다.

현재 베타 기능으로 제공되는 답변입니다만, 관련 자료가 만족스럽지 않을 경우 추가적으로 문의 주시면 지식공유자분께서 더 상세한 답변으로 도움을 주실 것입니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏

추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

민영 김님의 프로필 이미지
민영 김

작성한 질문수

전체 Q&A
질문하기