[GA4] 전자상거래 이벤트 기준으로 순매출 쿼리 작성해보기

 

이커머스의 매출 데이터 수집은 매우 견고해야 한다

이커머스의 매출 데이터는 사실 데이터 엔지니어가 만든 DB가 있다면 그쪽을 참고해서 GA4와 매칭하여 연결해도 좋을 것이다. 다만 그정도로 데이터 거버넌스가 구축이 되어있지 않거나, 매출 데이터가 정확하지 않다면 데이터 분석가가 풀어내기 어려울 것이다. 환불, 취소, 부분환불 등 여러 변수에 따른 매출 조정이 가장 큰 이슈가 되기 때문이다. 실제로 주문 데이터의 경우 cs가 들어오게 되면 해당 DB의 row에 UPDATE가 진행이 되어야 하는데 이는 데이터 엔지니어가 이커머스에 필요한 제일 첫번째 이유가 될 것이다. 여기서는 우선 하루동안 일어난 refund 이벤트를 수집하여 매출에 감산하는 작업을 해보고자 한다!

쿼리 전체 내용

내가 쓴 쿼리 전문을 첨부한다. 사실 자잘한 설명 보다는 직접 데이터를 겪어 보는 것이 가장 큰 도움이 된다.

-- WITH DATE_RANGE : 기간 잡아두기 (필요할 때 table 형태로 꺼내어서 쓸 예정)
with date_range as (
     select format_date('%Y%m%d',date_sub(current_date('Asia/Seoul'), interval 2 day)) as current_day
)
,

-- WITH ORDERS : 주문 데이터 전제하기
orders as (
  select user_pseudo_id
        , FORMAT_TIMESTAMP('%Y-%m-%d',TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 9 hour)) as date
        , (select value.string_value from unnest(event_params) where event_name = 'purchase' and key = 'transaction_id') as order_id
        , (select value.int_value from unnest(event_params) where event_name = 'purchase' and key = 'value') as revenue
  from `테이블 이름`, date_range
  where stream_id = '특정 스트림 아이디'
  and event_name = 'purchase'
  and _table_suffix between current_day and current_day
)
, 

-- WITH ORDER_COMPLETED : 결제 완료된 주문건수와 수익 집계
order_completed as (
select date as event_date
     , count(distinct order_id) as cnt_orderid
     , sum(revenue) as sum_sales
from orders
group by 1

),

-- WITH ORDER_REFUNDED : 주문한 결제건수 중 환불건수 집계하기 위한 테이블 작성
order_refunded as (
select a.date as event_date
     , count(distinct a.refund_id) as cnt_refund
     , sum(b.revenue) as sum_refund
from (
  select user_pseudo_id
        , FORMAT_TIMESTAMP('%Y-%m-%d',TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 9 hour)) as date
        , (select value.string_value from unnest(event_params) where event_name = 'refund' and key = 'transaction_id') as refund_id
  from `테이블 이름`, date_range
  where stream_id = '특정 스트림 아이디'
  and event_name = 'refund'
  and _table_suffix between date_range.current_day and date_range.current_day
) as a
left join orders as b
on a.refund_id = b.order_id
group by 1
)

-- SELECT : 전체 집계
select order_completed.event_date
     , order_completed.cnt_orderid
     , order_completed.sum_sales
     , order_refunded.cnt_refund
     , order_refunded.sum_refund
     , (cnt_orderid - cnt_refund) as cnt_netorder
     , (sum_sales - sum_refund) as sum_netsales
from order_completed 
inner join order_refunded
on  order_completed.event_date = order_refunded.event_date