유데미 스타터스 취업 부트캠프 4기 - 데이터분석/시각화(태블로) 11주차 학습 일지

2022.04.17 ~ 2022.04.21

11주차는 고객 분석을 중심으로 SQL 실습과 마지막 프로젝트를 대비한 미니 프로젝트를 진행했다. 

배운 내용을 복습하는 것은 이번 학습일지가 마지막이 될 듯해서 어떤 내용으로 마무리를 하면 좋을지 고민하다가 이번 주 학습일지에서는 유데미 스타터스 학습일지 포스팅을 통해서 블로그 유입이 얼마나 발생했을지 데이터로 확인해보기로 했다. 특히 재구매율 지표를 응용하여 블로그 재방문율을 구해보았다.

목차
월별 재구매율(복습)
일별 블로그 재방문율(응용)

---

월별 재구매율

step 1. 고객 구매 월 중복없이 구하기(date_trunc)

-- 월별 재구매율 step 1. 고객 구매 월 중복없이(date_trunc)
), cte_month_customer as (
select
	distinct customer_id,
	date_trunc('month', order_date) as month-- 년,월,일 기준 변경
from cte_customers
order by 1, 2

✅ 우선 고객별로 구매가 발생한 월을 모두 나열한다.

>>

step 2. self join하기(조건 : month1 + interval 1 = month2)

-- 월별 재구매율 step 2. self join(month1 + interval 1 = month2)
), cte_month_customer_reorder as (
select
	c.customer_id,
	c.month as month1,
	d.month as month2
from cte_month_customer as c left join cte_month_customer as d on c.customer_id = d.customer_id
and c.month + interval '1' month = d.month-- 년,월,일 기준에 따라 join 조건 변경
order by 1,2,3

✅ 아이디어를 얻은 것은 이 부분으로 셀프 조인, 즉 하나의 테이블을 가지고 month1과 month2를 연결할 수 있다.

>>

step 3. 월별 구매자수와 재구매율 구하기

-- 월별 재구매율 step 3. 월별 구매자수와 재구매율
), cte_month_reordercnt as (
select 
	to_char(month1, 'yyyy-mm') as date,-- 년,월,일 기준에 따라 출력형식 변경
	count(month1) customer_cnt,
	count(month2) reorder_customer_cnt,
	count(month2) / count(month1)::numeric * 100 reorder_rate	
from cte_month_customer_reorder
group by 1
order by 1
)
select *
from cte_month_customer;

✅ 그리고 month1과 month2의 갯수를 count하는 것으로 간결하게 재구매율을 구할 수 있다.

>>

위 재구매율 쿼리에서 차용한 셀프 조인의 개념을 리텐션에도 적용해보자.

---

일별 블로그 재방문율

사용 데이터

대상: 블로그 내 유데미 스타터스 취업 부트캠프 4기 콘텐츠
기간: 20230206~20230421
출처: 개인 블로그 구글 애널리틱스 로그 데이터
사용도구: 구글 빅쿼리

 

step 1. 고객 first_visit 일 중복없이 구하기(parse_date)

WITH cte_first_visit AS (
SELECT 
  distinct user_pseudo_id,
  PARSE_DATE("%Y%m%d", event_date) as eventdate 
FROM `blog-374206.analytics_331429714.events_*`,UNNEST(event_params) AS event_params
WHERE event_date between '20230206' AND '20230421'
AND key = "page_title" and value.string_value like "유데미 스타터스 취업 부트캠프 4기%"
ORDER BY eventdate

✅ parse_date를 사용해 문자열을 날짜 데이터 타입으로 변환해주었다. 그리고 page_title이 "유데미 스타터스 취업 부트캠프"로 시작하는 페이지에 한해서만 조건을 주었다.

step 2. self join하기(조건 : month1 + interval 1 = month2)

), cte_revisit as (
SELECT 
  a.user_pseudo_id,
  a.eventdate as eventdate1,
  f.eventdate as eventdate2         
FROM cte_first_visit a left JOIN cte_first_visit f ON a.user_pseudo_id = f.user_pseudo_id
and DATE_ADD(a.eventdate, INTERVAL 1 DAY) = f.eventdate

✅ date_add를 사용해 eventdate1과 eventdate2를 연결해주었다.

step 3. 일별 방문자수와 재방문율 구하기

), cte_revisit_rate as (
select 
	eventdate1,
	count(eventdate1) user_cnt,
	count(eventdate2) revisit_user_cnt
from cte_revisit
group by 1
order by 1
)
select *
from cte_revisit_rate;

✅ "유데미 스타터스 취업 부트캠프" 페이지에 방문한 일별 방문자수와 재방문율이 다음과 같이 추출되었다.

>>

 

❗ 다만 step1에서 event_name= 'first_visit' 조건을 주지 않았는데, 이는 단순히 user_id와 방문일자가 동일한 경우 이벤트 특성에 관계 없이 재방문으로 간주하도록 하기 위함이다.

따라서 리텐션(첫 방문 이후 잔존율)을 구한다면 위와 같이 셀프 조인 방법으로는 불가하고(셀프 조인 대상 테이블이 first_visit에 대한 로그만 담고 있으므로) 첫 방문 테이블과 전체 데이터를 대상으로 조인을 해주어야 한다.

-- 리텐션 구하기
WITH cte_first_visit AS (
SELECT 
  distinct user_pseudo_id,
  PARSE_DATE("%Y%m%d", event_date) as eventdate 
FROM `blog-374206.analytics_331429714.events_*`,UNNEST(event_params) AS event_params
WHERE event_name= 'first_visit' and event_date between '20230206' AND '20230421'
AND key = "page_title" and value.string_value like "유데미 스타터스 취업 부트캠프 4기%"
ORDER BY eventdate
), cte_retention as (
SELECT 
    f.eventdate
    ,COUNT(distinct a.user_pseudo_id) day0
    ,COUNT(distinct CASE WHEN PARSE_DATE('%Y%m%d', a.event_date)= DATE_ADD(eventdate, INTERVAL 1 DAY) 
                    THEN a.user_pseudo_id END ) day1 -- +1일 
    ,COUNT(distinct CASE WHEN PARSE_DATE('%Y%m%d', a.event_date)= DATE_ADD(eventdate, INTERVAL 2 DAY) 
                    THEN a.user_pseudo_id END ) day2 -- +2일                 
    ,COUNT(distinct CASE WHEN PARSE_DATE('%Y%m%d', a.event_date)= DATE_ADD(eventdate, INTERVAL 3 DAY) 
                    THEN a.user_pseudo_id END ) day3 -- +3일 
    ,COUNT(distinct CASE WHEN PARSE_DATE('%Y%m%d', a.event_date)= DATE_ADD(eventdate, INTERVAL 4 DAY) 
                    THEN a.user_pseudo_id END ) day4 -- +4일 
    ,COUNT(distinct CASE WHEN PARSE_DATE('%Y%m%d', a.event_date)= DATE_ADD(eventdate, INTERVAL 5 DAY) 
                    THEN a.user_pseudo_id END ) day5 -- +5일 
    ,COUNT(distinct CASE WHEN PARSE_DATE('%Y%m%d', a.event_date)= DATE_ADD(eventdate, INTERVAL 6 DAY) 
                    THEN a.user_pseudo_id END ) day6 -- +6일           
FROM `blog-374206.analytics_331429714.events_*` a
JOIN cte_first_visit f
ON a.user_pseudo_id = f.user_pseudo_id
GROUP BY f.eventdate
ORDER BY f.eventdate
), cte_retention_rate as (
select
  eventdate,
  day0,
  round(day1/day0 * 100,2) as day1_rate,
  round(day2/day0 * 100,2) as day2_rate,
  round(day3/day0 * 100,2) as day3_rate,
  round(day4/day0 * 100,2) as day4_rate,
  round(day5/day0 * 100,2) as day5_rate,
  round(day6/day0 * 100,2) as day6_rate
from cte_retention
)
select *
from cte_retention

>>

이제 정말 진짜 최종 마지막이다..!! 좋은 컨디션으로 마무리할 수 있었으면 좋겠다.

---

>> 유데미 바로가기
>> STARTERS 취업 부트캠프 공식 블로그 보러가기

본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터 분석 과정 학습 일지 리뷰로 작성되었습니다.

 

#유데미 #유데미코리아 #유데미큐레이션 #유데미부트캠프 #취업부트캠프 #스타터스부트캠프 #데이터시각화 #데이터분석 #데이터드리븐 #태블로