As part of the Safer Gambling initiative, it is important to make sure customers do not gamble above their financial means. One of the indicators of a financially strained customer is having their deposits declined due to insufficient funds. Our task is to design this journey and estimate this journey. Essentially, we want to fill in the tables:

Impacted customers“Freeze” period
Number of consecutive failed deposits6h12h24h
2a
3
4b
5

For example, if we lock deposits for 6h after 2 consecutive failed deposits “a” customers will be impacted, and “x” deposits (in GBP) will be lost. Alternatively, if we lock deposits for 12h after 4 consecutive failed deposits “b” customers will be impacted and “y” deposits will be lost.

Impacted deposits (GBP)“Freeze” period
Number of consecutive failed deposits6h12h24h
2x
3
4y
5

We want our solution to be scalable, meaning we can easily calculate what is the exposure if we change parameters to (for example) 6 consecutive failed deposits and 18h freeze period. We will use a dataset to help us design a solution and provide recommendations to the business. Assume all possible modifications are available and the above journey (X hours of freeze after Y failed deposits) is just the initial proposal. Thinking through balancing between commercial impact and protecting the vulnerable is critical.

Description of the dataset:
a. Player_id – unique player identifier
b. Txn_timestamp – indicator when payment was attempted
c. Attempted_deposit_amt_gbp, Deposit_status – self explanatory

Plan of Action

This dataset consists of log entries that contain a player_id, timestamp, and attempted deposit amount. Our first step would be to group each user by player_id then count each consecutive failed deposit. We can then use that counter to define a rule which flags this row of data and each consecutive row until the timestamp exits the freeze period specifies. This brings up a series of challenges such as:

  • Restarting the period if the freeze period sees more failed transactions.
  • Determining how the time delta is calculated.

Our next stage would be to create a new dataframe from each player output and append them to a final dataframe. The final stage would be to create a function that can repeat these processes to create an output with a combination of multiple rules. This can help our analysis later instead of creating them one-by-one.

1. Import Libraries

2. Gather Data

player_idtxn_timestampdeposit_statusattempted_deposit_amt_gbp
091090732018-04-22 23:31:20 UTCSuccess89.17
1128963732018-05-09 17:36:42 UTCSuccess261.44
2169560732018-04-15 00:09:49 UTCSuccess8.97
3169560732018-04-15 23:54:17 UTCSuccess8.97
4169560732018-04-16 01:52:41 UTCSuccess8.97

3. Transform Data

The first step is to transform the data into a way we can work on the transactions for each player individually. We can create a loop that iterates over each: player_id, then count each consecutive failed transaction until our target is reached. Once the rule target has been reached, we want to mark each successive entry as ‘frozen’ as long as the timestamp is within frozen period (FP) hours. The potential issues are that we may get additional frozen periods due to rules being unintentionally activated again during an active frozen period. The best way we can do this is to use a while loop or an if function, for example:

1. if counter >= n & timestamp is outside_fp:
2. while frozen == 'True':

4. Create DataFrame Based on Rules

We can now use the function above to combine with multiple functions which will generate multiple dataframes based on keyword arguments such as:

  • The number of consecutive failed transactions
  • Freeze Period (Hours)

5. Creating Multiple DataFrame Based on Rules

Create multiple dataframes and concatenate them together so we can see the combinations of each ruleset. This dataframe will be saved as data/declined_deposits_report.csv.

6. Report

Below is the main report, I have taken the steps to compile many possible outcomes and concatenate them into a single dataset which we can plot to compare the performance of each. Initially, I have chosen 6, 12 and 24 hours similarly to the example table.

rule_thresholdresult
freeze_hoursfailed_depositsimpacted_incomeimpacted_users
242£109,553.78593
122£89,004.70593
62£74,645.13593
243£41,781.46274
123£31,832.35274
63£25,993.99274
244£21,700.41150
124£16,462.30150
64£12,836.50150
245£12,131.7991
125£9,022.5491
65£8,637.5391
Affected customers by total impacted income (GBP)

24 hour freeze periods combined with 2 consecutive failed deposits predictively have the highest loss in deposits with £109k. In fact, all three freeze periods with the 2 consecutive failed deposit rules have much higher deposit loss with £74k being the lowest. This is obviously because it is the strictest combination we can enforce, the loosest combination would net a loss of between 12k and 8.6k.

Recommendations

A good balance between commercial impact and protecting the vulnerable would be between 3 and 4 consecutive failed deposits which would net between a 12k and 41k loss. My reasoning is that while you would prevent vulnerable people from gambling recklessly, you would still allow some flexibility.

The next thing is selecting a suitable freeze period, I would recommend a freeze period of 12 hours since that does not impact income as much as a 24 freeze period from a business standpoint. From a consumer protection standpoint, it would enable them to return within a shorter period of time but this “cooling off” period also gives customers time for reflection.

7. Key findings

Overview

  • 24 hour freeze period combined with 2 consecutive failed deposits has the highest loss (£109k)
  • There is a clear linear negative correlation between length of freeze periods and tighter deposit rules

Best rule combinations for balance:

  • 3 or 4 consecutive failed payments
  • 12 hour freeze period

References

Pandas

https://www.geeksforgeeks.org/different-ways-to-iterate-over-rows-in-pandas-dataframe/
https://stackoverflow.com/questions/19155718/select-pandas-rows-based-on-list-index
https://thispointer.com/python-pandas-how-to-drop-rows-in-dataframe-by-index-labels
https://stackoverflow.com/questions/61089768/how-to-perform-cumulative-sum-inside-iterrows
https://stackoverflow.com/questions/21317384/pandas-python-how-to-concatenate-two-dataframes-without-duplicates
https://www.geeksforgeeks.org/create-a-pandas-dataframe-from-lists/
https://pandas.pydata.org/pandas-docs/dev/reference/api/pandas.DataFrame.to_markdown.html
https://stackoverflow.com/questions/37003100/pandas-groupby-for-zero-values

Plotly

https://plotly.com/python-api-reference/generated/plotly.express.line
https://stackoverflow.com/questions/60907004/plotly-how-to-add-a-horizontal-line-to-a-line-graph
https://stackoverflow.com/questions/58637369/how-to-add-points-or-markers-to-line-chart-using-plotly-express
https://plotly.com/python/text-and-annotations/

Numpy

https://numpy.org/doc/stable/reference/generated/numpy.quantile.html

Other

https://www.kite.com/python/answers/how-to-convert-a-range-to-a-list-in-python
https://stackoverflow.com/questions/54948548/what-is-wrong-with-using-a-bare-except
https://github.com/kiwi0fruit/tabulatehelper
https://www.codegrepper.com/code-examples/python/compare+two+list+and+print+unique+values+python

Author

Write A Comment