摘要

Olist 是巴西本土电商平台，连接中小商家与消费者。本文基于 Kaggle 公开的 Olist Brazilian Ecommerce 数据集，以 “市场-产品再匹配”（Market-Product Fit） 为理论框架，从 供给侧、需求侧、用户运营、物流履约 四个维度，对平台经营状况进行系统诊断，并提出可落地的优化方案。

核心发现：

• 供给端“一超多强”：SP 州贡献 64.54% GMV，形成虹吸效应；RO、AM、PA 等偏远州跨州订单超 80%，本地供给严重不足。
• 商家结构高度集中：Top 10% 商家占据 66.72% GMV，中小商家成长困难。
• 需求端“三低”特征：复购率仅 3.02%，ARPU 仅 166，呈现低复购、低客单、低留存的典型问题。
• 物流滞后拖累体验：东北部准时率低至 81.7%，延迟每增加 1 天评分下降 0.38 分。

分析框架与价值主张：

本文采用 MPF（市场-产品再匹配）框架，通过 Python/SQL 数据清洗、特征构建与 Tableau 可视化，构建 L1-L4 四层指标体系，系统诊断平台“供给集中、需求分散、物流滞后”的结构性矛盾，并输出四端联动优化方案：

1. 供给端：定向招商（RJ/MG/GO 等州）与中小商家孵化，破解区域失衡。
2. 需求端：建立首购 30 天复购激活机制，巩固刚需品类基本盘。
3. 用户运营端：RFM 分层定向触达，优先挽回高价值“重挽”用户（20,737 人，ARPU≈300）。
4. 物流端：在东北部 PA/TO/MA 择优设立区域仓储，降低大件重货跨州配送成本。

本文为处于扩张期的初创电商平台提供了从数据诊断到行动决策的完整分析路径。

文章目录

一、数据预处理

1.1 数据来源

数据集来自 Kaggle 上的 Brazilian Ecommerce Dataset，包含 9 张 CSV 表，涵盖了订单、商品、卖家、客户、支付、评价、地理信息等维度，各表间可依据主键进行连接。

1.2 数据清理

我们使用 kagglehub 库自动下载最新版本，并通过 Python（pandas）将各 CSV 文件读取为 DataFrame，统一命名为易于理解的表名（如 orders、order_items、customers 等），存储于字典 dfs 中，便于后续逐表清洗。

清洗完成后的各表被写入 MySQL 数据库，后续的分析工作（包括 RFM 计算、销量趋势分析、地域分布统计等）均在 Tableau 中通过 SQL 查询构建星形结构来实现。

各表清洗逻辑如下：

表名	清洗动作
orders	按 order_id 去重；状态合法性检查；日期转换；时间逻辑校验（批准 ≤ 承运 ≤ 签收），异常行删除；新增 is_delivered、delay_days、purchase_month 等衍生字段。
order_items	按 (order_id, order_item_id) 去重；删除价格或运费为负的记录；使用 IQR 标记价格/运费异常（price_outlier、freight_outlier）。
order_payments	按 (order_id, payment_sequential) 去重；删除 payment_value 为负的记录。
order_reviews	按 (review_id, order_id) 去重；评分 1~5 合法性检查；评论时间早于回答时间则删除；新增 review_tier（Bad/Mid/Good）。
customers	按 customer_id 去重；州代码有效性检查；邮编格式化为 5 位字符串。
sellers	同 customers。
products	类别缺失填"未分类"；重量/尺寸字段的 0/负值转 NaN 并删除缺失行（缺失率极低）；按 product_id 去重；尺寸/重量 IQR 异常标记；计算体积 product_volume_cm3，关联类别翻译。
geolocation	按邮编分组聚合（经纬度取中位数，城市/州取首条）；经纬度合理性检查。
product_category	按类别名去重。

：

1.3 数据清洗代码实现

# ============================================================
# Olist 电商数据清洗与处理完整代码
# ============================================================
import kagglehub
import numpy as np
from scipy import stats
import matplotlib.pyplot as plt
import seaborn as sns
from pathlib import Path
import pandas as pd
from sqlalchemy import create_engine
import pymysql
import warnings
warnings.filterwarnings('ignore')
---------- 1. 下载数据 ----------
file_path = kagglehub.dataset_download("olistbr/brazilian-ecommerce")
folder_path = Path(file_path)
---------- 2. 表配置 ----------
table_config = {
"olist_orders_dataset": {"name": "orders", "primary_key": ["order_id"]},
"olist_order_items_dataset": {"name": "order_items", "primary_key": ["order_id", "order_item_id"]},
"olist_order_payments_dataset": {"name": "order_payments", "primary_key": ["order_id", "payment_type", "payment_sequential"]},
"olist_order_reviews_dataset": {"name": "order_reviews", "primary_key": ["review_id", "order_id"]},
"olist_customers_dataset": {"name": "customers", "primary_key": ["customer_id"]},
"olist_sellers_dataset": {"name": "sellers", "primary_key": ["seller_id"]},
"olist_products_dataset": {"name": "products", "primary_key": ["product_id"]},
"product_category_name_translation": {"name": "product_category", "primary_key": ["product_category_name"]},
"olist_geolocation_dataset": {"name": "geolocation", "primary_key": None}
}
---------- 3. 加载数据 ----------
dfs = {}
for file in folder_path.glob("*.csv"):
raw_name = file.stem
config = table_config.get(raw_name)
df = pd.read_csv(file)
table_name = config["name"] if config else raw_name
dfs[table_name] = df
---------- 4. 清洗函数 ----------
def detect_outliers_iqr(series, k=1.5):
q1, q3 = series.quantile(0.25), series.quantile(0.75)
iqr = q3 - q1
return (series < q1 - kiqr) | (series > q3 + kiqr)
def safe_datetime(col):
return pd.to_datetime(col, errors='coerce')
def clean_orders(df):
df = df.copy()
df.drop_duplicates(subset='order_id', keep='first', inplace=True)
valid_status = ['delivered','invoiced','shipped','processing','unavailable','canceled','created','approved']
df['status_abnormal'] = ~df['order_status'].isin(valid_status)
date_cols = ['order_purchase_timestamp','order_approved_at','order_delivered_carrier_date',
'order_delivered_customer_date','order_estimated_delivery_date']
for col in date_cols:
df[col] = safe_datetime(df[col])
df['time_logic_issue'] = False
m1 = (df['order_approved_at'].notna() & df['order_delivered_carrier_date'].notna()
& (df['order_approved_at'] > df['order_delivered_carrier_date']))
m2 = (df['order_delivered_carrier_date'].notna() & df['order_delivered_customer_date'].notna()
& (df['order_delivered_carrier_date'] > df['order_delivered_customer_date']))
m3 = (df['order_purchase_timestamp'].notna() & df['order_approved_at'].notna()
& (df['order_purchase_timestamp'] > df['order_approved_at']))
m4 = (df['order_purchase_timestamp'].notna() & df['order_delivered_carrier_date'].notna()
& (df['order_purchase_timestamp'] > df['order_delivered_carrier_date']))
df.loc[m1|m2|m3|m4, 'time_logic_issue'] = True
df = df[~df['time_logic_issue']]
df['is_delivered'] = (df['order_status'] == 'delivered') & df['order_delivered_customer_date'].notna()
df['delay_days'] = np.nan
mask_del = df['is_delivered'] & df['order_estimated_delivery_date'].notna()
df.loc[mask_del, 'delay_days'] = (df.loc[mask_del, 'order_delivered_customer_date'] -
df.loc[mask_del, 'order_estimated_delivery_date']).dt.days
df['purchase_month'] = df['order_purchase_timestamp'].dt.to_period('M')
df['purchase_year'] = df['order_purchase_timestamp'].dt.year
df['order_status'] = df['order_status'].astype('category')
return df
def clean_order_items(df):
df = df.copy()
df.drop_duplicates(subset=['order_id','order_item_id'], keep='first', inplace=True)
df['price_negative'] = df['price'] < 0
df = df[~df['price_negative']]
df['freight_negative'] = df['freight_value'] < 0
df = df[~df['freight_negative']]
df['price_outlier'] = detect_outliers_iqr(df['price'])
df['freight_outlier'] = detect_outliers_iqr(df['freight_value'])
df['shipping_limit_date'] = safe_datetime(df['shipping_limit_date'])
return df
def clean_payments(df):
df = df.copy()
df.drop_duplicates(subset=['order_id','payment_sequential'], keep='first', inplace=True)
df['payment_value_negative'] = df['payment_value'] < 0
df = df[~df['payment_value_negative']]
df['payment_type'] = df['payment_type'].astype('category')
return df
def clean_reviews(df):
df = df.copy()
df.drop_duplicates(subset=['review_id','order_id'], keep='first', inplace=True)
df['review_score_outlier'] = ~df['review_score'].between(1,5)
df['review_creation_date'] = safe_datetime(df['review_creation_date'])
df['review_answer_timestamp'] = safe_datetime(df['review_answer_timestamp'])
df['time_logic_issue'] = False
m_time = (df['review_creation_date'].notna() & df['review_answer_timestamp'].notna()
& (df['review_creation_date'] > df['review_answer_timestamp']))
df.loc[m_time, 'time_logic_issue'] = True
df = df[~df['time_logic_issue']]
def map_tier(score):
if pd.isna(score): return np.nan
if score <= 2: return 'Bad'
elif score == 3: return 'Mid'
else: return 'Good'
df['review_tier'] = df['review_score'].map(map_tier)
df['review_score'] = df['review_score'].astype('Int8')
return df
def clean_customers(df):
df = df.copy()
df.drop_duplicates(subset='customer_id', keep='first', inplace=True)
valid_states = ['AC','AL','AP','AM','BA','CE','DF','ES','GO','MA','MT','MS','MG','PA','PB','PR','PE','PI','RJ','RN','RS','RO','RR','SC','SP','SE','TO']
df['state_abnormal'] = ~df['customer_state'].isin(valid_states)
df['customer_zip_prefix'] = df['customer_zip_code_prefix'].astype(str).str.zfill(5).str[:5]
df['customer_state'] = df['customer_state'].astype('category')
return df
def clean_sellers(df):
df = df.copy()
df.drop_duplicates(subset='seller_id', keep='first', inplace=True)
valid_states = ['AC','AL','AP','AM','BA','CE','DF','ES','GO','MA','MT','MS','MG','PA','PB','PR','PE','PI','RJ','RN','RS','RO','RR','SC','SP','SE','TO']
df['state_abnormal'] = ~df['seller_state'].isin(valid_states)
df['seller_zip_prefix'] = df['seller_zip_code_prefix'].astype(str).str.zfill(5).str[:5]
df['seller_state'] = df['seller_state'].astype('category')
return df
def clean_products(df, df_category):
df = df.copy()
df['product_category_name'] = df['product_category_name'].fillna('未分类')
num_cols = ['product_weight_g','product_length_cm','product_height_cm','product_width_cm']
for col in num_cols:
df[col] = df[col].replace(0, np.nan).replace(-np.inf, np.nan)
df.dropna(subset=num_cols, inplace=True)
df.drop_duplicates(subset='product_id', keep='first', inplace=True)
for col in num_cols:
df[f'{col}_outlier'] = detect_outliers_iqr(df[col])
df = df.merge(df_category[['product_category_name','product_category_name_english']],
on='product_category_name', how='left')
df['product_volume_cm3'] = df['product_length_cm'] * df['product_height_cm'] * df['product_width_cm']
return df
def clean_geolocation(df):
df = df.copy()
df = df.groupby('geolocation_zip_code_prefix', as_index=False).agg({
'geolocation_lat': 'median',
'geolocation_lng': 'median',
'geolocation_city': 'first',
'geolocation_state': 'first'
})
df['lat_outlier'] = ~df['geolocation_lat'].between(-90,90)
df['lng_outlier'] = ~df['geolocation_lng'].between(-180,180)
df['geolocation_state'] = df['geolocation_state'].astype('category')
return df
def clean_product_category(df):
df = df.copy()
df.drop_duplicates(subset='product_category_name', keep='first', inplace=True)
return df
---------- 5. 执行清洗 ----------
dfs['orders'] = clean_orders(dfs['orders'])
dfs['order_items'] = clean_order_items(dfs['order_items'])
dfs['order_payments'] = clean_payments(dfs['order_payments'])
dfs['order_reviews'] = clean_reviews(dfs['order_reviews'])
dfs['customers'] = clean_customers(dfs['customers'])
dfs['sellers'] = clean_sellers(dfs['sellers'])
dfs['product_category'] = clean_product_category(dfs['product_category'])
dfs['products'] = clean_products(dfs['products'], dfs['product_category'])
dfs['geolocation'] = clean_geolocation(dfs['geolocation'])
print("清洗完成，各表尺寸已更新。")
---------- 6. 导入 MySQL ----------
engine = create_engine('mysql+pymysql://root@xxx.x.x.x:xxx/sql_brazilian_ecommerce?charset=utf8mb4')
for table_name, df in dfs.items():
df.to_sql(table_name, con=engine, if_exists='replace', index=False,             chunksize=1000)
print(f"✅ 表 {table_name} 导入成功，共 {len(df)} 行。")

二、指标构建

本项目的核心目标是基于 MPF 理论完成 “市场与产品的再匹配” ，实现供需方的动态调整，完善用户使用体验，促进消费，因此我们构建L1-L4四层指标：

L1 北极星指标：平台健康度总览

• 定位：回答“平台作为双边市场，整体是否健康？”，为后续下钻提供总览基准。

L2 供需基础盘：拆解供给侧与需求侧

• 定位：MPF 强调“先验证市场（需求），再匹配产品（供给）”。L2 将平台拆解为供需两侧，分别量化“市场在哪里”和“产品供给如何”。另外对用户进行分层，细分用户消费行为，为后续运行提供针对性指标。

L3 履约与体验质量：验证匹配效果

• 定位：匹配不只是“完成交易”，更是“用户满意”。L3 通过物流效率和评分数据，量化匹配的“质量”。

L4 增长与再匹配诊断：输出行动方案

• 定位：MPF 的最终目的是指导行动——明确“市场需要什么，产品该如何匹配”。L4 是整个指标体系的价值输出层。

三、指标分析与可视化实现（代码附在最后）

3.1 平台北极星指标

3.2 供给端———商家与产品

供给侧问题诊断：

（一）商家结构：头部集中，长尾孱弱

1、数据表现：

1. 商家总数 3,042 家，订单数中位数仅 7 单，最大 1,796 单，头尾差距达 256 倍。GMV 中位数仅 984.81，而头部商家最高达 24.7 万。前 10% 商家贡献了全平台 66.72% 的 GMV。

2、问题诊断：

1. 平台商家结构呈典型“长尾+巨头”两极分化——头部商家占据绝对主导，而半数以上商家订单量不足 10 单。

这种结构意味着：

1. 新商家冷启动困难：流量和订单高度集中于少数头部卖家，新入驻商家难以获得初始曝光和成交机会

2. 平台议价能力弱：过度依赖头部商家，平台在佣金政策、活动参与等谈判中处于被动地位

3. 供给弹性不足：一旦头部商家流失或产能波动，平台 GMV 将面临系统性风险

3、优化方向

1. 建立新商家成长扶持机制（流量倾斜、佣金减免、运营培训）

2. 排查搜索排名和推荐算法是否存在“马太效应”固化问题

3. 设计中小商家专属营销活动，降低参与门槛

（二）品类结构：表面繁荣下的结构性隐忧

1、数据表现

1. 平台 SPU 71 类，SKU 32,399 个，品类覆盖看似丰富。但核心品类高度集中于 bed_bath_table、furniture_decor、housewares、computer_accessories、telephony 等品类，这些品类的订单量均超过 3,000 单，占据平台订单的绝大部分。

2、问题诊断

1. 品类集中本身并非问题，但需要进一步追问两个层面的结构性隐忧：

2. 重货品类（如家具、家电）的物流成本：大件商品跨州配送成本高、易损率高、时效难保障。当这些品类需求集中于 SP 州之外的区域时，平台面临“有需求、难供给”的困境

3. 新兴品类的缺失：现有品类集中反映了平台初期的供给惯性，但未能充分响应当地消费者的多元化需求（通过 L4 的 LQ 系数验证）

3、优化方向

1. 针对大件重货品类，评估“区域仓储+本地配送”模式的可行性

2. 结合各州 LQ 系数，识别具有高需求潜力但供给不足的品类，作为品类拓展的方向

（三）区域分布：SP 州的“虹吸效应”与供给荒漠化

1、数据表现

1. 商家主要集中于 SP、PR、SC 三州，其中 SP 州 GMV 占比 64.54%，订单占比 71.32%，形成“一超”格局。本地供给率低于 30% 的品类-州组合占绝大多数，RO、AM、PA 等州跨州订单比例超过 80%。订单不足 10 单的品类-州组合高达 729 组。

2、问题诊断

• SP 州作为平台起点，凭借先发优势在物流、供应链和流量侧形成了“虹吸效应”——商家、订单、GMV 持续向 SP 州集中，而其他州则陷入“需求存在→本地无供给→依赖跨州→物流体验差→用户流失”的负向循环。

• 729 组“订单不足 10 单”的组合印证了这种负向循环的严重后果——部分州的市场需求尚未被有效激活，已陷入“无供给→无需求→更无供给”的僵局。

3、优化方向

• 短期：针对跨州订单比例高、本地供给率低的州-品类组合，定向引入或扶持本地商家，以点带面激活市场

• 中期：在跨州物流压力较大的区域中心（如北部、东北部）设置区域仓储节点，降低跨州配送时长和成本

• 长期：通过商家入驻激励政策，逐步推动商家布局向供给荒漠州扩散，形成多中心供给格局

3.3 需求端—用户与区域

需求侧问题诊断与优化路径：

需求侧呈现“三低一高”特征：低复购、低客单、低留存，高拉新依赖。核心矛盾在于用户进来后无法形成消费习惯。

（一）用户价值总览：规模尚可，粘性堪忧

1、数据表现

指标	数值	核心结论
GMV	1,552 万	平台具备一定交易规模
活跃买家	9.37 万	用户基数中等
ARPU	166	人均贡献低，消费力偏弱
复购率	3.02%	核心短板，96%+ 用户一次性消费

2、问题诊断

1. GMV 依赖拉新驱动，存量用户价值挖掘严重不足。ARPU 仅 166，叠加复购率极低，导致用户生命周期价值（LTV）基本等于首单价值，用户运营经济模型脆弱——拉新成本无法通过后续复购摊薄，平台长期增长缺乏内生动力。

3、优化方向

• 短期：建立首购后 30 天复购激活机制（第 7/15/30 天节点推送品类限定券），将复购率从 3% 向 5% 提升

• 中期：针对 ARPU > 300 的重发/重挽用户，通过关联推荐、凑单满减拉升客单价

• 长期：根据后续用户 LTV 模型，针对性地将用户运营重心从“拉新数量”转向“单用户价值深度”

（二）区域市场：消费者覆盖全境但渗透不均

1、数据表现

• SP 州买家占比 41.82%，GMV 占比 37.34%，形成单一核心市场

• RO、AM、PA 等偏远州买家渗透率不足 1%，跨州订单比例超 80%

• 距 SP 越远的区域，买家数量及 GMV 贡献呈阶梯式衰减

2、问题诊断

• 平台已触达全部 27 州，但 SP 州虹吸效应导致“需求外溢、本地无供给、依赖跨州、体验差、流失”的负向循环。偏远区域并非没有消费意愿，而是缺乏本地供给能力支撑。

3、优化方向

• 短期：针对渗透率最低的 AC、RO、AM、PA 等州，推出区域专属首购补贴（免运费/新客券），降低首购门槛

• 中期：在跨州订单集中区域设置仓储节点，同步优化供给与物流

• 长期：建立“区域渗透率”监控指标，按季度评估各州买家增长情况

（三）消费偏好：刚需品类主导，低频低价特征明显

1、数据表现

• 首购品类 TOP3：家居（27.5%）、数码（15.7%）、美妆（11.7%），合计占首购超 50%

• 复购异常点：家居占比从 27.5% 升至 34.6%（+7.1pct），但家居及数码评分均偏低

• 价格带：订单集中于 150 元以下，平台商品定位为中小件、中低价商品

2、问题诊断

• 家居品类复购率上升但评分偏低，说明需求刚性压过了体验负面反馈，用户“不得不买”。但这种情况不可持续——长期低评分会推动用户向其他平台迁移。品类复购评分普遍高于首购，核心原因是幸存者偏差：复购用户本就是“对首次体验基本满意”的筛选后人群，大量差评用户已流失，未进入复购评分池。

3、优化方向

• 短期：优先优化家居、数码两大核心品类的首购体验（二者首购评分偏低且占首购 GMV 比重高），缩小首购与复购评分差距

• 中期：首购后 30 天内主动推送品类限定券（如家居用户推厨具配件、数码用户推耗材），创造复购理由

• 长期：逐步引入中高价位商品，测试用户消费力天花板，提升 ARPU

（四）生命周期：脉冲式拉新，无延续性留存

1、数据表现

• 各 Cohort 队列 LTV 长期在 150 左右浮动，未见任何队列在次月有明显留存

• 

• 2017 年 11 月队列规模明显增长（黑五大促），但该增长仅为脉冲效应，后续留存未改善

• 

2、问题诊断

• 促销拉新有效，但平台缺乏“新客次月复购”的承接机制。流量进来却“留不住”，导致每次大促后用户迅速滑入重挽/流失池，营销费用无法转化为长期用户资产。

3、优化方向

• 短期：大促结束后 7 天内，对新增用户推送“感谢券”，锁定二次购买机会

• 中期：建立“首购-次购”自动化营销流，覆盖新客前 30 天关键触达节点

• 长期：将大促预算的 20% 转移至用户留存运营（如会员体系、订阅制），优化拉新与留存的结构性失衡

3.4 用户侧—用户RFM分层及消费偏好

用户侧深挖：RFM 分层下的用户行为逻辑与策略指向

（一）品类交叠：刚需品类是底座，图书是“沉默的信号”

1、数据表现

1. 截取 GMV 贡献较高的品类发现，bed_bath_table、housewares、health_beauty、sports_leisure、computers_accessories 等家庭刚需品类在“重发、重挽、一发、流失”等多类用户群体中高度交叠。

2. 图书品类则呈现异常集中——主要分布于“重挽”与“流失”两类用户中，在其他分层中占比极低。

2、问题诊断

1. 家庭刚需品类（厨卫、个护、配件等）购买周期短、需求刚性，是平台的流量基本盘，无论用户分层如何都会产生购买。其高度交叠意味着：这类商品是防止用户进一步流失的底线防线，一旦供给或价格出现问题，影响面将波及所有用户层级。

2. 图书品类的异常分布则揭示了非刚需类的另一层问题：供给端方面，早期 Olist 平台入驻的图书商家偏少、品类丰富度不足，早期因图书而来的用户在首次购买后无法找到持续更新的心仪书目，体验断层后直接沉寂；需求端方面，图书属于计划性、目的性消费，用户买完特定书籍后短期内无复购动因，缺乏家居用品“用完即补”的天然周期性复购场景。

3、优化方向

1. 刚需品类：重点保障供给稳定性和价格竞争力，建立库存预警机制，这是防止用户进一步流失的底线防线

2. 图书品类：对重挽/流失池中偏好图书的用户，定向推送新书榜单、同作者关联推荐，用内容驱动回流，而非发放通用优惠券

（二）RFM 人群结构：谁在贡献价值，谁在消耗成本

1、数据表现

1. 将用户按消费金额（M）、最近消费时间（R）、消费频次（F）划分为八类：

用户分层	人数	ARPU	核心特征
重发用户	14,481	303.3	平台底盘，兼具规模与高价值
重挽用户	20,737	~300	高价值流失风险区，规模最大
一发用户	21,774	~70	首购后未转化，占总量 23%
流失用户	33,848	72.6	已沉寂，占总量 36%
重价+重保	2,250	—	仅占 2.4%，高消费力用户极少

1. 一发 + 流失 = 55,622 人，占总量 59%，ARPU 仅 70+，是拉低全平台均值（166）的主因。重价+重保合计仅 2,250 人（占 2.4%），高消费力用户规模极小。

2、问题诊断

1. 平台用户结构呈“沙漏型”——底部（一发+流失）和中部（重发+重挽）庞大，顶部（重价+重保）极其狭小。

2. 

3. 整体复购率仅 3.02% 的根源在于：

4. 大量用户首次购买后未能进入“重发”通道，直接沉寂。

5. 重挽用户（20,737 人）是最大单一人群，ARPU≈300，属于“一脚在门外”的优质资产，挽回投资回报率约为流失用户的 4 倍——同样的召回成本，重挽用户产出的 LTV 远高于沉睡用户。

6. 高消费力用户规模极小，说明平台高客单价品类的渗透率严重不足。

3、优化方向

优先级	目标人群	策略	具体动作
P0	重挽用户（20,737人）	紧急挽回	高价值定向券+专属触达，当前最紧急的干预对象
P1	一发用户（21,774人）	首购后 30 天激活	建立“首购后 30 天激活机制”，引导完成二次购买，向重发池转化
P2	重发用户（14,481人）	巩固提频	关联推荐、凑单满减，向重价/重保群体迁移
P3	流失用户（33,848人）	收缩触达	仅高价值流失用户唤醒，降低 ROI 低的无效触达

（三）时间脉冲验证：2017 年 11 月活动有效性

1、数据表现

• RFM 分层中“重挽”与“流失”用户占比较大。结合 Cohort 分析，2017 年 11 月（黑五大促）用户规模出现脉冲式增长，但后续各月留存率极低，该增量用户随时间推移逐一滑入“重挽”甚至“流失”池，未形成持续性留存。

• 

2、问题诊断

• 年终大促（黑五/感恩节）确实有效触达并拉动了大量新用户，证明平台的拉新能力没有问题。但这些用户后续未被有效留存——平台缺乏“新客次月复购”的承接机制，导致营销费用无法转化为长期用户资产。活动拉新能力强，但流量进来后“留不住”，是当前用户运营的结构性短板。

3、优化方向

• 短期：大促结束后 7 天内，对新增用户推送“感谢券”和关联品类推荐，锁定二次购买机会

• 中期：建立“首购-次购”自动化营销流，覆盖新客前 30 天关键触达节点（第 7/15/30 天）

• 长期：将大促预算的 20% 转移至用户留存运营（会员体系、订阅制），从“脉冲式拉新”转向“持续性留存”

3.5 物流端—物流服务评价

物流分析：区域履约瓶颈与再匹配路径

（一）区域准时率：东北部为“延迟重灾区”，根源在供给错配

1、数据表现

• 从各州订单准时率来看，MA 州准时率仅 81.7%，为全国最低，其周边 PI、CE、AL 等东北部州也仅在 86% 左右浮动。结合供给侧分析的商家分布可知，这些低准时率州恰好对应商家分布稀疏区域——当前平台商家多集中于 SP、RJ、MG 等东南部，东北部订单大量依赖跨州长距离配送，履约时效自然恶化。

2、问题诊断

• 东北部准时率低并非单纯物流能力问题，根源在于商家分布与需求的严重错配——需求存在但本地供给不足，订单被迫跨州履行，配送链条延长导致时效恶化。跨州订单占比高的区域，准时率普遍偏低，两者呈强相关。

3、优化方向

• 短期：针对RJ、 MA、CE、PE 等跨州订单占比高且准时率低的州，优先引入或扶持本地商家，从源头缩短配送半径

• 中期：在东北部区域中心（如 MA 或 CE）设立区域仓储节点，覆盖周边 PI、AL、SE 等州，降低跨州配送比例

• 长期：建立“区域供给-物流协同”规划，新引入商家优先布局有仓储节点的区域，同步解决供给与物流问题

（二）大件重货：体积和重量越大，延迟越严重

1、数据表现

• 大件重货延迟率分析显示，超大（>200L）、大件（50-200L）以及超重件（>15kg）的延迟率显著高于中小件。大件物流对仓储网点密度和干线运输能力要求更高，而平台在上述低准时率区域缺乏前置仓，导致大件订单跨州调拨周期拉长、破损风险上升，进一步拖累评分。

2、问题诊断

1. 大件商品的物流特性（体积大、重量高、易损）使其对配送时效和仓储条件要求远高于中小件。平台当前物流网络以中小件为设计基准，缺乏针对大件的专项能力（如专用运输通道、区域大件仓），导致大件订单在跨州配送中延迟率和破损率双高。

3、优化方向

• 短期：对超大超重商品设置专门的物流通道或指定承运商，与大件物流商签订时效保障协议

• 中期：在主要需求区域（SP、RJ、MG 等）布局大件前置仓，缩短大件订单配送半径

• 长期：建立大件商品 SKU 分级管理制度，对大件商品设定差异化的时效承诺和定价策略

（三）商家发货准时率与评分的关联

1、数据表现

1. 观察商家 Avg Delivery Days 与 On Time Rate，两者呈明显负相关——发货准备时间越长的商家，准时率越低。而延迟订单对应的客户评分普遍偏低：延迟 1-3 天评分下降 0.98 分，延迟超过 7 天评分下降 2.51 分。

2、问题诊断

• 商家发货效率直接影响物流准时率，进而传导至用户评分。发货准备时间长的商家往往存在库存管理、订单处理等运营短板，这部分商家的订单不仅准时率低，且因延迟导致的评分损失直接损害平台口碑。物流时效是连接“交易”与“满意度”的关键桥梁，其影响已被量化验证。

3、优化方向

• 短期：对平均发货时长 > 5 天的商家进行定向运营干预（库存管理培训、发货流程优化）

• 中期：建立“商家物流健康度”评分（基于发货时长、准点率、评分等指标），对低分商家进行预警和辅导

• 长期：在平台规则中引入物流时效激励/惩罚机制（如准时率高的商家获得流量倾斜，持续低准时率商家限制参与促销活动）

3.6 供需再匹配

再匹配矩阵：从供需错配到行动清单

（一）矩阵构建逻辑

• 结合前文对供给端（3.2）、需求端（3.3）、用户侧（3.4）和物流端（3.5）的分析，为量化供需错配程度并识别优先级，我们构建了供需再匹配矩阵，综合考虑三个核心维度：

1、本地供给率（Local Supply Rate）

• 计算公式：本地订单数 / 总订单数 × 100%

• 值越低，说明该品类在该州越依赖外部供给，缺口越大

2、LQ 指数（Location Quotient）

• 计算公式：（州内该品类 GMV 占比）/（全国该品类 GMV 占比）

• LQ > 1：该品类在该州的需求强度高于全国平均水平，属于“偏好品类”

• LQ < 1：需求强度低于全国平均水平

3、品类 GMV（Category GMV）

• 衡量该品类在全国的市场规模

• 值越大，说明该品类整体市场空间越大，填补缺口的潜在收益越高

4、预期收益指数（Expected Gain）：

Expected Gain = LQ × 品类 GMV × (1 - 本地供给率/100)

• 该指数综合考虑了“需求强度”（LQ）、“市场规模”（GMV）和“供给缺口”（1 - 本地供给率），值越高代表再匹配的优先级越高、潜在收益越大。

（二）高优先级再匹配组合分析

• 根据再匹配矩阵计算结果，★ 高优先级再匹配 组合共 50 余组，覆盖 15 个州、30 余个品类。按驱动因素可分为三类：

1、缺口驱动型（本地供给率 < 5%，LQ 中等偏高）

• 本地供给几乎为零，市场需求完全依赖跨州满足，是“从0到1”的招商机会：

州	品类	本地供给率	LQ	expected_gain
RJ	fixed_telephony	0%	4.10	472,505
PE	watches_gifts	0%	1.52	60,645
BA	watches_gifts	0%	1.01	56,379
PE	health_beauty	0.45%	1.85	103,772
RS	telephony	0.81%	1.81	86,641
RJ	drinks	0%	3.56	122,335
BA	office_furniture	0%	2.18	120,921
GO	garden_tools	0%	2.09	88,581
MA	computers_accessories	0%	2.14	70,314
PI	auto	3.57%	3.56	69,319

2、高 LQ 驱动型（LQ > 1.5，需求偏好显著）

• 消费者对该品类有明显偏好，但本地供给未能满足：

州	品类	LQ	本地供给率	expected_gain
PR	office_furniture	1.53	7.27%	72,638
PE	watches_gifts	1.52	0%	60,645
PE	health_beauty	1.85	0.45%	103,772
RJ	furniture_living_room	1.88	20.83%	51,810
CE	health_beauty	1.70	3.29%	76,738
SC	baby	1.69	13.98%	50,370
RS	telephony	1.81	0.81%	86,641

3、高 GMV 驱动型（category_gmv > 15万，市场规模大）

• 品类整体市场空间大，填补缺口的绝对收益高：

州	品类	category_gmv	expected_gain	本地供给率	LQ
RJ	fixed_telephony	115,245	472,505	0%	4.10
RJ	bed_bath_table	246,118	259,133	1.60%	1.07
MG	bed_bath_table	214,683	227,432	4.56%	1.11
RJ	watches_gifts	214,303	214,673	10.56%	1.12
MG	health_beauty	193,089	183,563	8.59%	1.04
RJ	office_furniture	124,986	174,995	1.40%	1.42
RJ	computers_accessories	193,260	172,350	2.00%	0.91
RJ	furniture_decor	178,218	161,068	2.82%	0.93
GO	auto	57,724	158,307	1.35%	2.78
RJ	sports_leisure	173,068	145,614	9.53%	0.93

4、核心规律：

• RJ 州是最大缺口市场：高优先级组合数量最多（15+组），覆盖 fixed_telephony、bed_bath_table、watches_gifts、office_furniture、computers_accessories、furniture_decor、sports_leisure、housewares、health_beauty、auto、garden_tools、drinks 等品类，预期收益总计超过 200 万

• MG 州是第二大缺口市场：高优先级组合覆盖 bed_bath_table、health_beauty、toys、housewares、auto、agro_industry_and_commerce 等，预期收益总计超过 80 万

• GO 州单点机会突出：auto（158,307）和 garden_tools（88,581）两个品类缺口大、LQ 高，是中部区域的重点招商方向

• 固定电话（fixed_telephony）是单点最大机会：RJ 州该品类预期收益达 47 万，LQ 高达 4.10，但本地供给率为 0%，属于“需求极强、供给为零”的典型蓝海

（三）区域再匹配优先级总览

优先级	州	高优先级品类数	代表品类（预期收益）	推荐动作
★ 第一梯队	RJ	15+	fixed_telephony(47万)、bed_bath_table(25.9万)、watches_gifts(21.5万)	定向招商 + 建仓
★ 第一梯队	MG	10+	health_beauty(18.4万)、bed_bath_table(22.7万)、auto(9.1万)	定向招商 + 建仓
★ 第二梯队	RS	7	telephony(8.7万)、housewares(8.8万)、furniture_decor(10.4万)	定向招商
★ 第二梯队	GO	3	auto(15.8万)、garden_tools(8.9万)、housewares(6.1万)	定向招商 + 建仓
★ 第二梯队	BA	4	office_furniture(12.1万)、health_beauty(6.9万)、sports_leisure(6.4万)	定向招商
★ 第三梯队	PR	3	office_furniture(7.3万)、watches_gifts(9.9万)、furniture_decor(13.2万)	定向招商
★ 第三梯队	SC	3	sports_leisure(6.3万)、baby(5.0万)、auto(5.3万)	定向招商

（四）价格-评分匹配：铺货策略的量化依据

• 不同区域消费者对价格的敏感度存在显著差异。

1、基于各州不同价格带的评分分布，我们识别出三类市场：

区域类型	代表州	核心特征	铺货策略
成熟市场	SP、MG、PR、RS	各价格带评分均衡（高价-低价差距<0.1分），消费者对价格不敏感	全价格带铺货，中高价商品占比可达50%
发展中市场	RJ、SC、DF、GO	高价商品评分略低于中低价（差距0.05-0.15分），有一定价格敏感度	以中价为主（60%），高价商品控制在20%以内
欠发达市场	BA、CE、MA、PA	高价商品评分明显偏低（差距>0.1分），消费力有限	低价主导（70%），高价商品控制在10%以内

2、具体铺货建议：

• MG、PR、RS：结合高优先级品类（MG 的 health_beauty、bed_bath_table、RS 的 telephony、housewares），直接引入中高价位 SKU，无需刻意控制价格上限

• RJ：高价商品评分仅 3.82，低于 SP/MG 约 0.3 分。在 RJ 引入 high-priority 品类时，应以中价商品切入，待用户消费习惯养成后再逐步拉升客单价

• BA、CE、MA：引入 office_furniture、health_beauty 等品类时，应从低价位起步，避免直接铺入高价位商品导致评分塌陷

（五）与物流优化的协同

• 供需再匹配必须与物流优化协同推进，否则“引入商家”无法转化为“改善体验”：

1、仓储前置

• 高优先级区域（RJ、MG、GO、BA）跨州订单占比普遍超过 60%，本地供给提升后仍需配套仓储节点。建议在 RJ 和 GO 设立区域仓储中心，覆盖周边 3-5 个州，将配送时长缩短 30% 以上。

2、大件专线

• 对 auto、agro_industry_and_commerce、garden_tools 等大件重货品类（高优先级组合中约 15 组涉及），预期收益合计约 80 万。建议建立独立物流通道，与标准品配送分离，降低破损率和延迟率。

3、招商-物流协同机制

• 新引入商家的招商决策应同步评估物流配套能力，避免“商家来了，物流跟不上”的脱节。建议：

• 招商前：评估该区域仓储覆盖情况、配送时效是否达标

• 招商中：将物流配套承诺写入商家入驻协议

• 招商后：前 3 个月监控该品类的配送时效和评分变化，及时调整

四、项目优化建议：供需匹配与用户运营方案

一、供给端优化：从“虹吸”到“多极”

（一）优化目标

• 破解 SP 州虹吸效应，推动商家布局从“单极集中”向“多极协同”转变，提升供给弹性与区域覆盖。

（二）分层行动方案

层级	目标州	核心品类	行动类型	预期收益	时间窗口
Tier 1（立即执行）	RJ	fixed_telephony、drinks、bed_bath_table、watches_gifts	定向招商	~85 万	Q4 2026
Tier 1（立即执行）	MG	bed_bath_table、health_beauty、auto	定向招商 + 建仓	~50 万	Q4 2026
Tier 2（季度内）	GO、BA	auto、office_furniture、housewares	定向招商	~35 万	Q1 2027
Tier 2（季度内）	RS、PR、SC	telephony、sports_leisure、furniture_decor	定向招商	~30 万	Q1 2027
Tier 3（半年内）	MA、CE、PE	computers_accessories、health_beauty	定向招商	~18 万	Q2 2027

1、执行要点：

• 头部（Top 10%）商家：巩固合作，作为标杆案例

• 腰部（10%-50%）商家：重点扶持成长，提供流量扶持和运营培训

• 长尾（后 50%）商家：降低入驻门槛，提供轻量化开店工具

（三）成功标准

• 半年内，RJ 和 MG 两州合计新增活跃商家不少于 100 家，两州 GMV 合计增长不低于 25%。

二、需求端激活：从“脉冲”到“留存”

（一） 优化目标

• 破解“低复购、低客单、低留存”困境，建立从“首购”到“复购”的自动化运营链路，将用户生命周期价值（LTV）从当前的约 166 提升至 200 以上。

（二）首购 30 天激活机制（P0）

时间节点	触达动作	内容策略	预期效果
第 1 天	订单确认 + 物流追踪	透明化配送进度，降低首购焦虑	减少首购差评
第 3 天	使用指南 / 保养贴士	关联品类内容（如家居用户推搭配方案）	提升品牌好感
第 7 天	首购品类限定优惠券	家居→厨具配件，数码→耗材/配件	触发二次购买
第 15 天	同类商品推荐	基于首购品类的算法推荐	培养浏览习惯
第 30 天	复购专属折扣	“满 99-15”券，制造回流理由	完成复购转化

（三）RFM 分层差异化运营

用户分层	人数	运营策略	核心动作	预算优先级
重要价值客户	~1,000	巩固忠诚	VIP 专属折扣 + 优先客服 + 新品内测	P1
重要发展客户	~1,500	提频	品类关联推荐 + 满减凑单	P1
重要保持客户	~2,300	唤醒	高价值定向券 + 个性化推荐	P0
重要挽留客户	~3,700	挽回	专属挽回券 + 短信触达	P0
一般价值客户	~6,500	维持	常规促销触达	P2
一般发展客户	~20,600	提频	凑单满减 + 多件优惠	P2
流失客户	~33,800	收缩	仅高价值流失用户唤醒	P3

关键判断：

• 重挽用户（20,737人）是 ROI 最高的干预对象——ARPU 约 300，是流失用户的 4 倍。同样的召回成本，重挽用户产出的 LTV 远高于沉睡用户。建议每月固定预算优先覆盖重挽和重要保持用户。

（四）成功标准

• 半年内，复购率从 3.02% 提升至 5%，重挽用户中 15% 回流并完成二次购买。

三、物流与协同：从“跨州”到“本地”

（一）优化目标

• 破解区域履约瓶颈，通过“仓储前置 + 大件专线 + 招商-物流协同”三位一体，将东北部准点率从 81.7% 提升至 90% 以上。

（二）区域仓储节点布局

优先级	建仓位置	覆盖范围	预期效果
P0（立即执行）	MG（米纳斯吉拉斯）	RJ、MG、ES 南部	缩短高优先级品类配送距离
P1（季度内）	GO（戈亚尼亚）	GO、DF、MT、MS 中西部	覆盖中部区域缺口
P2（半年内）	MA/CE（东北部）	MA、CE、PI、PE、AL 东北部	解决东北部准点率最低问题

（三）大件物流专项优化

• 针对高优先级矩阵中涉及的大件重货品类（auto、garden_tools、agro_industry_and_commerce 等），预期收益合计约 80 万，需建立独立物流通道：

品类类别	代表品类	预期收益	物流策略
汽车用品	auto（GO、MG、PI、SC）	~37 万	独立承运商 + 大件专用仓
工具园艺	garden_tools（RJ、MG、RS）	~28 万	大件专线配送
农产品	agro_industry（MG）	~13 万	产地直达 + 整批配送

（四）商家引入-物流配套协同机制

• 招商前评估：评估该区域仓储覆盖情况、配送时效是否达标

• 招商中承诺：将物流配套承诺写入商家入驻协议（时效 SLA、仓储支持）

• 招商后监控：前 3 个月监控该品类的配送时效和评分变化，及时调整

（五）成功标准

半年内，东北部三州（MA、CE、PE）平均准点率从 83% 提升至 90%，大件重货延迟率降低 30%。

四、方案汇总：优先级矩阵与资源分配

（一）优先级总览

优先级	供给端	需求端/用户端	物流端	预期投入	预期产出
P0（立即执行）	RJ/MG 定向招商	首购 30 天激活机制	RJ 建仓	中高	复购率 3%→5% RJ/MG GMV 增长 25%
P1（季度内）	GO/BA/RS 招商	RFM 分层运营（重挽+重要保持）	GO 建仓 + 大件专线	中	重挽用户回流 15%
P2（半年内）	MA/CE/PE 招商	低价值用户提频	东北部 MA/CE 建仓	低	东北部准点率 81%→90%

（二）关键 KPI 追踪

维度	核心 KPI	当前值	2027 H1 目标	2027 H2 目标
商家结构	Top 10% 商家 GMV 占比	66.72%	≤64%	≤62%
商家区域分布	SP 州外商家占比	~35%	≥40%	≥45%
用户留存	复购率	3.02%	4.0%	5.0%
用户价值	ARPU	166	180	200
物流体验	东北部平均准点率	~85%	88%	90%
需求匹配	高优先级组合落地数	0	30%	60%

五、总结：从诊断到行动

核心问题	根本原因	解决方案	衡量标准	执行主体
SP 州虹吸效应	先发优势锁定供给	Tier 1-3 定向招商 + 区域建仓	SP 州外 GMV 占比	商家运营 + 供应链
低复购率	首购后无承接机制	首购 30 天自动化营销流	复购率 5%	用户运营
重挽用户流失	高价值用户流失风险	RFM 分层定向挽留	重挽用户回流率 15%	用户运营
东北部准点率低	商家稀疏 + 跨州配送	仓储节点 + 招商协同	东北部准点率 90%	物流运营 + 商家运营

特征指标构建代码

-- ============================================================
-- 1. 数据类型转换（保持原有结构）
-- ============================================================
ALTER TABLE orders
MODIFY order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
MODIFY customer_id VARCHAR(32) NOT NULL COMMENT '客户ID',
MODIFY order_status VARCHAR(20) NOT NULL DEFAULT 'processing' COMMENT '订单状态',
MODIFY order_purchase_timestamp DATETIME NOT NULL COMMENT '下单时间',
MODIFY order_approved_at DATETIME DEFAULT NULL COMMENT '审批时间',
MODIFY order_delivered_carrier_date DATETIME DEFAULT NULL COMMENT '交运时间',
MODIFY order_delivered_customer_date DATETIME DEFAULT NULL COMMENT '签收时间',
MODIFY order_estimated_delivery_date DATETIME NOT NULL COMMENT '预计交付时间',
MODIFY delay_days INT DEFAULT NULL COMMENT '延迟天数',
MODIFY is_delivered TINYINT NOT NULL DEFAULT 0 COMMENT '是否签收成功',
MODIFY purchase_month VARCHAR(7) DEFAULT NULL COMMENT '购买月份',
MODIFY purchase_year INT DEFAULT NULL COMMENT '购买年份',
MODIFY time_logic_issue TINYINT NOT NULL DEFAULT 0 COMMENT '时间逻辑异常',
MODIFY status_abnormal TINYINT NOT NULL DEFAULT 0 COMMENT '状态异常';
ALTER TABLE orders DROP PRIMARY KEY;
ALTER TABLE orders ADD PRIMARY KEY (order_id);
ALTER TABLE order_items
MODIFY order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
MODIFY order_item_id TINYINT NOT NULL COMMENT '订单内商品序号',
MODIFY product_id VARCHAR(32) NOT NULL COMMENT '商品ID',
MODIFY seller_id VARCHAR(32) NOT NULL COMMENT '卖家ID',
MODIFY shipping_limit_date DATETIME DEFAULT NULL COMMENT '发货截止时间',
MODIFY price DECIMAL(10,2) NOT NULL COMMENT '商品单价',
MODIFY freight_value DECIMAL(10,2) NOT NULL COMMENT '运费',
MODIFY price_negative TINYINT NOT NULL DEFAULT 0 COMMENT '价格负值标记',
MODIFY freight_negative TINYINT NOT NULL DEFAULT 0 COMMENT '运费负值标记',
MODIFY price_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '价格异常标记',
MODIFY freight_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '运费异常标记';
ALTER TABLE order_items DROP PRIMARY KEY;
ALTER TABLE order_items ADD PRIMARY KEY (order_id, order_item_id);
ALTER TABLE order_payments
MODIFY order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
MODIFY payment_sequential TINYINT NOT NULL COMMENT '支付顺序',
MODIFY payment_type VARCHAR(20) DEFAULT NULL COMMENT '支付方式',
MODIFY payment_installments TINYINT DEFAULT NULL COMMENT '分期数',
MODIFY payment_value DECIMAL(10,2) NOT NULL COMMENT '支付金额';
ALTER TABLE order_payments DROP PRIMARY KEY;
ALTER TABLE order_payments ADD PRIMARY KEY (order_id, payment_sequential);
ALTER TABLE order_reviews
MODIFY review_id VARCHAR(32) NOT NULL COMMENT '评论ID',
MODIFY order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
MODIFY review_score TINYINT DEFAULT NULL COMMENT '评分',
MODIFY review_comment_title TEXT DEFAULT NULL COMMENT '评论标题',
MODIFY review_comment_message TEXT DEFAULT NULL COMMENT '评论内容',
MODIFY review_creation_date DATETIME DEFAULT NULL COMMENT '评论创建时间',
MODIFY review_answer_timestamp DATETIME DEFAULT NULL COMMENT '回复时间',
MODIFY review_score_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '评分异常标记',
MODIFY time_logic_issue TINYINT NOT NULL DEFAULT 0 COMMENT '时间逻辑异常',
MODIFY review_tier VARCHAR(20) DEFAULT NULL COMMENT '评分等级';
ALTER TABLE order_reviews DROP PRIMARY KEY;
ALTER TABLE order_reviews ADD PRIMARY KEY (review_id, order_id);
ALTER TABLE customers
MODIFY customer_id VARCHAR(32) NOT NULL COMMENT '客户ID',
MODIFY customer_unique_id VARCHAR(32) NOT NULL COMMENT '客户唯一标识',
MODIFY customer_zip_code_prefix VARCHAR(5) NOT NULL COMMENT '邮编前缀',
MODIFY customer_city VARCHAR(50) NOT NULL COMMENT '城市',
MODIFY customer_state CHAR(2) NOT NULL COMMENT '州代码',
MODIFY customer_zip_prefix VARCHAR(5) NOT NULL COMMENT '标准化邮编前缀',
MODIFY state_abnormal TINYINT NOT NULL DEFAULT 0 COMMENT '州异常标记';
ALTER TABLE customers DROP PRIMARY KEY;
ALTER TABLE customers ADD PRIMARY KEY (customer_id);
ALTER TABLE sellers
MODIFY seller_id VARCHAR(32) NOT NULL COMMENT '卖家ID',
MODIFY seller_zip_code_prefix VARCHAR(5) NOT NULL COMMENT '邮编前缀',
MODIFY seller_city VARCHAR(50) NOT NULL COMMENT '城市',
MODIFY seller_state CHAR(2) NOT NULL COMMENT '州代码',
MODIFY seller_zip_prefix VARCHAR(5) NOT NULL COMMENT '标准化邮编前缀',
MODIFY state_abnormal TINYINT NOT NULL DEFAULT 0 COMMENT '州异常标记';
ALTER TABLE sellers DROP PRIMARY KEY;
ALTER TABLE sellers ADD PRIMARY KEY (seller_id);
SET SQL_SAFE_UPDATES = 0;
-- 1. 添加经纬度列（若列已存在，会报错，可跳过此步）
ALTER TABLE sellers ADD COLUMN lat DECIMAL(9,6) DEFAULT NULL;
ALTER TABLE sellers ADD COLUMN lng DECIMAL(9,6) DEFAULT NULL;
UPDATE sellers s
JOIN geolocation g ON s.seller_zip_code_prefix = g.geolocation_zip_code_prefix
SET s.lat = g.geolocation_lat,
s.lng = g.geolocation_lng
WHERE s.lat IS NULL;
UPDATE sellers s
JOIN geolocation g ON s.seller_zip_code_prefix = g.geolocation_zip_code_prefix
JOIN state_center sc ON s.seller_state = sc.geolocation_state
SET s.lat = sc.avg_lat,
s.lng = sc.avg_lng
WHERE s.seller_state != g.geolocation_state
AND s.lat IS NOT NULL;
UPDATE sellers s
JOIN state_center sc ON s.seller_state = sc.geolocation_state
SET s.lat = sc.avg_lat,
s.lng = sc.avg_lng
WHERE s.lat IS NULL;
SELECT seller_id, seller_state, seller_zip_code_prefix, lat, lng
FROM sellers
LIMIT 10;
SET SQL_SAFE_UPDATES = 1;
ALTER TABLE products
MODIFY product_id VARCHAR(32) NOT NULL COMMENT '商品ID',
MODIFY product_category_name VARCHAR(50) DEFAULT NULL COMMENT '品类名（葡萄牙语）',
MODIFY product_name_lenght INT DEFAULT NULL COMMENT '名称长度',
MODIFY product_description_lenght INT DEFAULT NULL COMMENT '描述长度',
MODIFY product_photos_qty TINYINT DEFAULT NULL COMMENT '图片数量',
MODIFY product_weight_g INT NOT NULL COMMENT '重量（克）',
MODIFY product_length_cm INT NOT NULL COMMENT '长度（厘米）',
MODIFY product_height_cm INT NOT NULL COMMENT '高度（厘米）',
MODIFY product_width_cm INT NOT NULL COMMENT '宽度（厘米）',
MODIFY product_category_name_english VARCHAR(50) DEFAULT NULL COMMENT '品类名（英文）',
MODIFY product_volume_cm3 INT NOT NULL COMMENT '体积（立方厘米）',
MODIFY product_weight_g_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '重量异常',
MODIFY product_length_cm_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '长度异常',
MODIFY product_height_cm_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '高度异常',
MODIFY product_width_cm_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '宽度异常';
ALTER TABLE products DROP PRIMARY KEY;
ALTER TABLE products ADD PRIMARY KEY (product_id);
ALTER TABLE geolocation
MODIFY geolocation_zip_code_prefix VARCHAR(5) NOT NULL COMMENT '邮编前缀',
MODIFY geolocation_lat DECIMAL(9,6) NOT NULL COMMENT '纬度',
MODIFY geolocation_lng DECIMAL(9,6) NOT NULL COMMENT '经度',
MODIFY geolocation_city VARCHAR(50) NOT NULL COMMENT '城市',
MODIFY geolocation_state CHAR(2) NOT NULL COMMENT '州代码',
MODIFY lat_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '纬度异常',
MODIFY lng_outlier TINYINT NOT NULL DEFAULT 0 COMMENT '经度异常';
ALTER TABLE geolocation DROP PRIMARY KEY;
ALTER TABLE geolocation ADD PRIMARY KEY (geolocation_zip_code_prefix);
ALTER TABLE product_category
MODIFY product_category_name VARCHAR(50) NOT NULL COMMENT '品类名（葡萄牙语）',
MODIFY product_category_name_english VARCHAR(50) NOT NULL COMMENT '品类名（英语）';
ALTER TABLE product_category DROP PRIMARY KEY;
ALTER TABLE product_category ADD PRIMARY KEY (product_category_name);
-- ============================================================
-- 2. 添加索引（提升查询性能）
-- ============================================================
ALTER TABLE orders ADD INDEX idx_orders_customer_id (customer_id);
ALTER TABLE orders ADD INDEX idx_orders_timestamp (order_purchase_timestamp);
ALTER TABLE orders ADD INDEX idx_orders_status_time (order_status, time_logic_issue);
ALTER TABLE order_payments ADD INDEX idx_payments_order (order_id);
ALTER TABLE order_items ADD INDEX idx_items_order (order_id);
ALTER TABLE order_items ADD INDEX idx_items_product (product_id);
ALTER TABLE order_items ADD INDEX idx_items_seller (seller_id);
ALTER TABLE customers ADD INDEX idx_customers_unique (customer_unique_id);
ALTER TABLE customers ADD INDEX idx_customers_state (customer_state);
ALTER TABLE sellers ADD INDEX idx_sellers_state (seller_state);
ALTER TABLE products ADD INDEX idx_products_category (product_category_name_english);
ALTER TABLE geolocation ADD INDEX idx_geo_zip (geolocation_zip_code_prefix);
-- ============================================================
-- 3. 创建分析视图（含时间限制 < 2018-09-01）
-- ============================================================
-- (1) L1 平台经营总览
DROP VIEW IF EXISTS L1-平台经营总览;
CREATE VIEW L1-平台经营总览 AS
WITH
-- 总订单统计（全量，含取消）
all_orders AS (
SELECT
COUNT(order_id) AS total_orders_all,
COUNT(CASE WHEN order_status = 'delivered' THEN 1 END) AS delivered_orders,
COUNT(CASE WHEN order_status IN ('canceled', 'unavailable') THEN 1 END) AS canceled_orders
FROM orders
WHERE time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
),
-- 聚合支付表，确保每订单一行
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
-- 有效订单（已支付且状态非取消/未交付）
valid_orders AS (
SELECT
o.order_id,
o.customer_id,
p.total_payment,
c.customer_unique_id
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
-- 有效订单聚合统计（含 SKU/SPU）
valid_stats AS (
SELECT
COUNT(order_id) AS total_valid_orders,
ROUND(SUM(total_payment), 2) AS gmv,
ROUND(SUM(total_payment) / COUNT(order_id), 2) AS aov,
COUNT(DISTINCT customer_unique_id) AS buyers,
-- 新增 SKU 和 SPU 指标
(SELECT COUNT(DISTINCT oi.product_id)
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id) AS active_skus,
(SELECT COUNT(DISTINCT p.product_category_name_english)
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name_english IS NOT NULL) AS active_spus
FROM valid_orders
),
-- 活跃卖家数（通过有效订单关联）
active_sellers AS (
SELECT COUNT(DISTINCT oi.seller_id) AS active_sellers
FROM order_items oi
JOIN valid_orders v ON oi.order_id = v.order_id
)
-- 最终输出
SELECT
a.total_orders_all,
v.total_valid_orders,
v.gmv,
v.aov,
v.buyers,
s.active_sellers,
v.active_skus,
v.active_spus,
CONCAT(ROUND(a.delivered_orders / a.total_orders_all * 100, 2), '%') AS order_completion_rate,
CONCAT(ROUND(a.canceled_orders / a.total_orders_all * 100, 2), '%') AS order_cancel_rate
FROM all_orders a
CROSS JOIN valid_stats v
CROSS JOIN active_sellers s;
-- (2) 月度指标汇总
DROP VIEW IF EXISTS 月度指标汇总;
CREATE VIEW 月度指标汇总 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
)
SELECT
DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') AS month,
COUNT(DISTINCT o.order_id) AS orders,
ROUND(SUM(p.total_payment), 2) AS gmv,
COUNT(DISTINCT c.customer_unique_id) AS buyers,
COUNT(DISTINCT oi.seller_id) AS sellers
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN payment_agg p ON o.order_id = p.order_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY month
ORDER BY month;
-- ============================================================
-- L2 供需基础盘
-- ============================================================
-- (1) 用户价值指标
DROP VIEW IF EXISTS L2-用户价值指标;
CREATE VIEW L2-用户价值指标 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
user_orders AS (
SELECT
c.customer_unique_id,
COUNT(o.order_id) AS order_count,
SUM(p.total_payment) AS total_spent,
MAX(o.order_purchase_timestamp) AS last_purchase_date,
MIN(o.order_purchase_timestamp) AS first_purchase_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY c.customer_unique_id
)
SELECT
COUNT(customer_unique_id) AS total_buyers,
ROUND(AVG(order_count), 2) AS avg_orders_per_user,
ROUND(AVG(total_spent), 2) AS avg_spent_per_user,
ROUND(SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS repeat_purchase_rate,
ROUND(
(SELECT AVG(total_spent)
FROM (
SELECT total_spent,
ROW_NUMBER() OVER (ORDER BY total_spent) AS rn,
COUNT() OVER () AS cnt
FROM user_orders
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
), 2
) AS median_spent_per_user,
MAX(order_count) AS max_orders_per_user,
MAX(total_spent) AS max_spent_per_user
FROM user_orders;
-- (2) RFM分层
DROP VIEW IF EXISTS L2-RFM分层_八类;
CREATE VIEW L2-RFM分层_八类 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
user_rfm_raw AS (
SELECT
c.customer_unique_id,
DATEDIFF('2019-01-01', MAX(o.order_purchase_timestamp)) AS recency_days,
COUNT(o.order_id) AS frequency,
SUM(p.total_payment) AS monetary
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY c.customer_unique_id
),
rfm_score AS (
SELECT
customer_unique_id,
recency_days,
frequency,
monetary,
NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
CASE WHEN frequency > 1 THEN 1 ELSE 0 END AS f_score,
NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
FROM user_rfm_raw
),
rfm_binary AS (
SELECT
customer_unique_id,
recency_days, frequency, monetary,
r_score, f_score, m_score,
CASE WHEN r_score >= 4 THEN '高' ELSE '低' END AS r_level,
CASE WHEN f_score >= 1 THEN '高' ELSE '低' END AS f_level,
CASE WHEN m_score >= 4 THEN '高' ELSE '低' END AS m_level
FROM rfm_score
)
SELECT
customer_unique_id,
recency_days, frequency, monetary,
r_score, f_score, m_score,
r_level, f_level, m_level,
CASE
WHEN r_level = '高' AND f_level = '高' AND m_level = '高' THEN '重价'
WHEN r_level = '高' AND f_level = '低' AND m_level = '高' THEN '重发'
WHEN r_level = '低' AND f_level = '高' AND m_level = '高' THEN '重保'
WHEN r_level = '低' AND f_level = '低' AND m_level = '高' THEN '重挽'
WHEN r_level = '高' AND f_level = '高' AND m_level = '低' THEN '一价'
WHEN r_level = '高' AND f_level = '低' AND m_level = '低' THEN '一发'
WHEN r_level = '低' AND f_level = '高' AND m_level = '低' THEN '一保'
WHEN r_level = '低' AND f_level = '低' AND m_level = '低' THEN '流失'
ELSE '未分类'
END AS user_segment
FROM rfm_binary;
-- (3) GMV订单区域分布
DROP VIEW IF EXISTS L2-GMV订单区域分布;
CREATE VIEW L2-GMV订单区域分布 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
state_gmv AS (
SELECT
c.customer_state,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT c.customer_unique_id) AS buyer_count,
ROUND(SUM(p.total_payment), 2) AS gmv
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY c.customer_state
),
total_stats AS (
SELECT
SUM(order_count) AS total_orders,
SUM(gmv) AS total_gmv,
SUM(buyer_count) AS total_buyers
FROM state_gmv
)
SELECT
s.customer_state,
CONCAT('BR-', UPPER(s.customer_state)) AS state_iso,
s.order_count,
ROUND(s.order_count / t.total_orders * 100, 2) AS order_share_pct,
s.gmv,
ROUND(s.gmv / t.total_gmv * 100, 2) AS gmv_share_pct,
ROUND(s.gmv / s.order_count, 2) AS state_aov,
s.buyer_count,
ROUND(s.buyer_count / t.total_buyers * 100, 2) AS buyer_share_pct
FROM state_gmv s
CROSS JOIN total_stats t
ORDER BY s.gmv DESC;
-- (4) 州产业领先分析
DROP VIEW IF EXISTS L2-州产业领先分析;
CREATE VIEW L2-州产业领先分析 AS
WITH
base AS (
SELECT
c.customer_state AS buyer_state,
s.seller_state,
p.product_category_name_english AS category,
oi.order_item_id
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
national AS (
SELECT
category,
COUNT() AS total_national_orders,
COUNT(CASE WHEN buyer_state != seller_state THEN 1 END) AS total_national_cross
FROM base
GROUP BY category
),
state_category AS (
SELECT
buyer_state AS state,
category,
COUNT() AS total_orders,
COUNT(CASE WHEN buyer_state != seller_state THEN 1 END) AS inflow,
COUNT(CASE WHEN seller_state != buyer_state THEN 1 END) AS outflow
FROM base
GROUP BY buyer_state, category
),
state_category_with_pct AS (
SELECT
sc.state,
sc.category,
sc.total_orders,
n.total_national_orders,
ROUND(sc.total_orders / n.total_national_orders, 4) AS total_order_pct,
sc.inflow,
n.total_national_cross,
ROUND(sc.inflow / NULLIF(n.total_national_cross, 0), 4) AS inflow_pct,
sc.outflow,
ROUND(sc.outflow / NULLIF(n.total_national_cross, 0), 4) AS outflow_pct
FROM state_category sc
JOIN national n ON sc.category = n.category
),
ranked AS (
SELECT
state,
category,
total_orders,
total_order_pct,
inflow,
inflow_pct,
outflow,
outflow_pct,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY total_orders DESC) AS rank_total,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY inflow DESC) AS rank_inflow,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY outflow DESC) AS rank_outflow
FROM state_category_with_pct
)
SELECT
state,
category,
total_orders,
total_order_pct,
inflow,
inflow_pct,
outflow,
outflow_pct,
rank_total,
rank_inflow,
rank_outflow,
CASE
WHEN rank_total <= 3 AND rank_inflow <= 3 AND rank_outflow <= 3 THEN '全维度领先'
WHEN rank_total <= 3 THEN '订单量领先'
WHEN rank_inflow <= 3 THEN '进州量领先'
WHEN rank_outflow <= 3 THEN '出州量领先'
ELSE '其他'
END AS lead_type
FROM ranked
WHERE rank_total <= 3 OR rank_inflow <= 3 OR rank_outflow <= 3
ORDER BY state, lead_type, rank_total, rank_inflow, rank_outflow;
-- (5) 区域买卖家渗透率
DROP VIEW IF EXISTS L2-区域买家渗透率;
CREATE VIEW L2-区域买家渗透率 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
valid_orders AS (
SELECT o.order_id, o.customer_id
FROM orders o
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
buyer_stats AS (
SELECT
c.customer_state AS state,
COUNT(DISTINCT c.customer_unique_id) AS buyer_count
FROM customers c
JOIN valid_orders v ON c.customer_id = v.customer_id
GROUP BY c.customer_state
),
total_buyers AS (
SELECT SUM(buyer_count) AS total FROM buyer_stats
),
seller_stats AS (
SELECT
s.seller_state AS state,
COUNT(DISTINCT s.seller_id) AS seller_count
FROM sellers s
JOIN order_items oi ON s.seller_id = oi.seller_id
JOIN valid_orders v ON oi.order_id = v.order_id
GROUP BY s.seller_state
),
total_sellers AS (
SELECT SUM(seller_count) AS total FROM seller_stats
),
all_states AS (
SELECT DISTINCT customer_state AS state FROM customers
UNION
SELECT DISTINCT seller_state FROM sellers
)
SELECT
a.state,
CONCAT('BR-', UPPER(a.state)) AS state_iso,
COALESCE(b.buyer_count, 0) AS buyer_count,
ROUND(COALESCE(b.buyer_count, 0) / (SELECT total FROM total_buyers) * 100, 2) AS buyer_penetration_pct,
COALESCE(s.seller_count, 0) AS seller_count,
ROUND(COALESCE(s.seller_count, 0) / (SELECT total FROM total_sellers) * 100, 2) AS seller_penetration_pct
FROM all_states a
LEFT JOIN buyer_stats b ON a.state = b.state
LEFT JOIN seller_stats s ON a.state = s.state
ORDER BY a.state;
-- (6) 跨州订单分析
DROP VIEW IF EXISTS L2-跨州订单分析;
CREATE VIEW L2-跨州订单分析 AS
WITH
order_seller_state AS (
SELECT
c.customer_state AS buyer_state,
s.seller_state,
COUNT(DISTINCT o.order_id) AS order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY c.customer_state, s.seller_state
),
state_summary AS (
SELECT
buyer_state,
SUM(order_count) AS total_orders,
SUM(CASE WHEN buyer_state = seller_state THEN order_count ELSE 0 END) AS local_orders,
SUM(CASE WHEN buyer_state != seller_state THEN order_count ELSE 0 END) AS cross_orders,
COUNT(DISTINCT seller_state) AS unique_seller_states
FROM order_seller_state
GROUP BY buyer_state
)
SELECT
buyer_state,
total_orders,
local_orders,
cross_orders,
ROUND(cross_orders / total_orders * 100, 2) AS cross_state_rate,
ROUND(local_orders / total_orders * 100, 2) AS local_state_rate,
ROUND((cross_orders - local_orders) / total_orders * 100, 2) AS net_flow_ratio,
unique_seller_states,
ROUND(unique_seller_states / 27 * 100, 2) AS seller_state_coverage_pct
FROM state_summary
ORDER BY cross_state_rate DESC;
-- (7) 支付方式区域分布
DROP VIEW IF EXISTS L2-支付方式区域分布;
CREATE VIEW L2-支付方式区域分布 AS
WITH
payment_order AS (
SELECT
order_id,
SUM(payment_value) AS total_payment,
(SELECT payment_type FROM order_payments op2
WHERE op2.order_id = op.order_id
ORDER BY payment_sequential LIMIT 1) AS main_payment_type,
AVG(payment_installments) AS avg_installments
FROM order_payments op
GROUP BY order_id
)
SELECT
c.customer_state,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(p.total_payment), 2) AS gmv,
ROUND(SUM(CASE WHEN p.main_payment_type = 'credit_card' THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS credit_card_pct,
ROUND(SUM(CASE WHEN p.main_payment_type = 'boleto' THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS boleto_pct,
ROUND(SUM(CASE WHEN p.main_payment_type = 'voucher' THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS voucher_pct,
ROUND(SUM(CASE WHEN p.main_payment_type = 'debit_card' THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS debit_card_pct,
ROUND(AVG(p.avg_installments), 2) AS avg_installments,
ROUND(SUM(CASE WHEN p.avg_installments >= 6 THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS high_installment_pct,
ROUND(SUM(p.total_payment) / COUNT(), 2) AS aov
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN payment_order p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY c.customer_state
ORDER BY order_count DESC;
-- (8) 各州 GMV 排名前三的品类
DROP VIEW IF EXISTS 州品类前三GMV分析;
CREATE VIEW 州品类前三GMV分析 AS
WITH
base AS (
SELECT
c.customer_state AS buyer_state,
s.seller_state,
p.product_category_name_english AS category,
oi.price + oi.freight_value AS item_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
state_total AS (
SELECT buyer_state AS state, SUM(item_amount) AS total_gmv
FROM base
GROUP BY buyer_state
),
state_category_buy AS (
SELECT
buyer_state AS state,
category,
SUM(item_amount) AS gmv_buy
FROM base
GROUP BY buyer_state, category
),
state_category_seller AS (
SELECT
buyer_state,
seller_state,
category,
SUM(item_amount) AS gmv_flow
FROM base
GROUP BY buyer_state, seller_state, category
),
state_category_local_outflow AS (
SELECT
buyer_state AS state,
category,
SUM(CASE WHEN buyer_state = seller_state THEN gmv_flow ELSE 0 END) AS local_gmv,
SUM(CASE WHEN buyer_state != seller_state THEN gmv_flow ELSE 0 END) AS outflow_gmv
FROM state_category_seller
GROUP BY buyer_state, category
),
combined AS (
SELECT
scb.state,
scb.category,
scb.gmv_buy,
st.total_gmv AS state_total_gmv,
sco.local_gmv,
sco.outflow_gmv
FROM state_category_buy scb
LEFT JOIN state_total st ON scb.state = st.state
LEFT JOIN state_category_local_outflow sco ON scb.state = sco.state AND scb.category = sco.category
),
ranked AS (
SELECT
state,
category,
gmv_buy,
state_total_gmv,
ROUND(gmv_buy / NULLIF(state_total_gmv, 0), 4) AS gmv_state_share,
outflow_gmv,
ROUND(outflow_gmv / NULLIF(gmv_buy, 0), 4) AS outflow_share,
local_gmv,
ROUND(local_gmv / NULLIF(gmv_buy, 0), 4) AS local_share,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY gmv_buy DESC) AS gmv_rank
FROM combined
)
SELECT
state,
category,
gmv_buy,
state_total_gmv,
gmv_state_share,
outflow_gmv,
outflow_share,
local_gmv,
local_share,
gmv_rank
FROM ranked
WHERE gmv_rank <= 3
ORDER BY state, gmv_rank;
-- (9) 州间GMV流向
DROP VIEW IF EXISTS 州间GMV流向;
CREATE VIEW 州间GMV流向 AS
WITH
base AS (
SELECT
c.customer_state AS buyer_state,
s.seller_state,
oi.price + oi.freight_value AS item_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
)
SELECT
buyer_state,
seller_state,
SUM(item_amount) AS gmv
FROM base
GROUP BY buyer_state, seller_state
ORDER BY buyer_state, seller_state;
DROP VIEW IF EXISTS L2-商家订单gmv及orders;
CREATE VIEW L2-商家订单gmv及orders AS
WITH
-- 1. 有效订单
valid_orders AS (
SELECT order_id
FROM orders
WHERE order_status NOT IN ('canceled', 'unavailable')
AND time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
),
-- 2. 按卖家汇总
seller_stats AS (
SELECT
oi.seller_id,
COUNT(DISTINCT oi.order_id) AS order_count,
SUM(oi.price + oi.freight_value) AS total_gmv
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id
GROUP BY oi.seller_id
),
-- 3. 总卖家数和全国总 GMV
total_stats AS (
SELECT
COUNT() AS total_sellers,
SUM(total_gmv) AS national_gmv
FROM seller_stats
),
-- 4. 为每个卖家按 GMV 降序排名
ranked_sellers AS (
SELECT
seller_id,
total_gmv,
ROW_NUMBER() OVER (ORDER BY total_gmv DESC) AS rn,
COUNT() OVER () AS total_cnt
FROM seller_stats
),
-- 5. 前 10% 卖家的 GMV 总和（向上取整）
top10_gmv AS (
SELECT SUM(total_gmv) AS top10_gmv
FROM ranked_sellers
WHERE rn <= CEIL(total_cnt * 0.1)   -- 取前 10% 卖家，不足 1 个时取 1 个
),
-- 6. 计算整体统计量（含中位数）
overall_stats AS (
SELECT
COUNT() AS seller_count,
MIN(order_count) AS min_orders,
MAX(order_count) AS max_orders,
ROUND(
(SELECT AVG(order_count)
FROM (
SELECT order_count,
ROW_NUMBER() OVER (ORDER BY order_count) AS rn,
COUNT() OVER () AS cnt
FROM seller_stats
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
), 0
) AS median_orders,
MIN(total_gmv) AS min_gmv,
MAX(total_gmv) AS max_gmv,
ROUND(
(SELECT AVG(total_gmv)
FROM (
SELECT total_gmv,
ROW_NUMBER() OVER (ORDER BY total_gmv) AS rn,
COUNT(*) OVER () AS cnt
FROM seller_stats
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
), 2
) AS median_gmv
FROM seller_stats
)
-- 7. 最终输出
SELECT
os.seller_count,
os.min_orders,
os.max_orders,
os.median_orders,
os.min_gmv,
os.max_gmv,
os.median_gmv,
ROUND(t10.top10_gmv / ts.national_gmv * 100, 2) AS top10_gmv_pct
FROM overall_stats os
CROSS JOIN top10_gmv t10
CROSS JOIN total_stats ts;
-- ============================================================
-- L3 履约与体验质量
-- ============================================================
-- ============================================================
-- (1) 订单履约明细（基础视图）
DROP VIEW IF EXISTS L3-订单履约明细;
CREATE VIEW L3-订单履约明细 AS
SELECT
o.order_id,
o.customer_id,
o.order_purchase_timestamp,
o.order_approved_at,
o.order_delivered_carrier_date,
o.order_delivered_customer_date,
o.order_estimated_delivery_date,
o.delay_days,
DATEDIFF(o.order_delivered_customer_date, o.order_purchase_timestamp) AS total_delivery_days,
DATEDIFF(o.order_delivered_carrier_date, o.order_purchase_timestamp) AS shipping_days,
DATEDIFF(o.order_delivered_customer_date, o.order_delivered_carrier_date) AS transport_days,
CASE WHEN o.delay_days > 0 THEN 1 ELSE 0 END AS is_delayed,
CASE
WHEN o.delay_days <= 0 THEN '准时/提前'
WHEN o.delay_days BETWEEN 1 AND 3 THEN '延迟1-3天'
WHEN o.delay_days BETWEEN 4 AND 7 THEN '延迟4-7天'
ELSE '延迟超过7天'
END AS delay_category,
oi.seller_id,
s.seller_state,
c.customer_state
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
AND o.order_purchase_timestamp IS NOT NULL
AND o.order_delivered_carrier_date IS NOT NULL
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01';
-- (2) 全平台履约质量汇总
DROP VIEW IF EXISTS L3-全平台履约质量汇总;
CREATE VIEW L3-全平台履约质量汇总 AS
SELECT
COUNT(DISTINCT order_id) AS delivered_orders,
ROUND(AVG(total_delivery_days), 1) AS avg_total_delivery_days,
ROUND(AVG(shipping_days), 1) AS avg_shipping_days,
ROUND(AVG(transport_days), 1) AS avg_transport_days,
ROUND(SUM(CASE WHEN is_delayed = 0 THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS on_time_rate,
ROUND(SUM(is_delayed) / COUNT() * 100, 2) AS delayed_rate,
ROUND(AVG(CASE WHEN is_delayed = 1 THEN delay_days ELSE NULL END), 1) AS avg_delay_days_delayed_only,
MAX(delay_days) AS max_delay_days,
ROUND(
(SELECT AVG(total_delivery_days)
FROM (
SELECT total_delivery_days,
ROW_NUMBER() OVER (ORDER BY total_delivery_days) AS rn,
COUNT(*) OVER () AS cnt
FROM L3-订单履约明细
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
), 1
) AS median_delivery_days
FROM L3-订单履约明细;
-- (3) 州级履约质量
DROP VIEW IF EXISTS L3-州级履约质量;
CREATE VIEW L3-州级履约质量 AS
SELECT
customer_state,
COUNT(DISTINCT order_id) AS delivered_orders,
ROUND(AVG(total_delivery_days), 1) AS avg_delivery_days,
ROUND(AVG(shipping_days), 1) AS avg_shipping_days,
ROUND(AVG(transport_days), 1) AS avg_transport_days,
ROUND(SUM(CASE WHEN is_delayed = 0 THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS on_time_rate,
ROUND(SUM(is_delayed) / COUNT() * 100, 2) AS delayed_rate,
ROUND(AVG(CASE WHEN is_delayed = 1 THEN delay_days ELSE NULL END), 1) AS avg_delay_days_delayed_only
FROM L3-订单履约明细
GROUP BY customer_state
ORDER BY on_time_rate ASC;
DROP VIEW IF EXISTS L3-州级履约质量_供给端;
CREATE VIEW L3-州级履约质量_供给端 AS
SELECT
seller_state AS state,
COUNT(DISTINCT order_id) AS delivered_orders,
ROUND(AVG(total_delivery_days), 1) AS avg_delivery_days,
ROUND(AVG(shipping_days), 1) AS avg_shipping_days,
ROUND(AVG(transport_days), 1) AS avg_transport_days,
ROUND(SUM(CASE WHEN is_delayed = 0 THEN 1 ELSE 0 END) / COUNT() * 100, 2) AS on_time_rate,
ROUND(SUM(is_delayed) / COUNT() * 100, 2) AS delayed_rate,
ROUND(AVG(CASE WHEN is_delayed = 1 THEN delay_days ELSE NULL END), 1) AS avg_delay_days_delayed_only
FROM L3-订单履约明细
GROUP BY seller_state
ORDER BY on_time_rate ASC;
-- (4) 延迟评分影响（按延迟类别分组）
DROP VIEW IF EXISTS L3-延迟评分影响;
CREATE VIEW L3-延迟评分影响 AS
WITH review_data AS (
SELECT
d.order_id,
d.delay_days,
d.delay_category,
r.review_score
FROM L3-订单履约明细 d
JOIN order_reviews r ON d.order_id = r.order_id
WHERE r.review_score IS NOT NULL
AND r.time_logic_issue = 0
)
SELECT
delay_category,
COUNT(*) AS order_count,
ROUND(AVG(review_score), 2) AS avg_review_score,
ROUND(MIN(review_score), 0) AS min_score,
ROUND(MAX(review_score), 0) AS max_score,
ROUND(AVG(review_score) - (SELECT AVG(review_score) FROM review_data WHERE delay_days <= 0), 2) AS score_diff_from_on_time
FROM review_data
GROUP BY delay_category
ORDER BY FIELD(delay_category, '准时/提前', '延迟1-3天', '延迟4-7天', '延迟超过7天');
-- (5) 延迟评分回归数据（用于散点图）
DROP VIEW IF EXISTS L3-延迟评分回归数据;
CREATE VIEW L3-延迟评分回归数据 AS
SELECT
d.delay_days,
r.review_score
FROM L3-订单履约明细 d
JOIN order_reviews r ON d.order_id = r.order_id
WHERE d.is_delayed = 1
AND r.review_score IS NOT NULL
AND r.time_logic_issue = 0
AND d.delay_days <= 30;
-- (6) 商家发货效率（订单量≥10的商家）
DROP VIEW IF EXISTS L3-商家发货效率;
CREATE VIEW L3-商家发货效率 AS
SELECT
seller_id,
COUNT(DISTINCT order_id) AS delivered_orders,
ROUND(AVG(shipping_days), 1) AS avg_shipping_days,
ROUND(AVG(total_delivery_days), 1) AS avg_delivery_days,
ROUND(SUM(CASE WHEN is_delayed = 0 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS on_time_rate
FROM L3-订单履约明细
GROUP BY seller_id
HAVING delivered_orders >= 10
ORDER BY avg_shipping_days ASC;
-- (7) 产品尺寸/重量对延迟的影响（交叉分组分析）
DROP VIEW IF EXISTS L3-产品尺寸重量延迟分析;
CREATE VIEW L3-产品尺寸重量延迟分析 AS
WITH
order_product_delay AS (
SELECT
oi.order_id,
oi.product_id,
p.product_weight_g,
p.product_length_cm,
p.product_height_cm,
p.product_width_cm,
p.product_volume_cm3,
d.delay_days,
d.is_delayed,
d.customer_state,
d.seller_state
FROM order_items oi
JOIN L3-订单履约明细 d ON oi.order_id = d.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_weight_g > 0
AND p.product_volume_cm3 > 0
),
weight_bucket AS (
SELECT
,
CASE
WHEN product_weight_g < 1000 THEN '轻 (<1kg)'
WHEN product_weight_g BETWEEN 1000 AND 5000 THEN '中 (1-5kg)'
WHEN product_weight_g BETWEEN 5001 AND 15000 THEN '重 (5-15kg)'
ELSE '超重 (>15kg)'
END AS weight_category,
CASE
WHEN product_volume_cm3 < 10000 THEN '小 (<10L)'
WHEN product_volume_cm3 BETWEEN 10000 AND 50000 THEN '中 (10-50L)'
WHEN product_volume_cm3 BETWEEN 50001 AND 200000 THEN '大 (50-200L)'
ELSE '超大 (>200L)'
END AS volume_category
FROM order_product_delay
)
SELECT
weight_category,
volume_category,
CONCAT(weight_category, '_', volume_category) AS weight_volumn_category,
COUNT(DISTINCT order_id) AS order_count,
ROUND(AVG(delay_days), 1) AS avg_delay_days,
ROUND(SUM(is_delayed) / COUNT() * 100, 2) AS delayed_rate,
ROUND(AVG(CASE WHEN is_delayed = 1 THEN delay_days ELSE NULL END), 1) AS avg_delay_days_delayed_only,
ROUND(SUM(CASE WHEN customer_state != seller_state THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS cross_state_rate
FROM weight_bucket
GROUP BY weight_category, volume_category
ORDER BY weight_category, volume_category;
-- (8) 物流延迟回归明细（含重量、体积、跨州标记）
DROP VIEW IF EXISTS L3-物流延迟回归数据;
CREATE VIEW L3-物流延迟回归数据 AS
SELECT
d.order_id,
d.delay_days,
d.is_delayed,
d.customer_state,
d.seller_state,
CASE WHEN d.customer_state != d.seller_state THEN 1 ELSE 0 END AS is_cross_state,
p.product_weight_g,
p.product_volume_cm3,
p.product_length_cm,
p.product_height_cm,
p.product_width_cm
FROM L3-订单履约明细 d
JOIN order_items oi ON d.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE d.is_delayed = 1
AND p.product_weight_g > 0
AND p.product_volume_cm3 > 0;
-- ============================================================
-- L4 增长与再匹配
-- ============================================================
DROP VIEW IF EXISTS L4-类目LQ系数;
CREATE VIEW L4-类目LQ系数 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
state_category_gmv AS (
SELECT
c.customer_state,
p.product_category_name_english AS category,
SUM(pay.total_payment) AS gmv
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN payment_agg pay ON o.order_id = pay.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY c.customer_state, p.product_category_name_english
),
state_total AS (
SELECT customer_state, SUM(gmv) AS state_gmv
FROM state_category_gmv
GROUP BY customer_state
),
category_total AS (
SELECT category, SUM(gmv) AS national_gmv
FROM state_category_gmv
GROUP BY category
),
national_total AS (
SELECT SUM(gmv) AS total_national_gmv
FROM state_category_gmv
)
SELECT
scg.customer_state,
scg.category,
scg.gmv,
ROUND(scg.gmv / st.state_gmv, 4) AS state_category_share,
ROUND(scg.gmv / ct.national_gmv, 4) AS national_category_share,
ROUND((scg.gmv / st.state_gmv) / (ct.national_gmv / nt.total_national_gmv), 2) AS lq,
CASE
WHEN (scg.gmv / st.state_gmv) > (ct.national_gmv / nt.total_national_gmv)
AND (scg.gmv / st.state_gmv) > 0.01
THEN '蓝海机会'
ELSE '一般'
END AS opportunity_flag
FROM state_category_gmv scg
JOIN state_total st ON scg.customer_state = st.customer_state
JOIN category_total ct ON scg.category = ct.category
CROSS JOIN national_total nt
WHERE ct.national_gmv > 0
AND NOT (scg.customer_state = 'RO' AND scg.category = 'security_and_services')
ORDER BY customer_state, lq DESC;
-- (2) 商家区域覆盖缺口
DROP VIEW IF EXISTS L4-商家区域覆盖缺口_品类级;
CREATE VIEW L4-商家区域覆盖缺口_品类级 AS
WITH
order_seller_category AS (
SELECT
c.customer_state AS buyer_state,
s.seller_state,
p.product_category_name_english AS category,
COUNT(DISTINCT o.order_id) AS order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY c.customer_state, s.seller_state, p.product_category_name_english
),
category_state_summary AS (
SELECT
buyer_state,
category,
SUM(order_count) AS total_orders,
SUM(CASE WHEN buyer_state = seller_state THEN order_count ELSE 0 END) AS local_orders,
SUM(CASE WHEN buyer_state != seller_state THEN order_count ELSE 0 END) AS cross_orders
FROM order_seller_category
GROUP BY buyer_state, category
)
SELECT
buyer_state,
category,
total_orders,
local_orders,
cross_orders,
ROUND(cross_orders / total_orders * 100, 2) AS cross_state_rate,
ROUND(local_orders / total_orders * 100, 2) AS local_supply_rate,
CASE
WHEN total_orders >= 10 AND (ROUND(local_orders / total_orders * 100, 2)) < 30 THEN '供给缺口严重'
WHEN total_orders >= 10 AND (ROUND(local_orders / total_orders * 100, 2)) < 50 THEN '供给不足'
WHEN total_orders >= 10 AND (ROUND(local_orders / total_orders * 100, 2)) < 70 THEN '供给一般'
WHEN total_orders >= 10 AND (ROUND(local_orders / total_orders * 100, 2)) >= 70 THEN '供给充足'
ELSE '订单数少于10'
END AS supply_gap_status,
CASE
WHEN total_orders >= 10 THEN '高可信'
ELSE '低可信'
END AS data_confidence
FROM category_state_summary
ORDER BY cross_state_rate DESC;
-- (3) 价格评分敏感度
DROP VIEW IF EXISTS L4-价格评分敏感度;
CREATE VIEW L4-价格评分敏感度 AS
WITH
price_band_orders AS (
SELECT
o.order_id,
c.customer_state,
oi.price,
rev.review_score,
CASE
WHEN oi.price < 50 THEN '低 (<50)'
WHEN oi.price BETWEEN 50 AND 150 THEN '中 (50-150)'
ELSE '高 (>150)'
END AS price_band
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN order_reviews rev ON o.order_id = rev.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND rev.review_score IS NOT NULL
AND o.order_purchase_timestamp < '2018-09-01'
)
SELECT
customer_state,
price_band,
COUNT(*) AS order_count,
ROUND(AVG(review_score), 2) AS avg_score,
ROUND(AVG(review_score) - AVG(AVG(review_score)) OVER (PARTITION BY customer_state), 2) AS score_diff_from_state_avg
FROM price_band_orders
GROUP BY customer_state, price_band
HAVING order_count >= 10
ORDER BY customer_state, price_band;
-- (4) 再匹配机会矩阵
DROP VIEW IF EXISTS L4-再匹配机会矩阵;
CREATE VIEW L4-再匹配机会矩阵 AS
WITH
lq_data AS (
SELECT
customer_state,
category,
lq,
opportunity_flag,
gmv
FROM L4-类目LQ系数
),
supply_data AS (
SELECT
buyer_state,
category,
total_orders,
local_supply_rate,
supply_gap_status,
data_confidence
FROM L4-商家区域覆盖缺口_品类级
),
stats AS (
SELECT
MAX(lq) AS max_lq,
MAX(gmv) AS max_gmv
FROM lq_data
)
SELECT
lq.customer_state,
lq.category,
lq.lq,
lq.opportunity_flag,
lq.gmv AS category_gmv,
COALESCE(s.local_supply_rate, 999) AS local_supply_rate,
CASE
WHEN s.buyer_state IS NULL THEN '数据不足'
ELSE s.supply_gap_status
END AS supply_gap_status,
COALESCE(s.data_confidence, '无数据') AS data_confidence,
s.total_orders AS sample_orders,
-- 预期收益 = LQ × GMV × (1 - 本地供给率/100)
CASE
WHEN COALESCE(s.local_supply_rate, 999) = 999 OR s.local_supply_rate IS NULL THEN NULL
ELSE ROUND(lq.lq * lq.gmv * (1 - s.local_supply_rate / 100), 2)
END AS expected_gain,
-- 综合匹配得分（权重可调）
ROUND(
0.3 * (1 - COALESCE(s.local_supply_rate, 100) / 100) +
0.3 * (lq.lq / NULLIF(st.max_lq, 0)) +
0.4 * (lq.gmv / NULLIF(st.max_gmv, 0)),
4
) AS match_score,
-- 匹配优先级（含“订单不足为10”处理）
CASE
WHEN lq.opportunity_flag = '蓝海机会'
AND s.buyer_state IS NOT NULL
AND s.data_confidence IN ('高可信')
AND s.supply_gap_status IN ('供给缺口严重', '供给不足')
THEN '★ 高优先级再匹配'
WHEN lq.opportunity_flag = '蓝海机会'
AND s.buyer_state IS NOT NULL
AND s.data_confidence IN ('高可信')
AND s.supply_gap_status = '供给一般'
THEN '◆ 蓝海且供给一般（建议关注）'
WHEN lq.opportunity_flag = '蓝海机会'
AND (s.buyer_state IS NULL
OR (s.data_confidence IN ('高可信') AND s.supply_gap_status = '供给充足'))
THEN '✓ 蓝海且供给充足（可加强营销）'
WHEN lq.opportunity_flag = '蓝海机会'
AND s.buyer_state IS NULL
THEN '? 蓝海但数据不足（需调研）'
WHEN lq.lq < 1
AND s.buyer_state IS NOT NULL
AND s.data_confidence IN ('高可信')
AND s.supply_gap_status IN ('供给缺口严重', '供给不足')
THEN '⚠ 供给不足但非偏好（需优化供给结构）'
WHEN s.supply_gap_status = '订单不足为10' THEN '○ 订单量少（数据置信度低）'
ELSE '维持现状'
END AS match_priority,
s.supply_gap_status AS raw_supply_gap_status
FROM lq_data lq
LEFT JOIN supply_data s ON lq.customer_state = s.buyer_state AND lq.category = s.category
CROSS JOIN stats st
ORDER BY
CASE match_priority
WHEN '★ 高优先级再匹配' THEN 1
WHEN '◆ 蓝海且供给一般（建议关注）' THEN 2
WHEN '✓ 蓝海且供给充足（可加强营销）' THEN 3
WHEN '? 蓝海但数据不足（需调研）' THEN 4
WHEN '⚠ 供给不足但非偏好（需优化供给结构）' THEN 5
WHEN '○ 订单量少（数据置信度低）' THEN 6
ELSE 7
END,
match_score DESC,
lq.lq DESC;
-- (5) 头部商家集中度
DROP VIEW IF EXISTS L4-头部商家集中度;
CREATE VIEW L4-头部商家集中度 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
seller_gmv AS (
SELECT
oi.seller_id,
SUM(pay.total_payment) AS gmv
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN payment_agg pay ON o.order_id = pay.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
GROUP BY oi.seller_id
),
ranked AS (
SELECT
seller_id,
gmv,
PERCENT_RANK() OVER (ORDER BY gmv DESC) AS pct_rank
FROM seller_gmv
)
SELECT
ROUND(SUM(CASE WHEN pct_rank <= 0.1 THEN gmv ELSE 0 END) / SUM(gmv) * 100, 2) AS top10_gmv_share
FROM ranked;
-- (6) 供给缺口诊断
DROP VIEW IF EXISTS L4-供给缺口诊断;
CREATE VIEW L4-供给缺口诊断 AS
SELECT
supply_gap_status,
COUNT(*) AS category_state_pairs,
SUM(total_orders) AS total_orders,
ROUND(AVG(local_supply_rate), 2) AS avg_local_supply_rate,
MIN(local_supply_rate) AS min_local_supply_rate,
MAX(local_supply_rate) AS max_local_supply_rate
FROM L4-商家区域覆盖缺口_品类级
GROUP BY supply_gap_status
ORDER BY
CASE supply_gap_status
WHEN '供给缺口严重' THEN 1
WHEN '供给不足' THEN 2
WHEN '供给一般' THEN 3
WHEN '供给充足' THEN 4
WHEN '订单数少于10' THEN 5
ELSE 6
END;
-- ============================================================
-- L5 层：RFM × 区域/品类/时间/运营辅助
-- ============================================================
-- (1) 区域RFM分布
DROP VIEW IF EXISTS L5-区域RFM分布;
CREATE VIEW L5-区域RFM分布 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
valid_orders AS (
SELECT o.order_id, o.customer_id
FROM orders o
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
rfm_users AS (
SELECT
c.customer_unique_id,
r.user_segment
FROM customers c
JOIN L2-RFM分层_八类 r ON c.customer_unique_id = r.customer_unique_id
)
SELECT
c.customer_state,
r.user_segment,
COUNT(DISTINCT c.customer_unique_id) AS user_count,
COUNT(DISTINCT v.order_id) AS order_count,
ROUND(SUM(p.total_payment), 2) AS gmv,
ROUND(SUM(p.total_payment) / NULLIF(COUNT(DISTINCT c.customer_unique_id), 0), 2) AS gmv_per_user
FROM valid_orders v
JOIN customers c ON v.customer_id = c.customer_id
JOIN rfm_users r ON c.customer_unique_id = r.customer_unique_id
JOIN payment_agg p ON v.order_id = p.order_id
GROUP BY c.customer_state, r.user_segment
ORDER BY c.customer_state, user_count DESC;
-- (2) RFM品类偏好 (Top 5)
DROP VIEW IF EXISTS L5-RFM品类偏好;
CREATE VIEW L5-RFM品类偏好 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
valid_orders AS (
SELECT o.order_id, o.customer_id
FROM orders o
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
rfm_orders AS (
SELECT
r.user_segment,
v.order_id,
c.customer_unique_id,
oi.product_id,
oi.price + oi.freight_value AS item_amount
FROM valid_orders v
JOIN customers c ON v.customer_id = c.customer_id
JOIN L2-RFM分层_八类 r ON c.customer_unique_id = r.customer_unique_id
JOIN order_items oi ON v.order_id = oi.order_id
),
category_stats AS (
SELECT
user_segment,
p.product_category_name_english AS category,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_unique_id) AS user_count,
SUM(item_amount) AS gmv
FROM rfm_orders ro
JOIN products p ON ro.product_id = p.product_id
GROUP BY user_segment, p.product_category_name_english
),
ranked AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_segment ORDER BY gmv DESC) AS rn
FROM category_stats
)
SELECT
user_segment,
category,
orders,
user_count,
gmv,
ROUND(gmv / NULLIF(orders, 0), 2) AS aov
FROM ranked
WHERE rn <= 5
ORDER BY user_segment, rn;
DROP VIEW IF EXISTS L5-RFM大类偏好;
CREATE VIEW L5-RFM大类偏好 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
valid_orders AS (
SELECT o.order_id, o.customer_id
FROM orders o
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
-- 基础数据：订单 + RFM + 商品行 + 品类映射
base AS (
SELECT
r.user_segment,
v.order_id,
c.customer_unique_id,
oi.product_id,
oi.price + oi.freight_value AS item_amount,
pr.product_category_name_english AS original_category,
cm.category_l1
FROM valid_orders v
JOIN customers c ON v.customer_id = c.customer_id
JOIN L2-RFM分层_八类 r ON c.customer_unique_id = r.customer_unique_id
JOIN order_items oi ON v.order_id = oi.order_id
JOIN products pr ON oi.product_id = pr.product_id
LEFT JOIN category_mapping cm ON pr.product_category_name_english = cm.original_category
WHERE cm.category_l1 IS NOT NULL   -- 仅保留已映射的大类
),
-- 每个用户人群的总 GMV（LQ 分母之一）
segment_total AS (
SELECT
user_segment,
SUM(item_amount) AS segment_gmv_total
FROM base
GROUP BY user_segment
),
-- 全量用户（不区分人群）的总 GMV 和各大类 GMV（LQ 分母/分子）
all_total AS (
SELECT SUM(item_amount) AS all_gmv_total
FROM base
),
all_category AS (
SELECT
category_l1,
SUM(item_amount) AS all_cat_gmv
FROM base
GROUP BY category_l1
),
-- 按人群 + 大类汇总
segment_category AS (
SELECT
user_segment,
category_l1,
SUM(item_amount) AS gmv,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_unique_id) AS user_count
FROM base
GROUP BY user_segment, category_l1
),
-- 计算占比和 LQ
segment_category_with_metrics AS (
SELECT
sc.user_segment,
sc.category_l1,
sc.gmv,
sc.orders,
sc.user_count,
ROUND(sc.gmv / NULLIF(sc.orders, 0), 2) AS aov,
ROUND(sc.gmv / NULLIF(st.segment_gmv_total, 0), 4) AS gmv_share_in_segment,
-- LQ 指数 = (该人群该大类占比) / (全量用户该大类占比)
ROUND(
(sc.gmv / NULLIF(st.segment_gmv_total, 0)) /
(ac.all_cat_gmv / NULLIF(at.all_gmv_total, 0)),
2
) AS lq
FROM segment_category sc
JOIN segment_total st ON sc.user_segment = st.user_segment
JOIN all_category ac ON sc.category_l1 = ac.category_l1
CROSS JOIN all_total at
),
-- 各人群 + 大类下，原始品类 GMV 排序（取前三）
category_rank AS (
SELECT
user_segment,
category_l1,
original_category,
SUM(item_amount) AS cat_gmv,
ROW_NUMBER() OVER (PARTITION BY user_segment, category_l1 ORDER BY SUM(item_amount) DESC) AS rn
FROM base
GROUP BY user_segment, category_l1, original_category
),
top3_categories AS (
SELECT
user_segment,
category_l1,
GROUP_CONCAT(original_category ORDER BY cat_gmv DESC SEPARATOR '; ') AS top3_original_categories
FROM category_rank
WHERE rn <= 3
GROUP BY user_segment, category_l1
)
-- 最终输出
SELECT
scm.user_segment,
scm.category_l1,
scm.gmv,
scm.orders,
scm.user_count,
scm.aov,
scm.gmv_share_in_segment,
scm.lq,
t3.top3_original_categories
FROM segment_category_with_metrics scm
LEFT JOIN top3_categories t3 ON scm.user_segment = t3.user_segment AND scm.category_l1 = t3.category_l1
ORDER BY scm.user_segment, scm.gmv DESC;
-- (3) RFM时间行为
DROP VIEW IF EXISTS L5-RFM时间行为;
CREATE VIEW L5-RFM时间行为 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
valid_orders AS (
SELECT o.order_id, o.customer_id, o.order_purchase_timestamp
FROM orders o
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
rfm_orders AS (
SELECT
r.user_segment,
v.order_id,
v.order_purchase_timestamp,
c.customer_unique_id,
p.total_payment
FROM valid_orders v
JOIN customers c ON v.customer_id = c.customer_id
JOIN L2-RFM分层_八类 r ON c.customer_unique_id = r.customer_unique_id
JOIN payment_agg p ON v.order_id = p.order_id
)
SELECT
user_segment,
DATE_FORMAT(order_purchase_timestamp, '%Y-%m') AS month,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_unique_id) AS user_count,
ROUND(SUM(total_payment), 2) AS gmv,
MAX(CASE WHEN DATE_FORMAT(order_purchase_timestamp, '%Y-%m') = '2017-11' THEN 1 ELSE 0 END) AS is_black_friday
FROM rfm_orders
GROUP BY user_segment, month
ORDER BY user_segment, month;
-- (4) RFM支付方式偏好
DROP VIEW IF EXISTS L5-RFM支付方式偏好;
CREATE VIEW L5-RFM支付方式偏好 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
main_payment AS (
SELECT
order_id,
payment_type,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY payment_sequential) AS rn
FROM order_payments
),
main_payment_filter AS (
SELECT order_id, payment_type
FROM main_payment
WHERE rn = 1
),
valid_orders AS (
SELECT o.order_id, o.customer_id
FROM orders o
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
rfm_orders AS (
SELECT
r.user_segment,
v.order_id,
c.customer_unique_id,
m.payment_type,
p.total_payment
FROM valid_orders v
JOIN customers c ON v.customer_id = c.customer_id
JOIN L2-RFM分层_八类 r ON c.customer_unique_id = r.customer_unique_id
JOIN main_payment_filter m ON v.order_id = m.order_id
JOIN payment_agg p ON v.order_id = p.order_id
)
SELECT
user_segment,
payment_type,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_unique_id) AS user_count,
ROUND(SUM(total_payment), 2) AS gmv,
ROUND(AVG(total_payment), 2) AS avg_order_gmv,
ROUND(AVG(
(SELECT AVG(payment_installments) FROM order_payments op WHERE op.order_id = ro.order_id)
), 2) AS avg_installments
FROM rfm_orders ro
GROUP BY user_segment, payment_type
ORDER BY user_segment, gmv DESC;
-- (5) RFM客单价分布
DROP VIEW IF EXISTS L5-RFM客单价分布;
CREATE VIEW L5-RFM客单价分布 AS
WITH
payment_agg AS (
SELECT order_id, SUM(payment_value) AS total_payment
FROM order_payments
GROUP BY order_id
),
valid_orders AS (
SELECT o.order_id, o.customer_id
FROM orders o
JOIN payment_agg p ON o.order_id = p.order_id
WHERE o.order_status NOT IN ('canceled', 'unavailable')
AND o.time_logic_issue = 0
AND o.order_purchase_timestamp < '2018-09-01'
),
rfm_order_gmv AS (
SELECT
r.user_segment,
v.order_id,
c.customer_unique_id,
p.total_payment AS order_gmv
FROM valid_orders v
JOIN customers c ON v.customer_id = c.customer_id
JOIN L2-RFM分层_八类 r ON c.customer_unique_id = r.customer_unique_id
JOIN payment_agg p ON v.order_id = p.order_id
),
quantiles AS (
SELECT DISTINCT
user_segment,
ROUND(MIN(order_gmv) OVER (PARTITION BY user_segment), 2) AS min_gmv,
ROUND(MAX(order_gmv) OVER (PARTITION BY user_segment), 2) AS max_gmv,
ROUND(AVG(order_gmv) OVER (PARTITION BY user_segment), 2) AS avg_gmv
FROM rfm_order_gmv
),
user_counts AS (
SELECT
user_segment,
COUNT(DISTINCT customer_unique_id) AS user_count
FROM rfm_order_gmv
GROUP BY user_segment
)
SELECT
q.user_segment,
q.min_gmv,
q.max_gmv,
q.avg_gmv,
u.user_count
FROM quantiles q
JOIN user_counts u ON q.user_segment = u.user_segment
ORDER BY q.user_segment;
DROP VIEW IF EXISTS L2-品类全国汇总;
CREATE VIEW L2-品类全国汇总 AS
WITH
-- 有效订单（状态正常、时间逻辑正常、时间限制）
valid_orders AS (
SELECT order_id, customer_id
FROM orders
WHERE order_status NOT IN ('canceled', 'unavailable')
AND time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
),
-- 商品行明细（关联品类，计算行金额）
item_data AS (
SELECT
oi.order_id,
oi.product_id,
p.product_category_name_english AS category,
oi.price + oi.freight_value AS item_amount,
vo.customer_id
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN valid_orders vo ON oi.order_id = vo.order_id
),
-- 按品类汇总基础指标
category_stats AS (
SELECT
category,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_id) AS users,
SUM(item_amount) AS gmv
FROM item_data
GROUP BY category
),
-- 用户在各品类的购买次数
user_category_orders AS (
SELECT
category,
customer_id,
COUNT(DISTINCT order_id) AS order_count
FROM item_data
GROUP BY category, customer_id
),
-- 各品类复购用户数（购买≥2次）
repeat_users AS (
SELECT
category,
COUNT(DISTINCT customer_id) AS repeat_users
FROM user_category_orders
WHERE order_count >= 2
GROUP BY category
)
-- 最终输出
SELECT
cs.category,
ROUND(cs.gmv, 2) AS gmv,
cs.orders,
ROUND(cs.gmv / NULLIF(cs.orders, 0), 2) AS aov,
ROUND(COALESCE(ru.repeat_users, 0) / cs.users * 100, 2) AS repeat_rate
FROM category_stats cs
LEFT JOIN repeat_users ru ON cs.category = ru.category
ORDER BY cs.gmv DESC;
DROP VIEW IF EXISTS L6-品类汇总;
CREATE VIEW L6-品类汇总 AS
WITH
-- 有效订单
valid_orders AS (
SELECT order_id, customer_id
FROM orders
WHERE order_status NOT IN ('canceled', 'unavailable')
AND time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
),
-- 商品行明细（关联品类）
item_data AS (
SELECT
oi.order_id,
oi.product_id,
oi.seller_id,
p.product_category_name_english AS category,
oi.price + oi.freight_value AS item_amount,
vo.customer_id
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN valid_orders vo ON oi.order_id = vo.order_id
WHERE p.product_category_name_english IS NOT NULL
),
-- 按品类汇总基础指标
category_stats AS (
SELECT
category,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_id) AS users,
COUNT(DISTINCT product_id) AS sku_count,
COUNT(DISTINCT seller_id) AS seller_count,
SUM(item_amount) AS gmv
FROM item_data
GROUP BY category
),
-- 各品类复购用户
user_category_orders AS (
SELECT
category,
customer_id,
COUNT(DISTINCT order_id) AS order_count
FROM item_data
GROUP BY category, customer_id
),
repeat_users AS (
SELECT
category,
COUNT(DISTINCT customer_id) AS repeat_users
FROM user_category_orders
WHERE order_count >= 2
GROUP BY category
)
-- 最终输出
SELECT
cs.category,
ROUND(cs.gmv, 2) AS gmv,
cs.orders,
ROUND(cs.gmv / NULLIF(cs.orders, 0), 2) AS aov,
cs.users,
ROUND(COALESCE(ru.repeat_users, 0) / cs.users * 100, 2) AS repeat_rate,
cs.sku_count,
cs.seller_count
FROM category_stats cs
LEFT JOIN repeat_users ru ON cs.category = ru.category
ORDER BY cs.gmv DESC;
-- 1. 创建映射表
DROP TABLE IF EXISTS category_mapping;
CREATE TABLE category_mapping (
original_category VARCHAR(50) NOT NULL COMMENT 'Olist原始品类英文名',
category_l1 VARCHAR(20) NOT NULL COMMENT '自定义一级类目',
PRIMARY KEY (original_category)
) COMMENT='Olist品类到一级类目的映射表';
-- 2. 插入映射数据
INSERT INTO category_mapping (original_category, category_l1) VALUES
('cool_stuff', '食品生鲜'),
('pet_shop', '宠物用品'),
('furniture_decor', '家居生活'),
('perfumery', '美妆个护'),
('garden_tools', '工业/工具'),
('housewares', '家居生活'),
('telephony', '数码电子'),
('health_beauty', '美妆个护'),
('books_technical', '图书文娱'),
('fashion_bags_accessories', '服饰鞋包'),
('bed_bath_table', '家居生活'),
('sports_leisure', '运动户外'),
('consoles_games', '数码电子'),
('office_furniture', '家居生活'),
('luggage_accessories', '服饰鞋包'),
('food', '食品生鲜'),
('agro_industry_and_commerce', '工业/工具'),
('electronics', '数码电子'),
('computers_accessories', '数码电子'),
('construction_tools_construction', '工业/工具'),
('baby', '母婴用品'),
('construction_tools_lights', '工业/工具'),
('toys', '母婴用品'),
('stationery', '图书文娱'),
('industry_commerce_and_business', '工业/工具'),
('watches_gifts', '服饰鞋包'),
('auto', '汽车用品'),
('home_appliances', '家居生活'),
('kitchen_dining_laundry_garden_furniture', '家居生活'),
('air_conditioning', '家居生活'),
('home_confort', '家居生活'),
('fixed_telephony', '数码电子'),
('small_appliances_home_oven_and_coffee', '家居生活'),
('diapers_and_hygiene', '母婴用品'),
('signaling_and_security', '数码电子'),
('musical_instruments', '图书文娱'),
('costruction_tools_garden', '工业/工具'),
('art', '图书文娱'),
('home_construction', '家居生活'),
('books_general_interest', '图书文娱'),
('party_supplies', '图书文娱'),
('small_appliances', '家居生活'),
('construction_tools_safety', '工业/工具'),
('cine_photo', '数码电子'),
('fashion_underwear_beach', '服饰鞋包'),
('fashion_male_clothing', '服饰鞋包'),
('food_drink', '食品生鲜'),
('drinks', '食品生鲜'),
('furniture_living_room', '家居生活'),
('market_place', '其他'),
('music', '图书文娱'),
('fashion_shoes', '服饰鞋包'),
('flowers', '食品生鲜'),
('home_appliances_2', '家居生活'),
('fashio_female_clothing', '服饰鞋包'),   -- 原表拼写错误，但保留
('audio', '数码电子'),
('computers', '数码电子'),
('books_imported', '图书文娱'),
('christmas_supplies', '图书文娱'),
('furniture_bedroom', '家居生活'),
('home_comfort_2', '家居生活'),
('dvds_blu_ray', '图书文娱'),
('cds_dvds_musicals', '图书文娱'),
('arts_and_craftmanship', '图书文娱'),
('furniture_mattress_and_upholstery', '家居生活'),
('tablets_printing_image', '数码电子'),
('未分类', '未分类'),
('costruction_tools_tools', '工业/工具'),
('fashion_sport', '服饰鞋包'),   -- 运动户外也可，但依据国内常归服饰
('la_cuisine', '家居生活'),
('fashion_childrens_clothes', '服饰鞋包'),
('security_and_services', '数码电子');
DROP VIEW IF EXISTS L6-各州优势产业;
CREATE VIEW L6-各州优势产业 AS
WITH
-- 有效订单（状态正常、时间逻辑正常、时间限制）
valid_orders AS (
SELECT order_id, customer_id
FROM orders
WHERE order_status NOT IN ('canceled', 'unavailable')
AND time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
),
-- 商品行明细，关联卖家州和品类
seller_category AS (
SELECT
s.seller_state AS state,
p.product_category_name_english AS category,
oi.price + oi.freight_value AS item_amount
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
JOIN products p ON oi.product_id = p.product_id
),
-- 按州-品类汇总 GMV（供给端）
state_category_gmv AS (
SELECT
state,
category,
SUM(item_amount) AS gmv
FROM seller_category
GROUP BY state, category
),
-- 各州总 GMV（供给端）
state_total AS (
SELECT state, SUM(gmv) AS total_gmv
FROM state_category_gmv
GROUP BY state
),
-- 计算占比和排名
ranked AS (
SELECT
scg.state,
scg.category,
scg.gmv,
st.total_gmv,
ROUND(scg.gmv / st.total_gmv, 4) AS gmv_share,
ROW_NUMBER() OVER (PARTITION BY scg.state ORDER BY scg.gmv DESC) AS rank_gmv
FROM state_category_gmv scg
JOIN state_total st ON scg.state = st.state
)
SELECT
state,
category,
gmv,
total_gmv,
gmv_share,
rank_gmv
FROM ranked
ORDER BY state, rank_gmv;
DROP VIEW IF EXISTS L6-卖家大类分布;
CREATE VIEW L6-卖家大类分布 AS
WITH
valid_orders AS (
SELECT order_id
FROM orders
WHERE order_status NOT IN ('canceled', 'unavailable')
AND time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
)
SELECT
oi.seller_id,
cm.category_l1,
COUNT(DISTINCT oi.order_id) AS orders,
SUM(oi.price + oi.freight_value) AS gmv,
COUNT(*) AS item_count
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id
JOIN products pr ON oi.product_id = pr.product_id
LEFT JOIN category_mapping cm ON pr.product_category_name_english = cm.original_category
WHERE cm.category_l1 IS NOT NULL
GROUP BY oi.seller_id, cm.category_l1
ORDER BY oi.seller_id, gmv DESC;
DROP VIEW IF EXISTS L6-供给端头部产业汇总;
CREATE VIEW L6-供给端头部产业汇总 AS
WITH
valid_orders AS (
SELECT order_id
FROM orders
WHERE order_status NOT IN ('canceled', 'unavailable')
AND time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
),
item_data AS (
SELECT
s.seller_state AS state,
oi.order_id,
oi.seller_id,
oi.price + oi.freight_value AS item_amount,
cm.category_l1
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id
JOIN sellers s ON oi.seller_id = s.seller_id
JOIN products pr ON oi.product_id = pr.product_id
LEFT JOIN category_mapping cm ON pr.product_category_name_english = cm.original_category
WHERE cm.category_l1 IS NOT NULL
),
state_cat_stats AS (
SELECT
state,
category_l1,
SUM(item_amount) AS gmv,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT seller_id) AS sellers
FROM item_data
GROUP BY state, category_l1
),
state_agg AS (
SELECT
state,
SUM(gmv) AS state_gmv,
SUM(orders) AS state_orders,
SUM(sellers) AS state_sellers
FROM state_cat_stats
GROUP BY state
),
ranked AS (
SELECT
state,
category_l1,
gmv,
orders,
sellers,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY gmv DESC) AS rn
FROM state_cat_stats
),
head AS (
SELECT
state,
category_l1 AS head_category,
gmv AS head_gmv,
orders AS head_orders,
sellers AS head_sellers
FROM ranked
WHERE rn = 1
)
SELECT
sa.state,
h.head_category,
sa.state_gmv,
h.head_gmv,
sa.state_orders,
h.head_orders,
sa.state_sellers,
h.head_sellers
FROM state_agg sa
JOIN head h ON sa.state = h.state
ORDER BY sa.state;
DROP VIEW IF EXISTS L7-需求端头部产业汇总;
CREATE VIEW L7-需求端头部产业汇总 AS
WITH
valid_orders AS (
SELECT order_id, customer_id
FROM orders
WHERE order_status NOT IN ('canceled', 'unavailable')
AND time_logic_issue = 0
AND order_purchase_timestamp < '2018-09-01'
),
item_data AS (
SELECT
c.customer_state AS state,
oi.order_id,
vo.customer_id,                     -- 添加 customer_id
oi.price + oi.freight_value AS item_amount,
cm.category_l1
FROM order_items oi
JOIN valid_orders vo ON oi.order_id = vo.order_id
JOIN customers c ON vo.customer_id = c.customer_id
JOIN products pr ON oi.product_id = pr.product_id
LEFT JOIN category_mapping cm ON pr.product_category_name_english = cm.original_category
WHERE cm.category_l1 IS NOT NULL
),
state_cat_stats AS (
SELECT
state,
category_l1,
SUM(item_amount) AS gmv,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_id) AS buyers
FROM item_data
GROUP BY state, category_l1
),
state_agg AS (
SELECT
state,
SUM(gmv) AS state_gmv,
SUM(orders) AS state_orders,
SUM(buyers) AS state_buyers
FROM state_cat_stats
GROUP BY state
),
ranked AS (
SELECT
state,
category_l1,
gmv,
orders,
buyers,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY gmv DESC) AS rn
FROM state_cat_stats
),
head AS (
SELECT
state,
category_l1 AS head_category,
gmv AS head_gmv,
orders AS head_orders,
buyers AS head_buyers
FROM ranked
WHERE rn = 1
)
SELECT
sa.state,
h.head_category,
sa.state_gmv,
h.head_gmv,
sa.state_orders,
h.head_orders,
sa.state_buyers,
h.head_buyers
FROM state_agg sa
JOIN head h ON sa.state = h.state
ORDER BY sa.state;