如何创建流程挖掘事件日志

方法二：使用SQL创建事件日志

对于大型数据集或需要定期执行的提取任务，SQL更高效，也更易于重复执行。关键技术是使用UNION ALL，将多个查询合并为一个结果集。

了解UNION ALL

UNION ALL会将多个SELECT语句的结果依次合并。每个SELECT都会向最终结果中添加行。所有SELECT语句必须包含相同数量的列，且数据类型兼容。

完整SQL示例

以下SQL查询会创建Pizza Palace事件日志：

-- Event Log Extraction for Pizza Palace
-- This query combines multiple event types into a single event log
-- Each SELECT block represents one activity type

-- Event 1: Order Placed
-- Source: orders table
-- This captures when customers submit their orders
SELECT 
    o.id AS case_id,                          -- The order ID is our case identifier
    o.created_at AS timestamp,                -- When the order was placed
    'Order Placed' AS activity,               -- The activity name (hardcoded)
    o.customer_name AS customer,              -- Case attribute: who ordered
    o.total_amount AS order_value,            -- Case attribute: order value
    NULL AS driver,                           -- Not applicable for this event
    NULL AS payment_method                    -- Not applicable for this event
FROM orders o
WHERE o.created_at >= '2025-01-01'            -- Filter to your desired date range

UNION ALL

-- Event 2: Payment Received
-- Source: payments table
-- This captures successful payment processing
SELECT 
    p.order_id AS case_id,
    p.payment_time AS timestamp,
    'Payment Received' AS activity,
    o.customer_name AS customer,              -- Join to get case attributes
    o.total_amount AS order_value,
    NULL AS driver,
    p.payment_method AS payment_method        -- Event-specific attribute
FROM payments p
JOIN orders o ON p.order_id = o.id            -- Join to get order details
WHERE p.payment_time >= '2025-01-01'
  AND p.status = 'successful'                 -- Only include successful payments

UNION ALL

-- Event 3: Order Sent to Kitchen
-- Source: kitchen_queue table
-- This captures when the kitchen starts working on the order
SELECT 
    k.order_id AS case_id,
    k.queue_entry_time AS timestamp,
    'Order Sent to Kitchen' AS activity,
    o.customer_name AS customer,
    o.total_amount AS order_value,
    NULL AS driver,
    NULL AS payment_method
FROM kitchen_queue k
JOIN orders o ON k.order_id = o.id
WHERE k.queue_entry_time >= '2025-01-01'

UNION ALL

-- Event 4: Order Ready
-- Source: kitchen_queue table (different timestamp field)
-- This is an inferred event based on when the kitchen marked it complete
SELECT 
    k.order_id AS case_id,
    k.completed_time AS timestamp,            -- Different timestamp than entry
    'Order Ready' AS activity,
    o.customer_name AS customer,
    o.total_amount AS order_value,
    NULL AS driver,
    NULL AS payment_method
FROM kitchen_queue k
JOIN orders o ON k.order_id = o.id
WHERE k.completed_time >= '2025-01-01'
  AND k.completed_time IS NOT NULL            -- Only include completed orders

UNION ALL

-- Event 5: Assigned to Driver
-- Source: delivery_assignments table
-- This captures when a driver is assigned to deliver the order
SELECT 
    d.order_id AS case_id,
    d.assigned_at AS timestamp,
    'Assigned to Driver' AS activity,
    o.customer_name AS customer,
    o.total_amount AS order_value,
    d.driver_name AS driver,                  -- Event-specific attribute
    NULL AS payment_method
FROM delivery_assignments d
JOIN orders o ON d.order_id = o.id
WHERE d.assigned_at >= '2025-01-01'

UNION ALL

-- Event 6: Delivery Completed
-- Source: delivery_assignments table (different timestamp field)
-- This captures when the order was delivered to the customer
SELECT 
    d.order_id AS case_id,
    d.delivered_at AS timestamp,
    'Delivery Completed' AS activity,
    o.customer_name AS customer,
    o.total_amount AS order_value,
    d.driver_name AS driver,
    NULL AS payment_method
FROM delivery_assignments d
JOIN orders o ON d.order_id = o.id
WHERE d.delivered_at >= '2025-01-01'
  AND d.delivered_at IS NOT NULL              -- Only include completed deliveries

-- Final ordering: by case, then by time
-- This makes the event log easy to read and follow
ORDER BY case_id, timestamp;

如何扩展此查询

如需向日志中添加更多事件：

1. 复制其中一个SELECT代码块作为模板
2. 更改表名为您的来源表
3. 更新时间戳字段为正确的列
4. 更改活动名称以描述该事件
5. 按需调整属性
6. 添加适当的WHERE条件以筛选数据

例如，如需添加“尝试配送”事件：

UNION ALL

-- Event 7: Delivery Attempted
-- Add this to track failed delivery attempts
SELECT 
    d.order_id AS case_id,
    d.attempt_time AS timestamp,
    'Delivery Attempted' AS activity,
    o.customer_name AS customer,
    o.total_amount AS order_value,
    d.driver_name AS driver,
    NULL AS payment_method
FROM delivery_attempts d
JOIN orders o ON d.order_id = o.id
WHERE d.attempt_time >= '2025-01-01'

下一步：将事件日志加载到流程挖掘工具

将事件日志创建为CSV文件或数据库导出文件后，您就可以将其加载到流程挖掘工具中。大多数工具的操作流程类似：

1. 上传文件，或将流程挖掘工具连接到已提取的数据。
2. 映射列（Case ID、Timestamp、Activity）
3. 配置其他属性
4. 生成流程图

像ProcessMind这样的现代流程挖掘工具可以简化这一过程。上传事件日志数据后，工具会自动将流程可视化，揭示瓶颈、变体和改进机会，帮助您降低成本、优化运营流程。

结论

创建流程挖掘事件日志不需要专用工具或深厚的技术知识。其核心是将流程挖掘数据整理成一张包含三个必需列的表：Case ID、Timestamp和Activity。

无论是使用Excel处理较小的数据集，还是使用SQL处理更大、更复杂的提取任务，基本原则都相同：

1. 确定要跟踪的事件
2. 找到每种事件类型对应的时间戳
3. 将所有内容合并到一张表中
4. 添加属性，丰富分析维度

最难的部分不是技术提取，而是充分了解业务运营，从而判断哪些事件真正重要。先从下单、订单完成等明显事件开始，再根据流程挖掘工具揭示的洞察逐步补充细节。

想进一步深入？请查看我们的持续流程改进页面，详细了解Purchase to Pay、Purchase to Pay、Order to Cash和Accounts Payable等常见流程的活动和数据要求。这些资源还提供SAP、Oracle和Microsoft Dynamics等知名系统的数据模板，帮助您更快创建事件日志。