[splunk] 리텐션 - 월별 코호트

https://docs.holistics.io/docs/charts/cohort-retention

샘플로 아래와 같은 데이터가 있다고 하자

reg_date	user_cnt	v1	v2	v3	v4	v5	v6	v7	v8	v9	v10	v11	v12
2024-01-03	300	120	90	80	73	64	55	51	0	0	0	0	0
2024-01-04	200	130	115	102	90	82	78	71	0	0	0	0	0
2024-01-05	190	120	100	92	86	70	71	60	0	0	0	0	0
2024-01-06	180	119	108	95	85	75	60	63	0	0	0	0	0

 

reg_date는 구독한 날이고 user_cnt는 해당 일의 신규 구독자 수다

v1~v12는 신규 구독자가 n개월 후 몇명 잔류했는지의 수치다

 

| eval reg_month=strftime(reg_date, "%Y-%m") 
| foreach * 
    [ eval <<FIELD>> = if('<<FIELD>>' == 0, null(), '<<FIELD>>')] 
| stats avg(user_cnt) as total_new_pay_v avg(v1) as total_v1 avg(v2) as total_v2 avg(v3) as total_v3 avg(v4) as total_v4 avg(v5) as total_v5 avg(v6) as total_v6 avg(v7) as total_v7 avg(v8) as total_v8 avg(v9) as total_v9 avg(v10) as total_v10 avg(v11) as total_v11 avg(v12) as total_v12 by reg_month 
| foreach total_v* 
    [ eval <<FIELD>>_ratio=round(('<<FIELD>>'/total_new_pay_v)*100, 1) ] 
| rename total_v1_ratio as after_1 total_v2_ratio as after_2 total_v3_ratio as after_3 total_v4_ratio as after_4 total_v5_ratio as after_5 total_v6_ratio as after_6 total_v7_ratio as after_7 total_v8_ratio as after_8 total_v9_ratio as after_9 total_v10_ratio as after_10 total_v11_ratio as after_11 total_v12_ratio as after_12 
| fields reg_month after_1 after_2 after_3 after_4 after_5 after_6 after_7 after_8 after_9 after_10 after_11 after_12 
| sort reg_month 
| where isnotnull(after_1)