暂无
任务描述
需求描述
一、背景
需对出单关键词进行整合，用于埋词引流，提升出单。
业务价值：整合SKU维度出单词，预估20000。
验收人：刘昊林。
数据流转流程：
数据来源（出单）--》出单listing（listing详情库-SearchTerms）--》提取关键词--》对应SKU下多少listing（标题，SearchTerms）含有这个关键词--》统计出单Listing个数与不出单listing个数。
2.1、新增关键词出单分析表
1、字段：关键词，站点，SKU，出单listing数（关键词对应的每个出单listing记录为1），在线listing数（关键词对应所有在线listing个数），出单率（出单listing数/在线listing数），关键词来源。
2、数据来源（满足下面其中之一条件就进入关键词出单分析表）：
1）1）出单sku：保证10w左右listing（US和MX站点），见出单数据来源参考。
2）ABA搜索词表现出单词，进入出单分析表条件：自身出单数大于0，PurchasesCount >0。
3）广告出单关键词，进入出单分析表条件：每天监控广告词表（Advert_Word_Report），出单数（NumOrder）大于0，出单类型记录为’广告出单‘，出单词来源记录为’广告词‘。
4）注：针对ABA搜索词表现出单词，广告出单关键词 进入关键词出单分析表需进行含有关键词在线listing数量统计（统计出单Listing个数与不出单listing个数）
3、关键词来源（类型）获取，新增关键词来源类型查询接口：
输入：关键词+SKU+站点调用关键词库接口查询对应的数据来源。
输出：关键词+SKU+站点+来源类型。
来源类型：1-ABA搜索词表现出单词，2-广告出单关键词，3-ABA关键词，4-Listing关键词，5-多套资料关键词，6-广告词，7-机器组装关键词，8-Chatgpt关键词合集，9-sif关键词。
4、数据唯一性：关键词+SKU+站点唯一。
2.2、出单数据来源参考
-- 基础数据整理
SELECT a.OrderSourceCountry AS 'Country',
a.SKU AS 'SKU',
COUNT(DISTINCT CONCAT(a.OrderSourceID, a.ASINorItemID)) AS 'AsinCount',
COUNT(DISTINCT d.OrderCode) AS 'Orders'
INTO #SkuCount
FROM dbo.RB_Amazon_Product_Base (NOLOCK) AS a
LEFT JOIN dbo.RB_OrderSource (NOLOCK) AS b ON a.CustomerID = b.CustomerID AND a.OrderSourceID = b.ID
LEFT JOIN dbo.RB_Admin (NOLOCK) AS c ON a.CustomerID = c.CustomerID AND b.SaleAdminID = c.ID
LEFT JOIN dbo.RB_Report_OrderSettlement (NOLOCK) AS d ON a.CustomerID = d.CustomerID AND a.OrderSourceID = d.OrderSourceID AND a.OrderSourceSKU = d.SellerSKU AND d.OrderCode != ''
WHERE a.CustomerID = 1
AND a.OrderSourceSKUID > 0
AND a.IsDelete = 0
AND a.IsOffShelf = 0
AND (c.UserFullPathName LIKE '%亚马逊平台运营中心%' OR c.UserFullPathName LIKE '%智能运营%')
AND d.ShipTime >= '2023-06-01'
GROUP BY a.OrderSourceCountry,
a.SKU
-- 瞅瞅大概
SELECT TOP 100 * FROM #SkuCount
-- 看看数量
SELECT COUNT(1) AS 'SkuCount',SUM(AsinCount) AS 'AsinCount'
FROM #SkuCount
WHERE Country = 'US' AND Orders >= 20
三、验收标准
关键词出单分析表数据准确。
需对出单关键词进行整合，用于埋词引流，提升出单。
业务价值：整合SKU维度出单词，预估20000。
验收人：刘昊林。
数据流转流程：
数据来源（出单）--》出单listing（listing详情库-SearchTerms）--》提取关键词--》对应SKU下多少listing（标题，SearchTerms）含有这个关键词--》统计出单Listing个数与不出单listing个数。
2.1、新增关键词出单分析表
1、字段：关键词，站点，SKU，出单listing数（关键词对应的每个出单listing记录为1），在线listing数（关键词对应所有在线listing个数），出单率（出单listing数/在线listing数），关键词来源。
2、数据来源（满足下面其中之一条件就进入关键词出单分析表）：
1）1）出单sku：保证10w左右listing（US和MX站点），见出单数据来源参考。
2）ABA搜索词表现出单词，进入出单分析表条件：自身出单数大于0，PurchasesCount >0。
3）广告出单关键词，进入出单分析表条件：每天监控广告词表（Advert_Word_Report），出单数（NumOrder）大于0，出单类型记录为’广告出单‘，出单词来源记录为’广告词‘。
4）注：针对ABA搜索词表现出单词，广告出单关键词 进入关键词出单分析表需进行含有关键词在线listing数量统计（统计出单Listing个数与不出单listing个数）
3、关键词来源（类型）获取，新增关键词来源类型查询接口：
输入：关键词+SKU+站点调用关键词库接口查询对应的数据来源。
输出：关键词+SKU+站点+来源类型。
来源类型：1-ABA搜索词表现出单词，2-广告出单关键词，3-ABA关键词，4-Listing关键词，5-多套资料关键词，6-广告词，7-机器组装关键词，8-Chatgpt关键词合集，9-sif关键词。
4、数据唯一性：关键词+SKU+站点唯一。
2.2、出单数据来源参考
-- 基础数据整理
SELECT a.OrderSourceCountry AS 'Country',
a.SKU AS 'SKU',
COUNT(DISTINCT CONCAT(a.OrderSourceID, a.ASINorItemID)) AS 'AsinCount',
COUNT(DISTINCT d.OrderCode) AS 'Orders'
INTO #SkuCount
FROM dbo.RB_Amazon_Product_Base (NOLOCK) AS a
LEFT JOIN dbo.RB_OrderSource (NOLOCK) AS b ON a.CustomerID = b.CustomerID AND a.OrderSourceID = b.ID
LEFT JOIN dbo.RB_Admin (NOLOCK) AS c ON a.CustomerID = c.CustomerID AND b.SaleAdminID = c.ID
LEFT JOIN dbo.RB_Report_OrderSettlement (NOLOCK) AS d ON a.CustomerID = d.CustomerID AND a.OrderSourceID = d.OrderSourceID AND a.OrderSourceSKU = d.SellerSKU AND d.OrderCode != ''
WHERE a.CustomerID = 1
AND a.OrderSourceSKUID > 0
AND a.IsDelete = 0
AND a.IsOffShelf = 0
AND (c.UserFullPathName LIKE '%亚马逊平台运营中心%' OR c.UserFullPathName LIKE '%智能运营%')
AND d.ShipTime >= '2023-06-01'
GROUP BY a.OrderSourceCountry,
a.SKU
-- 瞅瞅大概
SELECT TOP 100 * FROM #SkuCount
-- 看看数量
SELECT COUNT(1) AS 'SkuCount',SUM(AsinCount) AS 'AsinCount'
FROM #SkuCount
WHERE Country = 'US' AND Orders >= 20
三、验收标准
关键词出单分析表数据准确。
验收标准
关键词出单分析表数据准确。
涉及项目：amazonworker
1、初始化出单sku，只处理US和MX站点，数据存储：RB_Sku_Order_Summary
sql如下：
--站点：US
INSERT INTO RB_Sku_Order_Summary
(
Sku,
OrderSourceCountry,
AsinCount,
OrderCount,
AddTime,
UpdateTime
)
SELECT
a.SKU AS 'SKU',
a.OrderSourceCountry AS 'Country',
COUNT(DISTINCT CONCAT(a.OrderSourceID, a.ASINorItemID)) AS 'AsinCount',
COUNT(DISTINCT d.OrderCode) AS 'Orders',
GETDATE(),
GETDATE()
FROM dbo.RB_Amazon_Product_Base (NOLOCK) AS a
LEFT JOIN dbo.RB_OrderSource (NOLOCK) AS b ON a.CustomerID = b.CustomerID AND a.OrderSourceID = b.ID
LEFT JOIN dbo.RB_Admin (NOLOCK) AS c ON a.CustomerID = c.CustomerID AND b.SaleAdminID = c.ID
LEFT JOIN dbo.RB_Report_OrderSettlement (NOLOCK) AS d ON a.CustomerID = d.CustomerID AND a.OrderSourceID = d.OrderSourceID AND a.OrderSourceSKU = d.SellerSKU AND d.OrderCode != ''
WHERE a.CustomerID = 1
AND a.OrderSourceSKUID > 0
AND a.IsDelete = 0
AND a.OrderSourceCountry IN('US')
AND a.IsOffShelf = 0
AND (c.UserFullPathName LIKE '%亚马逊平台运营中心%' OR c.UserFullPathName LIKE '%智能运营%')
AND d.ShipTime >= '2023-06-01'
GROUP BY a.OrderSourceCountry,
a.SKU
HAVING COUNT(DISTINCT CONCAT(a.OrderSourceID, a.ASINorItemID))>5
--站点：MX
INSERT INTO RB_Sku_Order_Summary
(
Sku,
OrderSourceCountry,
AsinCount,
OrderCount,
AddTime,
UpdateTime
)
SELECT
a.SKU AS 'SKU',
a.OrderSourceCountry AS 'Country',
COUNT(DISTINCT CONCAT(a.OrderSourceID, a.ASINorItemID)) AS 'AsinCount',
COUNT(DISTINCT d.OrderCode) AS 'Orders',
GETDATE(),
GETDATE()
FROM dbo.RB_Amazon_Product_Base (NOLOCK) AS a
LEFT JOIN dbo.RB_OrderSource (NOLOCK) AS b ON a.CustomerID = b.CustomerID AND a.OrderSourceID = b.ID
LEFT JOIN dbo.RB_Admin (NOLOCK) AS c ON a.CustomerID = c.CustomerID AND b.SaleAdminID = c.ID
LEFT JOIN dbo.RB_Report_OrderSettlement (NOLOCK) AS d ON a.CustomerID = d.CustomerID AND a.OrderSourceID = d.OrderSourceID AND a.OrderSourceSKU = d.SellerSKU AND d.OrderCode != ''
WHERE a.CustomerID = 1
AND a.OrderSourceSKUID > 0
AND a.IsDelete = 0
AND a.OrderSourceCountry IN('MX')
AND a.IsOffShelf = 0
AND (c.UserFullPathName LIKE '%亚马逊平台运营中心%' OR c.UserFullPathName LIKE '%智能运营%')
AND d.ShipTime >= '2023-06-01'
GROUP BY a.OrderSourceCountry,
a.SKU
HAVING COUNT(DISTINCT CONCAT(a.OrderSourceID, a.ASINorItemID))>2
2、新增服务，sku关键词初始化，表：AmazonSkuKeyWordStatistics
3、新增服务，sku关键词出单汇总，表：AmazonSkuKeyWordStatistics