任务描述
一、背景
目前相关度服务由于压力大已停止，导致现有得标题埋词无词可以埋。故需要将相关度服务控制在有效范围进行重启相关度服务。
业务价值：保证有新得关键词相关度，用于标题埋词提升出单率，预估50W。
验收人：沈伯潜
二、需求描述
2.1、关键词相关度计算优化
操作位置：关键词相关度计算
逻辑：指定SKU 跑相关度，筛选FBM的 在去年11和12月卖的比较好的英语语种SKU进行跑相关度
-- 可通过年月+站点找对应SKU 跑相关度，进行循环跑相关度。
参考语句：
SELECT j.Country,
k.SKU,
COUNT(DISTINCT k.OrderCode) AS 'Orders'
FROM zhcxkj_dw_bi.rb_orderinfo AS j
LEFT JOIN zhcxkj_dw_bi.rb_orderlist k
ON j.CustomerID = k.CustomerID AND j.OrderCode = k.OrderCode
LEFT JOIN zhcxkj_dw_bi.rb_warehouse AS l
ON j.CustomerID = l.CustomerID AND j.WareHouseID = l.ID
LEFT JOIN zhcxkj_AutoPublish_bi.BI_Amazon_Ad_Department AS m
ON j.OrderSourceID = m.OrderSourceID
INNER JOIN zhcxkj_dw_bi.rb_product AS i
ON k.SKU = i.SKU AND i.IsGroup = 0
WHERE j.CustomerID = 1
AND j.DownLoadUpdateTime <> '2000-1-1'
AND j.PayTime BETWEEN '2023-11-01 00:00:00' AND '2023-12-31 00:00:00'
AND j.OrderStatus NOT IN (-1, 2)
AND j.IsStockOrder = 0
AND j.RefundType = 0
AND m.OrderSourceType = 1
AND l.WareHouseType = 1
AND m.SourceType IN ('独立账号', '运营中心')
AND j.Country in ('US','SG','SA','IN','GB','EG','CA','AU','AE')
GROUP BY j.Country,
k.SKU
1
ORDER BY COUNT(DISTINCT k.OrderCode) DESC
三、风险描述
无
四、验收标准
验收人：沈伯潜
验收标准：相关度可以正常跑起来，便于标题正常埋词。
需求描述
一、背景
目前相关度服务由于压力大已停止，导致现有得标题埋词无词可以埋。故需要将相关度服务控制在有效范围进行重启相关度服务。
业务价值：保证有新得关键词相关度，用于标题埋词提升出单率，预估50W。
验收人：沈伯潜
二、需求描述
2.1、关键词相关度计算优化
操作位置：关键词相关度计算
逻辑：指定SKU 跑相关度，筛选FBM的 在去年11和12月卖的比较好的英语语种SKU进行跑相关度
-- 可通过年月+站点找对应SKU 跑相关度，进行循环跑相关度。
参考语句：
SELECT j.Country,
k.SKU,
COUNT(DISTINCT k.OrderCode) AS 'Orders'
FROM zhcxkj_dw_bi.rb_orderinfo AS j
LEFT JOIN zhcxkj_dw_bi.rb_orderlist k
ON j.CustomerID = k.CustomerID AND j.OrderCode = k.OrderCode
LEFT JOIN zhcxkj_dw_bi.rb_warehouse AS l
ON j.CustomerID = l.CustomerID AND j.WareHouseID = l.ID
LEFT JOIN zhcxkj_AutoPublish_bi.BI_Amazon_Ad_Department AS m
ON j.OrderSourceID = m.OrderSourceID
INNER JOIN zhcxkj_dw_bi.rb_product AS i
ON k.SKU = i.SKU AND i.IsGroup = 0
WHERE j.CustomerID = 1
AND j.DownLoadUpdateTime <> '2000-1-1'
AND j.PayTime BETWEEN '2023-11-01 00:00:00' AND '2023-12-31 00:00:00'
AND j.OrderStatus NOT IN (-1, 2)
AND j.IsStockOrder = 0
AND j.RefundType = 0
AND m.OrderSourceType = 1
AND l.WareHouseType = 1
AND m.SourceType IN ('独立账号', '运营中心')
AND j.Country in ('US','SG','SA','IN','GB','EG','CA','AU','AE')
GROUP BY j.Country,
k.SKU
Having COUNT(DISTINCT k.OrderCode)>1
ORDER BY COUNT(DISTINCT k.OrderCode) DESC
三、风险描述
无
四、验收标准
验收人：沈伯潜
验收标准：相关度可以正常跑起来，便于标题正常埋词。
验收标准
关键词相关度计算服务正常启动