This vignette reproduces the Splink
“Linking banking transactions” demo in irelink. It
demonstrates two-table linkage with link_type = "link" by
matching each outgoing payment in the origin table to the corresponding
incoming payment in the destination table.
The data is synthetic and intentionally challenging. Amounts differ
because of fees and exchange-rate effects, dates can shift by a few
days, and memos are sometimes truncated. Because each origin payment has
exactly one destination counterpart, the prior match probability is
1 / n_origin.
This vignette requires nanoparquet to
read the remote Parquet files, and it only compiles when the package and
both data URLs are available. It also assumes DuckDB because the
blocking rules below use raw .where SQL with DuckDB date
helpers such as strftime() and yearweek().
Load the data
library(irelink)
#>
#> Attaching package: 'irelink'
#> The following object is masked from 'package:base':
#>
#> months
library(ggplot2)
df_origin
#> # A data frame: 45,326 × 5
#> ground_truth memo transaction_date amount unique_id
#> <dbl> <chr> <date> <dbl> <dbl>
#> 1 0 MATTHIAS C paym 2022-03-28 36.4 0
#> 2 1 M CORVINUS dona 2022-02-14 222. 1
#> 3 2 M C donation BG 2022-05-04 450. 2
#> 4 3 M C BGC 2022-03-03 208. 3
#> 5 4 M CORVINUS CSH 2022-02-04 79.7 4
#> 6 5 M C WRE 2022-03-26 835. 5
#> 7 6 M CORVINUS CSH 2022-05-01 66.7 6
#> 8 7 M C 1b097ab5 CH 2022-03-15 26246. 7
#> 9 8 M C WRE 2022-03-26 92.8 8
#> 10 9 M C payment CHQ 2022-05-04 211. 9
#> # ℹ 45,316 more rows
df_destination
#> # A data frame: 45,326 × 5
#> ground_truth memo transaction_date amount unique_id
#> <dbl> <chr> <date> <dbl> <dbl>
#> 1 0 "MATTHIAS C payment BGC" 2022-03-29 36.4 0
#> 2 1 "M CORVINUS BGC" 2022-02-16 222. 1
#> 3 2 "M C" 2022-05-05 450. 2
#> 4 3 "M C payment" 2022-03-04 199. 3
#> 5 4 "M CORVINUS " 2022-02-05 79.7 4
#> 6 5 "M C " 2022-03-27 835. 5
#> 7 6 "M CORVINUS dona" 2022-05-05 66.7 6
#> 8 7 "M C CHQ" 2022-03-27 25908. 7
#> 9 8 "M C WRE" 2022-03-27 91.9 8
#> 10 9 "M C payment CHQ" 2022-05-17 212. 9
#> # ℹ 45,316 more rowsProfile the data
il_profile(df_origin, memo, transaction_date, amount, con = con, top_n = 8)
#> # A tibble: 24 × 3
#> column value n
#> <chr> <chr> <dbl>
#> 1 memo J B payment BGC 27
#> 2 memo J B donation BG 25
#> 3 memo J B money BGC 24
#> 4 memo J B BGC 21
#> 5 memo J P BGC 18
#> 6 memo A B money BGC 18
#> 7 memo J S money BGC 18
#> 8 memo J C money BGC 17
#> 9 transaction_date 19122 696
#> 10 transaction_date 19119 693
#> # ℹ 14 more rowsChoose blocking rules
Because corresponding records differ in predictable ways, the
blocking rules need to be broad enough to retain true matches while
still shrinking the search space. Fees change amounts, dates shift, and
memos are truncated, so the rules below use SQL expressions in
.where rather than relying on exact agreement alone:
counts <- il_count_pairs(
df_origin,
df_destination,
# Same year-month, similar memo prefix, amount ratio within 30%
block_on(
.where = paste(
"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'
)
),
# Same but offset by 15 days to catch month boundaries
block_on(
.where = paste(
"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'
)
),
# Memo prefix (first 9 characters)
block_on(.where = 'substr(l.memo, 1, 9) = substr(r.memo, 1, 9)'),
# Rounded amount + same week
block_on(
.where = paste(
'round(l.amount / 2, 0) * 2 = round(r.amount / 2, 0) * 2',
'AND yearweek(r.transaction_date) = yearweek(l.transaction_date)'
)
),
# Amount offset + week offset
block_on(
.where = paste(
'round(l.amount / 2, 0) * 2 = round((r.amount + 1) / 2, 0) * 2',
'AND yearweek(r.transaction_date) = yearweek(l.transaction_date + 4)'
)
),
# Ground-truth "cheat" rule for completeness
block_on(unique_id),
con = con,
link_type = 'link'
)
counts
#> # A tibble: 6 × 4
#> rule n_pairs cumulative_pairs pct_of_cartesian
#> <chr> <dbl> <dbl> <dbl>
#> 1 strftime(l.transaction_date, '%Y%m'… 301614 301614 0.0147
#> 2 strftime(l.transaction_date + 15, '… 281675 428250 0.0208
#> 3 substr(l.memo, 1, 9) = substr(r.mem… 330510 710190 0.0346
#> 4 round(l.amount / 2, 0) * 2 = round(… 353563 1051372 0.0512
#> 5 round(l.amount / 2, 0) * 2 = round(… 352877 1321538 0.0643
#> 6 unique_id 45326 1321565 0.0643
autoplot(counts)
Define the specification
The transaction_date comparison is one-sided because a
payment can only arrive after it is sent. The comparison therefore
checks whether destination_date - origin_date is between 0
and N days:
spec <- il_spec() |>
il_compare(amount, cl_pct_diff(0.01, 0.03, 0.10, 0.30)) |>
il_compare(memo, cl_levenshtein(2, 6, 10)) |>
il_compare(
transaction_date,
cl_levels(
cl_null(),
cl_custom('(r.{col} - l.{col}) BETWEEN 0 AND 1'),
cl_custom('(r.{col} - l.{col}) BETWEEN 0 AND 4'),
cl_custom('(r.{col} - l.{col}) BETWEEN 0 AND 10'),
cl_custom('(r.{col} - l.{col}) BETWEEN 0 AND 30'),
cl_else()
)
) |>
il_block_on(
.where = paste(
"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'
)
) |>
il_block_on(
.where = paste(
"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'
)
) |>
il_block_on(.where = 'substr(l.memo, 1, 9) = substr(r.memo, 1, 9)') |>
il_block_on(
.where = paste(
'round(l.amount / 2, 0) * 2 = round(r.amount / 2, 0) * 2',
'AND yearweek(r.transaction_date) = yearweek(l.transaction_date)'
)
) |>
il_block_on(
.where = paste(
'round(l.amount / 2, 0) * 2 = round((r.amount + 1) / 2, 0) * 2',
'AND yearweek(r.transaction_date) = yearweek(l.transaction_date + 4)'
)
) |>
il_block_on(unique_id)
spec
#> Linkage Specification
#> Comparisons (3):
#> amount : pct_diff
#> memo : levenshtein
#> transaction_date : levels
#> Blocking rules (6, OR-ed):
#> 1. WHERE 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
#> 2. WHERE 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
#> 3. WHERE substr(l.memo, 1, 9) = substr(r.memo, 1, 9)
#> 4. WHERE round(l.amount / 2, 0) * 2 = round(r.amount / 2, 0) * 2 AND yearweek(r.transaction_date) = yearweek(l.transaction_date)
#> 5. WHERE round(l.amount / 2, 0) * 2 = round((r.amount + 1) / 2, 0) * 2 AND yearweek(r.transaction_date) = yearweek(l.transaction_date + 4)
#> 6. unique_idTrain the model
Because this benchmark is one-to-one, set the prevalence prior
directly with il_prior_prevalence() instead of changing
model$params by hand:
model <- il_model(
df_origin,
df_destination,
spec = spec,
con = con,
link_type = 'link'
)
model <- il_prior_prevalence(model, 1 / nrow(df_origin))
model <- il_estimate_u(model, max_pairs = 1e6) |>
il_estimate_em(block_on(memo)) |>
il_estimate_em(block_on(amount))
#> EM trained: amount and transaction_date | skipped (blocked on): memo
#> EM trained: memo and transaction_date | skipped (blocked on): amountInspect the trained model
summary(model)
#> irelink Model
#> Status: Trained
#> Link type: link
#> Records: 45326
#> Records (right): 45326
#> Comparisons: 3
#> Blocking rules: 6
#>
#> Parameters:
#> prior: 0.003558299
#> priors: # A tibble: 1 × 5
#> priors: family comparison gamma_level probability strength
#> priors: <chr> <chr> <int> <dbl> <dbl>
#> priors: 1 prevalence NA NA 0.0000221 NA
#> comparisons: # A tibble: 14 × 4
#> comparisons: comparison gamma_level m u
#> comparisons: <chr> <int> <dbl> <dbl>
#> comparisons: 1 amount 0 0.00340 0.879
#> comparisons: 2 amount 1 0.001000 0.0851
#> comparisons: 3 amount 2 0.232 0.0256
#> comparisons: 4 amount 3 0.318 0.00687
#> comparisons: 5 amount 4 0.445 0.00347
#> comparisons: 6 memo 0 0.0572 0.877
#> comparisons: 7 memo 1 0.148 0.0985
#> comparisons: 8 memo 2 0.261 0.0224
#> comparisons: 9 memo 3 0.534 0.00179
#> comparisons: 10 transaction_date 0 0.001000 0.737
#> comparisons: 11 transaction_date 1 0.0509 0.158
#> comparisons: 12 transaction_date 2 0.0954 0.0564
#> comparisons: 13 transaction_date 3 0.464 0.0294
#> comparisons: 14 transaction_date 4 0.389 0.0193
#> u_estimation: 974694
#> u_estimation: FALSE
#> u_estimation: NULL
#> u_estimation: NULL
#> u_estimation: 1000000
#> u_estimation: 1
autoplot(model)
autoplot(model, type = 'parameters')
Predict
predictions <- predict(model, threshold = 0.001)
predictions
#> # A tibble: 612,563 × 8
#> unique_id_l unique_id_r gamma_amount gamma_memo gamma_transaction_date
#> * <dbl> <dbl> <int> <int> <int>
#> 1 14152 14152 4 1 1
#> 2 15226 15226 3 1 3
#> 3 15516 15516 3 1 1
#> 4 42776 42776 2 2 1
#> 5 1 15 2 3 0
#> 6 5 8 0 3 4
#> 7 55 37632 0 3 2
#> 8 72 37628 0 3 1
#> 9 117 26283 0 3 1
#> 10 150 162 0 3 1
#> # ℹ 612,553 more rows
#> # ℹ 3 more variables: match_weight <dbl>, total_match_weight <dbl>,
#> # match_probability <dbl>
autoplot(predictions)
autoplot(predictions, which = 1)
Evaluate against ground truth
acc <- il_accuracy(model, labels_col = 'ground_truth')
acc
#> # A tibble: 102 × 16
#> threshold tp fp fn tn fn_blocking_miss precision recall f1
#> <dbl> <int> <int> <int> <int> <int> <dbl> <dbl> <dbl>
#> 1 0 45326 1.28e6 0 0 0 0.0343 1 0.0663
#> 2 1.22e-9 45326 1.28e6 0 0 0 0.0343 1 0.0663
#> 3 3.71e-9 45326 1.25e6 0 27038 0 0.0350 1 0.0677
#> 4 2.81e-8 45326 1.21e6 0 68900 0 0.0362 1 0.0698
#> 5 8.55e-8 45326 1.19e6 0 83586 0 0.0366 1 0.0706
#> 6 2.18e-7 45326 1.15e6 0 128271 0 0.0380 1 0.0732
#> 7 2.90e-7 45326 1.04e6 0 240600 0 0.0419 1 0.0805
#> 8 6.62e-7 45326 1.03e6 0 246447 0 0.0422 1 0.0809
#> 9 8.83e-7 45326 1.01e6 0 266780 0 0.0430 1 0.0824
#> 10 1.52e-6 45326 9.74e5 0 302556 0 0.0445 1 0.0852
#> # ℹ 92 more rows
#> # ℹ 7 more variables: f2 <dbl>, f0_5 <dbl>, specificity <dbl>, npv <dbl>,
#> # accuracy <dbl>, p4 <dbl>, phi <dbl>
autoplot(acc)
Error inspection
errors <- il_errors(model, labels_col = 'ground_truth', threshold = 0.5)
errors[errors$error_type == 'false_positive', ]
#> # A tibble: 53,342 × 6
#> unique_id_l unique_id_r match_weight match_probability true_label error_type
#> <dbl> <dbl> <dbl> <dbl> <lgl> <chr>
#> 1 1165 25409 10.1 0.797 FALSE false_posi…
#> 2 1593 2570 10.5 0.833 FALSE false_posi…
#> 3 1235 9990 15.4 0.994 FALSE false_posi…
#> 4 5451 2293 10.5 0.833 FALSE false_posi…
#> 5 9342 4708 11.6 0.916 FALSE false_posi…
#> 6 9685 33239 8.91 0.632 FALSE false_posi…
#> 7 14123 17627 11.1 0.884 FALSE false_posi…
#> 8 14214 8895 15.4 0.994 FALSE false_posi…
#> 9 15097 42156 13.4 0.975 FALSE false_posi…
#> 10 14674 39940 10.7 0.856 FALSE false_posi…
#> # ℹ 53,332 more rows
errors[errors$error_type == 'false_negative', ]
#> # A tibble: 4,839 × 6
#> unique_id_l unique_id_r match_weight match_probability true_label error_type
#> <dbl> <dbl> <dbl> <dbl> <lgl> <chr>
#> 1 5808 5808 7.40 0.376 TRUE false_nega…
#> 2 5842 5842 4.53 0.0761 TRUE false_nega…
#> 3 12168 12168 7.40 0.376 TRUE false_nega…
#> 4 13018 13018 3.23 0.0323 TRUE false_nega…
#> 5 14599 14599 8.10 0.495 TRUE false_nega…
#> 6 15422 15422 7.40 0.376 TRUE false_nega…
#> 7 15495 15495 5.93 0.178 TRUE false_nega…
#> 8 22599 22599 8.10 0.495 TRUE false_nega…
#> 9 26719 26719 5.58 0.145 TRUE false_nega…
#> 10 548 548 5.93 0.178 TRUE false_nega…
#> # ℹ 4,829 more rowsCleanup
il_cleanup(model)
DBI::dbDisconnect(con, shutdown = TRUE)il_cleanup(model) only removes tables owned by that
model. If an interactive run fails before you keep the model object,
call il_cleanup_all(con) to remove all irelink
tables from the connection before disconnecting.