[SQL 분석] CH 2. 이커머스 데이터를 통한 사업 현황 파악 : Google Big Query

이진호·2024년 10월 29일

제로베이스 데이터 분석 부트캠프

목록 보기
64/89

이번 실습에서 사용할 데이터셋은 ...
Kaggle의 'Brazilian E-Commerce Public Dataset by Olist' 데이터셋!

링크는 여기

  • 약 10만 건의 실제 브라질 이커머스 주문 데이터
  • 주문 데이터 외에도 지리 정보, 리뷰 등 다양한 데이터를 포함함
  • 2016-2018 집계
  • 샘플링, 익명화 완료
  • 여러 개의 데이터셋으로 나누어져 있음
  • 이번 실습에서 사용할 데이터는 아래에서 '제품', '주문', '주문 상품', '고객' 4가지

여러 개의 데이터셋으로 분리하는 이유?

자주 변경되는 데이터를 하나의 데이터셋에 모두 담아두면, 데이터를 업데이트할 때마다 방대한 데이터들을 수정해야 하므로

Data Warehouse 란?

먼저, Data Warehouse가 없을 경우 발생 가능한 문제점을 알아보겠음


위 그림처럼 client - DB - Web이 직접적으로 연결되어있으면, 수많은 쿼리로 인해 DB에 문제가 생길 경우 Web에 노출되는 내용에 직접적인 영향을 끼침

웨어하우스를 이용하면 위 문제가 해결됨

서비스와 직접 연결되어있는 DB가 아니라, '데이터 웨어하우스'에 쿼리를 날리기 때문에 더욱 안전 + 분석가들은 이 웨어하우스를 이용해서 업무를 진행할 수 있음

대표적인 웨어하우스로는 아마존 REDSHIFT, 구글 BigQuery 등이 있음

해당 포스팅에서는 구글의 Big Query 기준으로 진행함

Big query에 데이터셋 추가하기

Big Query는 프로젝트>데이터셋>테이블 로 구성되어 있음
프로젝트를 생성했다는 가정 하에, 데이터셋을 추가해보겠음


탐색기의 원하는 프로젝트 우측에 점 세개 클릭 > 데이터 세트 만들기 클릭


데이터세트 ID, 위치 등 설정하고 데이터세트 만들기 클릭하면


데이터셋이 생성됨

이제 이 데이터셋에 우리가 갖고 있는 테이블(csv 파일)을 올려야 함


데이터셋에서, 테이블 만들기 클릭


테이블을 만들 소스는 '업로드'로 설정하기,
파일 선택의 '찾아보기'로 로컬의 .csv 파일 올려주기,
테이블명 지정해주기,
자동 스키마 지정해주기 (스키마를 직접 설정할 수도 있음)
한 후에 '테이블 만들기'를 눌러 테이블을 생성해주면 됨

빅쿼리에서 날짜 다루기

빅쿼리 날짜 데이터 유형


일자만 필요한 경우: DATE
시간만 필요한 경우: TIME
시간 정보도 포함하는 경우: DATETIME
타임존을 포함하는 경우(한국의 경우, UTC보다 9시간 앞서가므로 TIMESTAMP에 9시간을 더해주면 됨): TIMESTAMP

예제

TIMESTAMP를 DATE로 변환한 후, 다시 TIMESTAMP로 변환할 경우

TIMESTAMP(DATE(order_purchase_timestamp))

/*
결과 :
2024-12-24 00:00:00 UTC
DATE를 하는 순간 '시간' 정보가 없어지기 때문에 위처럼 0시 0분 0초로 바뀜
*/

날짜에서 일부만 추출하기

WEEK vs WEEK(MONDAY) 비교

0 = 아직 일요일(또는 월요일)이 되지 않아서 1주차가 되기 전인 경우엔 0으로 출력됨

날짜에서 원하는 정보만 남기기

예시

select
	order_purchase_timestamp,
    TIMESTAMP_TRUNC(order_purchase_timestamp, YEAR) as year,
    TIMESTAMP_TRUNC(order_purchase_timestamp, QUARTER) as quarter,
    TIMESTAMP_TRUNC(order_purchase_timestamp, MONTH) as month,
    ...
    /*한 주의 첫 시작이 일요일일 경우, 이 날짜가 해당하는 주의 첫째 날*/
    TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK) as week,
    /*한 주의 첫 시작이 월요일일 경우, 이 날짜가 해당하는 주의 첫째 날*/
    TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK(MONDAY)) as week_monday
from 테이블명
where DATE(order_purchase_timestamp) = '2017-11-26'

해당 데이터셋에서 봐야 할 데이터

지표 계산하는 방법 요약

이제 구글 빅쿼리로 가서, order_items 테이블을 열고 아래 쿼리 써보기

select
  /* 전체 주문 건수 (distinct 이용) */
  count(distinct order_id) as unique_orders,
  /* 주문 금액 총합 (sum 이용) */
  sum(price) as ord_amt,
  /* 주문 건당 평균 금액*/
  sum(price) / count(distinct order_id) as amount_per_order
from zerobase-olist-440106.olist.olist_order_items;
select
  /*주문 건수 세기*/
  count(distinct ord.order_id) as order_cnt,
  /*주문한 고객 수*/
  count(distinct cust.customer_unique_id) as unique_cust_cnt,
  /*고객 1명 당 평균 주문 빈도수*/
  count(distinct ord.order_id) / count(distinct cust.customer_unique_id) as order_frequency
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
select
  count (order_item_id) as product_cnt,
  /*order_id를 중복되지 않게 처리한 후, 개수 세기*/
  count (distinct order_id) as order_cnt,
  /*주문 1건 당 제품 수*/
  count(order_item_id) / count(distinct order_id) as unit_per_order
from zerobase-olist-440106.olist.olist_order_items as ord;
select
  sum(price) as price_sum,
  count(order_item_id) as product_cnt,
  /*제품 1개당 평균 가격*/
  sum(price) / count(order_item_id) as avg_selling_price
from zerobase-olist-440106.olist.olist_order_items as ord;

만약, 도출한 결과를 하나의 테이블로 만들어서 재활용하고 싶다면 아래 코드처럼 with as를 사용하면 됨

with 테이블명 as (
select
  sum(price) as price_sum,
  count(order_item_id) as product_cnt,
  /*제품 1개당 평균 가격*/
  sum(price) / count(order_item_id) as avg_selling_price
from zerobase-olist-440106.olist.olist_order_items as ord;
);

select * from 테이블명;

대시보드 작성을 위한 데이터 만들기

주문 번호, 유저 고유 ID, 주문량, 주문 금액 집계하기

주문번호와 유저 고유 ID는 서로 다른 테이블에 떨어져 있고,
주문량과 주문금액은 직접 주문 테이블에서 집계해야 함

따라서, 순서대로 하나씩 select한 후, join으로 묶어주면 됨

STEP 1: 주문 1건 당 주문량&주문 금액 구하기

select
  order_id,
  sum(price) as order_amount,
  count(order_item_id) as product_cnt
from zerobase-olist-440106.olist.olist_order_items as item
group by order_id /*집계 기준 : 주문 번호*/

STEP 2: 주문번호와 유저 고유 ID를 join 활용하여 붙여주기

/*주문 1건 당 주문금액&상품 수 구하기*/
with result as(select
  order_id,
  sum(price) as order_amount,
  count(order_item_id) as product_cnt
from zerobase-olist-440106.olist.olist_order_items as item
group by order_id /*집계 기준 : 주문 번호*/
)

select
  ord.order_id,
  ord.order_status,
  ord.customer_id,
  cust.customer_unique_id,
  result.order_amount,
  result.product_cnt
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
inner join result
on ord.order_id = result.order_id;

STEP 3: 위에서 구한 결과 이용하여 결과 집계하기

/*주문 1건 당 주문금액&상품 수 구하기*/
with result as(select
  order_id,
  sum(price) as order_amount,
  count(order_item_id) as product_cnt
from zerobase-olist-440106.olist.olist_order_items as item
group by order_id /*집계 기준 : 주문 번호*/
),

base as(
select
  ord.order_id as order_id,
  ord.order_status,
  ord.customer_id,
  cust.customer_unique_id as customer_unique_id,
  result.order_amount as ord_amt,
  result.product_cnt as prd_cnt
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
inner join result
on ord.order_id = result.order_id)

select
  sum(ord_amt) as ord_amount_sum, /*총 매출*/
  count(distinct order_id) as total_order_count, /*총 주문 건수*/
  sum(prd_cnt) as total_product_count, /*총 판매 제품 수*/
  sum(ord_amt)/count(distinct order_id) as avg_amount_per_order, /*주문 1건당 평균 금액*/
  sum(prd_cnt)/count(distinct order_id) as avg_product_per_order, /*주문 1건당 제품 개수*/
  sum(ord_amt)/sum(prd_cnt) as avg_amount_per_product, /*제품 1개당 평균 가격*/
  count(distinct customer_unique_id) as total_customer_count, /*주문 고객 수*/
  count(distinct order_id)/count(distinct customer_unique_id) as frequency /*고객 1명 당 평균 주문 빈도*/
from base

+) 연도별로 집계해보기

/*주문 1건 당 주문금액&상품 수 구하기*/
with result as(select
  order_id,
  sum(price) as order_amount,
  count(order_item_id) as product_cnt
from zerobase-olist-440106.olist.olist_order_items as item
group by order_id /*집계 기준 : 주문 번호*/
),

base as(
select
  ord.order_id as order_id,
  ord.order_approved_at,
  DATE(ord.order_approved_at) as order_date,
  EXTRACT(YEAR from ord.order_approved_at) as order_year,
  ord.order_status,
  ord.customer_id,
  cust.customer_unique_id as customer_unique_id,
  result.order_amount as ord_amt,
  result.product_cnt as prd_cnt
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
inner join result
on ord.order_id = result.order_id
)

select
  order_year,
  sum(ord_amt) as ord_amount_sum, /*총 매출*/
  count(distinct order_id) as total_order_count, /*총 주문 건수*/
  sum(prd_cnt) as total_product_count, /*총 판매 제품 수*/
  sum(ord_amt)/count(distinct order_id) as avg_amount_per_order, /*주문 1건당 평균 금액*/
  sum(prd_cnt)/count(distinct order_id) as avg_product_per_order, /*주문 1건당 제품 개수*/
  sum(ord_amt)/sum(prd_cnt) as avg_amount_per_product, /*제품 1개당 평균 가격*/
  count(distinct customer_unique_id) as total_customer_count, /*주문 고객 수*/
  count(distinct order_id)/count(distinct customer_unique_id) as frequency /*고객 1명 당 평균 주문 빈도*/
from base
group by base.order_year
order by base.order_year;

+) 연도+월 별로 집계해보기

TIMESTAMP_TRUNC(, MONTH)로 연도와 월까지 살려놓고, 이걸 기준으로 groupby 하면 됨

/*주문 1건 당 주문금액&상품 수 구하기*/
with result as(select
  order_id,
  sum(price) as order_amount,
  count(order_item_id) as product_cnt
from zerobase-olist-440106.olist.olist_order_items as item
group by order_id /*집계 기준 : 주문 번호*/
),

base as(
select
  ord.order_id as order_id,
  ord.order_approved_at,
  DATE(ord.order_approved_at) as order_date,
  EXTRACT(YEAR from ord.order_approved_at) as order_year,
  TIMESTAMP_TRUNC(ord.order_approved_at, MONTH) as order_month_t,
  ord.order_status,
  ord.customer_id,
  cust.customer_unique_id as customer_unique_id,
  result.order_amount as ord_amt,
  result.product_cnt as prd_cnt
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
inner join result
on ord.order_id = result.order_id
)

select
  order_month_t,
  sum(ord_amt) as ord_amount_sum, /*총 매출*/
  count(distinct order_id) as total_order_count, /*총 주문 건수*/
  sum(prd_cnt) as total_product_count, /*총 판매 제품 수*/
  sum(ord_amt)/count(distinct order_id) as avg_amount_per_order, /*주문 1건당 평균 금액*/
  sum(prd_cnt)/count(distinct order_id) as avg_product_per_order, /*주문 1건당 제품 개수*/
  sum(ord_amt)/sum(prd_cnt) as avg_amount_per_product, /*제품 1개당 평균 가격*/
  count(distinct customer_unique_id) as total_customer_count, /*주문 고객 수*/
  count(distinct order_id)/count(distinct customer_unique_id) as frequency /*고객 1명 당 평균 주문 빈도*/
from base
group by base.order_month_t
order by base.order_month_t;

WHERE, ROUND, IFNULL, SAFE_DIVIDE 이용하기

SAFE_DIVDE를 이용하되, NULL값인 경우는 0으로 대체하고, 소수점 둘째자리까지만 표시하기~

select ROUND(IFNULL(SAFE_DIVIDE(값1, 값2),0),2)
from 테이블명
where order_status in ('shipped', 'delievered');

실제로 작성한 코드 (위에서 작성했던 코드의 일부를 수정함)

/*주문 1건 당 주문금액&상품 수 구하기*/
with result as(select
  order_id,
  sum(price) as order_amount,
  count(order_item_id) as product_cnt
from zerobase-olist-440106.olist.olist_order_items as item
group by order_id /*집계 기준 : 주문 번호*/
),

base as(
select
  ord.order_id as order_id,
  ord.order_approved_at,
  DATE(ord.order_approved_at) as order_date,
  EXTRACT(YEAR from ord.order_approved_at) as order_year,
  TIMESTAMP_TRUNC(ord.order_approved_at, DAY) as order_day_t,
  ord.order_status,
  ord.customer_id,
  cust.customer_unique_id as customer_unique_id,
  result.order_amount as ord_amt,
  result.product_cnt as prd_cnt
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
inner join result
on ord.order_id = result.order_id
where order_status in ('shipped', 'delivered')
)

select
  order_day_t,
  sum(ord_amt) as ord_amount_sum, /*총 매출*/
  count(distinct order_id) as total_order_count, /*총 주문 건수*/
  sum(prd_cnt) as total_product_count, /*총 판매 제품 수*/
  ROUND(IFNULL(SAFE_DIVIDE(sum(ord_amt),count(distinct order_id)),0),2) as avg_amount_per_order, /*주문 1건당 평균 금액*/
  ROUND(IFNULL(SAFE_DIVIDE(sum(prd_cnt),count(distinct order_id)),0),2) as avg_product_per_order, /*주문 1건당 제품 개수*/
  ROUND(IFNULL(SAFE_DIVIDE(sum(ord_amt),sum(prd_cnt)),0),2) as avg_amount_per_product, /*제품 1개당 평균 가격*/
  count(distinct customer_unique_id) as total_customer_count, /*주문 고객 수*/
  ROUND(IFNULL(SAFE_DIVIDE(count(distinct order_id),count(distinct customer_unique_id)),0),2) as frequency /*고객 1명 당 평균 주문 빈도*/
from base
group by base.order_day_t
order by base.order_day_t;

🔵 흥미로웠던 점:
SQL 수업을 들었더라도, 실제로 데이터를 만져보며 어떤 계산을 했을 때 무슨 값이 도출되는지 직접 경험해보는 게 중요하다는 것을 느꼈다.

🔵 다음 학습 계획:
빅쿼리의 유용한 함수에 대해 배울 예정입니다.

profile
이진호
이전 포스트

[SQL 분석] CH 1. 공공 데이터를 통한 시장 동향 이해해보기 : Looker Studio

다음 포스트

[SQL 분석] CH 2. 이커머스 데이터를 통한 사업 현황 파악 : 이커머스 비즈니스 지표

0개의 댓글