GROWTH

B2B SaaS MRR 계산하기

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

이재현

September 12, 2024

MRR

‍

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

‍

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

‍

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

Centered Boxed Text

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을 구할 수 있습니다.

Text Color Change

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']]
출처: https://botongsaram.tistory.com/entry/B2B-SaaS-MRR-계산하기 [알랭드보통사람:티스토리]

위의 과정에서 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)에서 활용했던 과정입니다.

‍

Text Color Change

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()