BigData

张某人的学习积累

业务知识 学习笔记 数仓建模

数仓建模 – 互联网营销业务 – 省钱记录数据建设

作者张, 宴银

8 月 19, 2024

业务背景 

⚫ 心智缺：目前对于平台官方补贴的三大机制（FS/EDC/LPI），缺少营销教育的主阵地，机制更新的实时传达较差，用户心智不强； 
⚫ 流量散：营销流量分散不集中，没有统一承接页面； 
⚫ 整合弱：不同页面领取不同权益，跨机制商品购买权益弱，频道间机制关联弱。

数据需求

将 FS/EDC/LPI 业务的用户省钱记录明细和周期统计及历史统计展示出来。 
数据需求如下： 
1.省钱记录明细； 
2.周期省钱记录汇总； 
3.省钱订单 Top3 商品展示；

省钱数据链路设计

省钱轻度汇总表.sql

WITH t_trade_order_info AS (
SELECT
        trade_order_line_id
        ,trade_order_id
        ,end_time as order_finish_time
        ,buyer_id
        ,venture
        ,SUBSTR(COALESCE(KEYVALUE(features,';',':','rdt')*1000,end_time),1,13) as order_delivery_time
        ,SUBSTR(KEYVALUE(features,';',':','codt')*1000,1,13) as order_fulfill_time
        ,store_id
        ,item_id
        ,item_title
        ,item_url
        ,item_pict_url
        ,sku_id
        ,sku_info
        ,category_id
        ,quantity
FROM    tdyh_dwd.dwd_lzd_trd_order_di
WHERE ds =MAX_PT('tdyh_dwd.dwd_lzd_trd_order_di')
AND delivery_status =7
AND TO_CHAR(sg_udf.epoch_to_timezone(end_time ,venture ),'yyyymmdd') = '${bizdate}'
),

t_vulcan_record AS (
SELECT
    *
    ,ROW_NUMBER() OVER (PARTITION BY user_id,trade_order_id,trade_order_line_id,venture,COALESCE(biz_code,tool_code),ref_benefit_id
    ORDER BY record_id) AS  record_rank
FROM
(
SELECT
    GETDATE() gmt_create
    ,GETDATE() as gmt_modified
    ,t_redeem.user_id
    ,t_vulcan.flow_id
    ,t_vulcan.record_id
    ,t_vulcan.benefit_id
    ,t_vulcan.benefit_type
    ,t_redeem.product_code
    ,t_redeem.product_sub_code
    ,t_redeem.biz_type
    ,COALESCE(t_redeem.promotion_id,t_vulcan.ref_benefit_id) AS ref_benefit_id
    ,COALESCE(t_redeem.collect_id,t_vulcan.ref_record_id) AS ref_record_id
    ,t_redeem.sales_order_id AS trade_order_id
    ,t_redeem.sales_order_item_id AS trade_order_line_id
    ,t_redeem.promotion_amount_min as amount
    ,t_redeem.tool_code
    ,t_trade.store_id
    ,t_trade.item_id
    ,t_trade.item_title
    ,t_trade.item_url
    ,t_trade.item_pict_url
    ,t_trade.sku_id
    ,t_trade.sku_info
    ,t_trade.category_id
    ,t_trade.quantity
    ,t_trade.order_fulfill_time
    ,t_trade.order_delivery_time
    ,t_trade.order_finish_time
    ,t_trade.venture
    ,COALESCE(t_vulcan.biz_code,t_redeem.biz_code,'unknown') as biz_code
FROM t_trade_order_info t_trade
JOIN
(
SELECT
    collect_id
    ,promotion_id
    ,case when venture = 'VN' then promotion_amount_local else promotion_amount_local*100  end AS promotion_amount_min
    ,sales_order_id
    ,sales_order_item_id
    ,user_id
    ,venture
    ,getBenefitBizCode(feature) AS biz_code
    ,'' as product_code
    ,'' as product_sub_code
    ,'' as biz_type
    ,tool_code  -- 新增字段
    ,ds
FROM tdyh_cdm.dwd_lzd_pro_promotion_redeem_di
WHERE ds>= TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), - 90, 'dd'), 'yyyymmdd')
AND  venture IN ('TH','PH','MY','SG','ID','VN')
-- AND  collect_id IS NOT  NULL
) t_redeem
ON t_trade.trade_order_line_id = t_redeem.sales_order_item_id
AND t_trade.trade_order_id = t_redeem.sales_order_id
AND t_trade.venture = t_redeem.venture
AND t_trade.buyer_id = t_redeem.user_id
LEFT JOIN
(
SELECT
        id  as record_id
        ,flow_id
        ,biz_code
        ,value_type as benefit_type
        ,benefit_id
        ,ref_record_id
        ,ref_benefit_id
        ,user_id
        ,venture
FROM tdyh_mkt.plutus_lottery_record_history
WHERE ds>= TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), - 120, 'dd'), 'yyyymmdd')
AND venture IN ('TH','PH','MY','SG','ID','VN')
) t_vulcan
ON t_redeem.collect_id = t_vulcan.ref_record_id
AND t_redeem.venture = t_vulcan.venture
AND t_redeem.user_id = t_vulcan.user_id
)  res
),
t_save_money_detail (
SELECT
  gmt_create,
  gmt_modified,
  user_id,
  flow_id,
  record_id,
  benefit_id,
  benefit_type,
  product_code,
  product_sub_code,
  biz_type,
  ref_benefit_id,
  ref_record_id,
  trade_order_id,
  trade_order_line_id,
  amount,
  store_id,
  item_id,
  item_title,
  item_url,
  item_pict_url,
  sku_id,
  sku_info,
  category_id,
  quantity,
  order_fulfill_time,
  order_delivery_time,
  order_finish_time,
  tool_code,
  venture,
  biz_code
FROM t_vulcan_record
WHERE record_rank = 1
)

INSERT  OVERWRITE TABLE dws_lzd_ug_save_money_di PARTITION (ds='${bizdate}',venture,biz_code)
SELECT
     GETDATE() gmt_create
    ,GETDATE() as gmt_modified
    ,user_id
    ,trade_order_id
    ,trade_order_line_id
    ,SUM(amount) AS amount
    ,store_id
    ,item_id
    ,item_title
    ,item_url
    ,item_pict_url
    ,sku_id
    ,sku_info
    ,category_id
    ,SUM(quantity) AS quantity
    ,order_fulfill_time
    ,order_delivery_time
    ,order_finish_time
    ,venture
    ,'LAZ_CLUB' AS save_biz_code
FROM t_save_money_detail
WHERE biz_code = 'LAZ_CLUB'
GROUP BY
    user_id
    ,venture
    ,trade_order_id
    ,trade_order_line_id
    ,store_id
    ,item_id
    ,item_title
    ,item_url
    ,item_pict_url
    ,sku_id
    ,sku_info
    ,category_id
    ,order_fulfill_time
    ,order_delivery_time
    ,order_finish_time

UNION ALL
SELECT
    GETDATE() gmt_create
    ,GETDATE() as gmt_modified
    ,user_id
    ,trade_order_id
    ,trade_order_line_id
    ,SUM(amount) AS amount
    ,store_id
    ,item_id
    ,item_title
    ,item_url
    ,item_pict_url
    ,sku_id
    ,sku_info
    ,category_id
    ,SUM(quantity) AS quantity
    ,order_fulfill_time
    ,order_delivery_time
    ,order_finish_time
    ,venture
    ,'FREE_SHIPPING' AS save_biz_code
FROM t_save_money_detail
WHERE tolower(tool_code) LIKE '%shippingfee%'
GROUP BY
    user_id
    ,venture
    ,trade_order_id
    ,trade_order_line_id
    ,store_id
    ,item_id
    ,item_title
    ,item_url
    ,item_pict_url
    ,sku_id
    ,sku_info
    ,category_id
    ,order_fulfill_time
    ,order_delivery_time
    ,order_finish_time
UNION ALL
SELECT
    GETDATE() gmt_create
    ,GETDATE() as gmt_modified
    ,user_id
    ,trade_order_id
    ,trade_order_line_id
    ,SUM(amount) AS amount
    ,store_id
    ,item_id
    ,item_title
    ,item_url
    ,item_pict_url
    ,sku_id
    ,sku_info
    ,category_id
    ,SUM(quantity) AS quantity
    ,order_fulfill_time
    ,order_delivery_time
    ,order_finish_time
    ,venture
    ,'LPI' AS save_biz_code
FROM t_save_money_detail
WHERE tolower(tool_code)= 'purchaseincentive'
GROUP BY
    user_id
    ,venture
    ,trade_order_id
    ,trade_order_line_id
    ,store_id
    ,item_id
    ,item_title
    ,item_url
    ,item_pict_url
    ,sku_id
    ,sku_info
    ,category_id
    ,order_fulfill_time
    ,order_delivery_time
    ,order_finish_time
;

周期省钱汇总表.sql

-- 历史数据初始化
WITH t_day_change AS (
SELECT
    user_id
    ,1 AS  period_type
    ,TO_CHAR(sg_udf.epoch_to_timezone(order_fulfill_time ,venture),'yyyymm')  AS period_id
    ,SUM(amount) as stat_amount
    ,COUNT(DISTINCT trade_order_line_id) AS stat_count  -- 子订单数 : todo 修改
    ,venture
    ,'LAZ_CLUB' as biz_code
FROM tdyh_mkt.dws_lzd_ug_save_money_di
WHERE ds = '${bizdate}'
AND biz_code ='LAZ_CLUB'
GROUP BY user_id,venture,
TO_CHAR(sg_udf.epoch_to_timezone(order_fulfill_time ,venture ) ,'yyyymm')
UNION ALL
SELECT
    user_id
    ,1 AS  period_type
    ,TO_CHAR(sg_udf.epoch_to_timezone(order_fulfill_time ,venture),'yyyymm')  AS period_id
    ,SUM(amount) as stat_amount
    ,COUNT(DISTINCT trade_order_line_id) AS stat_count  -- 子订单数 : todo 修改
    ,venture
    ,'FREE_SHIPPING' as biz_code
FROM tdyh_mkt.dws_lzd_ug_save_money_di
WHERE ds = '${bizdate}'
AND biz_code ='FREE_SHIPPING'
GROUP BY user_id,venture,
TO_CHAR(sg_udf.epoch_to_timezone(order_fulfill_time ,venture ) ,'yyyymm')
UNION ALL
SELECT
    user_id
    ,1 AS  period_type
    ,TO_CHAR(sg_udf.epoch_to_timezone(order_fulfill_time ,venture),'yyyymm')  AS period_id
    ,SUM(amount) as stat_amount
    ,COUNT(DISTINCT trade_order_line_id) AS stat_count  -- 子订单数 : todo 修改
    ,venture
    ,'LPI' as biz_code
FROM tdyh_mkt.dws_lzd_ug_save_money_di
WHERE ds = '${bizdate}'
AND biz_code ='LPI'
GROUP BY user_id,venture,
TO_CHAR(sg_udf.epoch_to_timezone(order_fulfill_time ,venture ) ,'yyyymm')
) ,

t_update_all AS (
SELECT
    user_id
    ,period_id
    ,venture
   ,biz_code
   ,period_type
   ,SUM(stat_amount) AS stat_amount
   ,SUM(stat_count) AS stat_count
FROM
(
SELECT
   user_id
   ,period_type
   ,CAST(period_id AS BIGINT ) AS period_id
   ,stat_amount
   ,stat_count
   ,venture
   ,biz_code
FROM  t_day_change  t_change
UNION ALL
SELECT
    user_id
   ,period_type
   ,period_id
   ,stat_amount
   ,stat_count
   ,venture
   ,biz_code
FROM  tdyh_mkt.dws_lzd_ug_save_money_mtd
WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -1, 'dd'), 'yyyymmdd')
) t_all
GROUP BY  user_id
    ,period_id
    ,venture
   ,biz_code
   ,period_type
)

INSERT OVERWRITE TABLE dws_lzd_ug_save_money_mtd PARTITION(ds='${bizdate}',venture,biz_code,is_change)
SELECT
    GETDATE() gmt_create
    ,GETDATE() as gmt_modified
    ,t_all.user_id
    ,t_all.period_type
    ,t_all.period_id
    ,t_all.stat_amount
    ,t_all.stat_count
    ,t_all.venture AS site_id
    ,t_all.venture
    ,t_all.biz_code
    ,IF(t_change.user_id is not null ,1,0) AS is_change
FROM t_update_all t_all
LEFT JOIN t_day_change t_change
ON t_all.venture = t_change.venture
AND t_all.user_id = t_change.user_id
AND t_all.biz_code = t_change.biz_code
;

省钱订单Top3商品表.sql

-- 对每天增量省钱的用户重排序
WITH t_history_change AS (
SELECT
        t_history_df.user_id
        ,t_history_df.venture
        ,t_history_df.biz_code
        ,t_history_df.trade_order_id
        ,t_history_df.trade_order_line_id
        ,t_history_df.amount
        ,t_history_df.amount_rank
        ,t_history_df.store_id
        ,t_history_df.item_id
        ,t_history_df.item_title
        ,t_history_df.item_url
        ,t_history_df.item_pict_url
        ,t_history_df.sku_id
        ,t_history_df.sku_info
        ,t_history_df.category_id
        ,t_history_df.quantity
        ,t_history_df.order_fulfill_time
        ,t_history_df.order_delivery_time
        ,t_history_df.order_finish_time
FROM (
SELECT
        *
FROM    tdyh_mkt.dwd_lzd_ug_save_money_rank_df
WHERE   ds = TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -1, 'dd'), 'yyyymmdd')
) t_history_df
JOIN (
SELECT user_id,venture,biz_code
FROM   tdyh_mkt.dws_lzd_ug_save_money_di
WHERE   ds = '${bizdate}'
GROUP BY user_id,venture,biz_code
) t_delta_save
ON t_history_df.user_id = t_delta_save.user_id
AND t_history_df.venture = t_delta_save.venture
AND t_history_df.biz_code = t_delta_save.biz_code
),

t_change_all AS (
SELECT
  user_id
  ,trade_order_id
  ,venture
  ,biz_code
  ,store_id
  ,item_id
  ,item_title
  ,item_url
  ,item_pict_url
  ,sku_id
  ,sku_info
  ,category_id
  ,order_fulfill_time
  ,order_delivery_time
  ,order_finish_time
  ,amount
  ,quantity
FROM t_history_change
UNION ALL
SELECT
  user_id
  ,trade_order_id
  ,venture
  ,biz_code
  ,0 as store_id
  ,item_id
  ,item_title
  ,item_url
  ,item_pict_url
  ,sku_id
  ,'' AS sku_info
  ,category_id
  ,max(order_fulfill_time) as order_fulfill_time
  ,max(order_delivery_time) as order_delivery_time
  ,max(order_finish_time) as order_finish_time
  ,SUM(amount) AS amount
  ,SUM(quantity) AS quantity
FROM    tdyh_mkt.dws_lzd_ug_save_money_di
WHERE   ds = '${bizdate}'
GROUP BY
  user_id
  ,trade_order_id
  ,venture
  ,biz_code
  ,item_id
  ,item_title
  ,item_url
  ,item_pict_url
  ,sku_id
  ,category_id
),

t_change_all_rank AS (
SELECT
        user_id
        ,amount_rank
        ,venture
        ,biz_code
        ,trade_order_id
        ,amount
        ,store_id
        ,item_id
        ,item_title
        ,item_url
        ,item_pict_url
        ,sku_id
        ,sku_info
        ,category_id
        ,quantity
        ,order_fulfill_time
        ,order_delivery_time
        ,order_finish_time
FROM
(
SELECT
    *
    ,ROW_NUMBER() OVER (PARTITION BY user_id,venture,biz_code ORDER BY amount DESC,sku_id DESC ,trade_order_id DESC) AS  amount_rank
FROM
(
SELECT
  user_id
  ,trade_order_id
  ,venture
  ,biz_code
  ,0 as store_id
  ,item_id
  ,item_title
  ,item_url
  ,item_pict_url
  ,sku_id
  ,'' AS sku_info
  ,category_id
  ,max(order_fulfill_time) as order_fulfill_time
  ,max(order_delivery_time) as order_delivery_time
  ,max(order_finish_time) as order_finish_time
  ,SUM(amount) AS amount
  ,SUM(quantity) AS quantity
FROM t_change_all
GROUP BY
  user_id
  ,trade_order_id
  ,venture
  ,biz_code
  ,item_id
  ,item_title
  ,item_url
  ,item_pict_url
  ,sku_id
  ,category_id
) t
) res
WHERE amount_rank <=3
)

INSERT OVERWRITE TABLE dwd_lzd_ug_save_money_rank_df PARTITION (ds='${bizdate}',venture,biz_code,is_change)
SELECT
        t_history_df.gmt_create
        ,t_history_df.gmt_modified
        ,t_history_df.user_id
        ,t_history_df.amount_rank
        ,t_history_df.trade_order_id
        ,t_history_df.trade_order_line_id
        ,t_history_df.amount
        ,t_history_df.store_id
        ,t_history_df.item_id
        ,t_history_df.item_title
        ,t_history_df.item_url
        ,t_history_df.item_pict_url
        ,t_history_df.sku_id
        ,t_history_df.sku_info
        ,t_history_df.category_id
        ,t_history_df.quantity
        ,t_history_df.order_fulfill_time
        ,t_history_df.order_delivery_time
        ,t_history_df.order_finish_time
        ,t_history_df.venture AS site_id
        ,t_history_df.venture
        ,t_history_df.biz_code
        ,0 as is_change
FROM (
SELECT *
FROM tdyh_mkt.dwd_lzd_ug_save_money_rank_df
WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -1, 'dd'), 'yyyymmdd')
) t_history_df
LEFT JOIN
(
SELECT
    user_id,venture,biz_code
FROM tdyh_mkt.dws_lzd_ug_save_money_di
WHERE   ds = '${bizdate}'
GROUP BY  user_id,venture,biz_code
) t_delta_save
ON t_history_df.user_id = t_delta_save.user_id
AND t_history_df.venture = t_delta_save.venture
AND t_history_df.biz_code = t_delta_save.biz_code
WHERE t_delta_save.user_id IS NULL
UNION ALL
SELECT
        GETDATE() AS gmt_create
        ,GETDATE() AS gmt_modified
        ,t_change_rank.user_id
        ,t_change_rank.amount_rank
        ,t_change_rank.trade_order_id
        ,0 as trade_order_line_id
        ,t_change_rank.amount
        ,t_change_rank.store_id
        ,t_change_rank.item_id
        ,t_change_rank.item_title
        ,t_change_rank.item_url
        ,t_change_rank.item_pict_url
        ,t_change_rank.sku_id
        ,t_change_rank.sku_info
        ,t_change_rank.category_id
        ,t_change_rank.quantity
        ,t_change_rank.order_fulfill_time
        ,t_change_rank.order_delivery_time
        ,t_change_rank.order_finish_time
        ,t_change_rank.venture AS site_id
        ,t_change_rank.venture
        ,t_change_rank.biz_code
        ,CASE WHEN t_his_change.user_id IS NOT NULL AND t_change_rank.amount_rank = t_his_change.amount_rank THEN 0 ELSE 1 END AS is_change
FROM t_change_all_rank t_change_rank
LEFT JOIN t_history_change t_his_change
ON t_change_rank.user_id = t_his_change.user_id
AND t_change_rank.venture = t_his_change.venture
AND t_change_rank.biz_code = t_his_change.biz_code
AND t_change_rank.trade_order_id = t_his_change.trade_order_id
AND t_change_rank.item_id = t_his_change.item_id
AND t_change_rank.sku_id = t_his_change.sku_id
;

作者 张, 宴银

相关文章

业务知识 学习笔记

物流知识讲堂 – 物流业务知识

5 月 13, 2025 admin
学习笔记 数仓建模

中级数据建模师

2 月 8, 2025 张, 宴银
学习笔记 数仓建模

初级模型设计

1 月 24, 2025 张, 宴银

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

You missed

业务知识 学习笔记

物流知识讲堂 – 物流业务知识

2025 年 5 月 13 日 admin
数据治理

零基础学习数据治理 – 黎山

2025 年 2 月 27 日 张, 宴银
AI

AI大模型工具

2025 年 2 月 25 日 张, 宴银
AI 机器学习

机器学习 – Numpy

2025 年 2 月 11 日 张, 宴银