The Challenge

The US bank aimed to gain a comprehensive understanding of its customers’ transaction patterns over time, particularly distinguishing between online and offline transactions. This analysis was crucial to justify ongoing and future investments in its extensive brick-and-mortar branches. Additionally, the bank required insights into the usage of its own ATMs versus foreign ATMs. However, several challenges emerged:

Categorizing Transactions: Accurately categorizing transactions as online or offline based on attributes such as action code, source code, branch code, and fuzzy matching through comments proved to be a complex task.

Identifying Tricky Cases: Some cases were particularly challenging, such as determining whether a cheque was deposited at a bank or scanned by the customer at home based on transaction comments.

ATM Usage Classification: The bank needed to classify ATM/ITM (Interactive Teller Machine) transactions into those at its own ATMs/ITMs and those at foreign ATMs.

The Solution

To overcome these challenges and derive meaningful insights, a comprehensive solution was implemented:

Data Model Development: An extensive data model was created to properly attribute the data, as the legacy system lacked proper identifiers.

Data Extraction and Transformation: A combination of Snowflake Streams, Tasks, and an Alteryx workflow was developed to insert data into a new table daily in batch. This process ensured that the data was consistently updated and readily available for analysis.

BI Tool Integration: The new data model table was made accessible to a Business Intelligence (BI) Tool, specifically ThoughtSpot. This BI tool was utilized to calculate various metrics and Key Performance Indicators (KPIs) from the data.

The Result

The implementation of this solution led to significant outcomes for the US bank:

Branch Efficiency Insights: The bank was able to identify branches experiencing a decreasing volume of offline transactions. This valuable information enabled the bank to consider branch closures, optimizing its operations.

ATM Optimization: The analysis highlighted the most frequently used foreign ATM locations. Armed with this data, the bank could strategically install its own ATMs in these locations, reducing interchange fees and resulting in direct cost savings.

This client success story demonstrates how a US bank harnessed data modeling, Snowflake streams and tasks, and a BI tool to gain insights into customer transaction patterns, driving informed decisions regarding branch investments and ATM optimization.

Tools and Platforms Used