[리뷰] 네이버 플레이스 카츠 - RFM

이번 포스팅은 지난 주 리뷰 포스팅에서 활용한 데이터로 RFM 분석을 수행했다. 네이버 플레이스 리뷰에 정확히 어떤 메뉴를 먹었는지 명시된 경우가 없기 때문에, 식당의 메뉴를 참조하여 리뷰에 메뉴가 포함되어 있는 경우 해당 음식을 먹었다고 가정했다. KNIME, python, SQL을 모두 활용하여 돈카츠 전문점 "톤쇼우"의 RFM 레벨을 도출했는데, 과정을 확인해보자.

 

리뷰 추출

 

 

먼저, 전체 리뷰에서 메뉴 이름이 포함된 리뷰를 추출하고, csv 파일로 저장하였다.

리뷰에서 메뉴 추출

 

import re
import pandas as pd

data = pd.read_csv('톤쇼우_rfm.csv', encoding='cp949')

menus = ['버크셔', '히레', '로스', '특로스', '대로스', '산도', '모둠']
pattern = '|'.join(menus)

review = "오늘 버크셔와 히레카츠는 정말 환상적이에요."
found_menus = re.findall(pattern, review)
print(found_menus)  # ['버크셔', '히레']

data['found_menus'] = data['content'].apply(lambda x: re.findall(pattern, x))
data['found_menus'] = data['found_menus'].apply(lambda x: ', '.join(x))
data['found_menus'] = data['found_menus'].replace(to_replace="", value = "로스") 
# 비어있는 값은 로스카츠로 간주

 

전체 메뉴 중, 칸쥬쿠와 에비카츠와 같이 비교적 인기가 없는 품목은 리뷰에서 언급이 없는 경우를 확인했다. 따라서, 해당 메뉴를 제외하고 리뷰에서 다수 보였던 메뉴들을 키워드로 선정하여 정해진 항목을 추출했다. 전체 리뷰를 추출할 때 "카츠" 라는 키워드도 추출했는데, 만약 위에 menus가 없다면 카츠를 먹었다고 가정하고 로스 카츠로 간주했다.

 

def remove_duplicates(s):
    return ', '.join(set(s.split(', ')))

data['found_menus'] = data['found_menus'].apply(remove_duplicates) # 중복 메뉴 제거
data

 

하나의 리뷰에 지정한 메뉴가 여러 번 반복되는 경우가 있다. 이런 경우, 중복이 발생한다고 가정하고 겹치는 메뉴는 제거하였다.

 

def change_menu_name(menu):
    if '버크셔' in menu and '로스' in menu and '특로스' in menu:
        return '버크셔 특로스, 버크셔 로스'
    elif '버크셔' in menu and '특로스' in menu:
        return '버크셔 특로스'
    elif '버크셔' in menu and '로스' in menu:
        return '버크셔 로스'
    elif '버크셔' in menu:
        return '버크셔 로스'
    else:
        return menu

data['product'] = data['found_menus'].apply(change_menu_name)
data

 

고객이 작성한 리뷰는 정확히 메뉴 이름을 명시하는 경우도 있지만, 그렇지 않은 경우가 더 많다. 따라서, 위에서 추출한 키워드를 실제 메뉴 이름으로 변환해야 한다. 톤쇼우는 "버크셔K"라는 돼지 품종으로 만든 로스와 특로스를 시그니쳐로 판매하고 있다. 따라서, 버크셔가 포함된 열은 해당 메뉴로 변환하였다. 또한, 버크셔만 추출된 열의 경우 시그니쳐를 먹었다고 간주하여, 버크셔 로스카츠로 분류했다.

 

def calculate_price(menu, menu_prices):
    prices = [menu_prices[item] for item in menu.split(', ') if item in menu_prices]
    return sum(prices)

menu_prices = {'버크셔 특로스':16500,
               '버크셔 로스':14000,
               '특로스':13500,
               '대로스':13000,
               '로스':11000,
               '히레':11500,
               '산도':9500,
               '모둠':14000
               }

data['amount'] = data['product'].apply(lambda x: calculate_price(x, menu_prices))
data

 

실제 메뉴를 찾았으면, 제품의 전체 가격을 더하여 구매 금액을 찾는다. 개별 상품의 가격은 네이버 플레이스의 판매 가격과 동일하게 설정했다.

 

from sklearn.preprocessing import LabelEncoder

encoder = LabelEncoder()
data['encoded_nickname'] = encoder.fit_transform(data['nickname'])

# 'U' 붙이기
data['user_id'] = 'U' + data['encoded_nickname'].astype(str).str.pad(3, fillchar='0')

data.to_csv('톤쇼우_rfm.csv, encoding = 'euc-kr', index = False)

 

RFM 분석을 위해, Labelencoding으로 닉네임을 마스킹(Masking)했다. 데이터 분석에서 고객의 개인정보를 관리하는 것은 상당히 중요한 요소로, 반드시 확실하게 처리해야한다.

 

이제 필요한 전처리가 끝났으니, dataframe을 csv로 저장하고 SQL로 RFM 분석을 시작해보자.

RFM 분석

RFM 분석은 마케팅에서 널리 사용되는 방법론으로, 고객의 가치를 파악하는 데 활용된다. 고객을 여러 그룹으로 분류하고, 각 그룹의 특성을 파악함으로써 효율적인 마케팅 전략을 수립할 수 있다. 그룹화의 방법은 다양하며, 고객의 행동 패턴, 구매력, 브랜드 충성도 등을 고려하여 세분화할 수 있다. 세부 항목은 다음과 같다.

1. Recency(최근 구매일) : 최근 무언가를 구매한 사용자를 우량 고객으로 취급
2. Frequency(구매 횟수) : 사용자가 구매한 횟수를 세고, 많은수록 우량 고객으로 취급
3. Monetary(구매 금액 합계) : 사용자의 구매 금액 합계를 집계하고, 금액이 높을수록 우량 고객으로 취급

비즈니스와 고객의 특성에 따라 R,F,M에 가중치를 다르게 줘서 집계할 수 있다. 

 

RFM 분석에 대해 간단히 알아봤으니, 이제 실습을 해보자. 실습 환경은 PostgreSQL의 pgAdmin 4로 진행했다.

 

Create Table

 

 

먼저, csv 파일을 가져올 table을 만든다. Column의 이름과 Type을 지정하고, save를 누르면 Table이 생성된다.

 

Csv import

 

Table이 정상적으로 만들어졌으면, Schema 안의 Table이 추가된 것을 확인할 수 있다. 생성된 Table에 마우스 우클릭을 누르고, import/export data를 선택한다.

 

 

load csv

 

python으로 저장한 csv의 경로를 설명하고, format과 encoding을 지정한다. 이 때, encoding이 맞지 않으면 import 과정에서 오류가 발생하므로 주의한다. Header 유무와 구분자를 설정하고 OK를 누르면, csv가 table로 import 된다.

 

Data

 

Data를 성공적으로 Load 했다. 이제 간단한 쿼리를 확인해보자. 

 

-- product 판매 순위 확인

select product, val, row_number() over(order by val desc)
from(
select product, count(*) as val
from ton_comment
group by product
limit 5);

 

메뉴 판매량 Top 5

 

일식 돈까스의 기본인 로스, 히레카츠와 시그니쳐 메뉴 버크셔K가 선호됨을 확인했다. 다음으로, 고객을 살펴보자.

 

select user_id, val as freq , row_number() over(order by val desc)
from(select user_id, count(*) as val
from ton_comment
group by user_id);

 

고객 방문 Top 5

 

추출한 데이터에서 고객의 재방문 빈도는 그렇게 높지 않음을 확인했다. 그렇다면, 사용자들의 행태는 어떨까? 정확한 분석을 위해 쉼표로 결합된 메뉴를 분리하고, RFM 척도를 계산해보았다.

 

with split_menu as(
select user_id, date, 
	trim(regexp_split_to_table(product,',')), amount
from ton_comment)
,user_rfm as(
select user_id, max(date) as recent_date,
	current_date - 7 - max(date::date) as recency,
	count(user_id) as frequency,
	sum(amount) as monetary
from split_menu
group by user_id
order by recency)

select * from user_rfm;

 

• regexp_split_to_table : 쉼표로 구분된 문자열을 행으로 분리
• trim : text column 양옆의 공백 제거
  
  

메뉴 분할 및 RFM 척도

 

데이터는 2023년 11월 23일을 기준으로 하기 때문에, 원래의 값에 7을 추가로 제했다. 방문 횟수, 빈도, 총 구매액이 정상적으로 출력됨을 확인할 수 있다, 이제 척도를 지정해보자. 데이터가 다소 부정확하기 때문에 percentile_cont 함수를 사용하여 분포에 따른 분할을 실시했다.

 

with split_menu as(
select user_id, date, 
	trim(regexp_split_to_table(product,',')), amount
from ton_comment)
,user_rfm as(
select user_id, max(date) as recent_date,
	current_date - 7 - max(date::date) as recency,
	count(user_id) as frequency,
	sum(amount) as monetary
from split_menu
group by user_id
order by recency)
--,user_rfm_per as(
select 
	percentile_cont(0.25) within group (order by recency) as r_q1,
	percentile_cont(0.5) within group (order by recency) as r_q2,
	percentile_cont(0.75) within group (order by recency) as r_q3,
	percentile_cont(0.25) within group (order by frequency) as f_q1,
	percentile_cont(0.5) within group (order by frequency) as f_q2,
	percentile_cont(0.75) within group (order by frequency) as f_q3,
	percentile_cont(0.25) within group (order by monetary) as m_q1,
	percentile_cont(0.5) within group (order by monetary) as m_q2,
	percentile_cont(0.75) within group (order by monetary) as m_q3
from user_rfm
)
,user_rfm_rank as(
select user_id, recent_date, recency, frequency, monetary, case
	when recency < 30 then 4
	when recency < 90 then 3
	when recency < 180 then 2
	else 1 end as r, 
case
	when 7 <= frequency then 4
	when 4 <= frequency then 3
	when 2 <= frequency then 2
	else 1 end as f,
case
	when 100000 <= monetary then 4
	when 50000 <= monetary then 3
	when 20000 <= monetary then 2
	else 1 end as m
from user_rfm)

select * from user_rfm_rank;

 

R, F, M

 

분포에 맞춰 분할했음에도, 원본 데이터의 한계로 크게 의미없는 결과가 도출됨을 확인했다. 그렇다 해도, 사용자가 어떻게 분할되는지 확인하기 위해 추가적인 프로세스를 진행했다.

 

with(
위의 CTE와 동일)
, mst_rfm_index as (
select generate_series(1,4) as rfm_index
	-- generate_series : 일련의 숫자를 지정된 간격으로 생성
), rfm_flag as(
select m.rfm_index,
	case when m.rfm_index = r.r then 1 else 0 end as r_flag,
	case when m.rfm_index = r.f then 1 else 0 end as f_flag,
	case when m.rfm_index = r.m then 1 else 0 end as m_flag
	from mst_rfm_index as m
	cross join
	user_rfm_rank as r
)

select rfm_index, 
sum(r_flag) as r, sum(f_flag) as f, sum(m_flag) as m
from rfm_flag
group by rfm_index
order by rfm_index desc;

 

그룹에 속한 사람 수

 

CTE에서 분할한 대로, 1-4까지의 범위 사이에서 r,f,m이 해당하는 범위를 확인했다. 정상 데이터를 기준으로, 이렇게 그룹이 구분되면 개별 RFM 랭크별로 어떠한 대책을 수행할지 검토할 수 있다. 즉, 고객군을 분할하여 서로 다른 전략을 수립하는 것 이다.

 

만약, R/F/M 3가지 요소로 고객군을 분할하면 위의 코드를 기준으로 64개의 그룹이 발생한다. 데이터가 방대해질수록, 그룹의 수는 자연스럽게 늘어나기 마련이다. 과연 몇백개의 그룹을 관리할 수 있을까? 가능하지만, 굉장히 어려우며 비효율적이다. 따라서, 각 랭크의 합계를 기반으로 통합 랭크를 만들어 그룹을 분류할 수 있다. 

 

with split_menu as(
	select user_id, date, trim(regexp_split_to_table(product, ',')), amount
	from ton_comment)
, user_rfm as(
select user_id, max(date) as recent_date, 
	current_date - 7 - max(date::date) as recency,
	count(user_id) as frequency,
	sum(amount) as monetary
from split_menu
group by user_id)
, user_rfm_rank as(
select user_id, recent_date, recency, frequency, monetary, case 
	when recency < 30 then 4
	when recency < 90 then 3
	when recency < 180 then 2
	else 1 end as r, 
case
	when 7 <= frequency then 4
	when 4 <= frequency then 3
	when 2 <= frequency then 2
	else 1 end as f,
case
	when 100000 <= monetary then 4
	when 50000 <= monetary then 3
	when 20000 <= monetary then 2
	else 1 end as m
from user_rfm
)

-- 통합 랭크 계산

select r+f+m as total_rank,r,f,m
from user_rfm_rank
group by r,f,m
order by total_rank desc, r desc, f desc, m desc;

-- 통합 랭크별 사용자 수

select r+f+m as total_rank, count(r+f+m)
from user_rfm_rank
group by total_rank
order by total_rank desc;

 

통합 랭크 계산

 

통합 랭크는 r,f,m을 모두 합친 값으로, 여기서는 최소 3부터 최대 12까지 존재한다. 불완전 데이터를 사용했기 때문에 r,f,m이 모두 1인 경우가 제일 많음을 확인할 수 있다. 하지만, 실제 데이터 역시 통합 랭크가 높아질수록 고객의 수는 줄어들기 때문에 추세만으로 봤을 때는 유사한 형태라고 이해했다. 수치가 높을수록 우량 고객으로, 낮을수록 비우량 고객으로 해석하면 된다.

 

2차원 사용자 인식

 

RFM의 지표 중 2개를 사용해서 고객군을 정의하는 방법도 있다. 개별 사용자층에 어떤 마케팅 전략을 사용하고, 어떻게 지금보다 상위 계층으로 옮길 수 있을지를 고민할 수 있다. 

 

with split_menu as(
	select user_id, date, trim(regexp_split_to_table(product, ',')), amount
	from ton_comment)
, user_rfm as(
select user_id, max(date) as recent_date, 
	current_date - 7 - max(date::date) as recency,
	count(user_id) as frequency,
	sum(amount) as monetary
from split_menu
group by user_id)
, user_rfm_rank as(
select user_id, recent_date, recency, frequency, monetary, case 
	when recency < 30 then 4
	when recency < 90 then 3
	when recency < 180 then 2
	else 1 end as r, 
case
	when 7 <= frequency then 4
	when 4 <= frequency then 3
	when 2 <= frequency then 2
	else 1 end as f,
case
	when 100000 <= monetary then 4
	when 50000 <= monetary then 3
	when 20000 <= monetary then 2
	else 1 end as m
from user_rfm
)

select concat('r_',r) as r_rank,
	count(case when f=4 then 1 end) as f_4,
	count(case when f=3 then 1 end) as f_3,
	count(case when f=2 then 1 end) as f_2,
	count(case when f=1 then 1 end) as f_1
from user_rfm_rank
group by r
order by r_rank desc
;

 

2차원 사용자 분석 결과

 

분석을 진행하며 부정확한 데이터의 한계를 느꼈다. 해당 데이터는 단순히 방문자 리뷰만으로 추출한 데이터이기 때문에 정확한 결과가 아니다. 또한, 분포 기준을 넓게 잡았음에도 여전히 rank 1에 대다수의 리뷰가 몰려있음을 확인할 수 있다. 톤쇼우 부산대점은 캐치테이블 어플로 확인하면 매일 웨이팅이 있고, 실제로 위치한 곳을 지나가면 늘 손님으로 붐비고 있다. 따라서, 오늘의 포스팅은 RFM 분석을 했다는 것에 의의를 두었다. 실제 판매 데이터를 접할 기회가 있다면, 조금 더 정확한 분석을 할 수 있을 것 이다.