B2B SaaS MRR 계산하기 - 구글 스프레드시트에 저장하고 데이터 스튜디어로 시각화하기(python)

 

MRR

 

MRR 이란?
Monthly Recurring Revenue로 월간 반복 매출 구독형 서비스의 경우 핵심 지표로 활용됩니다.

 

요즘은 누구나 한 번쯤은 구독형 서비스를 결제해본 경험이 있을텐데 구독 비즈니스는 사용자가 카드를 등록하면 자동으로 월마다 반복적으로 자동결제가 됩니다. (유튜브 프리미엄 구독, 넷플릭스 등)

 

MRR 산출 공식은 다음과 같습니다.

MRR = 고객수 * 평균 청구 금액

 

예를 들면, 유튜브 프리미엄 월 구독 비용이 10,000원이고 이용자가 10명이라면 MRR 은 100,000원이 되는 겁니다. 

 

그런데 MRR이 100,000원 입니다. 에서  끝나면 안되겠죠?

 

MRR이 어떻게 변화했는지를 분석하는 것도 굉장히 중요합니다. 

 

MRR 지표를 쪼개보면 아래와 같습니다.

• NEW MRR : 신규 고객의 MRR
• Expansion MRR : 기존 고객의 구독 상품 업그레이드의 MRR
• Contraction MRR : 기존 고객의 구독 상품 다운그레이드의 MRR
• Churn MRR : 구독 취소 
• Reactivation MRR : 재활성화된 MRR 

그러면 이 지표를 가지고 우리는 New MRR을 구할 수 있습니다. 

Net MRR = New MRR + Upgrade MRR - Churned MRR - Downgrade MRR + Reactivation MRR 

 

실무하면서 MRR calculation 하는데 고생 고생을 했는데 이 방법으로 도움을 받을 수 있는 누군가를 위해 공유합니다.

 

우선 방법은 DB에 적재된 결제 데이터와 구글 스프레드 시트에서 세금계산서로 처리되는 고객 결제 내역을 바탕으로 Rawdata를 준비하고 파이썬을 활용해서 frequency(월결제, 연결제)를 flatten 해주고 이를 Looker Studio로 시각화해줍니다. 

 

1. DB에서 데이터 불러오기

2. 구글 스프레드시트에서 데이터 불러오기

3. 파이썬으로 데이터 클렌징하기

4. 클렌징한 DB, 스프레드시트 데이터 합치기

5. 클렌징 결과 시각화를 위해 구글 스프레드시트로 보내기

6. Looker Studio로 시각화 하기

 

 

아래는 실제 활용했던 MRR 대시보드 입니다. 

 

첫 번째 대시보드는 월매출과, MRR 지표 현황을 살펴보고 고객사별 월간 구독 현황을 확인 해볼 수 있고 ARR, 이번달 예상 매출도 확인 할 수 있게 구성했었습니다.

MRR 대시보드 예시(1)

 

두번째 대시보드는 위에서 언급한대로 MRR 지표를 쪼개서 모니터링하는 대시보드입니다.

이를 통해 MRR 상승하는데 어떤 지표 때문에 상승했는지 한눈에 볼 수 있습니다.  또한 MRR 성장추이도 함께 볼 수 있도록 구성되었습니다. 이제 이 대시보드를 통해서 전사가 우리 서비스의 MRR 현황을 볼 수 있고 이탈한 유저수가 특히 많았던 월에는 고객 인터뷰를 진행해보거나 해당 유저들의 특징을 파악해보면 어떤 지점에서 불편함을 느꼈는지 페인 포인트는 무엇이었는지 원인을 파악하고 제품 개선에 반영해볼 수 있겠죠? 

MRR 대시보드 예시(2)

 

 

DB에서 결제 데이터를 위의 컬럼만 파이썬 SQLAlchemy 를 활용해서 데이터를 불러옵니다. 

 

SELECT  
            user_id,
            company_name,
            sales,
            pay_datetime_id,
            freq,
            user_status    
FROM
        (SELECT tmp2.user_id,
               tmp2.company_name,
               tmp2.id,
               tmp1.sales,
               date_format(tmp1.auth_date, '%Y-%m-01') auth_date,
               tmp1.subscription_id,
               tmp1.user_status,
               tmp1.card_updated_at
          FROM
                    (SELECT B.user_id,
                           A.sales,
                           A.auth_date,
                           A.subscription_id,
                           A.user_status,
                           A.card_updated_at
                    FROM
                    (SELECT st0.*,
                            st1.user_status,
                            st1.card_updated_at
                    FROM
                    -- billing_payment_history 테이블에서 조건에 맞는 데이터 가져오기
                    (SELECT billing_id,
                           subscription_id,
                           auth_date,
                           IF(cancelled_at IS NULL ,amount, IF (amount <= cancellation_cancel_amount,0,cancellation_remain_amt)) AS sales
                    FROM nicepay_billing_payment_history
                    ) st0
                    left join
                    	-- nicepay_card_info 테이블에서 가장 최근의 카드 정보(created_at이 최대인)를 가져오며, user_status를 is_deleted와 is_active 값에 따라 ‘churned_user’ 또는 ‘active_user’로 설정
                        (select t1.billing_id,
                                t1.created_at,
                                t1.is_deleted,
                                t1.is_active,
                                t1.created_at card_updated_at,
                                (case when t1.is_deleted = 1 and t1.is_active = 0 then 'churned_user' else 'active_user' END) user_status
                                from nicepay_card_info t1
                        inner join (select billing_id,
                                            max(created_at) max_date
                                    from nicepay_card_info
                                    group by 1) t2
                        on t1.billing_id = t2.billing_id and t1.created_at = t2.max_date) st1
                    ON (st0.billing_id = st1.billing_id)) A
                    LEFT OUTER JOIN
                                    (SELECT billing_id,
                                            user_id
                                     FROM nicepay_billing_info) B
                    ON (A.billing_id = B.billing_id)) tmp1
INNER JOIN (SELECT user_id,
                        company_name,
                        id
            FROM user
          ) tmp2
ON (tmp1.user_id = tmp2.user_id)) tmp3
inner join
                    (select seq,
                            freq,
                            date_format(plan_start_datetime_id, '%Y-%m-01') subscription_plan_start_datetime_id
                     from subscription) tmp4
ON (tmp3.subscription_id = tmp4.seq)
where sales > 0;

 

먼저 계산을 위해 필요한 데이터를 DB에서 추출해주고 flatten을 해줄껍니다.

 

flatten이 뭐냐 뜻 그대로 평탄화한다는 의미입니다. 예를들어 고객이 연간 결제를 1,200,000원을 했다고 하면 월별 결제액은 12개월로 나눠서 월별로 데이터를 평탄화 해주는 과정이라고 보시면 됩니다.

 

#expand the yearly records

mrr_base = mrr_df.loc[np.repeat(mrr_df.index, mrr_df['freq'].map({"years":12,"months": 1}))]

mrr_base.loc[mrr_base["freq"] == "years", "sales"] /= 12

mrr_base.loc[mrr_base["freq"] == "years", "pay_datetime_id"] += \

mrr_base.groupby(["user_id", "freq"]).cumcount().loc[mrr_base["freq"] == "years"]\

.map(lambda i: pd.DateOffset(months=i)) 

 

저의 경우 DB에 기록되지 않은 세금계산서 데이터를 가지고 있었는데 

 

해당 데이터는 구글 스프레드시트에 기록하고 있으므로 구글 스프레드시트에서 데이터를 불러옵니다. 

 

#구글 스프레드시트에서 data load하기

scope = ['https://spreadsheets.google.com/feeds',

'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name(

'credential 파일 경로(json파일)', scope)

gc = gspread.authorize(credentials)

spreadsheet_url = "가져올 스프레드시트 주소"

gc1 = gc.open_by_url(spreadsheet_url).worksheet('시트 이름')

#기존에 기록된 데이터 가져와서 리스트 형태로 리턴

gc2 = gc1.get_all_values()

#데이터프레임으로 판다스로 가져오기

gc2 = pd.DataFrame(gc2, columns=gc2[0])

gc2 = gc2.reindex(gc2.index.drop(0))

#날짜 형식으로 변경

gc2['pay_datetime_id'] = pd.to_datetime(gc2['pay_datetime_id'])

#sales 컬럼 숫자로 변경

gc2['sales'] = gc2['sales'].astype(str).astype(int)

#expand the yearly records

gc2_base = gc2.loc[np.repeat(gc2.index, gc2['freq'].map({"years":12, "months": 1, "2years":24}))]

# compute monthly fee and join date

#years 12개월로 나누기

gc2_base.loc[gc2_base["freq"] == "years", "sales"] /= 12

gc2_base.loc[gc2_base["freq"] == "years", "pay_datetime_id"] += \

gc2_base.groupby(["user_id", "freq"]).cumcount().loc[gc2_base["freq"] == "years"] \

.map(lambda i: pd.DateOffset(months=i))

#2years 24개월로 나누기 / years 12개월로 나누기

gc2_base.loc[gc2_base["freq"] == "2years", "sales"] /= 24

gc2_base.loc[gc2_base["freq"] == "2years", "pay_datetime_id"] += \

gc2_base.groupby(["user_id", "freq"]).cumcount().loc[gc2_base["freq"] == "2years"] \

.map(lambda i: pd.DateOffset(months=i))

gc2_base_result = gc2_base[['user_id', 'company_name', 'sales', 'pay_datetime_id', 'freq']]

 

위의 과정에서 DB에서 불러온 데이터와 구글 스프레드시트의 데이터 형태를 통일 시켰습니다. 

이제 Raw Data를 만들기 위해서 합쳐줍니다

#구글시트rawdata와 DB에서 불러온 데이터의 결합

df_union= pd.concat([mrr_result, gc2_base_result])

 

데이터를 통합한 다음에 데이터 시각화를 위해 데이터 시각화를 위해 스프레드시트에 최종 정리된 데이터를 다시 구글 스프레드시트로 전달합니다.

 

# union 된 결과를 다시 구글 스프레드시트로

scope = ['https://spreadsheets.google.com/feeds',

'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name(

'credential 파일 경로(json)', scope)

gc = gspread.authorize(credentials)

spreadsheet_url = '스프레드시트주소

gc_mrr = gc.open_by_url(spreadsheet_url).worksheet('새로 데이터를 업로드할 시트명')

#기존에 기록되어 있던 데이터 삭제(처음 업로드할 때는 필요 없음)

gc_mrr.clear()

# 오늘 가져온 데이터 업로드

gd.set_with_dataframe(gc_mrr,merge)

 

위의 결과는 MRR 대시보드 예시(1)에서 활용했던 과정입니다. 

 

New MRR + Upgrade MRR - Churned MRR - Downgrade MRR + Reactivation MRR 

 

다음으로 MRR detail view 에 사용될 지표를 만들 차례입니다. 

 

# pay_datetime_id 열에서 월과 연도를 추출하여 새로운 열 생성
mrr_detail_result['month_year'] = mrr_detail_result['pay_datetime_id'].dt.to_period("M")

# max_date 컬럼 만들기
mrr_detail_result['max_date'] = pd.NaT

mrr_detail_result.head()

# user_id를 기준으로 그룹화
grouped = mrr_detail_result.groupby('user_id')

# 각 그룹에서 최대 결제일을 찾고 max_date 열에 할당
for name, group in grouped:
    max_date = group['pay_datetime_id'].max()
    max_index = group['pay_datetime_id'].idxmax()
    mrr_detail_result.loc[group.index, 'max_date'] = max_date

# 데이터프레임을 corporate_id와 pay_datetime_id 기준으로 정렬
transactions_ver2 = mrr_detail_result.sort_values(by=['corporate_id', 'pay_datetime_id'])

# 이전 거래 금액을 저장할 새로운 열 prev_amount 추가
transactions_ver2['prev_amount'] = transactions_ver2.groupby('corporate_id')['sales'].shift(1)

# 사용자 상태를 저장할 새로운 열 user_status 추가
transactions_ver2['mrr_status'] = 'new'

# 이전 거래 금액과 동일한 금액을 가진 사용자에게 'existing' 할당
transactions_ver2.loc[transactions_ver2['sales'] == transactions_ver2['prev_amount'], 'mrr_status'] = 'existing'

# 이전 거래 금액보다 높은 금액을 가진 사용자에게 'upgrade' 할당
transactions_ver2.loc[transactions_ver2['sales'] > transactions_ver2['prev_amount'], 'mrr_status'] = 'upgrade'

# 이전 거래 금액보다 낮은 금액을 가진 사용자에게 'downgrade' 할당
transactions_ver2.loc[transactions_ver2['sales'] < transactions_ver2['prev_amount'], 'mrr_status'] = 'downgrade'

# 이전에 'churned' 상태였고 이제 새로운 거래가 있는 사용자에게 'reactivation' 할당
# 모든 고유한 corporate_id 값을 포함하는 리스트 생성
corporate_ids = transactions_ver2['corporate_id'].unique()

# 각 corporate_id에 대해 루프를 돌며 각 거래의 상태를 업데이트
for corporate_id in corporate_ids:
    user_data = transactions_ver2[transactions_ver2['corporate_id'] == corporate_id]
    for i in range(1, len(user_data)):
        prev_month = user_data.iloc[i-1]['month_year']
        curr_month = user_data.iloc[i]['month_year']
        if (curr_month - prev_month).n > 1:
            transactions_ver2.loc[(transactions_ver2['corporate_id'] == corporate_id) & (transactions_ver2['month_year'] == curr_month), 'mrr_status'] = 'reactivation'

# user_status가 churn_user인 사용자의 마지막 결제일에 'churn' 상태 할당
transactions_ver2.loc[(transactions_ver2['user_status'] == 'churned_user') & (transactions_ver2['pay_datetime_id'] == transactions_ver2['max_date']), 'mrr_status'] = 'churned'

transactions_ver2.head()

 

이제 MRR 대시보드 예시(2)에 활용된 데이터가 전처리되었고 구글 스프레드시트로 데이터를 적재해주면 됩니다. 

 

이걸 매일 하기는 귀찮으니 Airflow DAG를 활용해서 자동화해주면 됩니다. 

 

여기까지 MRR계산을 위해 SQL, 구글 스프레드시트, python을 활용한 과정을 소개해봤습니다.

누군가 B2B SaaS에서 MRR 계산을 위해 고군분투하고 계시다면 이 코드가 도움이 되시면 좋겠네요 

 

추가로 초기에 대시보드 기획에 많은 참고가 되었던 Baremetrics라는 MRR 대시보드 외산 툴이 있는데 상당히 잘만들었다고 생각되는 서비스입니다.

 

SaaS 비즈니스를 운영하신다면 참고해보시면 좋을 서비스네요!