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? Carica il tuo event log in uno strumento di Process Mining

Una volta creato l’event log come file CSV o export dal database, sei pronto a caricarlo in uno strumento di Process Mining. La procedura, in genere, è questa:

1. Fai l’upload del file o collega lo strumento ai dati estratti.
2. Mappa le colonne (Case ID, Timestamp, Attività)
3. Configura gli attributi aggiuntivi
4. Genera la mappa di processo

Gli strumenti moderni di Process Mining come ProcessMind rendono il tutto semplice: fai l’upload dell’event log e lo strumento visualizza automaticamente il processo, mettendo in luce colli di bottiglia, varianti e opportunità di miglioramento.

Conclusione

Creare un event log per il Process Mining non richiede strumenti specializzati né competenze tecniche avanzate. In sostanza, stai organizzando i tuoi dati in una tabella con tre colonne essenziali: Case ID, Timestamp e Attività.

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

1. Identifica gli eventi che vuoi tracciare
2. Trova il timestamp per ogni tipo di evento
3. Unisci tutto in un’unica tabella
4. Aggiungi attributi per arricchire l’analisi

La parte più difficile non è l’estrazione tecnica: è conoscere il processo abbastanza bene da capire quali eventi contano. Parti da quelli evidenti (ordine effettuato, ordine completato) e aggiungi dettagli man mano che scopri insight con il tuo strumento di Process Mining.

Vuoi approfondire? Esplora le nostre pagine sul miglioramento continuo dei processi dove trovi dettagli su attività e requisiti dati per processi come Purchase to Pay, Order to Cash e Accounts Payable. Troverai anche modelli di dati per sistemi diffusi come SAP, Oracle e Microsoft Dynamics: un ottimo punto di partenza per creare il tuo event log.