5
$\begingroup$

I'm working on a dataset of banking transactions and would like to find recurrent transactions. I've been mapping transactions per merchant in timeseries, and tried to use acf from statsmodels.tsa.stattools to calculate the autocorrelation function but i'm not getting the expected results:

r = acf(ts, fft=False)

For example this set of transaction (ASSURANCE DESJ) is getting an acf score of 0.3159 when it's obviously a recurring transaction (same amount, same frequency).

enter image description here

Another example of recurring transactions with acf=0.22775:

enter image description here

But this one should not be found as a recurring transactions, and get a score not too far from the previous set (0.26919):

enter image description here

I've been checking a lot of different methods, I acutally came up with a combination of auto-correlation on the regular timeserie, auto-correlation on a timeserie with amount=1, with stationnary checking and other rules to have a not so perfect results. I've also checked at ARIMA and other methodology without luck.

Would you have a better way to detect recurring transactions from timeseries ?

Link to Datasets

$\endgroup$
2
  • $\begingroup$ This package is for detecting recurring transactions: pypi.org/project/BankDataInvestigation $\endgroup$ Commented Jan 25, 2021 at 18:18
  • $\begingroup$ Hey did you manage to find a solution to tackle a problem like this? $\endgroup$
    – frcake
    Commented Jun 15, 2021 at 15:32

2 Answers 2

1
$\begingroup$

I don't know the model you use, but I would suggest the perhaps old-fashioned feature engineering approach:

  • In each set of transactions, calculate the duration between two consecutive transactions for every pair of consecutive transactions (i.e. difference between the dates). Then add the standard deviation of these durations as a a feature, so that a very regular transaction set should get a near zero value.
  • Same idea with the amount of the transaction: the standard deviation will be zero if the amount never changes.
  • Additional ideas:
    • number of distinct amounts across all transactions
    • number of distinct duration values between transactions in days, weeks, months, years.
    • standard deviation of the sum by day/week/month/year
$\endgroup$
1
  • $\begingroup$ Could you clear very first point you've made for me? When you say "each set of transactions", does that mean I should take all the combinations with each transaction, for every single transaction? Would that not result in a large feature explosion? $\endgroup$ Commented Dec 27, 2021 at 15:26
1
$\begingroup$

I am not exactly sure what you want to achieve, but here goes my solution

So acf generally tells you the autocorrelation between all lags compared with original trend

so for example, for Dataset 1

 a={'2019-07-15': 9831.0,
  '2019-08-15': 9818.0,
  '2019-09-16': 9818.0,
  '2019-10-15': 9818.0,
  '2019-11-15': 9818.0,
  '2019-12-16': 9818.0,
  '2020-01-15': 9818.0,
  '2020-02-17': 9818.0,
  '2020-03-16': 9818.0}
df = pd.DataFrame([[x,y] for x,y in zip(a.keys(),a.values())])
df[0] = pd.to_datetime(df[0])

new_df=pd.date_range(start=min(df[0]), end=max(df[0]))  ## adding blank dates in between so that data become continuous
new_df = pd.DataFrame(new_df)
new_df = new_df.merge(df, how="left", on=0)
new_df[1]=new_df[1].fillna(0)
new_df.head()
>>

    0   1
0   2019-07-15  9831.0
1   2019-07-16  0.0
2   2019-07-17  0.0
3   2019-07-18  0.0
4   2019-07-19  0.0


plt.plot(new_df[0], new_df[1])
plt.show()

data plot

If I do acf of this data(plotting acf to get a better idea of what is happening)

plot_acf(new_df[1], lags=35)

ACF PLOT

I see that at ~31 there is a significant value(>0.2 significance level) of acf, significant enough to assume that the next transaction would probably happen in ~30-31 days (There is this region from 27-32 which have non zero values, because of how data is build, the dates are not exactly 30 days apart but vary slightly)

The score in itself does not mean much, if it does not include when the score was high(i.e. the lag period where it was high), this is actually used to identify seasonality pattern, so in this case you know that since the pattern repeats at interval of ~30 days you can build a SARIMA model on top of this, to forecast the next transaction

As for the Dataset C:

Dataset C

The acf plot comes out to be something like this:

ACF dataset c

This means that the pattern repeats every 7 or so days, you can use that info to build a SARIMA model

There are many different ways to tackle seasonal trends, maybe building SARIMA like model or a linear model would be using Seasonal decompose(statsmodels) or Prophet (http://facebook.github.io/prophet/)

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.