Event log per Process Mining: come crearlo

Metodo 2: creare un event log con SQL

Per dataset più grandi o estrazioni periodiche, SQL è più efficiente e ripetibile. La tecnica chiave è usare UNION ALL per unire più query in un unico set di risultati.

Capire UNION ALL

UNION ALL impila i risultati di più istruzioni SELECT una sopra l’altra. Ogni SELECT diventa un blocco di righe nel risultato finale. Tutte le SELECT devono avere lo stesso numero di colonne con tipi di dato compatibili.

Esempio SQL completo

Qui sotto trovi una query SQL che genera un event log per 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;

Come estendere questa query

Per aggiungere altri eventi al tuo log:

1. Copia uno dei blocchi SELECT come modello
2. Cambia il nome della tabella con la tabella sorgente
3. Aggiorna il campo timestamp con la colonna corretta
4. Modifica il nome dell’attività per descrivere l’evento
5. Adatta gli attributi secondo necessità
6. Aggiungi le condizioni WHERE appropriate per filtrare i dati

Ad esempio, per aggiungere un evento “Delivery Attempted”:

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'

E adesso? Carichi l’Event Log in uno strumento di Process Mining

Una volta creato l’Event Log come file CSV o come un’esportazione dal database, può caricarlo in uno strumento di Process Mining. La maggior parte degli strumenti segue un percorso simile:

1. Carichi il file oppure connetta lo strumento ai dati estratti.
2. Mappi le colonne (Case ID, Timestamp, Activity)
3. Configuri eventuali Attributi aggiuntivi
4. Generi la mappa di processo

Gli strumenti moderni di Process Mining, come ProcessMind , rendono il tutto semplice. Le basta caricare l’Event Log e lo strumento visualizza automaticamente il processo, evidenziando colli di bottiglia, variazioni e opportunità di miglioramento.

Conclusioni

Creare un Event Log per il Process Mining non richiede strumenti specializzati né competenze tecniche avanzate. In sostanza, si tratta di organizzare i dati in una tabella con tre colonne fondamentali: Case ID, Timestamp e Activity.

Che usi Excel per dataset piccoli o SQL per estrazioni più grandi e complesse, i principi restano gli stessi:

1. Identificare gli eventi da monitorare
2. Reperire il Timestamp per ogni tipo di evento
3. Riunire tutto in un’unica tabella
4. Aggiungere Attributi per arricchire l’analisi

La parte più impegnativa non è l’estrazione tecnica, ma comprendere a fondo il processo per capire quali eventi contano davvero. Parta dagli eventi evidenti (ordine inserito, ordine completato) e aggiunga via via più dettagli man mano che scopre gli spunti offerti dallo strumento di Process Mining.

Vuole approfondire? Visiti le nostre pagine sul miglioramento continuo dei processi , dove trova informazioni dettagliate su attività e requisiti dei dati per processi diffusi come Purchase to Pay , Order to Cash  e Accounts Payable . Troverà anche template di dati per sistemi noti come SAP, Oracle e Microsoft Dynamics, così da accelerare la creazione del suo Event Log.