在凤凰计划中，导出22年1月1月至22年12月31日的自动促销数据，以评估自动促销策略的效果
Sheet总自动促销—2022.01.01～2022.12.31凤凰计划做过自动促销的fba和自发货数据
1、促销listing数量：做了促销的listing
2、出单listing数量：做了促销的listing中出单的
3、促销出单率：2/1
4、促销期间销量：促销出单的listing在促销期间出单的产品数量
5、销售额：4*单价
6、估算总利润：参考之前的产品开发成功率的利润算法
| 所属迭代 | 技术研发中心 12月分支版本（2022年） |
|---|---|
| 所属模块 | / |
| 相关需求 | 暂无 |
| 指派给 | Closed 于 2022-12-26 18:38:44 |
| 任务类型 | 后端 |
| 任务标签 | 初始 |
| 开发组 | 百货开发组 |
| 任务状态 | 已关闭 |
| 进度 | 100% |
| 优先级 | P2 |
| 关键词 | |
| 抄送给 |
| 由谁创建 | 梁贵荣 于 2022-12-26 15:18:01 |
|---|---|
| 由谁完成 | 冯名明 于 2022-12-26 18:36:56 |
| 由谁取消 | 暂无 |
| 由谁关闭 | 冯名明 于 2022-12-26 18:38:44 |
| 关闭原因 | 已完成 |
| 最后编辑 | 冯名明 于 2022-12-26 18:38:44 |
| 团队 | 最初预计 | 总计消耗 | 预计剩余 |
|---|
| 最初预计 | 8工时 |
|---|---|
| 总计消耗 | 3工时 |
| 预计剩余 | 0工时 |
| 预计开始 | 2022-12-23 |
| 实际开始 | 2022-12-26 |
| 截止日期 | 2022-12-26 |
1.了解导出数据源2小时
2.导出数据1小时
3.导出文件
4.导出数据脚本
-- 促销Listing数量 IsAutoPromotion =1 为促销产品
DECLARE @BeginTime VARCHAR(50);
SET @BeginTime = '2022-12-01 00:00:00';
DECLARE @EndTime VARCHAR(50);
SET @EndTime = '2022-12-31 23:59:59';
SELECT apb.CustomerID 'CustomerID',
ISNULL(apb.IsFBA, 0) 'isfba',
COUNT(DISTINCT (apb.OrderSourceSKUID)) '促销listing数量'
FROM RB_Amazon_Product_Base (NOLOCK) apb
INNER JOIN RB_Amazon_PhoenixListingDetail (NOLOCK) apld
ON apld.CustomerId = apb.CustomerID
AND apld.OrderSourceSku = apb.OrderSourceSKU
AND apld.OrderSourceId = apb.OrderSourceID
AND apld.PlanType = 3
INNER JOIN dbo.RB_OrderSource (NOLOCK) os
ON os.CustomerID = apb.CustomerID
AND apb.OrderSourceID = os.ID
AND os.OrderSourceType = 1
WHERE apb.CustomerID = 1
AND apb.IsAutoPromotion = 1
AND apld.AddTime >= @BeginTime
AND apld.AddTime <= @EndTime
AND apb.OrderSourceSKUID > 0
AND os.IsActive = 1
AND os.SourceStatus = 0
AND apb.IsDelete = 0
AND apb.IsActive = 1
AND ISNULL(apb.IsFBA, 0)=1
GROUP BY apb.CustomerID,
ISNULL(apb.IsFBA, 0)
DECLARE @BeginTime VARCHAR(50);
SET @BeginTime = '2022-12-01 00:00:00';
DECLARE @EndTime VARCHAR(50);
SET @EndTime = '2022-12-31 23:59:59';
SELECT apb.CustomerID 'CustomerID',
ISNULL(apb.IsFBA, 0) 'isfba',
COUNT(DISTINCT (apb.OrderSourceSKUID)) '促销出单listing数量',
SUM(ISNULL(ros.ProductNum, 0) + ISNULL(ros.ProdutNumUpdate, 0)) 'TotalSaleNumber',
SUM((ISNULL(ros.ProductNum, 0) + ISNULL(ros.ProdutNumUpdate, 0))*ISNULL(ros.ProductSalesPrice, 0)) 'TotalSale',
SUM(ISNULL(ros.Profits,0)) '估算利润'
FROM RB_Report_OrderSettlement (NOLOCK) ros
INNER JOIN RB_Amazon_PhoenixListingDetail (NOLOCK) apld
ON apld.CustomerId = ros.CustomerID
AND apld.OrderSourceId = ros.OrderSourceID
AND apld.OrderSourceSku = ros.SellerSKU
AND apld.PlanType = 3
INNER JOIN dbo.RB_Amazon_Product_Base apb (NOLOCK)
ON apb.CustomerID = apld.CustomerId
AND apb.OrderSourceID = apld.OrderSourceId
AND apb.OrderSourceSKU = apld.OrderSourceSku
INNER JOIN dbo.RB_OrderSource (NOLOCK) os
ON os.CustomerID = ros.CustomerID
AND os.ID = ros.OrderSourceID
AND os.OrderSourceType = 1
WHERE ros.CustomerID = 1
AND apb.OrderSourceSKUID > 0
AND ros.PayTime >= @BeginTime
AND ros.PayTime <= @EndTime
AND apld.AddTime >= @BeginTime
--AND ISNULL(apb.IsFBA, 0) = 0
AND apld.AddTime <= @EndTime
AND apb.IsDelete = 0
AND apb.IsActive = 1
AND ISNULL(apb.IsFBA, 0)=0
GROUP BY apb.CustomerID,
ISNULL(apb.IsFBA, 0)
SELECT COUNT(DISTINCT (apld.OrderSourceSku)) '成功下架数量'
FROM RB_Amazon_PhoenixListingDetail (NOLOCK) apld
WHERE apld.CustomerID = 1
AND apld.PlanType = 1
AND apld.AddTime >= '2022-01-01 00:00:00'
AND apld.AddTime <= '2022-12-31 23:59:59';