1. Preparing the event data
In this last chapter we will undertake the analysis of a process from the very first until the last step, using everything we have seen so far. The process under consideration is an order-to-cash process, one of the most common processes found in traditional organizations.
2. Sales
The process contains different stages, touching different departments of the organization:
- ¬The sales department, which is responsible for the handling for sales orders and quotations.
3. Purchasing
- The purchasing department, appointed with all the activities related to the acquisition of materials and other supplies
4. Manufacturing
- Manufacturing, responsible for producing and assembling products
5. Packaging & Delivery
- Packaging & Delivery, taking care that all products get packed and shipped to the customer.
6. Accounting
And finally
- The Accounting department, which is responsible for everything related to invoices, credit notes and credit collection.
In order to make sure the process runs smoothly, it is important that all these departments work closely together. However, in practice, it is common that each division still maintains different rules and systems. Obtaining all the data to analyze the process and combining them might thus be a tough stage to go through.
Even if they use a single ERP system, as in our example, there is still a transformation needed from the relational data to event data, which is mandatory before we can start really analyzing to end-to-end process.
7. Sales (1/5)
For example, consider the sales department. This department is the origin of the order-to-cash process, as it receives sales orders from its customers. But even earlier, it also sends out quotations, after customers have requested them.
8. Sales (2/5)
After a request for a quotation is received, the sales departments starts gathering information from various partners.
9. Sales (3/5)
Using this information, it prepares the offer.
10. Sales (4/5)
Subsequently, the offer is send out, and if needed a reminder is send after a certain time.
11. Sales (5/5)
Finally, a sales order is received, or the offer is cancelled.
In our example, information on this part of the process is stored in several different tables.
12. Data on Quotations 1
Firstly, the quotation table include various information on quotations, such as when the request was received, if and when various departments or partners were consulted etc.
13. Data on Quotations 2
Secondly, we have information on so-called validations. These are actually assessments of quotation request, to see whether the request is valid and the customer does not imposes a risk for the company. Here, we also find if and when a request was declined, and by whom.
14. Data on Quotations 3
Finally, the preparations table has information on the actual construction of the quotations: when it started, when it was completed, and by whom.
15. Data on Quotations 4
Important to note are these fields ending in at, which means that they contain timestamps. As you know by now, each event needs a timestamp. The fieldnames tell us which event each of the timestamps relate to.
16. Data on Quotations 5
We can use these fieldnames to get a first idea of the different activity types that are recorded.
17. Data on Quotations 6
Also important are the fields ending in by, which tells us who is responsible for certain actions. This will give us the resource information.
Finally, you can see that some activities, like the preparation and the validation have actually two timestamp: a start and a complete one. This means that we also have transactional data for these events.
18. dplyr
In order to perform these transformations, we will use some tools from the tidyverse: dplyr to create new variables, combine rows and perform joins,
19. tidyr
and tidyr's pivot wider and pivot longer to reshape our data.
20. Let's practice!
Let's see how we can transform these datasets into the appropriate event data structure.