[BigQuery] 빅쿼리 활용 이커머스 데이터 분석 예시 (EDA)

활용 데이터: 빅쿼리 이커머스 공개 데이터 세트 (BigQuery Public Datasets)

지난 포스팅에서 빅쿼리 공개 데이터 세트를 빅쿼리 탐색 패널에 추가하는 방법에 대해 알아봤습니다. 이 글에서는 가상의 이커머스 데이터셋을 대상으로 BigQuery 쿼리문을 작성하는 방법에 대해 알아보겠습니다.

 

이커머스는 매우 많은 데이터를 생성하고 수집하며, 이 데이터에서 통찰력을 이끌어내는 것은 중요합니다. Google BigQuery를 사용하면 대용량 데이터를 빠르게 분석할 수 있는데요. 앞서 추가한 공개 데이터 세트 중 이커머스 데이터인 thelook_ecommerce 데이터 세트를 활용해 간단한 BigQuery GoogleSQL 쿼리문을 작성하면서 이커머스 데이터 셋을 이해하는 시간을 가져보도록 하겠습니다.

 

thelook_ecommerce 데이터셋은 아래와 같이 구성되어 있는데요. 실제 이커머스 서비스의 데이터베이스 구조와 유사하면서 조금 더 단순하게 구축되어 있는 것 같습니다.

 

BigQuery 공개 데이터 세트 중 thelook_ecommerce 데이터 셋의 테이블 구조

 

 

 

빅쿼리 GoogleSQL로 쿼리 작성하기

만약 GoogleSQL에 익숙해지고 싶거나 SQL을 공부하고 있다면, 공개 데이터 세트를 빅쿼리에 추가하고 아래 질문들에 답할 수 있는 쿼리를 직접 작성해 보는 것을 추천드립니다.

 

EDA 1. 일별 주문 트렌드 파악하기

가장 먼저 orders 테이블이 눈에 띄네요. 이커머스 서비스의 데이터를 처음 받아봤다면 가장 먼저 궁금한 것은 ‘주문이 얼마나 들어오고 얼마를 벌고 있는가’ 겠죠? 여기에 더해 얼마나 많은 유저가 구매하는지 까지 살펴보도록 하겠습니다.

(만약 어렵다면 쿼리 결과를 먼저 보고 나서 동일한 결과를 출력하는 쿼리를 작성해 보는 것을 추천)

 

쿼리 조건

• 일별로 주문 수, 주문 고객 수, 판매 금액을 출력할 것
• 판매 금액은 소수점 둘째 자리까지 반올림해서 표시할 것
• 단, 취소나 반품된 주문은 제외할 것
• 최종 결과는 주문일자 기준으로 내림차순으로 정렬할 것

 

쿼리문

SELECT
  FORMAT_DATE("%Y-%m-%d", o.created_at) AS order_date,
  -- 1) DATE(o.created_at) AS order_date 로도 작성이 가능
  COUNT(DISTINCT o.order_id) AS order_count,
  ROUND(SUM(o.num_of_item * i.sale_price), 2) AS revenue,
  COUNT(DISTINCT o.user_id) AS user_count
FROM
  `bigquery-public-data.thelook_ecommerce.orders` AS o
LEFT JOIN
  `bigquery-public-data.thelook_ecommerce.order_items` AS i
ON
  o.order_id = i.order_id
WHERE
  o.status NOT IN ('Returned',
    'Cancelled')
GROUP BY
  order_date
ORDER BY
  order_date desc;

 

참고) MySQL에서는 날짜 데이터의 포맷팅을 위해 DATE_FORMAT 방식을 사용했었는데, 빅쿼리 GoogleSQL에서는 해당 함수의 이름이 FORMAT_DATE이다. 거기다 전달하는 인자의 순서도 (형식, 데이터)로 MySQL의 DATE_FORMAT 함수와 반대다. 한편, 여기서는 FORMAT_DATE를 사용하지 않고 DATE(o.created_at)를 사용해서 쿼리를 작성할 수도 있다.

 

 

쿼리 결과

첫번째 쿼리 결과

 

 

 

EDA 2. 고객의 특성 살펴보기 (국가, 성별, 연령별 구매 현황)

이제 판매 현황을 살펴봤다면 유저를 조금 더 깊이 이해하는 단계입니다. 우리 서비스를 이용하는 유저가 어떤 지역에서 많이 분포되어 있는지, 어떤 성별의 고객들이 많이 가입했는지 살펴보는 것은 타겟 고객과 서비스를 이해하는데 큰 도움이 됩니다.

(만약 어렵다면 쿼리 결과를 먼저 보고 나서 동일한 결과를 출력하는 쿼리를 작성해 보는 것을 추천)

 

쿼리 조건

• 국가별 총 고객 수, 여성 고객 수, 남성 고객 수를 차례대로 출력할 것 (구매 이력 무관)
• 이때 총 고객 수를 기준으로 내림차순 정렬할 것

 

쿼리문

SELECT
  country,
  COUNT(DISTINCT id) AS customer_count,
  SUM(CASE WHEN gender = "F" THEN 1 ELSE NULL END) AS female_customer_count,
  SUM(CASE WHEN gender = "M" THEN 1 ELSE NULL END) AS male_customer_count
FROM
  `bigquery-public-data.thelook_ecommerce.users`
GROUP BY
  country
ORDER BY
  customer_count desc;

 

 

쿼리 결과

두 번째 쿼리 결과

 

 

마무리

이 글에서는 BigQuery GoogleSQL을 활용해서 이커머스 데이터셋에 대한 간단한 쿼리를 작성하는 방법을 살펴보았습니다. BigQuery는 대용량 데이터셋에 대해 빠른 처리 속도 및 강력한 분석 기능으로 데이터 엔지니어, 분석가, 비즈니스 전문가에게 매우 유용한 도구가 될 수 있는데요. 만약 부족한 점이나 오류가 있다면 언제든 알려주세요 :)

 

앞으로도 추가적인 예제를 바탕으로 BigQuery의 다양한 기능과 문법을 자세히 살펴보고 실제 업무에 유용하게 적용할 수 있는 꿀팁들을 담은 포스팅으로 찾아오도록 하겠습니다.