How to Create a Process Mining Event Log

Method 2: Creating an Event Log with SQL

For larger datasets or regular extractions, SQL is more efficient and repeatable. The key technique is using UNION ALL to combine multiple queries into one result set.

Understanding UNION ALL

UNION ALL stacks the results of multiple SELECT statements on top of each other. Each SELECT becomes a set of rows in your final result. All SELECTs must have the same number of columns with compatible data types.

Complete SQL Example

Here’s a SQL query that creates a Pizza Palace event log:

-- 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;

How to Extend This Query

To add more events to your log:

1. Copy one of the SELECT blocks as a template
2. Change the table name to your source table
3. Update the timestamp field to the correct column
4. Change the activity name to describe the event
5. Adjust the attributes as needed
6. Add appropriate WHERE conditions to filter the data

For example, to add a “Delivery Attempted” event:

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'

What’s Next? Load Your Event Log into a Process Mining Tool

Once you’ve created your event log as a CSV file or database export, you’re ready to load it into a process mining tool. Most tools follow a similar process:

1. Upload your file or connect the process mining tool to your extracted data.
2. Map your columns (Case ID, Timestamp, Activity)
3. Configure any additional attributes
4. Generate your process map

Modern process mining tools like ProcessMind make this process straightforward. Simply upload your event log, and the tool automatically visualizes your process, revealing bottlenecks, variations, and improvement opportunities.

Conclusion

Creating a process mining event log doesn’t require specialized tools or deep technical knowledge. At its core, you’re simply organizing your data into a table with three essential columns: Case ID, Timestamp, and Activity.

Whether you use Excel for smaller datasets or SQL for larger, more complex extractions, the principles remain the same:

1. Identify the events you want to track
2. Find the timestamp for each event type
3. Combine everything into a single table
4. Add attributes to enrich your analysis

The hardest part isn’t the technical extraction, it’s understanding your process well enough to know which events matter. Start with the obvious events (order placed, order completed) and gradually add more detail as you learn what insights your process mining tool reveals.

Ready to go deeper? Explore our continuous process improvement pages where you’ll find detailed information about activities and data requirements for popular processes like Purchase to Pay, Order to Cash, and Accounts Payable. These resources include data templates for well-known systems like SAP, Oracle, and Microsoft Dynamics, giving you a head start on your event log creation journey.