[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개월 후 몇명 잔류했는지의 수치다

30일 간격으로 측정

| eval reg_date=strptime(reg_date, "%Y-%m-%d") 
| eval reg_date=strftime(reg_date, "%Y-%m-%d") 
| dedup reg_date sortby -_time 
| sort reg_date 
| streamstats count as row_num 
| eval group_num = ceil(row_num / 30) 
| stats count as row_count sum(user_cnt) as user_cnt sum(v1) as v1 sum(v2) as v2 sum(v3) as v3 sum(v4) as v4 sum(v5) as v5 sum(v6) as v6 sum(v7) as v7 sum(v8) as v8 sum(v9) as v9 sum(v10) as v10 sum(v11) as v11 sum(v12) as v12 by group_num 
| eventstats max(group_num) as max_row 
| eval incomplete = if(row_count < 30, 1, 0) 
| eventstats max(incomplete) as has_incomplete_group 
| eval user_cnt = if(group_num == max_row AND has_incomplete_group == 1, null(), user_cnt) 
| eval v1 = if(group_num == (max_row-1) AND has_incomplete_group == 1, null(), v1) 
| eval v2 = if(group_num == (max_row-2) AND has_incomplete_group == 1, null(), v2) 
| eval v3 = if(group_num == (max_row-3) AND has_incomplete_group == 1, null(), v3) 
| eval v4 = if(group_num == (max_row-4) AND has_incomplete_group == 1, null(), v4) 
| eval v5 = if(group_num == (max_row-5) AND has_incomplete_group == 1, null(), v5) 
| eval v6 = if(group_num == (max_row-6) AND has_incomplete_group == 1, null(), v6) 
| eval v7 = if(group_num == (max_row-7) AND has_incomplete_group == 1, null(), v7) 
| eval v8 = if(group_num == (max_row-8) AND has_incomplete_group == 1, null(), v8) 
| eval v9 = if(group_num == (max_row-9) AND has_incomplete_group == 1, null(), v9) 
| eval v10 = if(group_num == (max_row-10) AND has_incomplete_group == 1, null(), v10) 
| eval v11 = if(group_num == (max_row-11) AND has_incomplete_group == 1, null(), v11) 
| eval v12 = if(group_num == (max_row-12) AND has_incomplete_group == 1, null(), v12) 
| foreach * 
    [ eval <<FIELD>> = if('<<FIELD>>' == 0, null(), '<<FIELD>>')] 
| table group_num user_cnt v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 
| foreach v* 
    [ eval <<FIELD>>_r=round(('<<FIELD>>'/user_cnt)*100, 1) ] 
| table group_num v1_r v2_r v3_r v4_r v5_r v6_r v7_r v8_r v9_r v10_r v11_r v12_r 
| rename v*_r as after_* 
| where isnotnull(after_1) 
| transpose 0 header_field=group_num 
| rename * as new_pay_group_* 
| where isnotnull(new_pay_group_1)