Linking financial transactions
Linking banking transactions¶
This example shows how to perform a one-to-one link on banking transactions.
The data is fake data, and was generated has the following features:
- Money shows up in the destination account with some time delay
- The amount sent and the amount received are not always the same - there are hidden fees and foreign exchange effects
- The memo is sometimes truncated and content is sometimes missing
Since each origin payment should end up in the destination account, the probability_two_random_records_match of the model is known.
from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets
from splink.internals.misc import show
df_origin = splink_datasets.transactions_origin
df_destination = splink_datasets.transactions_destination
show(df_origin, rows=2)
show(df_destination, rows=2)
downloading: https://raw.githubusercontent.com/moj-analytical-services/splink_datasets/master/data/transactions_origin.parquet
downloading: https://raw.githubusercontent.com/moj-analytical-services/splink_datasets/master/data/transactions_destination.parquet
┌──────────────┬─────────────────┬──────────────────┬────────┬───────────┐
│ ground_truth │ memo │ transaction_date │ amount │ unique_id │
│ int64 │ varchar │ date │ double │ int64 │
├──────────────┼─────────────────┼──────────────────┼────────┼───────────┤
│ 0 │ MATTHIAS C paym │ 2022-03-28 │ 36.36 │ 0 │
│ 1 │ M CORVINUS dona │ 2022-02-14 │ 221.91 │ 1 │
└──────────────┴─────────────────┴──────────────────┴────────┴───────────┘
┌──────────────┬────────────────────────┬──────────────────┬────────┬───────────┐
│ ground_truth │ memo │ transaction_date │ amount │ unique_id │
│ int64 │ varchar │ date │ double │ int64 │
├──────────────┼────────────────────────┼──────────────────┼────────┼───────────┤
│ 0 │ MATTHIAS C payment BGC │ 2022-03-29 │ 36.36 │ 0 │
│ 1 │ M CORVINUS BGC │ 2022-02-16 │ 221.91 │ 1 │
└──────────────┴────────────────────────┴──────────────────┴────────┴───────────┘
In the following chart, we can see this is a challenging dataset to link:
- There are only 151 distinct transaction dates, with strong skew
- Some 'memos' are used multiple times (up to 48 times)
- There is strong skew in the 'amount' column, with 1,400 transactions of around 60.00
from splink.exploratory import profile_columns
db_api = DuckDBAPI()
df_origin_sdf = db_api.register(df_origin)
df_destination_sdf = db_api.register(df_destination)
profile_columns(
[df_origin_sdf, df_destination_sdf],
column_expressions=[
"memo",
"transaction_date",
"amount",
],
)
from splink import DuckDBAPI, block_on
from splink.blocking_analysis import (
chart_comparisons_from_blocking_rules,
)
# Design blocking rules that allow for differences in transaction date and amounts
blocking_rule_date_1 = """
strftime(l.transaction_date, '%Y%m') = strftime(r.transaction_date, '%Y%m')
and substr(l.memo, 1,3) = substr(r.memo,1,3)
and l.amount/r.amount > 0.7 and l.amount/r.amount < 1.3
"""
# Offset by half a month to ensure we capture case when the dates are e.g. 31st Jan and 1st Feb
blocking_rule_date_2 = """
strftime(l.transaction_date+15, '%Y%m') = strftime(r.transaction_date, '%Y%m')
and substr(l.memo, 1,3) = substr(r.memo,1,3)
and l.amount/r.amount > 0.7 and l.amount/r.amount < 1.3
"""
blocking_rule_memo = block_on("substr(memo,1,9)")
blocking_rule_amount_1 = """
round(l.amount/2,0)*2 = round(r.amount/2,0)*2 and yearweek(r.transaction_date) = yearweek(l.transaction_date)
"""
blocking_rule_amount_2 = """
round(l.amount/2,0)*2 = round((r.amount+1)/2,0)*2 and yearweek(r.transaction_date) = yearweek(l.transaction_date + 4)
"""
blocking_rule_cheat = block_on("unique_id")
brs = [
blocking_rule_date_1,
blocking_rule_date_2,
blocking_rule_memo,
blocking_rule_amount_1,
blocking_rule_amount_2,
blocking_rule_cheat,
]
db_api = DuckDBAPI()
df_origin_sdf = db_api.register(df_origin)
df_destination_sdf = db_api.register(df_destination)
chart_comparisons_from_blocking_rules(
[df_origin_sdf, df_destination_sdf],
blocking_rules=brs,
link_type="link_only",
record_sample_proportion=0.2,
)
/home/runner/work/splink/splink/splink/internals/blocking_analysis.py:668: UserWarning: The sampled blocking analysis estimate for blocking rule 'l."unique_id" = r."unique_id"' is based on 0 sampled pairwise comparisons. This is below the recommended minimum of 1,000, so the estimate may be unstable. Increase record_sample_proportion for a more stable estimate.
return _cumulative_comparisons_to_be_scored_from_blocking_rules(
# Full settings for linking model
import splink.comparison_level_library as cll
import splink.comparison_library as cl
comparison_amount = {
"output_column_name": "amount",
"comparison_levels": [
cll.NullLevel("amount"),
cll.ExactMatchLevel("amount"),
cll.PercentageDifferenceLevel("amount", 0.01),
cll.PercentageDifferenceLevel("amount", 0.03),
cll.PercentageDifferenceLevel("amount", 0.1),
cll.PercentageDifferenceLevel("amount", 0.3),
cll.ElseLevel(),
],
"comparison_description": "Amount percentage difference",
}
# The date distance is one sided becaause transactions should only arrive after they've left
# As a result, the comparison_template_library date difference functions are not appropriate
within_n_days_template = "transaction_date_r - transaction_date_l <= {n} and transaction_date_r >= transaction_date_l"
comparison_date = {
"output_column_name": "transaction_date",
"comparison_levels": [
cll.NullLevel("transaction_date"),
{
"sql_condition": within_n_days_template.format(n=1),
"label_for_charts": "1 day",
},
{
"sql_condition": within_n_days_template.format(n=4),
"label_for_charts": "<=4 days",
},
{
"sql_condition": within_n_days_template.format(n=10),
"label_for_charts": "<=10 days",
},
{
"sql_condition": within_n_days_template.format(n=30),
"label_for_charts": "<=30 days",
},
cll.ElseLevel(),
],
"comparison_description": "Transaction date days apart",
}
settings = SettingsCreator(
link_type="link_only",
probability_two_random_records_match=1 / df_origin.num_rows,
blocking_rules_to_generate_predictions=[
blocking_rule_date_1,
blocking_rule_date_2,
blocking_rule_memo,
blocking_rule_amount_1,
blocking_rule_amount_2,
blocking_rule_cheat,
],
comparisons=[
comparison_amount,
cl.LevenshteinAtThresholds("memo", [2, 6, 10]),
comparison_date,
],
retain_intermediate_calculation_columns=True,
)
db_api = DuckDBAPI()
df_origin_sdf = db_api.register(df_origin, dataset_display_name="__ori")
df_destination_sdf = db_api.register(df_destination, dataset_display_name="_dest")
linker = Linker([df_origin_sdf, df_destination_sdf], settings)
linker.training.estimate_u_using_random_sampling(max_pairs=1e6)
You are using the default value for `max_pairs`, which may be too small and thus lead to inaccurate estimates for your model's u-parameters. Consider increasing to 1e8 or 1e9, which will result in more accurate estimates, but with a longer run time.
----- Estimating u probabilities using random sampling -----
Estimating u with: max_pairs = 1,000,000, min_count_per_level = 100, num_chunks = 10
Estimating u for: amount (Comparison 1 of 3)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 0 for comparison level Exact match on amount (cvv=5)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 1 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 3 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 7 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 7 for level Exact match on amount (cvv=5). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 5/10
Count of 10 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 6/10
Count of 11 for level Exact match on amount (cvv=5). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 7/10
Count of 14 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 8/10
Count of 14 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 9/10
Count of 17 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 10/10
Count of 17 for level Exact match on amount (cvv=5). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Estimating u for: memo (Comparison 2 of 3)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 0 for comparison level Exact match on memo (cvv=4)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 1 for level Exact match on memo (cvv=4). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 6 for level Exact match on memo (cvv=4). Chunk took 0.4 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 10 for level Exact match on memo (cvv=4). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 10 for level Exact match on memo (cvv=4). Chunk took 0.4 seconds.
Min u_count not hit, continuing.
Running chunk 5/10
Count of 11 for level Exact match on memo (cvv=4). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Running chunk 6/10
Count of 15 for level Exact match on memo (cvv=4). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 7/10
Count of 17 for level Exact match on memo (cvv=4). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Running chunk 8/10
Count of 18 for level Exact match on memo (cvv=4). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Running chunk 9/10
Count of 20 for level Exact match on memo (cvv=4). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Running chunk 10/10
Count of 21 for level Exact match on memo (cvv=4). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Estimating u for: transaction_date (Comparison 3 of 3)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 87 for comparison level 1 day (cvv=4)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 1,902 for level 1 day (cvv=4). Chunk took 0.0 seconds.
Exiting early since min count of 1,902 exceeds min_count_per_level = 100
Estimated u probabilities using random sampling
Your model is not yet fully trained. Missing estimates for:
- amount (no m values are trained).
- memo (no m values are trained).
- transaction_date (no m values are trained).
linker.training.estimate_parameters_using_expectation_maximisation(block_on("memo"))
----- Starting EM training session -----
[EM sampling] max_pairs is None — no sampling will be applied
Estimating the m probabilities of the model by blocking on:
l."memo" = r."memo"
Parameter estimates will be made for the following comparison(s):
- amount
- transaction_date
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- memo
Iteration 1: Largest change in params was -0.594 in the m_probability of amount, level `Exact match on amount`
Iteration 2: Largest change in params was -0.17 in the m_probability of transaction_date, level `1 day`
Iteration 3: Largest change in params was 0.00946 in the m_probability of transaction_date, level `<=30 days`
Iteration 4: Largest change in params was 0.00207 in the m_probability of transaction_date, level `<=30 days`
Iteration 5: Largest change in params was 0.000354 in the m_probability of transaction_date, level `<=30 days`
Iteration 6: Largest change in params was 0.000199 in the m_probability of amount, level `All other comparisons`
Iteration 7: Largest change in params was 0.000181 in the m_probability of amount, level `All other comparisons`
Iteration 8: Largest change in params was 0.000164 in the m_probability of amount, level `All other comparisons`
Iteration 9: Largest change in params was 0.000148 in the m_probability of amount, level `All other comparisons`
Iteration 10: Largest change in params was 0.000133 in the m_probability of amount, level `All other comparisons`
Iteration 11: Largest change in params was 0.00012 in the m_probability of amount, level `All other comparisons`
Iteration 12: Largest change in params was 0.000107 in the m_probability of amount, level `All other comparisons`
Iteration 13: Largest change in params was 9.6e-05 in the m_probability of amount, level `All other comparisons`
EM converged after 13 iterations
Your model is not yet fully trained. Missing estimates for:
- memo (no m values are trained).
<EMTrainingSession, blocking on l."memo" = r."memo", deactivating comparisons memo>
session = linker.training.estimate_parameters_using_expectation_maximisation(block_on("amount"))
----- Starting EM training session -----
[EM sampling] max_pairs is None — no sampling will be applied
Estimating the m probabilities of the model by blocking on:
l."amount" = r."amount"
Parameter estimates will be made for the following comparison(s):
- memo
- transaction_date
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- amount
Iteration 1: Largest change in params was -0.404 in the m_probability of memo, level `Exact match on memo`
Iteration 2: Largest change in params was -0.087 in the m_probability of memo, level `Exact match on memo`
Iteration 3: Largest change in params was 0.0159 in the m_probability of memo, level `Levenshtein distance of memo <= 10`
Iteration 4: Largest change in params was 0.00679 in the m_probability of memo, level `All other comparisons`
Iteration 5: Largest change in params was 0.00723 in the m_probability of memo, level `All other comparisons`
Iteration 6: Largest change in params was 0.00695 in the m_probability of memo, level `All other comparisons`
Iteration 7: Largest change in params was 0.00613 in the m_probability of memo, level `All other comparisons`
Iteration 8: Largest change in params was 0.00503 in the m_probability of memo, level `All other comparisons`
Iteration 9: Largest change in params was 0.00389 in the m_probability of memo, level `All other comparisons`
Iteration 10: Largest change in params was 0.00289 in the m_probability of memo, level `All other comparisons`
Iteration 11: Largest change in params was 0.00208 in the m_probability of memo, level `All other comparisons`
Iteration 12: Largest change in params was 0.00147 in the m_probability of memo, level `All other comparisons`
Iteration 13: Largest change in params was 0.00102 in the m_probability of memo, level `All other comparisons`
Iteration 14: Largest change in params was 0.000702 in the m_probability of memo, level `All other comparisons`
Iteration 15: Largest change in params was 0.00048 in the m_probability of memo, level `All other comparisons`
Iteration 16: Largest change in params was 0.000327 in the m_probability of memo, level `All other comparisons`
Iteration 17: Largest change in params was 0.000222 in the m_probability of memo, level `All other comparisons`
Iteration 18: Largest change in params was 0.00015 in the m_probability of memo, level `All other comparisons`
Iteration 19: Largest change in params was 0.000102 in the m_probability of memo, level `All other comparisons`
Iteration 20: Largest change in params was 6.86e-05 in the m_probability of memo, level `All other comparisons`
EM converged after 20 iterations
Your model is fully trained. All comparisons have at least one estimate for their m and u values
linker.visualisations.match_weights_chart()
df_predict = linker.inference.predict(threshold_match_probability=0.001)
Blocking time: 1.43 seconds
Predict time (post-blocking): 1.57 seconds
linker.visualisations.comparison_viewer_dashboard(
df_predict, "dashboards/comparison_viewer_transactions.html", overwrite=True
)
from IPython.display import IFrame
IFrame(
src="./dashboards/comparison_viewer_transactions.html", width="100%", height=1200
)
pred_errors = linker.evaluation.prediction_errors_from_labels_column(
"ground_truth", include_false_positives=True, include_false_negatives=False
)
linker.visualisations.waterfall_chart(pred_errors.as_record_list(limit=5))
Blocking time: 1.18 seconds
Predict time (post-blocking): 1.72 seconds
pred_errors = linker.evaluation.prediction_errors_from_labels_column(
"ground_truth", include_false_positives=False, include_false_negatives=True
)
linker.visualisations.waterfall_chart(pred_errors.as_record_list(limit=5))
Blocking time: 1.33 seconds
Predict time (post-blocking): 1.75 seconds