In our previous lesson, we transformed time series data by creating lag and rolling window features, enabling standard machine learning models to understand trends and seasonality. This was our first step in moving from raw, time-ordered data to a feature-rich tabular format.
Today, we will continue this journey by focusing on another, more complex type of time-ordered data: transactional data. This is the lifeblood of any fintech company. Every payment, transfer, or credit card swipe is a data point rich with behavioral signals. Your objective for this lesson is to learn how to engineer features from transactional data, specifically focusing on the classic RFM (Recency, Frequency, Monetary) framework and more general behavioral aggregations. Mastering these techniques will allow you to build features that can power everything from customer segmentation models to real-time fraud detection systems.
From Transactions to Customer Profiles: RFM Analysis
A raw log of transactions is a stream of events. To a model, it's just a long list. The first step in understanding customer behavior is to aggregate this stream into a static profile for each customer. The most established method for this is RFM analysis. It summarizes a customer's history along three simple but powerful dimensions:
- Recency: How recently did the customer transact?
- Frequency: How often do they transact?
- Monetary: How much do they spend?
The intuition is straightforward: customers who have purchased recently, purchase often, and spend a lot are typically your most valuable customers. Customers who haven't purchased in a long time might be at risk of churning. By segmenting customers based on these three scores, a business can tailor marketing campaigns, offer personalized discounts, or identify VIPs.
Calculating RFM in Python
Let's look at a practical, hands-on example of how to build these features using Python and pandas. The following video walks through a project that uses K-means clustering for customer segmentation, but the initial steps are a perfect demonstration of RFM feature engineering.
Hands On Data Science Project: Understand Customers with KMeans Clustering in Python
This video from the channel TrentDoesMath provides a clear, step-by-step guide to calculating RFM features from a transactional dataset.
First, watch the introduction from the beginning to understand the project's goal and the definition of RFM. Next, see how a 'sales line total' is calculated as a prerequisite for the Monetary value in the segment from here. The core aggregation logic is in the next part. Watch from aggregating the data to see how groupby() is used to compute the Monetary value (sum), Frequency (nunique), and the last invoice date for each customer. Finally, see how Recency is calculated by finding the difference between each customer's last purchase and a reference date. Watch the segment from calculating recency.
As you can see, the process involves a groupby operation on the customer_id followed by different aggregation functions (.agg()) for each RFM component.
Calculating RFM in SQL
In a data engineering or analysis context, you are just as likely to perform these calculations in SQL, especially when working with data warehouses. The logic is identical, but the syntax relies on SQL's aggregation and window functions.
This next video demonstrates the same RFM calculation process, but this time using SQL. Being able to articulate the process in both pandas and SQL is a significant asset in an interview.
How To Perform an RFM (Recency, Frequency, Monetary) Analysis Using SQL
This video from Jie Jenn shows how to perform an RFM analysis using SQL, which is a common task in data analytics and data engineering roles.
Watch how to calculate Recency using MAX(order_date) and the DATEDIFF function from here. Next, see how Frequency is calculated using COUNT(order_number) from this section. Then, observe the calculation for Monetary value by summing a calculated revenue column from this segment. Finally, pay close attention to the section from using the NTILE function. The NTILE window function is a powerful tool for automatically bucketing customers into quantiles (e.g., 5 groups from best to worst) for each RFM score. This is a common and efficient way to create customer segments.
Beyond RFM: Behavioral Aggregations for Real-Time Systems
RFM provides a static, high-level summary of a customer. However, for many critical fintech applications like real-time fraud detection, we need features that capture more dynamic, granular, and recent behavior. This is where behavioral aggregations come in.
Conceptually, this is very similar to the rolling window features we created in the last lesson. The difference is that instead of applying a window over a single, continuous time series, we apply it to the sequence of transactions belonging to a specific entity (e.g., a customer or a credit card).
The architecture of a real-time fraud detection system often looks something like this:
To build these features, we aggregate transaction data over various time windows (e.g., the last hour, the last 24 hours) and calculate statistics. A foundational paper in this domain provides an excellent overview of this strategy.
Feature engineering strategies for credit card fraud detection
This paper details several feature engineering strategies for fraud detection. We'll focus on the core aggregation method.
Read the subsection titled Capturing customer spending patterns. Pay close attention to the "transaction aggregation strategy." The key idea is to group transactions by customer ID within a specific time window (tp) and then calculate metrics like the count or sum of transactions. The paper also highlights that you can create more powerful features by adding more dimensions to the grouping, such as merchant group or country.
This strategy gives us features that describe a customer's recent "velocity" of spending. For example:
transactions_count_1h: Number of transactions in the last hour.transactions_sum_24h: Total amount spent in the last 24 hours.avg_txn_amount_7d: Average transaction amount over the last 7 days.distinct_merchants_count_1h: Number of unique merchants visited in the last hour.distinct_countries_count_24h: Number of unique countries where transactions occurred in the last 24 hours.
A sudden spike in any of these features could be a strong indicator of fraudulent activity. For example, a high value for distinct_countries_count_24h is highly anomalous and a classic fraud pattern.
The same paper also introduces a more subtle but powerful feature based on the time of day of transactions.
Feature engineering strategies for credit card fraud detection
Let's continue with the same paper to explore a more advanced temporal feature.
Read the section Time features. The authors point out that simply taking the arithmetic mean of transaction times (e.g., 23:00 and 01:00 averaging to noon) is misleading for periodic data. They propose using a von Mises distribution, which is essentially a normal distribution on a circle, to correctly model the "average" time of day a customer is active. Skim the results in Section 5 and the Conclusion. The key takeaway is that these engineered behavioral features provide a massive performance boost, increasing savings from detected fraud by over 200% compared to using only raw transaction data. This is a powerful statistic to have in your pocket for an interview.
The Modern Frontier: Transactions as Language
Your background in NLP and LLMs provides a unique lens through which to view this problem. The most recent and advanced approaches to modeling transactional data do exactly what you might expect: they treat a sequence of customer transactions as a sentence and apply transformer-based models to learn a rich representation (embedding) of the customer.
This approach, pioneered by fintech companies like Nubank, moves away from manual feature engineering towards automated representation learning.
Defining an Interface Between Transaction Data and Foundation Models - Building Nubank
This blog post from Nubank's engineering team provides a fascinating look at how they apply foundation models to transaction data.
Read the section that explains the need to convert transactions into embeddings. Then, jump to the section describing their chosen approach. They represent numerical and categorical features using special tokens. For example, a transaction like "Netflix, $15.99, on 2023-10-27" is converted into a string of tokens like [DESC: netflix] [AMT_BIN: 15-20] [DAY: 27] .... This sequence is then fed into a transformer, which learns to understand customer behavior directly.
While a deep dive into this is for a later lesson, it's crucial to be aware of this trend. In an interview, being able to discuss the trade-offs between traditional feature engineering (like RFM and behavioral aggregations) and end-to-end representation learning with transformers demonstrates a comprehensive and modern understanding of the field.
Conclusion
Today, we've explored how to transform raw, event-level transaction logs into meaningful, predictive features. This skill is fundamental to data science and machine learning in the fintech domain.
Key Takeaways:
- Aggregation is Key: The core technique is to aggregate a customer's transaction history to create a fixed-size feature vector representing their behavior.
- RFM for Segmentation: Recency, Frequency, and Monetary value form a simple but powerful framework for understanding customer value and for marketing segmentation. You should be comfortable calculating these in both pandas and SQL.
- Behavioral Aggregations for Dynamic Systems: For tasks like fraud detection, creating "velocity" features by aggregating transaction statistics over multiple, short-term time windows is crucial.
- Context Dictates Features: The features you engineer depend entirely on the problem. RFM is for long-term customer value, while velocity metrics are for real-time anomaly detection.
- The Future is Learned: Modern approaches are increasingly using sequence models like Transformers to learn behavioral representations automatically, treating transactions as a form of language.
In our last few lessons, we've focused heavily on creating data and engineering features. But how do we know if these features are actually useful? How do we measure the performance of a model built upon them? In our next lesson, we will address this by diving into model evaluation and validation. We'll cover essential classification metrics like precision, recall, F1-score, and AUC-ROC, which are absolutely critical for evaluating models on imbalanced datasets, a common scenario in fintech applications like fraud detection.