Project 1: Competitor Hours Analysis Dashboard
Objective: To develop a comprehensive dashboard for Scotiabank executives, providing detailed insights into branch operating hours across Canada. This tool aims to optimize operational strategies in response to market dynamics and competitor activities.
Challenges:
Data inconsistency from other banks (BMO, CIBC, RBC, TD) and third party provider (Chain XY)
Standardized Data Framework: Establish a unified reporting format based on Scotiabank's requirements. This framework serves as the baseline for all data integration, ensuring consistency across different data sources.
Baseline Data Utilization: Use data directly from the Big 5 banks for the initial quarter to set a comprehensive baseline, ensuring the first layer of consistency and completeness.
Dynamic Data Augmentation: In subsequent quarters (Q2, Q3, Q4), when banks do not provide complete data, supplement with third-party data and employ web scraping techniques, particularly from Google, to fill in the gaps. This ensures the dashboard remains accurate and up-to-date.
Verification and Accuracy Checks: Implement verification processes to cross-check the augmented data for accuracy, ensuring the dashboard's reliability over time.
The automation of the dashboard through Python and PowerBI must be user friendly and easy to understand for future update
Video Documentation: Produce concise video guides detailing the coding and dashboard setup process. Emphasize the rationale behind each step and coding decision to provide users with a clear understanding of the system's functionality.
Clear Code Comments: Annotate the code within the Jupyter notebook with clear, accessible comments explaining each section's purpose and functionality, catering to users at different levels of technical expertise.
Key Features:
Branch Operating Hours Analysis: Displays current opening and closing times of all Scotiabank branches in Canada, segmented by days of the week. Special emphasis on extended hours and weekend operations, with a total hour calculation for each branch, district, region, and nationally.
Sales Volume Integration: Incorporates branch sales data for enhanced reporting by Market Leads.
Historical Comparison: Utilizes historical data to compare pre-COVID and post-COVID operating hours against current schedules.
Competitor Analysis: Mirrors the same data analysis for the other Big 5 banks (TD, CIBC, RBC, BMO), including their branch operating hours, with a focus on highlighting extended and weekend hours.
Proximity Mapping: Determines prime competitors based on the closest distance to Scotiabank branches, further categorizing competitors into Urban (<2.5KM), Suburban (<5KM), and Rural (<10KM) zones.
Step Performed:
Data Preprocessing: Leveraged Python for data cleaning and preparation. (remove duplication + white spaces, handling missing value, data type conversion...)
Spatial Analysis: Employed QGIS for mapping distances between Scotiabank branches and competitors.
Automation: Utilized Python to automate data processing and creation of the report, enhancing efficiency as this report is update once per month
Dashboard Creation: Developed the Competitors Hours Report Dashboard using Power BI. Later on could be updated by anyone by inputting Excel files from automation process
Documentation and Presentation: Compiled a comprehensive process documentation and produced an explanatory video. Presented the final dashboard and findings to the executive team.
Outcome: This dashboard serves as a strategic tool for executives, enabling informed decisions on adjusting branch operating hours to maintain competitive advantage. Additionally, it offers a nuanced analysis of operational shifts pre and post-COVID, contributing to a more thorough understanding of the banking landscape.
Project 2: Scotiabank ABM Performance Dashboard
Objective: Develop an advanced, user-friendly dashboard to monitor and analyze the performance of Scotiabank's Automated Banking Machines (ABMs) throughout Canada. This tool aims to identify ABMs experiencing frequent issues or prolonged downtime, facilitating proactive maintenance and quick resolution.
Challenges:
The dataset required meticulous cleaning and careful selection to identify the most relevant data for the project, ensuring it meets the specific needs of the ABM team.
Early and effective communication with the client team was crucial to accurately understand their requirements and constraints, ensuring the solution directly addresses their core issues within the project timeline.
Developing and sharing prototypes early in the process ensured ongoing alignment with the ABM team's expectations. This approach facilitated clear communication and feedback, essential for meeting the project's objectives.
The challenge in designing the dashboard
The dashboard must be able to answer and solve the provided issues while avoid being flooded with visuals so a dashboard with a focus on story telling is needed
The users must have an overview of the data before being able to perform an in-depth analysis of their point of interest (branches with problematic ABMs)
Key Features:
First Page - Comprehensive Overview: Presents a top 50 list of the most problematic ABMs, enriched with visual aids like pie charts categorizing component issues, and bar charts illustrating monthly performance trends.
Second Page - In-Depth Analysis: Enables users to conduct detailed investigations with advanced filters. This feature allows for a deep dive into specific branches or ABMs flagged in the overview, with flexible search capabilities.
Dynamic Comparison Tools: Both pages feature month-by-month comparisons, along with the ability to normalize data across different organizational levels (district, region, national). Users can apply multiple filter combinations for granular analysis (e.g., national and regional, or regional and district).
Step Performed:
Data Preprocessing: Employed Python for the rigorous cleaning and organization of datasets from the ABM team.
Component Classification & Ranking: Systematically analyzed and ranked ABMs based on the number of issues and the duration of downtime. This included categorizing ABM components and models by their downtime and issue frequency.
Automation: Utilized Python scripting to streamline data processing and automate the generation of monthly reports.
Dashboard Creation: Designed the ABM Performance Dashboard using Power BI, with an intuitive update mechanism via Excel file inputs from the automated process.
Documentation & Presentation: Compiled extensive documentation and an instructional video detailing the dashboard's development. Presented these findings to Scotiabank's executive team for strategic review.
Outcome: The dashboard emerges as a critical instrument for pinpointing and addressing the most problematic ABMs, marked by their downtime and frequency of issues. It equips executives with a visual understanding of performance trends and irregularities, empowering them to make informed, preemptive decisions (based on many factor shown in the dashboard like machine brand, or branch's location...). The dashboard's has advanced filtering options, alongside its analytical charts, enable a precise and effective approach to enhancing ABM performance. Consequently, Scotiabank is well-positioned to proactively address emerging issues, optimizing resource allocation, improving operational efficiency, and significantly enhancing customer satisfaction.