[TIL 27]

• NoSQL : Not Only SQL의 약자. / 전통적인 관계형 데이터베이스 와 다른 방식으로 데이터를 저장하고 관리하는 데이터베이스 유형
  • 다양한 데이터 구조를 저장 가능(JSON, XML, 키-값 쌍 등) 때문에 ga4 데이터에 컬럼 안에 컬럼이 있는 중첩 컬림이 존재
  • ⇒ 관계형 데이터 베이스(표)가 아님
  • {}로 시작하는 딕셔너리 형태 : JSON / []로 시작하는 배열관계는 키-값

키-값은 UNNEST() 명령어로 풀어줌. 이때 중복값이 생성이 되어서 중복 제거를 잘 제거해줘야 함.

 

WITH
  t_cnt as (
    SELECT count(*) as one_pur_total_cnt  # 4. 각 구매수가 1인 것을 확인하고(서브쿼리) 그 행의 개수를 셈. 즉 전체 유저수
    FROM (
      SELECT fullVisitorId,  # 1. 구매한 사람의 고유값을 구하기 위해 fullVisitorId를 사용.
				     count(distinct hits.transaction.transactionId)  # 3. unnest를 사용하며 풀어진 중복값을 제거하기 위해 distinct transactionId 사용.
      FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
        UNNEST(hits) AS hits
      WHERE hits.eCommerceAction.action_type = '6'
        and (_TABLE_SUFFIX BETWEEN  '20160801' AND '20170801')
      GROUP BY fullVisitorId #2) 고유값을 기준으로
      )
), #한 번이라도 물품을 구매한 사람 수를 확인.
  re_cnt as (
   SELECT count(*) as repur_cnt
    FROM (
     SELECT fullVisitorId, count(distinct hits.transaction.transactionId) as cnt_pur
     FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
       UNNEST(hits) AS hits
      WHERE hits.eCommerceAction.action_type = '6'
       and (_TABLE_SUFFIX BETWEEN  '20160801' AND '20170801')
     GROUP BY fullVisitorId
     HAVING cnt_pur>1)
) #재구매를 한 사람 수 확인
SELECT repur_cnt/one_pur_total_cnt  #각 계산 된 수로 비율 확인.
FROM t_cnt, re_cnt;

이커머스 중요 지표들을 확인하려고 그 중 재구매율을 봤음.

1년에 재구매율 : 약 9.69%

수치가 너무 적고 월별 매출과 방문자수를 봤을 때 특이한 값을 확인해서 그것을 중점으로 파고들기 시작함.

 

새로운 방문자 수가 10월과 11월에 높았지만, 매출은 4월이 가장 높음.

 

4월에 매출이 높은 이유가 이 달에 가격이 높은 물건을 많이 샀거나 작은 물건을 많이 샀을 거라 생각해서 확인해 봄.

 

1. 가장 많이 팔린 물건의 수 순위

#가장 많이 팔린 물건의 순위
SELECT 
  rank_num,
  month, 
  product_name, 
  price, 
  amount,
  price * amount AS total_price
FROM (
  SELECT 
    month, 
    product_name, 
    price, 
    row_number() OVER (PARTITION BY month ORDER BY price desc) AS rank_num,
    amount  
  FROM (
    SELECT 
      CASE 
        WHEN CAST(_TABLE_SUFFIX AS NUMERIC) BETWEEN 20170401 AND 20170430 THEN '201704'
        WHEN CAST(_TABLE_SUFFIX AS NUMERIC) BETWEEN 20170501 AND 20170531 THEN '201705'
      END AS month,
      product.v2ProductName AS product_name,
      product.localProductPrice / 1000000 AS price, 
      COUNT(DISTINCT hits.transaction.transactionId) AS amount
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
      UNNEST(hits) AS hits,
      UNNEST(hits.product) AS product
    WHERE 
      _TABLE_SUFFIX BETWEEN '20170401' AND '20170531'
      AND hits.eCommerceAction.action_type = '6'
    GROUP BY month, product_name, price
  )
)
WHERE rank_num < 6;

=> 오히려 5월달의 1~5위 순위의 물건의 양이 많았고, 4월보다 5월의 매출이 높았음.

 

때문에 가격이 높은 물건 1~5위까지 뽑아서 확인해 봄.

 

2. 가격이 높은 물건의 순위

#가격이 높은 물건 비교
SELECT 
  rank_num,
  month, 
  product_name, 
  price, 
  amount,
  price * amount AS total_price
FROM (
  SELECT 
    month, 
    product_name, 
    price, 
    row_number() OVER (PARTITION BY month ORDER BY price desc) AS rank_num,
    amount  
  FROM (
    SELECT 
      CASE 
        WHEN CAST(_TABLE_SUFFIX AS NUMERIC) BETWEEN 20170401 AND 20170430 THEN '201704'
        WHEN CAST(_TABLE_SUFFIX AS NUMERIC) BETWEEN 20170501 AND 20170531 THEN '201705'
      END AS month,
      product.v2ProductName AS product_name,
      product.localProductPrice / 1000000 AS price, 
      COUNT(DISTINCT hits.transaction.transactionId) AS amount
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
      UNNEST(hits) AS hits,
      UNNEST(hits.product) AS product
    WHERE 
      _TABLE_SUFFIX BETWEEN '20170401' AND '20170531'
      AND hits.eCommerceAction.action_type = '6'
    GROUP BY month, product_name, price
  )
)
WHERE rank_num < 6;

=> 4월 달이 높았지만 5월달과 크게 다르지 않았음.

 

 

생각해보니 월별 1~5위의 순위를 본다고 하더라도 그 밑에 있는 값에서 차이가 날 수 도 있다는 것을 알았음. 그렇다면 물건 고유값의 매출을 확인해보고 비교 해봐야 되나 고민함.

너무 많은 수의 카테고리가 있고 비효율적이기 때문에 평균 주문 금액과 월별 구매율(전환율)을 보고 비교해보기로 함.

 

1. 평균 주문 금액(AOV)	2. 구매율(전환율)

 

=> 전체 매출액이 중복값 없이 맞게 구해 졌는지 확인

위에 말한 지표 확인

 

 

 

 

액션 타입으로 퍼널 분석을 진행함

각 타입별 전환율을 확인해 봄.

고유 사용자 수를 세기 위해서 fullVisitorId에 distinct를 함. (동일 사용자가 같은 제품을 여러 번 클릭 / 장바구니에 동일 제품을 반복적으로 추가 한 경우를 제외)

select action, cnt,pre_cnt, if(pre_cnt is null, 0,cnt/pre_cnt*100)
from (select action,cnt, lag(cnt)over(ORDER BY action) as pre_cnt
from
(SELECT CASE WHEN hits.eCommerceAction.action_type = '0' THEN 0 #알 수 없음
        WHEN hits.eCommerceAction.action_type = '1' THEN 1  #제품보기
        WHEN hits.eCommerceAction.action_type = '2' THEN 2  #제품상세보기
        WHEN hits.eCommerceAction.action_type = '3' THEN 3  #장바구니 추가
        WHEN hits.eCommerceAction.action_type = '4' THEN 4  #장바구니 삭제
        WHEN hits.eCommerceAction.action_type = '5' THEN 5  #결제
        WHEN hits.eCommerceAction.action_type = '6' THEN 6  #구매 완료
       END as action,
       count(distinct fullVisitorID) as cnt
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
    UNNEST(hits) AS hits,
    UNNEST(hits.product) AS produc
WHERE
    (_TABLE_SUFFIX BETWEEN '20160801' AND '20170801')
    and hits.eCommerceAction.action_type != '4' #장바구니 삭제
GROUP BY 1))
ORDER BY action

3. 고유 방문자 수로 구한 게 아니기 때문에, 0단계 수치를 다시 구함(고유 방문자 수)

4. 0~6 단계 중에서 이탈이 가장 많은 단계 확인.

5. 이탈율을 줄이면 얼만큼 구매 전환율에 영향이 있는지 확인...<근데 이거 할 수 있으려나???

 

+추가적으로 확인 해보고 싶은 것

이탈이 많은 페이지 확인.....(home 이였던 거 같은디... not set 이었나)

 

 

 

다시 태어나지 못했다 젠장~

하지만 오늘부로 진짜 다시 태어난다. 꾸준히 쓸 것이다.

 

팀별 과제로 사람들과의 화합을 맞춰보는게 힘들다. 근데 재미있음(ㅋㅋ)

이 과정에서 나의 근본적인 성격?..성향?..을 되돌아 볼 수 있었음

내가 원하는대로 상황이 흘러가지 않으면 자꾸 반복적으로 생각하는 경향이 있음 (쉽게 말하면 이겨야 함)

이 의문이 풀리면 감정도 풀리는 거 같음 이걸 알게 되니까 더 이상 반복적으로 생각하지 않음 완전 러키비키자너

 

분석 지표도 알아보는 것도 너무 힘들고 어렵고 뭘 봐야하는지 모르겠고 잘하는지도 모르겠고... 설명을 들어도 뭔 말인지도 모르겠고.. 도메인 지식도 하나도 없고.................. 한달 밖에 하지 않았으니 잘하고 싶다는 건 오만하다는 건 알지만, 너무 힘들어서 의심이 되는 걸 어덕함?

일단 오늘 정리해 둔 5개를 내일 해보고..생각해봐야겠음...........................................