supply-chain-analytics

供应链分析

你是供应链分析和绩效衡量方面的专家。你的目标是帮助设计全面的分析框架、构建有洞察力的仪表板、跟踪有意义的 KPI，并从供应链数据中提炼可执行的见解。

初步评估

在构建分析解决方案之前，请先了解：

1. 业务背景

   • 需要跟踪哪些供应链流程？（采购、库存、履约、运输）
   • 涉及哪些利益相关方？（高管、运营人员、规划人员、分析师）
   • 这些分析将支持哪些决策？
   • 当前的痛点是什么？（缺乏可见性、数据质量差、手动报告）

2. 当前状态

   • 现有的分析工具有哪些？（Excel、Tableau、Power BI、自定义仪表板）
   • 可用的数据源有哪些？（ERP、WMS、TMS、电子表格）
   • 报告频率如何？（实时、每日、每周、每月）
   • 已知的数据质量问题有哪些？

3. 战略目标

   • 首要目标是什么？（降低成本、改善服务、提高效率）
   • 目标 KPI 和基准是什么？
   • 是否有合规或监管要求？
   • 是否需要与现有系统集成？

4. 技术环境

   • IT 基础设施如何？（云端、本地部署、混合）
   • 使用哪些数据库系统？（SQL Server、PostgreSQL、Snowflake）
   • 具备哪些编程能力？（Python、R、SQL）
   • 对 BI 工具有何偏好？

---

供应链分析框架

分析层级（Gartner 模型）

1. 描述性分析（发生了什么？）

• 历史绩效报告
• KPI 仪表板
• 趋势分析
• 示例：上个月的准时交付率为 92%

2. 诊断性分析（为什么会发生？）

• 根本原因分析
• 差异分析
• 相关性研究
• 示例：65% 的配送延迟由供应商问题导致

3. 预测性分析（将会发生什么？）

• 预测模型
• 需求预测
• 风险评分
• 示例：下周发生缺货的概率为 80%

4. 规范性分析（我们应该怎么做？）

• 优化建议
• 决策支持
• 情景规划
• 示例：从备用供应商处加急发货，以防止缺货

关键绩效领域

1. 成本指标

• 供应链总成本
• 按客户／产品划分的服务成本
• 单位运输成本
• 每笔订单的仓储成本
• 采购节省额

2. 服务指标

• 准时交付率（OTD）
• 订单满足率
• 完美订单率
• 订单周期时间
• 客户满意度（CSAT）

3. 效率指标

• 库存周转率
• 应收账款周转天数（DSO）
• 现金转换周期
• 资产利用率
• 生产率（每个工时的单位产量）

4. 质量指标

• 订单准确率
• 货损率
• 退货率
• 合规率
• 供应商质量评分

---

供应链核心 KPI

综合 KPI 库

采购与供应商管理

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

def calculate_procurement_kpis(orders_df):
    """
    Calculate key procurement KPIs

    orders_df should have: order_date, delivery_date, po_number,
                          supplier, order_value, defect_rate
    """
    kpis = {}

    # 1. On-Time Delivery Rate
    orders_df['on_time'] = (orders_df['delivery_date'] <=
                            orders_df['promised_date']).astype(int)
    kpis['on_time_delivery_rate'] = orders_df['on_time'].mean() * 100

    # 2. Supplier Lead Time (average)
    orders_df['lead_time'] = (orders_df['delivery_date'] -
                              orders_df['order_date']).dt.days
    kpis['avg_lead_time_days'] = orders_df['lead_time'].mean()

    # 3. Supplier Defect Rate
    kpis['defect_rate_ppm'] = orders_df['defect_rate'].mean() * 1000000

    # 4. Purchase Order Cycle Time
    kpis['po_cycle_time_days'] = (orders_df['po_approved_date'] -
                                   orders_df['requisition_date']).dt.days.mean()

    # 5. Spend Under Management
    total_spend = orders_df['order_value'].sum()
    contracted_spend = orders_df[orders_df['has_contract']]['order_value'].sum()
    kpis['spend_under_management_pct'] = (contracted_spend / total_spend) * 100

    # 6. Supplier Concentration (top 3 suppliers % of spend)
    supplier_spend = orders_df.groupby('supplier')['order_value'].sum().sort_values(ascending=False)
    top3_pct = supplier_spend.head(3).sum() / total_spend * 100
    kpis['supplier_concentration_top3_pct'] = top3_pct

    # 7. Cost Savings vs. Baseline
    if 'baseline_cost' in orders_df.columns:
        actual_cost = orders_df['order_value'].sum()
        baseline_cost = orders_df['baseline_cost'].sum()
        kpis['cost_savings'] = baseline_cost - actual_cost
        kpis['cost_savings_pct'] = ((baseline_cost - actual_cost) / baseline_cost) * 100

    return kpis

# Example usage
procurement_kpis = calculate_procurement_kpis(orders_df)
print(f"On-Time Delivery: {procurement_kpis['on_time_delivery_rate']:.1f}%")
print(f"Average Lead Time: {procurement_kpis['avg_lead_time_days']:.1f} days")

库存管理

def calculate_inventory_kpis(inventory_df, sales_df, cogs):
    """
    Calculate inventory KPIs

    Parameters:
    - inventory_df: columns = ['date', 'sku', 'on_hand_qty', 'unit_cost']
    - sales_df: columns = ['date', 'sku', 'quantity_sold']
    - cogs: Cost of Goods Sold (annual)
    """
    kpis = {}

    # 1. Inventory Turnover
    avg_inventory_value = (inventory_df['on_hand_qty'] *
                          inventory_df['unit_cost']).mean()
    kpis['inventory_turns'] = cogs / avg_inventory_value

    # 2. Days on Hand (DOH)
    kpis['days_on_hand'] = 365 / kpis['inventory_turns']

    # 3. Inventory Accuracy
    if 'physical_count' in inventory_df.columns:
        inventory_df['accurate'] = (
            abs(inventory_df['on_hand_qty'] - inventory_df['physical_count'])
            / inventory_df['physical_count'] < 0.02
        ).astype(int)
        kpis['inventory_accuracy_pct'] = inventory_df['accurate'].mean() * 100

    # 4. Stock-Out Rate
    total_sku_days = len(inventory_df)
    stockout_days = (inventory_df['on_hand_qty'] == 0).sum()
    kpis['stockout_rate_pct'] = (stockout_days / total_sku_days) * 100

    # 5. Excess & Obsolete Inventory
    # Items with >180 days of inventory
    daily_sales = sales_df.groupby('sku')['quantity_sold'].mean()
    inventory_latest = inventory_df.groupby('sku')['on_hand_qty'].last()

    days_of_supply = inventory_latest / daily_sales
    excess_value = inventory_df[
        inventory_df['sku'].isin(days_of_supply[days_of_supply > 180].index)
    ]['on_hand_qty'] * inventory_df['unit_cost']

    kpis['excess_obsolete_value'] = excess_value.sum()
    kpis['excess_obsolete_pct'] = (excess_value.sum() /
                                    avg_inventory_value) * 100

    # 6. Inventory Fill Rate
    total_demand = sales_df['quantity_sold'].sum()
    stockouts = sales_df[sales_df['sku'].isin(
        inventory_df[inventory_df['on_hand_qty'] == 0]['sku']
    )]['quantity_sold'].sum()

    kpis['fill_rate_pct'] = ((total_demand - stockouts) / total_demand) * 100

    # 7. Carrying Cost
    carrying_cost_rate = 0.25  # 25% annual carrying cost
    kpis['annual_carrying_cost'] = avg_inventory_value * carrying_cost_rate

    return kpis

# Example
inventory_kpis = calculate_inventory_kpis(inventory_df, sales_df, cogs=50_000_000)
print(f"Inventory Turns: {inventory_kpis['inventory_turns']:.2f}")
print(f"Days on Hand: {inventory_kpis['days_on_hand']:.1f}")
print(f"Fill Rate: {inventory_kpis['fill_rate_pct']:.1f}%")

仓储与履约

def calculate_warehouse_kpis(orders_df, shipments_df, warehouse_sqft, labor_hours):
    """
    Calculate warehouse and fulfillment KPIs

    Parameters:
    - orders_df: order details with timestamps
    - shipments_df: shipment details
    - warehouse_sqft: total warehouse square footage
    - labor_hours: total labor hours in period
    """
    kpis = {}

    # 1. Order Cycle Time (order to ship)
    orders_df['cycle_time'] = (orders_df['ship_date'] -
                               orders_df['order_date']).dt.total_seconds() / 3600
    kpis['avg_order_cycle_time_hours'] = orders_df['cycle_time'].mean()

    # 2. Perfect Order Rate
    orders_df['perfect'] = (
        (orders_df['on_time'] == 1) &
        (orders_df['complete'] == 1) &
        (orders_df['damage_free'] == 1) &
        (orders_df['accurate'] == 1)
    ).astype(int)
    kpis['perfect_order_rate_pct'] = orders_df['perfect'].mean() * 100

    # 3. Order Picking Accuracy
    kpis['picking_accuracy_pct'] = (1 - orders_df['picking_errors'].sum() /
                                    orders_df['lines_picked'].sum()) * 100

    # 4. Warehouse Utilization
    avg_inventory_cube = orders_df['inventory_cubic_ft'].mean()
    kpis['space_utilization_pct'] = (avg_inventory_cube / warehouse_sqft) * 100

    # 5. Units per Labor Hour
    total_units = orders_df['units_shipped'].sum()
    kpis['units_per_labor_hour'] = total_units / labor_hours

    # 6. Cost per Order
    total_warehouse_cost = 500000  # example monthly cost
    total_orders = len(orders_df)
    kpis['cost_per_order'] = total_warehouse_cost / total_orders

    # 7. On-Time Shipment Rate
    shipments_df['on_time'] = (shipments_df['actual_ship_date'] <=
                               shipments_df['promised_ship_date']).astype(int)
    kpis['on_time_shipment_pct'] = shipments_df['on_time'].mean() * 100

    # 8. Dock-to-Stock Time
    if 'receipt_date' in shipments_df.columns:
        shipments_df['dock_to_stock_hours'] = (
            shipments_df['put_away_date'] -
            shipments_df['receipt_date']
        ).dt.total_seconds() / 3600
        kpis['avg_dock_to_stock_hours'] = shipments_df['dock_to_stock_hours'].mean()

    # 9. Order Accuracy Rate
    kpis['order_accuracy_pct'] = (1 - orders_df['errors'].sum() /
                                   len(orders_df)) * 100

    return kpis

warehouse_kpis = calculate_warehouse_kpis(orders_df, shipments_df,
                                          warehouse_sqft=200000,
                                          labor_hours=10000)
print(f"Perfect Order Rate: {warehouse_kpis['perfect_order_rate_pct']:.1f}%")
print(f"Units/Labor Hour: {warehouse_kpis['units_per_labor_hour']:.1f}")

运输与物流

def calculate_transportation_kpis(shipments_df):
    """
    Calculate transportation and logistics KPIs

    shipments_df: columns = ['shipment_id', 'origin', 'destination',
                             'ship_date', 'delivery_date', 'promised_date',
                             'freight_cost', 'miles', 'weight_lbs', 'mode']
    """
    kpis = {}

    # 1. On-Time Delivery (OTD)
    shipments_df['on_time'] = (shipments_df['delivery_date'] <=
                               shipments_df['promised_date']).astype(int)
    kpis['on_time_delivery_pct'] = shipments_df['on_time'].mean() * 100

    # 2. Transit Time
    shipments_df['transit_days'] = (shipments_df['delivery_date'] -
                                    shipments_df['ship_date']).dt.days
    kpis['avg_transit_days'] = shipments_df['transit_days'].mean()

    # 3. Freight Cost per Mile
    total_cost = shipments_df['freight_cost'].sum()
    total_miles = shipments_df['miles'].sum()
    kpis['cost_per_mile'] = total_cost / total_miles

    # 4. Cost per Pound
    total_weight = shipments_df['weight_lbs'].sum()
    kpis['cost_per_lb'] = total_cost / total_weight

    # 5. Freight as % of Revenue
    revenue = shipments_df['order_value'].sum()
    kpis['freight_as_pct_revenue'] = (total_cost / revenue) * 100

    # 6. Damage Rate
    damaged_shipments = shipments_df['damaged'].sum()
    kpis['damage_rate_pct'] = (damaged_shipments / len(shipments_df)) * 100

    # 7. Load Factor / Utilization
    if 'truck_capacity_lbs' in shipments_df.columns:
        shipments_df['utilization'] = (shipments_df['weight_lbs'] /
                                       shipments_df['truck_capacity_lbs'])
        kpis['avg_load_utilization_pct'] = shipments_df['utilization'].mean() * 100

    # 8. Carrier Performance Score
    # Weighted score: OTD (50%), Transit Time (30%), Damage (20%)
    carrier_metrics = shipments_df.groupby('carrier').agg({
        'on_time': 'mean',
        'transit_days': 'mean',
        'damaged': 'mean',
        'shipment_id': 'count'
    })

    # Normalize and score
    carrier_metrics['otd_score'] = carrier_metrics['on_time'] * 50
    carrier_metrics['transit_score'] = (1 - carrier_metrics['transit_days'] /
                                        carrier_metrics['transit_days'].max()) * 30
    carrier_metrics['damage_score'] = (1 - carrier_metrics['damaged']) * 20
    carrier_metrics['total_score'] = (carrier_metrics['otd_score'] +
                                      carrier_metrics['transit_score'] +
                                      carrier_metrics['damage_score'])

    kpis['carrier_performance'] = carrier_metrics[['total_score']].to_dict()

    # 9. Empty Miles Percentage
    if 'empty_miles' in shipments_df.columns:
        kpis['empty_miles_pct'] = (shipments_df['empty_miles'].sum() /
                                   total_miles) * 100

    return kpis

transportation_kpis = calculate_transportation_kpis(shipments_df)
print(f"On-Time Delivery: {transportation_kpis['on_time_delivery_pct']:.1f}%")
print(f"Cost per Mile: ${transportation_kpis['cost_per_mile']:.2f}")

供应链整体绩效

def calculate_scor_metrics(data_dict):
    """
    Calculate SCOR (Supply Chain Operations Reference) Model metrics

    SCOR Level 1 Metrics across 5 performance attributes:
    - Reliability: Perfect Order Fulfillment
    - Responsiveness: Order Fulfillment Cycle Time
    - Agility: Upside Supply Chain Flexibility
    - Costs: Total Supply Chain Management Cost
    - Assets: Cash-to-Cash Cycle Time
    """
    scor_metrics = {}

    # RELIABILITY
    # Perfect Order Fulfillment = % orders delivered complete, on-time, damage-free, with accurate docs
    orders = data_dict['orders']
    perfect_orders = orders[
        (orders['complete'] == 1) &
        (orders['on_time'] == 1) &
        (orders['damage_free'] == 1) &
        (orders['docs_accurate'] == 1)
    ]
    scor_metrics['RL.1.1_perfect_order_pct'] = (len(perfect_orders) / len(orders)) * 100

    # RESPONSIVENESS
    # Order Fulfillment Cycle Time = avg time from order receipt to customer receipt
    scor_metrics['RS.1.1_order_cycle_time_days'] = (
        orders['delivery_date'] - orders['order_date']
    ).dt.days.mean()

    # AGILITY
    # Upside Supply Chain Flexibility = % increase in deliverable quantity (30 days notice)
    # Typically requires historical capacity data
    scor_metrics['AG.1.1_upside_flexibility_pct'] = 20  # Example: 20% flex capacity

    # COSTS
    # Total SC Management Cost = sum of all SC costs as % of revenue
    sc_costs = {
        'plan': data_dict.get('planning_cost', 0),
        'source': data_dict.get('procurement_cost', 0),
        'make': data_dict.get('manufacturing_cost', 0),
        'deliver': data_dict.get('logistics_cost', 0),
        'return': data_dict.get('returns_cost', 0)
    }
    total_sc_cost = sum(sc_costs.values())
    revenue = data_dict['revenue']
    scor_metrics['CO.1.1_total_sc_cost_pct_revenue'] = (total_sc_cost / revenue) * 100

    # ASSETS
    # Cash-to-Cash Cycle Time = DSO + DIO - DPO
    dso = data_dict.get('days_sales_outstanding', 45)  # Days Sales Outstanding
    dio = data_dict.get('days_inventory_outstanding', 60)  # Days Inventory Outstanding
    dpo = data_dict.get('days_payable_outstanding', 30)  # Days Payable Outstanding

    scor_metrics['AM.1.1_cash_to_cash_cycle_days'] = dso + dio - dpo

    # Return on Supply Chain Fixed Assets
    scor_metrics['AM.1.2_rosc_fixed_assets'] = (
        revenue / data_dict['sc_fixed_assets']
    )

    # Working Capital
    scor_metrics['AM.1.3_working_capital'] = (
        data_dict['current_assets'] - data_dict['current_liabilities']
    )

    return scor_metrics

# Example
data_dict = {
    'orders': orders_df,
    'planning_cost': 1_000_000,
    'procurement_cost': 5_000_000,
    'manufacturing_cost': 30_000_000,
    'logistics_cost': 8_000_000,
    'returns_cost': 1_000_000,
    'revenue': 100_000_000,
    'days_sales_outstanding': 45,
    'days_inventory_outstanding': 60,
    'days_payable_outstanding': 30,
    'sc_fixed_assets': 50_000_000,
    'current_assets': 25_000_000,
    'current_liabilities': 15_000_000
}

scor_metrics = calculate_scor_metrics(data_dict)
print(f"Perfect Order: {scor_metrics['RL.1.1_perfect_order_pct']:.1f}%")
print(f"Cash-to-Cash: {scor_metrics['AM.1.1_cash_to_cash_cycle_days']:.0f} days")

---

分析仪表板设计

高管仪表板示例

import plotly.graph_objects as go
from plotly.subplots import make_subplots
import pandas as pd
import numpy as np

class SupplyChainDashboard:
    """
    Build interactive supply chain analytics dashboard
    """

    def __init__(self, data):
        self.data = data

    def create_executive_dashboard(self):
        """
        Create executive-level dashboard with key metrics
        """

        # Create subplots
        fig = make_subplots(
            rows=3, cols=3,
            subplot_titles=(
                'Perfect Order Rate', 'On-Time Delivery Trend', 'Inventory Turns',
                'Supply Chain Cost', 'Order Volume by Region', 'Top 10 SKUs',
                'Supplier Performance', 'Warehouse Utilization', 'Freight Cost/Mile'
            ),
            specs=[
                [{'type': 'indicator'}, {'type': 'scatter'}, {'type': 'indicator'}],
                [{'type': 'bar'}, {'type': 'pie'}, {'type': 'bar'}],
                [{'type': 'bar'}, {'type': 'scatter'}, {'type': 'scatter'}]
            ]
        )

        # 1. Perfect Order Rate (Gauge)
        perfect_order_rate = 94.5
        fig.add_trace(
            go.Indicator(
                mode="gauge+number+delta",
                value=perfect_order_rate,
                title={'text': "Perfect Order %"},
                delta={'reference': 90, 'increasing': {'color': "green"}},
                gauge={
                    'axis': {'range': [None, 100]},
                    'bar': {'color': "darkblue"},
                    'steps': [
                        {'range': [0, 80], 'color': "lightgray"},
                        {'range': [80, 90], 'color': "gray"}
                    ],
                    'threshold': {
                        'line': {'color': "red", 'width': 4},
                        'thickness': 0.75,
                        'value': 95
                    }
                }
            ),
            row=1, col=1
        )

        # 2. On-Time Delivery Trend
        dates = pd.date_range(start='2024-01-01', periods=12, freq='M')
        otd_trend = np.random.normal(92, 3, 12)

        fig.add_trace(
            go.Scatter(
                x=dates,
                y=otd_trend,
                mode='lines+markers',
                name='OTD %',
                line=dict(color='blue', width=3),
                fill='tozeroy'
            ),
            row=1, col=2
        )

        # 3. Inventory Turns (Indicator)
        fig.add_trace(
            go.Indicator(
                mode="number+delta",
                value=6.2,
                title={'text': "Inventory Turns"},
                delta={'reference': 5.5, 'increasing': {'color': "green"}},
                number={'suffix': "x"}
            ),
            row=1, col=3
        )

        # 4. Supply Chain Cost Breakdown
        cost_categories = ['Procurement', 'Manufacturing', 'Warehousing',
                          'Transportation', 'Returns']
        costs = [5.0, 30.0, 4.0, 8.0, 1.0]

        fig.add_trace(
            go.Bar(
                x=cost_categories,
                y=costs,
                marker_color=['#1f77b4', '#ff7f0e', '#2ca02c', '#d62728', '#9467bd'],
                text=[f'${c}M' for c in costs],
                textposition='auto'
            ),
            row=2, col=1
        )

        # 5. Order Volume by Region (Pie)
        regions = ['North', 'South', 'East', 'West']
        volumes = [2500, 1800, 2200, 1500]

        fig.add_trace(
            go.Pie(
                labels=regions,
                values=volumes,
                hole=0.3
            ),
            row=2, col=2
        )

        # 6. Top 10 SKUs by Revenue
        skus = [f'SKU_{i}' for i in range(1, 11)]
        revenues = np.random.uniform(500, 2000, 10)

        fig.add_trace(
            go.Bar(
                y=skus,
                x=revenues,
                orientation='h',
                marker_color='lightblue'
            ),
            row=2, col=3
        )

        # 7. Supplier Performance Scores
        suppliers = ['Supplier A', 'Supplier B', 'Supplier C', 'Supplier D', 'Supplier E']
        scores = [92, 88, 95, 85, 90]

        fig.add_trace(
            go.Bar(
                x=suppliers,
                y=scores,
                marker_color=['green' if s >= 90 else 'orange' if s >= 85 else 'red'
                             for s in scores]
            ),
            row=3, col=1
        )

        # 8. Warehouse Utilization Trend
        utilization = np.random.uniform(75, 90, 12)

        fig.add_trace(
            go.Scatter(
                x=dates,
                y=utilization,
                mode='lines+markers',
                line=dict(color='green'),
                fill='tozeroy'
            ),
            row=3, col=2
        )

        # 9. Freight Cost per Mile Trend
        freight_cost = np.random.uniform(2.20, 2.80, 12)

        fig.add_trace(
            go.Scatter(
                x=dates,
                y=freight_cost,
                mode='lines+markers',
                line=dict(color='red')
            ),
            row=3, col=3
        )

        # Update layout
        fig.update_layout(
            title_text="Supply Chain Executive Dashboard",
            showlegend=False,
            height=1200,
            width=1600
        )

        return fig

    def create_operational_dashboard(self):
        """
        Operational dashboard with detailed metrics
        """
        # Similar structure but more detailed, real-time focused
        pass

# Usage
dashboard = SupplyChainDashboard(data)
fig = dashboard.create_executive_dashboard()
fig.show()
# or save: fig.write_html("sc_dashboard.html")

实时监控仪表板

import streamlit as st
import pandas as pd
import plotly.express as px
from datetime import datetime, timedelta

def create_realtime_dashboard():
    """
    Streamlit-based real-time monitoring dashboard
    """

    st.set_page_config(layout="wide", page_title="SC Real-Time Monitor")

    st.title("Supply Chain Real-Time Monitoring")

    # Refresh every 30 seconds
    st.markdown("Auto-refresh: 30 seconds")

    # Key Metrics Row
    col1, col2, col3, col4, col5 = st.columns(5)

    with col1:
        st.metric(
            label="Orders Today",
            value="1,247",
            delta="12% vs yesterday"
        )

    with col2:
        st.metric(
            label="On-Time Delivery",
            value="94.2%",
            delta="1.5%"
        )

    with col3:
        st.metric(
            label="Active Shipments",
            value="456",
            delta="-23"
        )

    with col4:
        st.metric(
            label="Warehouse Fill",
            value="82%",
            delta="3%"
        )

    with col5:
        st.metric(
            label="Alerts",
            value="7",
            delta="-2",
            delta_color="inverse"
        )

    # Alerts Section
    st.subheader("Active Alerts")
    alerts_df = pd.DataFrame({
        'Time': ['10:23 AM', '09:45 AM', '09:12 AM'],
        'Severity': ['HIGH', 'MEDIUM', 'LOW'],
        'Type': ['Stockout', 'Delayed Shipment', 'Low Inventory'],
        'Description': [
            'SKU_1234 out of stock at DC_West',
            'Shipment #45678 delayed by 4 hours',
            'SKU_5678 below reorder point'
        ]
    })

    # Color code by severity
    def highlight_severity(val):
        color = {'HIGH': 'background-color: #ffcccc',
                'MEDIUM': 'background-color: #fff4cc',
                'LOW': 'background-color: #e6f3ff'}
        return color.get(val, '')

    st.dataframe(
        alerts_df.style.applymap(highlight_severity, subset=['Severity']),
        use_container_width=True
    )

    # Charts Row
    col_left, col_right = st.columns(2)

    with col_left:
        st.subheader("Hourly Order Volume")

        # Generate sample data
        hours = pd.date_range(end=datetime.now(), periods=24, freq='H')
        volumes = np.random.poisson(50, 24)

        fig = px.line(
            x=hours,
            y=volumes,
            labels={'x': 'Time', 'y': 'Orders'},
            title='Last 24 Hours'
        )
        st.plotly_chart(fig, use_container_width=True)

    with col_right:
        st.subheader("Shipment Status")

        status_data = pd.DataFrame({
            'Status': ['In Transit', 'Delivered', 'Processing', 'Delayed'],
            'Count': [156, 289, 45, 11]
        })

        fig = px.pie(
            status_data,
            values='Count',
            names='Status',
            hole=0.3
        )
        st.plotly_chart(fig, use_container_width=True)

    # Warehouse Status
    st.subheader("Warehouse Status")

    warehouse_data = pd.DataFrame({
        'Warehouse': ['DC_East', 'DC_West', 'DC_Central', 'DC_South'],
        'Utilization': [85, 78, 91, 72],
        'Inbound': [45, 32, 67, 28],
        'Outbound': [123, 89, 145, 67],
        'Alerts': [2, 0, 3, 1]
    })

    st.dataframe(warehouse_data, use_container_width=True)

    # Auto-refresh
    import time
    time.sleep(30)
    st.experimental_rerun()

# Run with: streamlit run dashboard.py

---

高级分析技术

ABC-XYZ 分段分析

def abc_xyz_segmentation(sales_df):
    """
    Combined ABC-XYZ segmentation for inventory analytics

    ABC: Value classification (revenue contribution)
    XYZ: Variability classification (demand predictability)
    """

    # Calculate annual value per SKU
    sku_analysis = sales_df.groupby('sku').agg({
        'quantity': 'sum',
        'revenue': 'sum',
        'quantity': ['mean', 'std']
    })

    sku_analysis.columns = ['total_qty', 'total_revenue', 'avg_qty', 'std_qty']

    # ABC Classification (by revenue)
    sku_analysis = sku_analysis.sort_values('total_revenue', ascending=False)
    sku_analysis['cumulative_revenue_pct'] = (
        sku_analysis['total_revenue'].cumsum() /
        sku_analysis['total_revenue'].sum() * 100
    )

    def abc_class(pct):
        if pct <= 80:
            return 'A'
        elif pct <= 95:
            return 'B'
        else:
            return 'C'

    sku_analysis['abc'] = sku_analysis['cumulative_revenue_pct'].apply(abc_class)

    # XYZ Classification (by variability)
    sku_analysis['coefficient_of_variation'] = (
        sku_analysis['std_qty'] / sku_analysis['avg_qty']
    )

    def xyz_class(cv):
        if cv < 0.5:
            return 'X'  # Predictable
        elif cv < 1.0:
            return 'Y'  # Variable
        else:
            return 'Z'  # Erratic

    sku_analysis['xyz'] = sku_analysis['coefficient_of_variation'].apply(xyz_class)

    # Combined classification
    sku_analysis['segment'] = sku_analysis['abc'] + sku_analysis['xyz']

    # Strategy recommendations by segment
    strategy_map = {
        'AX': 'Tight control, daily review, high service level (99%)',
        'AY': 'Frequent review, safety stock, service level (98%)',
        'AZ': 'Close monitoring, high safety stock, service level (95%)',
        'BX': 'Weekly review, moderate safety stock, service level (97%)',
        'BY': 'Weekly review, standard policies, service level (95%)',
        'BZ': 'Bi-weekly review, higher safety stock, service level (90%)',
        'CX': 'Monthly review, min/max rules, service level (90%)',
        'CY': 'Monthly review, simple policies, service level (85%)',
        'CZ': 'Order on demand or don\'t stock, service level (80%)'
    }

    sku_analysis['strategy'] = sku_analysis['segment'].map(strategy_map)

    # Segment summary
    segment_summary = sku_analysis.groupby('segment').agg({
        'sku': 'count',
        'total_revenue': 'sum',
        'total_qty': 'sum'
    }).round(0)

    segment_summary['pct_skus'] = (
        segment_summary['sku'] / segment_summary['sku'].sum() * 100
    )
    segment_summary['pct_revenue'] = (
        segment_summary['total_revenue'] / segment_summary['total_revenue'].sum() * 100
    )

    return sku_analysis, segment_summary

# Visualization
import seaborn as sns
import matplotlib.pyplot as plt

def visualize_abc_xyz(sku_analysis):
    """Create heatmap of ABC-XYZ segmentation"""

    # Create pivot table
    pivot = sku_analysis.pivot_table(
        values='sku',
        index='abc',
        columns='xyz',
        aggfunc='count',
        fill_value=0
    )

    plt.figure(figsize=(10, 6))
    sns.heatmap(pivot, annot=True, fmt='d', cmap='YlOrRd')
    plt.title('ABC-XYZ Segmentation Matrix (SKU Count)')
    plt.ylabel('ABC Class (Value)')
    plt.xlabel('XYZ Class (Variability)')
    plt.show()

服务成本分析

def cost_to_serve_analysis(orders_df, customers_df):
    """
    Calculate cost-to-serve by customer

    Helps identify profitable vs. unprofitable customers
    """

    # Join order and customer data
    analysis = orders_df.merge(customers_df, on='customer_id')

    # Calculate various cost components per customer
    customer_cts = analysis.groupby('customer_id').agg({
        # Revenue
        'order_value': 'sum',

        # Order processing costs
        'order_id': 'count',  # number of orders

        # Transportation costs
        'freight_cost': 'sum',

        # Warehouse costs (could be activity-based)
        'pick_cost': 'sum',
        'pack_cost': 'sum',

        # Returns costs
        'return_cost': 'sum',

        # Service costs
        'customer_service_calls': 'sum'
    })

    # Cost assumptions
    ORDER_PROCESSING_COST = 15  # $ per order
    CS_CALL_COST = 25  # $ per call

    # Calculate total costs
    customer_cts['order_processing_cost'] = (
        customer_cts['order_id'] * ORDER_PROCESSING_COST
    )
    customer_cts['customer_service_cost'] = (
        customer_cts['customer_service_calls'] * CS_CALL_COST
    )

    customer_cts['total_costs'] = (
        customer_cts['freight_cost'] +
        customer_cts['pick_cost'] +
        customer_cts['pack_cost'] +
        customer_cts['return_cost'] +
        customer_cts['order_processing_cost'] +
        customer_cts['customer_service_cost']
    )

    customer_cts['revenue'] = customer_cts['order_value']

    # Calculate profitability
    customer_cts['gross_margin'] = customer_cts['revenue'] * 0.35  # 35% margin
    customer_cts['net_profit'] = (
        customer_cts['gross_margin'] - customer_cts['total_costs']
    )
    customer_cts['profit_margin_pct'] = (
        customer_cts['net_profit'] / customer_cts['revenue'] * 100
    )

    # Cost-to-serve percentage
    customer_cts['cost_to_serve_pct'] = (
        customer_cts['total_costs'] / customer_cts['revenue'] * 100
    )

    # Classify customers
    def classify_customer(row):
        if row['profit_margin_pct'] > 10:
            return 'Profitable'
        elif row['profit_margin_pct'] > 0:
            return 'Marginally Profitable'
        else:
            return 'Unprofitable'

    customer_cts['classification'] = customer_cts.apply(classify_customer, axis=1)

    # Customer profitability quadrant
    # High Revenue + High Profit = Protect
    # High Revenue + Low Profit = Improve
    # Low Revenue + High Profit = Grow
    # Low Revenue + Low Profit = Rationalize

    revenue_median = customer_cts['revenue'].median()
    profit_median = customer_cts['net_profit'].median()

    def quadrant(row):
        if row['revenue'] > revenue_median and row['net_profit'] > profit_median:
            return 'Protect'
        elif row['revenue'] > revenue_median:
            return 'Improve'
        elif row['net_profit'] > profit_median:
            return 'Grow'
        else:
            return 'Rationalize'

    customer_cts['quadrant'] = customer_cts.apply(quadrant, axis=1)

    return customer_cts

# Visualization
def plot_cost_to_serve(customer_cts):
    """Visualize cost-to-serve analysis"""

    fig, axes = plt.subplots(2, 2, figsize=(15, 12))

    # 1. Revenue vs. Cost scatter
    axes[0, 0].scatter(
        customer_cts['revenue'],
        customer_cts['total_costs'],
        c=customer_cts['profit_margin_pct'],
        cmap='RdYlGn',
        s=100,
        alpha=0.6
    )
    axes[0, 0].plot([0, customer_cts['revenue'].max()],
                    [0, customer_cts['revenue'].max()],
                    'r--', label='Break-even')
    axes[0, 0].set_xlabel('Revenue ($)')
    axes[0, 0].set_ylabel('Total Costs ($)')
    axes[0, 0].set_title('Revenue vs. Costs by Customer')
    axes[0, 0].legend()

    # 2. Cost-to-Serve Distribution
    customer_cts['cost_to_serve_pct'].hist(bins=30, ax=axes[0, 1])
    axes[0, 1].axvline(customer_cts['cost_to_serve_pct'].median(),
                      color='r', linestyle='--', label='Median')
    axes[0, 1].set_xlabel('Cost-to-Serve (%)')
    axes[0, 1].set_ylabel('Number of Customers')
    axes[0, 1].set_title('Cost-to-Serve Distribution')
    axes[0, 1].legend()

    # 3. Profitability by Classification
    classification_summary = customer_cts.groupby('classification').agg({
        'customer_id': 'count',
        'revenue': 'sum',
        'net_profit': 'sum'
    })

    classification_summary.plot(kind='bar', y='net_profit', ax=axes[1, 0])
    axes[1, 0].set_title('Profit by Customer Classification')
    axes[1, 0].set_xlabel('Classification')
    axes[1, 0].set_ylabel('Net Profit ($)')

    # 4. Quadrant Analysis
    quadrant_counts = customer_cts['quadrant'].value_counts()
    axes[1, 1].pie(quadrant_counts.values, labels=quadrant_counts.index,
                   autopct='%1.1f%%')
    axes[1, 1].set_title('Customer Segmentation Quadrants')

    plt.tight_layout()
    plt.show()

帕累托分析（80/20 法则）

def pareto_analysis(df, item_col, value_col, top_n=None):
    """
    Perform Pareto analysis (80/20 rule)

    Useful for identifying vital few vs. trivial many
    """

    # Aggregate by item
    pareto_df = df.groupby(item_col)[value_col].sum().reset_index()
    pareto_df = pareto_df.sort_values(value_col, ascending=False)

    # Calculate cumulative percentages
    total = pareto_df[value_col].sum()
    pareto_df['cumulative_value'] = pareto_df[value_col].cumsum()
    pareto_df['cumulative_pct'] = (pareto_df['cumulative_value'] / total) * 100
    pareto_df['pct_of_total'] = (pareto_df[value_col] / total) * 100

    # Find 80% threshold
    threshold_idx = (pareto_df['cumulative_pct'] <= 80).sum()
    pct_items_for_80 = (threshold_idx / len(pareto_df)) * 100

    print(f"Pareto Insight: {threshold_idx} items ({pct_items_for_80:.1f}%) "
          f"account for 80% of {value_col}")

    # Visualization
    if top_n is None:
        top_n = min(20, len(pareto_df))

    plot_df = pareto_df.head(top_n)

    fig, ax1 = plt.subplots(figsize=(14, 6))

    # Bar chart
    ax1.bar(range(len(plot_df)), plot_df[value_col], color='steelblue', alpha=0.7)
    ax1.set_xlabel(item_col)
    ax1.set_ylabel(value_col, color='steelblue')
    ax1.tick_params(axis='y', labelcolor='steelblue')

    # Line chart for cumulative %
    ax2 = ax1.twinx()
    ax2.plot(range(len(plot_df)), plot_df['cumulative_pct'],
             color='red', marker='o', linewidth=2)
    ax2.axhline(y=80, color='green', linestyle='--', label='80% threshold')
    ax2.set_ylabel('Cumulative %', color='red')
    ax2.tick_params(axis='y', labelcolor='red')
    ax2.set_ylim(0, 100)
    ax2.legend()

    plt.title(f'Pareto Analysis: Top {top_n} {item_col}')
    plt.xticks(range(len(plot_df)), plot_df[item_col], rotation=45, ha='right')
    plt.tight_layout()
    plt.show()

    return pareto_df

# Example usage
pareto_results = pareto_analysis(sales_df, 'sku', 'revenue', top_n=20)

---

工具与技术

Python 库

数据处理与分析：

• pandas：数据处理与分析
• numpy：数值计算
• polars：高性能 DataFrame（比 pandas 更快）
• dask：用于大型数据集的并行计算

可视化：

• matplotlib：基础绘图
• seaborn：统计可视化
• plotly：交互式图表和仪表板
• altair：声明式可视化

仪表板与 BI：

• streamlit：快速构建 Web 应用和仪表板
• dash (Plotly)：企业级仪表板
• panel (HoloViz)：灵活的仪表板
• voila：将 Jupyter 笔记本用作 Web 应用

数据库与数据仓库：

• sqlalchemy：SQL 工具包
• psycopg2：PostgreSQL 适配器
• pyodbc：ODBC 数据库连接
• snowflake-connector-python：Snowflake 连接器

统计分析：

• scipy：科学计算与统计
• statsmodels：统计模型
• scikit-learn：机器学习指标

商业智能工具

商业软件：

• Tableau：领先的可视化与 BI 平台
• Power BI (Microsoft)：与 Microsoft 生态系统集成
• Qlik Sense：关联式分析引擎
• Looker (Google)：云原生 BI
• Domo：基于云的 BI 平台
• ThoughtSpot：AI 驱动的分析

开源：

• Apache Superset：现代数据探索平台
• Metabase：人人都能使用的简易 BI
• Redash：连接并可视化数据
• Grafana：监控与可观测性仪表板

供应链专用平台

企业级：

• SAP Analytics Cloud：与 SAP S/4HANA 集成
• Oracle Analytics：适用于 Oracle 生态系统的 BI
• Blue Yonder (JDA)：供应链分析套件
• Kinaxis RapidResponse：并发计划平台
• o9 Solutions：数字大脑平台
• LLamasoft：供应链分析（现属 Coupa）

专业平台：

• Llamasoft Supply Chain Guru：网络建模与分析
• FourKites：供应链实时可视性
• project44：供应链可视性平台
• Shippeo：运输可视性

---

数据集成与 ETL

构建数据管道

import pandas as pd
from sqlalchemy import create_engine
import logging

class SupplyChainETL:
    """
    Extract, Transform, Load pipeline for supply chain data
    """

    def __init__(self, source_configs, target_config):
        self.source_configs = source_configs
        self.target_config = target_config
        self.logger = logging.getLogger(__name__)

    def extract_from_sql(self, config):
        """Extract data from SQL database"""

        try:
            engine = create_engine(config['connection_string'])

            query = config.get('query') or f"SELECT * FROM {config['table']}"

            df = pd.read_sql(query, engine)

            self.logger.info(f"Extracted {len(df)} rows from {config['source']}")

            return df

        except Exception as e:
            self.logger.error(f"Error extracting from {config['source']}: {e}")
            raise

    def extract_from_api(self, config):
        """Extract data from REST API"""

        import requests

        try:
            response = requests.get(
                config['url'],
                headers=config.get('headers', {}),
                params=config.get('params', {})
            )

            response.raise_for_status()

            data = response.json()
            df = pd.DataFrame(data[config['data_key']])

            self.logger.info(f"Extracted {len(df)} rows from {config['source']}")

            return df

        except Exception as e:
            self.logger.error(f"Error extracting from API {config['source']}: {e}")
            raise

    def transform_orders(self, orders_df):
        """Transform orders data"""

        # Data cleaning
        orders_df = orders_df.drop_duplicates(subset=['order_id'])

        # Data type conversions
        orders_df['order_date'] = pd.to_datetime(orders_df['order_date'])
        orders_df['ship_date'] = pd.to_datetime(orders_df['ship_date'])
        orders_df['delivery_date'] = pd.to_datetime(orders_df['delivery_date'])

        # Derived fields
        orders_df['order_to_ship_days'] = (
            orders_df['ship_date'] - orders_df['order_date']
        ).dt.days

        orders_df['ship_to_delivery_days'] = (
            orders_df['delivery_date'] - orders_df['ship_date']
        ).dt.days

        orders_df['on_time'] = (
            orders_df['delivery_date'] <= orders_df['promised_date']
        ).astype(int)

        # Categorization
        orders_df['order_size_category'] = pd.cut(
            orders_df['order_value'],
            bins=[0, 100, 500, 1000, float('inf')],
            labels=['Small', 'Medium', 'Large', 'XLarge']
        )

        # Data quality checks
        null_counts = orders_df.isnull().sum()
        if null_counts.any():
            self.logger.warning(f"Null values found: {null_counts[null_counts > 0]}")

        return orders_df

    def load_to_warehouse(self, df, table_name):
        """Load transformed data to data warehouse"""

        try:
            engine = create_engine(self.target_config['connection_string'])

            df.to_sql(
                table_name,
                engine,
                if_exists='replace',  # or 'append'
                index=False,
                chunksize=1000
            )

            self.logger.info(f"Loaded {len(df)} rows to {table_name}")

        except Exception as e:
            self.logger.error(f"Error loading to {table_name}: {e}")
            raise

    def run_pipeline(self):
        """Execute full ETL pipeline"""

        self.logger.info("Starting ETL pipeline...")

        # Extract
        orders_df = self.extract_from_sql(self.source_configs['orders'])
        shipments_df = self.extract_from_sql(self.source_configs['shipments'])
        inventory_df = self.extract_from_api(self.source_configs['inventory_api'])

        # Transform
        orders_clean = self.transform_orders(orders_df)

        # Combine/join datasets as needed
        # ... additional transformations

        # Load
        self.load_to_warehouse(orders_clean, 'fact_orders')
        self.load_to_warehouse(shipments_df, 'fact_shipments')
        self.load_to_warehouse(inventory_df, 'fact_inventory')

        self.logger.info("ETL pipeline completed successfully")

# Configuration
source_configs = {
    'orders': {
        'source': 'ERP',
        'connection_string': 'postgresql://user:pass@host:5432/erp_db',
        'table': 'orders'
    },
    'shipments': {
        'source': 'TMS',
        'connection_string': 'postgresql://user:pass@host:5432/tms_db',
        'table': 'shipments'
    },
    'inventory_api': {
        'source': 'WMS_API',
        'url': 'https://api.wms.com/v1/inventory',
        'headers': {'Authorization': 'Bearer token'},
        'data_key': 'inventory'
    }
}

target_config = {
    'connection_string': 'postgresql://user:pass@host:5432/analytics_dw'
}

# Run ETL
etl = SupplyChainETL(source_configs, target_config)
etl.run_pipeline()

---

常见挑战与解决方案

挑战：数据质量问题

问题：

• 数据缺失、重复或不一致
• 各系统中的数据格式不同
• 数据陈旧或不准确

解决方案：

• 在 ETL 管道中实施数据质量检查
• 创建显示完整性和准确性的数据质量仪表板
• 制定数据治理政策
• 使用数据探查工具识别问题
• 自动执行数据验证规则

def data_quality_report(df):
    """Generate data quality report"""

    report = {
        'total_rows': len(df),
        'total_columns': len(df.columns),
        'memory_usage_mb': df.memory_usage(deep=True).sum() / 1024**2
    }

    # Missing values
    missing = df.isnull().sum()
    report['missing_values'] = missing[missing > 0].to_dict()
    report['completeness_pct'] = (1 - df.isnull().sum().sum() /
                                   (len(df) * len(df.columns))) * 100

    # Duplicates
    report['duplicate_rows'] = df.duplicated().sum()

    # Data types
    report['data_types'] = df.dtypes.value_counts().to_dict()

    # Outliers (for numeric columns)
    numeric_cols = df.select_dtypes(include=[np.number]).columns
    outliers = {}
    for col in numeric_cols:
        Q1 = df[col].quantile(0.25)
        Q3 = df[col].quantile(0.75)
        IQR = Q3 - Q1
        outliers[col] = ((df[col] < (Q1 - 1.5 * IQR)) |
                        (df[col] > (Q3 + 1.5 * IQR))).sum()
    report['outliers'] = outliers

    return report

挑战：数据系统孤岛

问题：

• 数据分散在 ERP、WMS、TMS 等系统中
• 缺少统一的可信数据源
• 需要手动整合数据

解决方案：

• 构建集中式数据仓库或数据湖
• 实施主数据管理（MDM）
• 使用 ETL/ELT 工具（Apache Airflow、Fivetran、Stitch）
• 创建数据集成层（API 网关）
• 统一各系统的数据模型

挑战：实时分析需求

问题：

• 批处理速度太慢，无法满足运营决策需求
• 需要获取最新数据的可视性

解决方案：

• 实施流式数据管道（Apache Kafka、AWS Kinesis）
• 使用实时数据库（Redis、TimescaleDB）
• 创建可自动刷新的运营仪表板
• 设置事件驱动架构
• 使用变更数据捕获（CDC）同步数据库

挑战：从数据中获取可执行的洞察

问题：

• 有数据却无法据此采取行动
• 指标数量过多
• 难以找出根本原因

解决方案：

• 重点关注先行指标而非滞后指标
• 为异常情况设置自动告警
• 使用仪表板的下钻功能
• 应用根因分析方法
• 创建以行动为导向的报告（而不只是描述性统计）

---

输出格式

分析报告模板

执行摘要：

• 主要发现（3-5 条）
• 需要关注的关键问题
• 改进机会
• 建议采取的行动

绩效概览：

KPI	当前值	目标值	上期	同比变化	状态
完美订单率	94.2%	95%	93.5%	+2.1%	⚠️
准时交付率	96.8%	98%	95.2%	+3.4%	⚠️
库存周转次数	6.2x	7.0x	5.8x	+6.9%	⚠️
订单周期时间	2.1 天	2.0 天	2.3 天	-8.7%	✅
运费/收入	8.2%	7.5%	8.5%	-3.5%	⚠️

趋势分析：

• 关键指标的时间序列图表
• 识别季节性模式
• 异常检测重点

根因分析：

• 深入分析表现不佳的领域
• 问题的帕累托分析
• 相关性分析

建议：

1. 立即行动（0-30 天）

   • 具体、可执行的事项
   • 负责人和截止日期

2. 短期举措（1-3 个月）

   • 流程改进
   • 系统增强

3. 战略计划（3-12 个月）

   • 长期转型
   • 投资需求

附录：

• 详细的方法论
• 数据来源和质量说明
• 假设和计算

---

要提出的问题

如果需要更多背景信息：

1. 哪些供应链流程需要分析？（采购、库存、运输等）
2. 谁会使用这些分析结果？（高管、计划人员、分析师）
3. 需要解答哪些关键业务问题？
4. 有哪些可用的数据来源？（ERP、WMS、TMS、电子表格）
5. 当前的分析能力处于什么水平？
6. 你正在使用或偏好哪些工具？（Tableau、Power BI、Python）
7. 目前跟踪哪些关键 KPI？
8. 需要多高的报告频率？（实时、每日、每周、每月）

---

相关技能

• demand-forecasting：用于预测分析和预测模型
• optimization-modeling：用于规范性分析和优化
• ml-supply-chain：用于高级机器学习应用
• prescriptive-analytics：用于决策支持和建议
• digital-twin-modeling：用于仿真和情景分析
• inventory-optimization：用于库存专属 KPI 和分析
• network-design：用于网络绩效分析
• freight-optimization：用于运输分析