Machine Learning - Aggregate granular cluster predictions on denormalized data

Question

I have a question that occurred when thinking about the following use case: A bank wants to group their customers into segments using the database tables 'customer', 'account' and 'transaction'. The first table contains customer information (e.g. id, birth date, sex, age), the second one account information (e.g. id, cust_id, balance, product) and the last one transactions (e.g. id, acct_id, value). One customer can have multiple accounts and one account can have multiple associated transactions.

Based on this information the bank wants to cluster their customers into segments. Traditionally, they would manually create segments like ‘age < 21’, ‘balance > 10000’, etc. But, now they want a machine to tell them which customers are similar (so they can be targeted for marketing).

There is no specific output column and the data is not labeled, thus, unsupervised machine learning algorithms like K-Means are well suited. However, K-Means for example does require the data in one single table. One cannot input multiple tables into the algorithm and link the tables through foreign constraints (at least not using common implementations like scikit-learn, h2o.ai, mahout, etc.). Therefore, the three tables need to be joined into one large table. This large table will have one row per unique transaction, since this is the most detailed information. I believe this step is called denormalization (or normalization for machine learning).

Only then the ML algorithm (e.g. K-Means) can be executed to gain a list of which rows belong to which cluster. Then, as a result of the cluster algorithm the output is X clusters with Y transactions assigned each. An example could be:

• Transaction 1 = Cluster 1
• Transaction 2 = Cluster 1
• Transaction 3 = Cluster 2
• Transaction 4 = Cluster 1
• Transaction 5 = Cluster 3
• Transaction 6 = Cluster 2
• Transaction 7 = Cluster 3

However, the initial question was to cluster the customers (not transactions) into segments. For the sake of the above example lets say transactions 1 to 3 belong to customer 1 and the rest to customer 2. Transactions that belong to customer 1 are included in clusters 1 and 2 and transactions belonging to customer 2 are in clusters 1, 2 and 3.

Finally my question: Is there a best practice for this? How does the bank now get k clusters which include similar customers from this?

Answer 1

1. You want the data in a format optimal for analysis, not work on your raw data. Clustering is expensive, and the performance overhead of working on multiple tables will usually make it unacceptably slow.

2. You need to carefully preprocess the data to guide your algorithm. Just running k-means on age, transaction amount will return garbage results. And of course, preprocessing requires a copy of the data.

3. You also need to aggregate the data appropriately. If you want to cluster transactions, then a simple "join" may be okay. But to cluster customers, you need to find an appropriate aggregation (read: "feature engineering") of your data, one row per customer.

All of above steps provide good reasons to copy your data from your storage format (e.g. SQL tables) into a separate analysis format (e.g. CSV files) to load into your analysis tool (e.g. sklearn, R, ELKI, Weka).