订阅制收入分摊测算

在互联网行业中很多产品是采取的订阅制收费的模式，例如：订阅付费专栏，音乐视频会员，游戏会员，广告推广等等。

这类型的商品都有一个特点，就是用户在一个时点充值，在一段期间受益，收入需要按照这段期间进行分摊。

之前写过一篇用 Python 计算广告收入的文章，今天我们换一种方法用 SQL 来计算分摊的收入。

项目背景

下面摘取主要字段，作为示例数据：

( 注：金额单位为“分”）

上面数据对应的场景是，我在 2023 年 1 月 2 日花了12.9元，购买了一张季卡，可以有 90 天的订阅天数。

我在购买这个商品时，享受了 1 天的免费订阅天数，同时商品有个加价购的活动，加价 4 元可以增加 20 天的订阅天数。

因此，我总共花了12.9+4=16.9元，获取了1+90+20 = 111天的订阅天数。

公司的业务系统在每个订单充值时自动计算未来各月消耗，计算的方法为：

先消耗免费时长，不分摊金额，因此上表中有个 startTime 订单开始时间是从 1 月 3 号开始。

然后，从 startTime 开始计算未来所有月份的消耗，分摊金额为TotalFee - additionPrices，即加价购的不参与这段期间的分摊。

最后，再将加价购的金额分摊给加价购买天数。

系统每个月会生成消耗报表，对于这个订单来说，系统生成的消耗报表为：

总共订单数据量大概7000多万 。

这里我们首先需要验证下报表的准确性，即从订单数据出发按照系统计算逻辑计算后，与每月报表做比较。

其次，我们还需要按照会计准则的要求将总费用按照总天数平均分摊，看与报表的差异金额，是否达到重要性水平。

系统报表逻辑验证

这里我们先不考虑加价购买的情况，只考虑正常购买的消耗问题。

这也是数学中化归思想，先将复杂问题化归成简单问题，待简单问题解决后，再进行深入探讨。

假设我们计算 2023 年 1 月的应当消耗的金额。

如图所示，开始日为2023-01-01,结束日为2023-01-31.

startTime=2023-01-03 22:25:36

endTime=2023-01-03 22:25:36 + 90 day

需要摊销的金额为 TotalFee - additionPrices = 1290

我们知道需要摊销的金额，这里的关键就是计算消耗期间，而这个期间的计算可以总结为下面公式：

min(endTime, 结束日) - max(startTime, 开始日)

看到这个公式第一眼你可能不理解，没关系，你可以带入一些值去思考。

例如min(endTime,结束日) 就是报表期间结束日和订阅时间结束的较小值，这里报表期间结束日是2023-01-31 ，而订阅结束的日期肯定是大于 1 月的，所以取值为2023-01-31。

同样max(startTime,开始日) 就是报表期间开始日和订阅时间开始的较大值，这里报表期间开始日是2023-01-01, 而订阅时间开始日是2023-01-03所以取值是2023-01-03。

那么，min(endTime,结束日) - max(startTime, 开始日) 就是31-2 = 29天。

( 注：系统规定充值当天不足一天，不计入摊销，所以实际这里是 28 天，可以考虑通过计算小时数/24, 再向下取整得到正确的值）

当然，你还可以想象一订阅期间在报表期间不同位置的情况，也可以验证这个公式的正确性。

接下来，我们就可以使用 DuckDB 中写 SQL 语句了（注：数据量较大，考虑使用 DuckDB 计算）

with a as
(
select startTime,payType,totalFee,creatTime,accelDays,additionDays,additionPrices,
GREATEST(startTime,DATE '2023-01-01 00:00:00') beginTime,
LEAST(startTime + interval 1 day * accelDays,DATE '2023-02-01 00:00:00') endTime
from your_table
)
select payType,sum(cast(totalFee-additionPrices as float)/accelDays * calcDays)/100 AS money
from
(
select *,date_diff('hour',beginTime,
case when date_trunc('day', endTime) = endTime then endTime
else date_trunc('day', endTime + INTERVAL '1 day') end
)/24 calcDays from a
where endTime>=beginTime
) t
group by payType
order by payType

这里对上述 SQL 几处关键地方进行解释：

GREATEST(startTime,DATE '2023-01-01 00:00:00') beginTime ，这里就是前面公式max(startTime,开始日) ，其中GREATEST函数是取两个数的较大值，将计算结果重命名为 beginTime

LEAST(startTime + interval 1 day * accelDays,DATE '2023-02-01 00:00:00') endTime ，就里就是前面公式min(endTime,结束日) ，其中LEAST函数是取两个数的较小值，将计算结果重命名为 endTime

cast(totalFee-additionPrices as float) 将待分摊的费用，转为float数据类型，如果不转换的话，在 DuckDB 导入数据时自动将数据识别为整形Int, 这样后面在进行除法时会丢失精度，造成计算结果的不准确。

date_diff('hour',beginTime,
case when date_trunc('day', endTime) = endTime then endTime
else date_trunc('day', endTime + INTERVAL '1 day') end
)/24 calcDays

这里一长串实际就是求beginTime与 endTime 的间隔天数，但这个结束时间使用了case when语句进行判断，将 endTime 没有小时、分钟的时候使用endTime，如果有分钟的话就需要加 1 天，计算出两个时间的间隔小时数后除以24 ，由于小时数是整形，所以除法的时候就是自动向下取整。

这里是比较巧妙、简洁的作法，主要是考虑到了订阅开始日和结束日分别在报表期间的情况，能够正确计算当期天数。

如果你看懂了上面正常购买金额的摊销，那么加价购买的摊销与之是相同算法,

无非是开始日从startTime变成了startTime+accelDays,结束日从startTime+accelDays变成了startTime + accelDays +additionDays，然后把待分摊的金额变成了 additionPrices:

with a as
(
select startTime,payType,totalFee,creatTime,accelDays,additionDays,additionPrices,
GREATEST(startTime+ interval 1 day * accelDays,DATE '2023-01-01 00:00:00') beginTime,
LEAST(startTime + interval 1 day * (accelDays+additionDays),DATE '2023-02-01 00:00:00') endTime
from yourtable
)
select payType,sum(cast(additionPrices as float)/additionDays * calcDays)/100 AS money
from
(
select *,date_diff('hour',beginTime,
case when date_trunc('day', endTime) = endTime then endTime
else date_trunc('day', endTime + INTERVAL '1 day') end
)/24 calcDays from a
where endTime>=beginTime
) t
group by payType
order by payType

上面我们是计算的 2023 年 1 月消耗金额，我们实际上可以计算任意期间的消耗金额，只需要将对应日期进行改变。

我们甚至可以不分月，直接求一整年的金额。

按收入准则重新计算

上述计算过程，是我们按照系统报表逻辑计算的。如果按收入准则，我们可以将总金额平均分摊到所有订阅天数（包括免费赠送的和加价购的）。

这个计算方法和上述过程几乎一样，无非是改变了分摊金额和开始日、结束日，这里不再演示。

结语

本文对订阅制销售模式的收入计算进行了详细介绍，由此可以看出依托互联网销售的企业，高度依赖信息系统。

对于这类企业，在财务审计过程中，如果只简单听取财务对计算方法的介绍，是不足的。

甚至你抽取一些订单穿行测试也是不足的，因为很可能你都不知道系统在销售活动中有免费赠送和加价购的情况，自然不清楚其收入计算逻辑。

如果直接对这类高度依赖信息系统企业的业务系统报表直接利用，可能会存在审计风险。

需要我们利用 IT 审计的工作，对系统计算逻辑以及其产生报表进行验证。

若有 IT 审计业务需求，可以联系工作邮箱： tujiabing_cd@shinewing.com