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 deposits | 6h | 12h | 24h | |
| 2 | a | |||
| 3 | ||||
| 4 | b | |||
| 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 deposits | 6h | 12h | 24h | |
| 2 | x | |||
| 3 | ||||
| 4 | y | |||
| 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_id | txn_timestamp | deposit_status | attempted_deposit_amt_gbp | |
|---|---|---|---|---|
| 0 | 9109073 | 2018-04-22 23:31:20 UTC | Success | 89.17 |
| 1 | 12896373 | 2018-05-09 17:36:42 UTC | Success | 261.44 |
| 2 | 16956073 | 2018-04-15 00:09:49 UTC | Success | 8.97 |
| 3 | 16956073 | 2018-04-15 23:54:17 UTC | Success | 8.97 |
| 4 | 16956073 | 2018-04-16 01:52:41 UTC | Success | 8.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_threshold | result | ||
|---|---|---|---|
| freeze_hours | failed_deposits | impacted_income | impacted_users |
| 24 | 2 | £109,553.78 | 593 |
| 12 | 2 | £89,004.70 | 593 |
| 6 | 2 | £74,645.13 | 593 |
| 24 | 3 | £41,781.46 | 274 |
| 12 | 3 | £31,832.35 | 274 |
| 6 | 3 | £25,993.99 | 274 |
| 24 | 4 | £21,700.41 | 150 |
| 12 | 4 | £16,462.30 | 150 |
| 6 | 4 | £12,836.50 | 150 |
| 24 | 5 | £12,131.79 | 91 |
| 12 | 5 | £9,022.54 | 91 |
| 6 | 5 | £8,637.53 | 91 |
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
