题解 | #10月的新户客单价和获客成本#

注意点

• 时间限定：2021年10月
• 对象限定：新用户，首单
• 求值目标：月客单价为总支付金额除以用户数，月获客成本为优惠金额除以用户数
• 识别新用户，首次购买日期在2021年10月（先找再筛）
• 识别首单，每个用户的最小订单号（这默认订单号按下单先后顺序生成）

思路

• 对订单总表查询每个用户的最早下单日期和最早订单id
• 上述表内连接订单明细表，筛选日期、新用户、首单
  • 注意：这里返回的是每个新用户的首单产品销售单价、下订数量及支付总价
• 对上述连接表按用户汇总销售总价（售单价*下订数）、订单支付总价
• 最后对上述汇总表计算客单价和获客成本

SQL代码

select round(sum(total_pay) / count(uid), 1)                     as avg_amount,
       round((sum(total_sale) - sum(total_pay)) / count(uid), 1) as avg_cost
from (
         select too.uid,
                max(total_amount)        as total_pay,
                sum(tod.price * tod.cnt) as total_sale
         from (
                  select order_id,
                         uid,
                         date(event_time)                              as dt,
                         total_amount,
                         total_cnt,
                         min(order_id) over (partition by uid)         as first_order,
                         min(date(event_time)) over (partition by uid) as first_purchase
                  from tb_order_overall
              ) as too
                  inner join tb_order_detail as tod on (tod.order_id = too.order_id)
         where too.dt = too.first_purchase
           and tod.order_id = first_order
           and date_format(too.dt, '%Y-%m') = '2021-10'
         group by too.uid
     ) as t