Event data models (sometimes called event logs) are becoming more common these days for a number of reasons. They are really the easiest and most error-prone way to record all the relevant data from different processes, including business processes. Take Customer Journeys data from an online retailer as an example of Event Logs: they include user's behaviour on the web site, his interaction with the recommendation engines, the shopping cart, etc. Then the order is shipped and received by the user and the user may leave a review of the site and come back for another shopping experience. All the different business processes and systems involved are better positioned to log the relevant information right when they are executed.

If you build separate systems with their own data stores instead and try to integrate them later in order to analyze and process the data, you will be risking losing important information and introducing inconsistencies into the global dataset.

However, when your data about a customer is a stream of heterogenous events from different subsystems and business processes, querying this data can become a serious problem. For example its easy to store this type of data in an SQL database, but formulating analytical queries over such data can become a nightmare and a lot of them will end up being so inefficient that they will be impractical. So here is where PythonQL with its ability to work with nested data models comes to the rescue.

Event log data can be stored as relational or XML or even JSON data. The actual format doesn't matter that much, the issues of going from one format to another are rather trivial, compared to the difficulties one might encounter actually querying these data sets. So usually you would have a different schema for each different event type. In a relational database each different event type could have its own table, in JSON or XML storage they could all live in a single collection.

Let's focus on the logical part of the data and highlight the challenges that we'll face processing this data. We can model the data as if its already in Python's memory with the named tuples. So let take an artificial case from banking, where we focus on customer facing business processes and mix in a little bit of banks internal business processes. So we'll be interested in the following events:

• Customer opening an account with the bank
• Customer depositing/withdrawing money
• Customer applying for a loan
• Bank approving a loan
• Bank paying out a loan
• Customer making a repayment on a loan
• Bank's collection department contacting customer
• Customer closing an account with the bank

So lets do a few examples of different events. Let's start with customers opening up accounts. When opening their accounts, customers provide their personal data, we include it in the events. Let's define the named tuples for the open account events and the client data and client address fields.

from collections import namedtuple
from datetime import date

open_account_e = namedtuple('open',['event_name','client_id','date','client_data'])
client_data_e = namedtuple('client_data',['firstName','lastName','birthdate','address'])
address_e = namedtuple('address',['street','city','state','zip'])

e_1 = open_account_e('open', 1, date(2015,1,16), 
                     client_data('John', 'Smith', date(1968,12,5),
                          address('10 Main st.','Austin','TX',81234)))
e_2 = open_account_e('open', 2, date(2015,5,23), 
                     client_data('Mike', 'Leery', date(1974,8,29),
                          address('1043 Grand','San Francisco','CA',90841)))

We have created two open account events e_1 and e_2. All our events will have an event_name field which signifies the type of event we are dealing with, a client_id and a date field. Lets do one more event type, a bank's collection department contacting the client:

collection_e = namedtuple('collection',['event_name','client_id','date','dialogue'])

e_1 = collection('collection', 1, date(2016,10,15),
                  [ {"operator":"Hi sir, we're calling you about your loan"},
                    {"client":"Oh yes, I forgot to make the payment"},
                    {"operator":"Will you be able to make the payment soon?"},
                    {"client":"Yes, I will make the payment today"},
                    {"operator":"Thank you and have a good day!"} ])

We have created a collection event, which contains a dialogue between the operator and the client. The dialog is a list of utterances made by the operator or the client. So the event may include arbitrary complex data structures within it, as long as we are okay for them belonging to a single event. For example bundling all collection events into a single one is probably a bad idea.

First of all, we need to figure out what kind of queries we'll need to run. The event logs provide us with such rich data, that we can do some really powerful things with it. But some queries will be much easier to express and will run a lot more efficiently than others.

Traditionally, the simplest queries are selections. I.e. we select all or some types of events that belong to a particular customer or a group of customers. We will give an example of such queries as a warm up. But we want to focus on much more useful queries.

Aggregation and basic reporting comes next. Here start with a simple query to compute the balance of a customer for a specific date, and then generate a small report of average balance for each state.

Then we look at complex ad-hoc queries that test various hypothesis. For example we will test whether folks that

The last group of queries are process mining queries. Process mining is a new approach toward analyzing business processes via event logs. We will model our process as a linear process and will compute conversion rates for each state of the process.

Lets collect all of the events of a particular client:

[ select e for e in events where e.client_id == 1 ]

As you can see, this is a pretty simple query. Now let's get the customer journeys of all clients living in San Francisco. This is actually a pretty hard query, because the address information is present only in the open_account events.

[ select e
  for e in events
  group by e.client_id
        where [select e2
               for e2 in e
               where e2.event_name == 'open_account' and
                     e2.client_data.address.city == 'San Francisco']
]

As you can see, queries that select customer journeys on some fields that are only present in certain events are certainly doable, but are not that convenient to express and are usually rather inefficient. So its wise to build some pre-aggregated view of customer journeys and to bring out these fields to the customer journey level. Then you won't need to first fish out this data from some event in the customer journey, before doing the selection. But of course this is only possible once you know which fields need to be in the queries, so we always need the ability to query raw event data and to be able to formulate such conditions rather easily.

We have seen how we can select individual journeys or groups of journeys for further analysis. But lets do the analysis right inside our queries. The simplest aggregation query is to compute the balance of each of our customers (its an aggregation within the customer journey). The savings balance will be the sum of deposit transactions and received loans minus withdraws and loan repayments:

[ select client_id, balance
  for e in events
  group by e.client_id as client_id
  let balance = sum([ select e2.amount 
                     for e2 in e 
                     where e.event_name in ['deposit','loan']]) -
                sum([ select e2.amount 
                     for e2 in e 
                     where e.event_name in ['withdrawal', 'repayment']])
]

Again, such aggregates should be pre-computed for any reasonable bank, however one can never precompute all possible aggregates that might interest your analysts from the event logs, so sometimes these queries need to be run against raw log data. Here is a bit more complex query that looks like a real reporting query: let's compute the average balance of bank's customers per each state:

import numpy as np

[ select state, np.mean(balance)
  
  # First we compute the balance of each customer
  for e in events
  group by e.client_id as client_id
  let balance = sum([ select e2.amount 
                     for e2 in e 
                     where e.event_name in ['deposit','loan']]) -
                sum([ select e2.amount 
                     for e2 in e 
                     where e.event_name in ['withdrawal', 'repayment']])

  # Extract the state
  let state = [select e2.client_data.address.state as state 
               for e2 in e 
               where e2.event_name == 'open_account'][0]

  # Group by state
  group by state
]

Lets try some interesting ad-hoc queries that also require us to look at some patterns in the event sequences. We will expand on this further in the next section, but for now lets analyse this scenario. Suppose we are exploring our churn rate and we suspect that the collection department may be responsible for customer churn by communicating too often with the customers. Lets select customers that didn't pay the loan on time, got a large amount of calls from collections, then payed out the loan, but closed the account eventually. And we'll compare the churn rates of these customers.

So this will be a pretty complex query, let's do with a number of steps:

1. Group event log data by client_id
2. Count the number of collection calls, they all need to happen before repayment event
3. Filter out the customers who didn't repay the loan
4. Filter out the customers who didn't close the account within one month after this
5. Compute the churn rate for each group

[ # We will return churn_rates and number of customers for each
  # number of collection calls, with 0 - being customer that didn't
  # receive any

  select n_calls, n_custs, churn_rate
  
  # Group log data by client_id

  for e in events
  group by e.client_id as client_id

  # Compute the maximum number of collection calls before repayment
  # We will use a window query here to collect sequences of collection
  # events and then to pick the last such sequence

  n_calls = [ select len(w)
              for sliding window w in [ select e2
                                        for e2 in e
                                        where e2.event_name 
                                           in ['collection','repayment']]
              start when True
              end w_end when w_end.event_name == 'repayment' ][-1]

  # Let's only keep those who paid all their loans 

  where len([select e2 for e2 in e where e.event_name=='loan']) ==
        len([select e2 for e2 in e where e.event_name=='repayment'])

  # Lets now group the clients by number of calls
  group by n_calls
  
  # Compute the size of each group
  n_custs = len(e)

  # For each group, compute the number of customers who closed their accounts
  n_churned = len([ select cj
                    for cj in e
                    where [select e2 for e2 in cj where e2.event_name == 'close']])

  # Compute the churn rate
  churn_rate = n_churned / n_custs
]

So we went through a pretty tough query, but the good news is that these queries are quite doable and still rather declarative and easy to understand. So they won't be too much to expect from a data scientist when he needs to test some hypotheses about the data.

The final set of queries deals with process mining. We don't really have a complicated process with many different steps here, but we can look at repeat loan applications and map out the number of customers going through this process.

So let's consider customers who apply for and take out loans. We're interested in the following events then: apply, loan, repayment. And we'd like to build the following structure as the result of the query.

[{"loan_number":1, "result":[{"applied":350}, {"issued":200}, {"repaid":100"}]}, 
 {"loan_number":2, ...}, 
 ...]

Lets process each customer in turn and fill out this structure:

[
  select {"loan_number":loan_number, "result":result}

  # Group the events by client_id
  for e in events
  group by client_id

  # Select only the events we're interested and assign loan numbers to them
  for e3 in [ select e2.event_name as name, i//3 as loan_number
                   for e2 in e
                   where e2.event_name in ['apply','loan','repayment']]
                   count i]

  # Group by event name and loan number, and count the number of such clients
  group by e3.name as name, e3.loan_number as loan_number
  let count = len(e3)

  # Create the result structure
  let result = {name : count}

  # Group by the loan number
  group by loan_number
]

Event Log data model is incredibly useful and captures all the information we need to analyze our business processes and customer experience. However its challenging to make maximal use out of it. We believe the best approach is to:

• Build and maintain a number of pre-aggregated views that simplify common queries
• If the event log is small enough - query raw event log directly with a tool like PythonQL
• If the event log has grown very large - use a relational database to select relevant logs, then analyze them in PythonQL