[Tableau] 태블로 실습 개인과제 풀이과정
데이터 (kaggle)
Google Analytics Customer Revenue Prediction 데이터 train.csv 데이터
https://www.kaggle.com/competitions/ga-customer-revenue-prediction/data?select=train.csv
Google Analytics Customer Revenue Prediction | Kaggle
www.kaggle.com
데이터 EDA
총 방문 수 (count) = 903653
총 방문 유저 수 (fullVisitorId .nunique) = 731421
<> 재방문 수 = 172232
즉, 전체 유저 중에 몇 명이 몇 번씩 중복 방문했는지는 모르겠지만 172232번 다시 방문됨
바로 확인 ㄱㄱ
중복된 유저 수 (nunique) = 89346명
한 번만 방문한 유저 수 = 642075명
df[df['fullVisitorId'].duplicated()].nunique()즉, 총 재방문 수 172232 중에 89346명의 유저가 재방문 함
즉, 89346명의 유저가 각각 여러 번 방문했을 수도 있음
총 유저 731421명 중에 89346명은 적게는 두 번째 방문까지, 많게는 252번까지 방문함
total_duplicated = df['fullVisitorId'].value_counts().reset_index()
total_duplicated.columns = ['fullVisitorId', 'count']
total_duplicated = total_duplicated[total_duplicated['count'] != 1]
total_duplicated
(newVisits == 1)인 수 (count) = 703060
df[df['newVisits'] == 1]['fullVisitorId'].count()신규 방문이라고 명명한 총 방문 수
(newVisits == 1)인 유저 수 (nunique) = 701904
df[df['newVisits'] == 1]['fullVisitorId'].nunique()신규 방문이라고 명명한 총 유저 수
+ 여기에서 알 수 있는 것
이 둘 사이에 1156이라는 차이가 발생하는데
즉, fullVisitorId가 첫 방문했을 때만 newVisits == 1이라고 하지 않고
1156번은 두 번째 방문에서도 newVisits == 1이라고 명명해줌
>> 이게 지금 오류인 것
정리 + 문제점 확인
총 유니크 유저 수는 731421명
총 유저 중에서 중복 집계되고 있는 유저의 수는 172232명
즉, 172232번의 재방문이 있었던 것
>> 이 재방문 수는 89346명의 유저들이 여러 번의 재방문을 했다는 말
>> 이 89346명의 유저들은 2번부터 많게는 200번이 넘게 재방문을 하고 있었음
1156번이 첫 번째 방문이 아닌데도 newVisits를 1이라고 잘못 명명해주고 있는 오류가 있음
>> 이런 오류가 89346명의 재방문 유저들 중 어떤 유저에게 일어나고 있는 오류인지는 잘 모르겠음
과제 풀이 시작
1. (Acquistion) 신규 유입 유저 수
▶ 신규 유입 유저 수
: New Visits가 1인 Full Visitor Id를 고유 카운트 해주기
신규 유입 유저 수 계산식
IF [New Visits] == 1 THEN [Full Visitor Id] END이렇게 해주고, 선반에 올려놓을 때 카운트(고유)로 해줌
2. DAU, WAU, MAU
내가 정한 Active의 기준은, 세션 방문자
세션에 방문한 사람들의 고유 카운트
Unique_Visitors
COUNTD([Full Visitor Id])
▶ DAU
각 일(Day)마다의 Unique_Visitors 수
DAU의 변화는 제어할 수 없는 외부 요인이나 특정 이벤트의 영향에 의한 것일 수 있기 때문에
전체적인 추세를 보기 위해 이동평균 그래프를 라인그래프로 같이 그려봄.
평균, 이전6일, 다음0일로 그린 이동평균 그래프라서 결론적으로는 7일간의 평균을 나타내기 때문에
WAU와 비슷해졌음..ㅎㅎㅎ
3. 평균 활성 사용자 수_ 캘린더 차트
평균 활성 사용자 수
{FIXED DATEPART('weekday', [Utc Time]),DATEPART('hour', [Utc Time]): AVG([Full Visitor Id])}사실 각 행과 열에 요일과 시간을 넣어줘서
AVG([Full Visitor Id])그냥 위와 같은 계산된 필드를 넣어줘도 결과가 같긴 했음.
4. 채널 별 유입 유저 수, CAC
Unique_Visitors
COUNTD([Full Visitor Id])
Count_Visitors
: 유입 채널로 총 몇 명의 유저가 들어왔는지 확인
COUNT([Full Visitor Id])
▶ CAC (Customer Aquisition Cost)
한명의 고객을 유치하기 위해 들어가는 비용
CAC = 총 획득 비용 / 획득한 신규 고객 수
(총 획득 비용에는 영업 및 마케팅 관련 비용이 모두 포함됨.
: 광고, 판촉, 영업 및 마케팅 직원의 급여, 기타 관련 비용 등)
CAC를 계산할 때는 Direct로 들어온 유저나 Organic Search로 들어온 유저는 계산X
S&M 총 비용(단위: 백원)
CASE [Channel Grouping]
WHEN '(Other)' THEN 5000
WHEN 'Affiliates' THEN 100000
WHEN 'Direct' THEN 0
WHEN 'Display' THEN 50000
WHEN 'Organic Search' THEN 0
WHEN 'Paid Search' THEN 100000
WHEN 'Referral' THEN 200000
WHEN 'Social' THEN 250000 END
CAC
{FIXED [Channel Grouping] : MEDIAN([S&M 총 비용]) / COUNTD([신규 유입 유저 수])}여기에서 S&M 총 비용은 특성 값으로 설정해줬는데
해당 값을 신규 유입 유저의 고유 카운트 수로 나눠주기 위해서 MEDIAN()함수를 같이 사용해줌
>> S&M 총 비용의 특성값과 MEDIAN() 해준 결과값이 동일해서 중앙값 설정으로 일단 해줌
5. (Activation) 전환율_퍼널
전환 순서는
먼저 광고 페이지(페이지뷰)를 통해 홈페이지로 유입 > 홈페이지 방문 > 홈페이지 재방문
CVR_방문
[Count_Visitors]/SUM([Pageviews])
CVR_재방문
[재방문 유저 수]/SUM([Pageviews])
재방문 유저 수
[Count_Visitors]-[Unique_Visitors]전체 방문자 수에서 유니크 방문자 수를 빼면 중복 유저수(즉, 재방문 유저 수)를 구할 수 있음
6. (Aquisition) 고착도 / PV /디바이스 별 유입 비중_도넛차트
▶ Stickiness(고착도) = DAU/WAU
DAU
{FIXED DATEPART('day', [Utc Time]): [Unique_Visitors]}
MAU
{FIXED DATEPART('month', [Utc Time]): [Unique_Visitors]}
고착도
[DAU]/[MAU]
PV : Pageviews
SUM([Pageviews])
7. (Activation) 평균 전환율
CVR_평균전환율
내가 전에 구한 CVR_방문과 CVR_재방문의 평균을 구하면 되는 걸까..?
([CVR_방문]+[CVR_재방문])/28. (Revenue) Total Revenue / PU / ARPU / ARPPU
국가별 매출 지도 / 유입 채널 별 Scatterplot
- x축: 총 유저 수
- y축: 총 구매자 유저 수
R_Total Revenue
한 유저마다 가져다주는 수익의 합계
{FIXED [Full Visitor Id] : SUM([Transaction Revenue])}
R_PU(Paid User)
PU(Paid User): 중복을 제외한 특정 기간 결재한 순수 사용자의 수
COUNTD([R_Total Revenue])수익을 가져다주는 유저들의 카운트(고유)
R_ARPU
ARPU = 매출 / PU + Non-PU
수익을 전체 이용자 또는 가입자로 나눈 단위당 매출
SUM([Transaction Revenue])/[Unique_Visitors]
R_ARPPU
ARPPU = 매출 / PU
수익을 결제 유저로 나눈 단위당 매출
SUM([Transaction Revenue])/[R_PU(Paid User)]
▶ 유입 채널 별 총 유저 수 scatterplot
x축 : 총 유저 수 >> Unique_Visitors
y축 : 총 구매자 유저 수 >> R_PU(Paid User)
9. (Retention) 리텐션 차트
▶ N-day Retention
: 첫 방문 후 특정 일자에 재방문한 유저 비율
▶ Rolling Retention (Unbounded Retention)
: 특정 날짜를 포함하여 그 이후에 한 번이라도 재방문한 유저의 비율
▶ Bracket Retention
: Bracket 리텐션은 N-Day 리텐션을 보다 유연하게 확장시킨 개념으로, 리텐션 분석을 특정 일/주/월 단위로 한정 짓는 것이 아닌, 지정된 구간으로 나누어 리텐션 분석을 수행할 수 있게 해줌
RT_일일 유입자 수
{FIXED DATETRUNC('day', [Utc Time]): [Unique_Visitors]}
RT_리텐션
([RT_일일 유입자 수]/714167) *100여기에서 714167은 Unique_Visitors 숫자
날짜를 고정시켜 놓고, 해당 날짜의 유입 유저들(RT_일일 유입자 수)을 전체 유입 유저로 나눈 뒤 *100을 해줌
10. 코호트 차트(리텐션)
CO_유저 별 최초 방문일
{FIXED [Full Visitor Id]: MIN([Utc Time])}최초 방문일 = 방문일 중 가장 작은(과거의) 날짜
CO_유저 별 최초 재방문일
{FIXED [Full Visitor Id]:
MIN(
IIF([C_유저 별 최초 방문일] < [Utc Time], [Utc Time], NULL)
)
}재방문일 = 최초 방문일보다는 큰(미래인) 방문일자중 가장 작은 날짜
NULL은 재방문이 없었던 유저
CO_유저 당 재방문 경과기간
DATEDIFF('month',[C_유저 별 최초 방문일], [C_유저 별 최초 재방문일])경과기간 = 재방문일 - 최초 방문일
11. (Referral) Referral 유입O,X 유저 간 매출 비교
RF_유입 유저 매출
IF [Channel Grouping] == 'Referral' THEN [Transaction Revenue] END
RF_그 외 유입 유저 매출
IF [Channel Grouping] != 'Referral' THEN [Transaction Revenue] END
대시보드 만들 때 참고한 사이트
Color Palettes for Designers and Artists - Color Hunt
Discover the newest hand-picked color palettes of Color Hunt. Get color inspiration for your design and art projects.
colorhunt.co