Checking Growth Metrics for Subscription Services #2

(2) Subscription Revenue Metrics

Previous Post: Checking Growth Metrics for Subscription Services #1. Subscriber Metrics

In the previous post, we looked into subscribers, one of the growth metrics for subscription services. In this post, we'll dive into the second growth metric for subscription services: subscription revenue (or sales).

If the number of paid subscribers increased this month compared to last month, we can say our service is growing well. In a similar vein, if subscription revenue increased this month compared to last month, can we also assume our service is growing well? The answer to this question is "we can't know for sure." Unlike "subscribers," where an increment can directly translate to performance, evaluating the performance of "subscription revenue" requires more analysis than a simple period-by-period aggregation. Let's take a closer look at this right now.

1. Aggregating payment amounts based on the time of revenue generation

When analyzing subscription revenue, it is not advisable to simply aggregate daily or monthly payment amounts. In a typical subscription payment process, the amount for the entire subscription period is paid upfront once (if you pay for a 1-month or 1-year pass, you can use the service during that period without additional payments), so subscription revenue has the characteristic of bringing future revenue to the present and pre-reflecting it. (Because of this characteristic, from an accounting perspective, it is common to first record subscription revenue as a liability corresponding to an advance payment, and then separate only the elapsed portion according to the service usage period to record it as revenue. I'm not trying to talk about accounting in this post, so that's enough TMI for now...)

Even without considering complex accounting treatments, simply aggregating daily or monthly payment amounts provides data with a resolution too low to glean meaningful insights. Even if both last month's and this month's revenues are identical at 100 million KRW, last month's 100 million KRW subscription revenue and this month's 100 million KRW subscription revenue could mean entirely different things depending on various payment properties, such as the ratio of first-time subscriptions to renewals, the types of subscription products, the subscription periods (monthly, annual...), or the prices of the subscription products.

2. Flattening revenue by proportioning it across the entire subscription period

If it is difficult to judge the revenue growth of a subscription service by simply aggregating daily or monthly payment amounts, what alternatives are there? The first method you might consider is recognizing subscription revenue by proportioning (flattening) it across the entire subscription period (rather than recognizing it all at the time of payment). For example, if a user pays for an annual subscription product worth 36,500 KRW, you divide this by 365 and calculate it as if 100 KRW of revenue was generated every day for a year starting from the day of payment. In this case, you can solve the problem of pre-reflecting future revenue all at once, allowing you to check a much more reliable revenue trend. You could call it a sort of Flattening Revenue. (Of course, you can also group the flattened data by week or month to see the total by period.)

However, even if you flatten the data considering the subscription period, it is hard to say the data resolution is high. You can see the big picture—like whether the revenue trend is sloping upward or stagnating... but the amount of information you can check is too insufficient and simple to analyze the cause of current revenue stagnation or delicately fish out hypotheses to boost revenue.

3. Breaking down and examining revenue based on the properties of paying users

A widely recommended framework when analyzing subscription revenue is MRR (Monthly Recurring Revenue). The core of MRR lies in distinguishing between recurring and non-recurring revenue, and breaking down the context in which each revenue was generated. For instance, if one new subscriber joins a subscription service that charges 10,000 KRW per month, we can expect a recurring monthly revenue of 10,000 KRW until this user cancels their subscription. Identifying this monthly recurring payment revenue and separately calculating the revenue affected by events that impact the increase or decrease of recurring payment revenue (new subscriptions, subscription cancellations, subscription product upgrades...) is the core of MRR analysis.

If last month's MRR was 1 million KRW and there were no events affecting recurring revenue for a month (=no new subscribers, no cancellations, and no one changing their subscription product), this month's MRR remains the same 1 million KRW. In other words, the new month's MRR can be calculated by using the previous month's MRR as a baseline and reflecting the amount of change caused by events that occurred this month. The events that occurred this month can be divided into those that increase MRR and those that decrease it. For events that increase MRR, we can think of cases where a new subscriber joins, someone who previously subscribed and canceled comes back, or a current subscriber upgrades their subscription product. Conversely, events that decrease MRR could include subscriber churn or subscription product downgrades. Expressing this in a simple formula looks like the below.

In the formula above, Previous month's MRR — left_subs can also be expressed as renew_subs (renewal revenue from current subscribed users). In this case, the formula can also be written in the following format.

If we know renew_subs, we can also calculate the churn rate of subscription revenue. It can be easily obtained through the formula below.

That concludes the general(?) explanation of MRR. As explained above, MRR itself is a simple concept, and if you search with related keywords, you can easily find reference articles. (Ref 1, Ref 2, Ref 3) However, in reality(!), conducting MRR analysis is often easier said than done. I was well aware of this theory(?) too, but when I actually tried to calculate and utilize MRR during work, I ran into a few difficulties -_-;; I'd like to explain this part in a bit more detail.

4. Calculating and utilizing MRR in practice

1) Identifying users

For MRR analysis, you basically need to be able to check payment products and frequencies on a per-user basis. Based on this, you must be able to map and identify each payment as a first-time payment or a renewal payment to determine recurring revenue. If you internalize and possess all subscription and payment info, it probably won't be much of an issue. But if you're using in-app purchases provided by the Apple and Google App Stores, this part isn't as simple as you'd think. (The Alarmy service has a structure where subscription payments are made based on Apple or Google accounts, separate from the Alarmy login.)

In Google's case, you can check data in the following format through Google Play's Sales Report. (We use Fivetran to load subscription-related data from each store into our internal DW.) It's a structure where you can check raw data for each payment, but it doesn't include identifiers like individual users' GAID or email. So, to determine a first-time payment versus a repurchase (or consecutive payment), you have to utilize the order number (order_number). You can identify the same user through the prefix of the order_number and determine the number of consecutive payments according to the serial number displayed in the suffix. When looking at consecutive payments, if you check whether a consistent interval from past payment dates is maintained, you might also be able to distinguish whether it's a renew or a return.

It's TMI, but because the suffix portion indicating the first payment appears in various cases like 0, 1, null, etc. (explaining based on SQL syntax), you can utilize it by calculating the user's payment frequency separately using the row_number() function like below.

split(order_number, '..')[safe_ordinal(1)] as pid 
--해서 동일 유저(?)를 식별한 다음에,

row_number() over(partition by pid order by order_charged_date) as subs_no
--해서 날짜에 따라 누적 구독결제횟수 집

Relatively speaking, the Apple App Store is much trickier to analyze because it doesn't provide user-level info in the first place and gives all data in an aggregated form. At least they distinguish first-time payments and repurchases with a separate flag, but since they log trial payments as first-time payments too, it's hard to accurately distinguish the true first paid payment for user flows moving from trial -> paid. Because of this, in order to analyze Apple App Store subscription data in detail, you absolutely need your own subscription data logging system separate from the data received from the store.

2) Calculating canceled subscription revenue (left_subs)

left_subs means the revenue decreased due to subscription cancellation. What you need to be careful about here is that a subscription cancellation is a passive event. It doesn't mean a negative payment actually occurs while performing a "cancel subscription" event, but rather it's an event where the amount that could have been paid consecutively is "not paid" due to the passive event of "not making a repurchase." (Based on behavioral logs, we can specify a "cancel subscription" event, but the "subscription was canceled" status value doesn't show up in Sales reports checking through stores. Naturally, because only completed payments are recorded here.)

So, theoretically, it looks like you get renew_subs by subtracting left_subs from the previous month's MRR, but in reality, it's more natural to aggregate renew_subs first and then calculate left_subs by subtracting this number from the previous month's MRR. In other words, you can calculate it as left_subs = Previous month's MRR — renew_subs.

3) Distinguishing between Renew_subs and Return_subs

Should we distinguish between consecutive repurchases (renew_subs) and non-consecutive repurchases (return_subs)? There are many cases where they aren't strictly separated (in this case, return_subs is often classified as a subset of new_subs), but personally, I think it's advisable to view them separately. This is because a first-time payment and a non-consecutive repurchase have different characteristics. Since the initial start is incredibly important for subscriptions in particular, funnel analysis connecting install (or signup) -> new_subs is crucial, but if the proportion of return_subs is large, there is room to misinterpret the data when analyzing this funnel.

The technical method to distinguish between renew_subs and return_subs is to check whether payments repeat without omission on a regular cycle. (For monthly payments, if the difference from the previous payment date is within 32 days, judge it as renew, and if it's longer, judge it as return...)

LAG(order_charged_date) OVER (partition by pid ORDER BY order_charged_date) previous_order_date
-- 를 통해 직전 결제일 정보 별도 컬럼화

case when date_diff(order_charged_date, previous_order_date, day) < 32 then 1 else 0 end as cont 
-- 를 통해 연속결제 여부를 식별한 후, 아래와 같이 renew와 return 구분

sum(case when order_cnt>1 and cont=1 then sales_amount_usd END) as renew_subs
sum(case when order_cnt>1 and cont=0 then sales_amount_usd END) as return_subs

4) How to view MRR for annual subscription products?

In what way can we view MRR for annual subscription products? Honestly, since annual subscriptions don't structurally differ from monthly subscriptions, it's simplest to view ARR (Annual Recurring Revenue) after dividing the Duration into annual units. However, this has the problem of having to wait (twiddling your thumbs) for a year while performance accumulates. If an issue arises, noticing it or responding to it might be way too late, and in many ways, it's not a recommended method. Come to think of it, making decisions while watching data pile up one line per year is too unrealistic.

For this reason, even if the subscription period is annual, it's better to accumulate and analyze data on a monthly basis. Conceptually, you could explain this as looking at it by dividing the annual subscription payment amount by 12, flattening it as if(!) monthly payments occurred. But, there is one difference from the monthly subscription analysis mentioned earlier. We need one more status value called cont_subs. cont_subs means that although a payment wasn't made that month, the subscription is being maintained because they already paid for an annual subscription and it's before the subscription expires. In short, it's a Status that isn't a target for churn or repurchase. If we reflect this and reorganize the annual subscription MRR formula, it looks like below.

In the formula above, we can swap it to renew_subs = Previous month's MRR — left_subs — cont_subs. If we apply and modify this, we can also express the relationship between the previous month's MRR and the current month's MRR with the formula below. (It's the same formula we saw when analyzing monthly subscription MRR, right? That's because cont_subs is included in the Baseline.)

5) Handling refunds

When refunds occur after payment, how should we reflect this? A simple method is to process the original payment details for the refunded order by excluding them from the analysis. (Like there was no payment at all stemming from that order_id if a refund occurs...) Except in this case, because past date data gets continuously modified when refunds happen, there's the problem of having to update the entire data every day.

A more complex but accurate method is to separately categorize the status value for refunds. Earlier, we defined subscription cancellation with a status value of left_subs. You need to keep in mind that a refund is a different event from a subscription cancellation. While left_subs (subscription cancellation) is a passive event that stops upcoming payments (past payments remain unchanged), refund_subs (refund) is an active event that reverts past payment data.

6) Both user-based and revenue-based calculations are possible

The last part to mention is that when breaking down and analyzing the MRR of subscription products like above, you shouldn't confuse aggregation on a "user" basis with aggregation on a "revenue" basis.

For example,

The formula above can be created based on the "number of subscribers," and it can also be created based on "subscription revenue." Remember that new_subs in the formula above can either be "number of new subscribers" or "revenue from new subscribers" depending on the purpose of the analysis, and please be careful not to confuse this part when building aggregation queries or dashboards.

Over two posts, we've looked into the growth metrics for subscription services (subscribers, subscription revenue). (I originally intended to write the two posts back-to-back, but pushing it off little by little for other things meant it took a whopping 4 months to finish... -_-;;; ) There's no doubt that subscriber numbers or subscription revenue are important metrics, but since both are closer to lagging metrics that get aggregated in the end, it would be great to remember that breaking down and viewing the various components that make them up can greatly increase the resolution of subscription data analysis. I hope this was helpful to those in charge of subscription services!

(I'm especially looking hard for a Data Engineer who will be working closely with me. ^^;;)