[Funnel Analysis] 홈페이지 - 장바구니 - 결제에 따른 퍼널 변화

시간 차이 계산

homefunnel = df[df['event_name'].isin(['HOMEPAGE', 'ADD_TO_CART', 'BOOKING'])]
homefunnel['datetime'] = pd.to_datetime(homefunnel['datetime'])
homefunnel = homefunnel[['session_id', 'event_name', 'datetime']]

# 1. 각 이벤트 필터링
homepage_data = homefunnel[homefunnel['event_name'] == 'HOMEPAGE']
add_to_cart_data = homefunnel[homefunnel['event_name'] == 'ADD_TO_CART']
booking_data = homefunnel[homefunnel['event_name'] == 'BOOKING']

# 2. Homepage와 Add to Cart 병합
homepage_to_cart = pd.merge(
    homepage_data, add_to_cart_data, on='session_id', how = 'left', suffixes=('_homepage', '_add_to_cart')
)

# 3. Add to Cart와 Booking 병합
cart_to_booking = pd.merge(
    homepage_to_cart, booking_data, on='session_id', how = 'left', suffixes=('', '_booking')
)

# 4. 시간 차이 계산
cart_to_booking['time_to_add_to_cart'] = (
    cart_to_booking['datetime_add_to_cart'] - cart_to_booking['datetime_homepage']
).dt.total_seconds() / 60  # 분 단위로 변환

cart_to_booking['time_to_booking'] = (
    cart_to_booking['datetime'] - cart_to_booking['datetime_add_to_cart']
).dt.total_seconds() / 60  # 분 단위로 변환

result = cart_to_booking[['session_id', 'time_to_add_to_cart', 'time_to_booking']].fillna(1000)
result = result.groupby('session_id')[['time_to_add_to_cart', 'time_to_booking']].first()

 

click_stream df 준비

mask = click_stream_df['event_date'] > '2021-07-01'
df = click_stream_df[mask]

 

고객 세그먼트

# 장바구니에 빨리 넣은 고객 구분
mask = result['time_to_add_to_cart'] <= 30
more = result[mask]

merged_df['customer_segment'] = merged_df['session_id'].isin(more.index)
merged_df['customer_segment']

 

funnel_data 준비

session = df[['session_id', 'event_name']]
customer = merged_df[['customer_id', 'session_id', 'customer_segment']]
data = pd.merge(session, customer, how='inner', on='session_id')

# 데이터 준비
funnel_data = data.groupby(['customer_segment', 'event_name'])['customer_id'].count().reset_index()
funnel_data = funnel_data.sort_values(['customer_segment', 'customer_id'], ascending=False)
# 퍼널 전환율 계산
funnel_data['conversion_rate'] = funnel_data.groupby('customer_segment')['customer_id'].apply(lambda x: x / x.iloc[0])

# 각 단계별 이탈률 계산
funnel_data['dropoff_rate'] = funnel_data.groupby('customer_segment')['customer_id'].apply(lambda x: x.diff(-1).fillna(0).abs() / x)
funnel_data

 

퍼널 그래프_False

funnel_graph = funnel_data[funnel_data['customer_segment'] == False]
funnel_graph = funnel_graph[funnel_graph['event_name'].isin(['ADD_TO_CART', 'BOOKING'])]
funnel_graph['dropoff_rate'] = funnel_graph.groupby('customer_segment')['customer_id'].apply(lambda x: x.diff(-1).fillna(0).abs() / x)

# 퍼널 
fig = px.funnel(data_frame=funnel_graph, x = 'event_name', y = 'customer_id')
fig.update_traces(texttemplate = '%{value: ,.0f}') # 숫자 형식 지정
fig.show()

# 전환율
fig = px.funnel(data_frame = funnel_graph, x = 'event_name', y = 'dropoff_rate')
fig.update_traces(texttemplate = '%{value:,.2%}')
fig.show()

 

 

퍼널 그래프_True

funnel_graph = funnel_data[funnel_data['customer_segment'] == True]
funnel_graph = funnel_graph[funnel_graph['event_name'].isin(['ADD_TO_CART', 'BOOKING'])]
funnel_graph['dropoff_rate'] = funnel_graph.groupby('customer_segment')['customer_id'].apply(lambda x: x.diff(-1).fillna(0).abs() / x)

# 퍼널 
fig = px.funnel(data_frame=funnel_graph, x = 'event_name', y = 'customer_id')
fig.update_traces(texttemplate = '%{value: ,.0f}') # 숫자 형식 지정
fig.show()

# 전환율
fig = px.funnel(data_frame = funnel_graph, x = 'event_name', y = 'dropoff_rate')
fig.update_traces(texttemplate = '%{value:,.2%}')
fig.show()

결론

큰 차이 없다.