logo

Retail Banking Analytics with Atoti

Andrew Yang

Make smarter decisions by analyzing consumer credit cards and risk profiles.

Retail banking, otherwise known as consumer banking, focuses on providing financial services to the individual consumer rather than established businesses. For example, banks might partner with retailers when issuing credit cards (think airline credit cards), or they may issue personal loans to individual consumers for reasons like debt consolidation or education.

In the interests of the bank, risk managers may want to know things like:

  • How many retailer credit cards have been issued, and to whom?
  • What are the spending behaviors of the bank’s credit card customers as well as their credit utilizations?
  • What is the bank’s expected credit loss, if customers default?

As a result, banks have an immense amount of data that they need to sift through, and slicing-and-dicing their data in the many ways that they want can get quite complex (and computationally heavy), resulting in slow, hard to understand queries. Not to mention it’s quite a task to then share this information and collaborate! Though with Atoti, our in-memory data cube allows us to slice-and-dice data quickly, and our web-based UI keeps things intuitive while promoting collaboration. Let’s see how this works.

💡For this walk through, check out the Credit Card Analytics notebook to follow along!

Setting the Stage

By leveraging customizable widgets, interactive charts, and drill-down capabilities, banks can use Atoti to tailor their dashboards to suit specific business requirements and drive data-driven decision-making. For example, one such dashboard that retail banks could want for answering the aforementioned questions could be:

Retail banking overview dashboard

Here we can see an overview of what a retail bank may want to focus on, such as the amount of credit card exposure by retailer credit cards issued or the breakdown of FICO scores for customers with credit cards (among other metrics). In addition, in the left-hand pane, we can see the many hierarchies and measures that allowed for slicing-and-dicing our data to produce the associated visualizations. But what if we want to drill-down further and look at the granularity of a single customer? Well we can do that too.

Retail banking customer details dashboard

In either case of these dashboards, we can use the Atoti Python API to write a minimal amount of code to achieve a maximum amount of insight — slicing-and-dicing our data as we see fit and creating new risk measures with user-defined scopes for aggregations. But before we can do all of this, we first need to load our cube and set up our data model.

Loading the Cube

To simulate the retail bank use case, we will load into Atoti 5 million records worth of credit card sales transactions, along with associated user and credit card number info, pulled from Kaggle. Once we have loaded our data into the cube, we will also enrich the data model with additional attributes such as retailer info, loans, and FICO classifications. By the end of our data load process, we should have a data model that looks like the following:  

Retail banking cube data model

With such a rich data model, we can then define new measures that a retail bank would want to see, such as credit card limits, utilization percentages, revolving balances, and so on and so forth.

Defining New Measures with Aggregation Scopes

Measures in Atoti are the aggregated metrics that we want to see for a given numerical attribute. For example, an easy measure (Amount) could simply be the SUM of the Amount column in our dataset consisting of 5 million sales transactions, indicating the total amount of purchases that have been made with credit cards in our use case scenario. 

However, sometimes measures are not so straightforward. For example, perhaps we want to create a measure that shows the total credit limit issued for each customer, based on how many credit cards each customer has been issued (they can have multiple).

Retail banking user credit card information

In such a case, how would we then create this measure? Answer: we can define an aggregation scope that takes this factor into account. When we create a new measure, we can also aggregate our metrics at a specific level or granularity. In this case, by defining the aggregation scope at the Card Number level, the measure for Credit Limit aggregates the credit limits for each card number and displays the total credit limit for each user accordingly (rather than for a single card number as suggested by Credit_Limit.SV).

Retail banking measures with aggregation scopes

Based on this methodology, we can create measures that suit our needs for aggregating at specific levels, depending on what we are looking for at the time of our analysis.

Incorporating Expected Credit Loss (ECL)

One critical aspect of risk management in retail banking is the calculation of Expected Credit Loss (ECL). ECL represents the estimated loss that banks expect to incur on credit exposures, taking into account factors such as probability of default (PD), loss given default (LGD), and exposure at default (EAD). We can calculate ECL using the following formula:

Expected credit loss formula

Similar to how we created our previous measures in Atoti, we can do the same for the Expected Credit Loss formula and its components. Once we have created our new risk measures, we can then use them to display the ECL for each user.

Pivot table for ECL

Final Thoughts

At the end of the notebook, you will also see additional dashboards showcasing banking analytics at the retailer and sales transaction levels, thereby again showcasing the power of Atoti’s slice-and-dicing capabilities. Feel free to check them out and play around with the data to get a feel for how Atoti can empower retail banking! And If you have any questions or feedback, we’d love to hear from you — stay tuned for our next notebook drop!

Like this post ? Please share

Latest Articles

View All

A cleaner, more streamlined Atoti Python API

Read More

More flexibility and observability with Atoti Python API 0.9.0

Read More

What’s new with DirectQuery in Atoti Server and Java API 6.1

Read More

NEWSLETTER

Join our Community for the latest insights and information about Atoti