TD Bank: Point of Sale Analytics with Microsoft SQL Server and Power BI
TD Bank is a leading banking institution in Canada. With a mission to become “the bank of the future”, the company is continuously investing in digital technology initiatives – placing particular emphasis on Microsoft enterprise technology tools such as Microsoft 365, Microsoft SQL Server and Power BI.
Within TD Bank, the Merchant Solutions division is responsible for various services pertaining to the company’s retail customers. Most prominently, this division oversees the development and implementation of all retail point of sale (POS) solutions and various payment processing services. Every month, millions of dollars worth of transactions are processed through these systems and as such, the amount of data generated is staggering.
TD Bank Merchant Solutions engaged with MRS, specifically turning to the experience and expertise of the MRS Business Intelligence (BI) team, to lead a substantial analytics/BI project and to help the division manage and leverage this vast amount of data.
Managing and analyzing millions of dollars worth of transaction data
Thousands of storefronts across Canada use TD Bank POS terminals and payment processing services every day, including some of the most prominent retail organizations. Every month, as noted, millions of dollars worth of transactions go through these systems. With this significant a transaction-level, the Merchant Solutions department at TD Bank needed an advanced analytics application capable of extracting this data and transferring it into accessible information and insights that decision-makers could use to their advantage.
In particular, the pricing and marketing team needed detailed insights and information on the usage of different payment methods in order to more accurately set card rates/fees, types of customers to market different payment methods to, etc.
Examples of the required transaction information include:
- Frequency of use of various payment methods such as credit cards, debit cards and various reward cards
- Frequency of use of payment methods based on geolocation
- Frequency of use payment methods based on types of transactions (such as size of transaction, type of store, etc.)
- Time-based statistics on usage of various payment methods
*Note: shoppers personal data and information was kept private and secure. Data was only used on an aggregate level.
Architecting a modern data platform using Microsoft SQL Server, leveraging Power BI for insights and reports
The MRS BI team spearheaded the initiative and assumed full project management roles, including working with stakeholders across various departments to dissect business and technical requirements, determining the optimal solution, implementing the strategy/roadmap and architecting the solution.
The solution leverages Microsoft SQL Server (CUBE’s, ETL’s, etc.) for backend data processing and warehousing and uses advanced querying to transfer the data to the TD Bank Office 365 platform for frontend usage, where Power BI is used as the data visualization and reporting tool.
Analysts and decision-makers across the division have access to the reports and data visualizations through multiple outlets – whether on the isolated Power BI application or embedded on various SharePoint sites/intranets and CRM platforms. Furthermore, the reports, deployed on the cloud and with a mobile-responsive design, are accessible remotely and across any device. And as with any data analytics solution, security is paramount. Only users with explicitly defined permission are granted access to the data and the modern data platform and infrastructure is built utilizing impeccable database security best practices.
The MRS BI team was ultimately integral in helping TD Bank greatly expand and optimize their data infrastructure while empowering TD employees with powerful insights that improve critical decision-making processes.
(The MRS team) helped support and expand TD Merchant Solution’s BI data infrastructure for over 1 year, tripling the size of our data warehouse to over 1TB, integrating SSIS packages and streamline the process away from our technology group, minimizing both failure risk greatly as well as shortening data processing time remarkably. I believe the MRS team was instrumental in the success of our BI expansion project.
