Category: Uncategorized

The Challenge 

A US based Credit Union wanted to streamline its employee onboarding process. Earlier all stakeholders used to exchange numerious emails to communicate status of creation of credentials in various banking platforms, softwares, systems, services and departments for new employees. This resulted in chaos, delayed onboarding, repeat communication and wasted man-hours.

The solution 

To resolve this coordination and streamiling issue, we suggested a central control by SharePoint List. The list access was provided to all the concerened owners of various banking platforms, softwares, systems, services and departments. The owners can write all credentials and status of a new employee in a collaborative manner. The credentials are to be set new on first log in by the new employee. The the new employee data came from MS-SQL Server populated by a HRMS System. An Alteryx Workflow was developed to pick up data from SQL Server and create a new JSON File for each new employee in a Local Machine’s Shared Folder.

A Power Automate Flow was developed to fetch the JSON Files whenever created via a On-Prem Data Gateway. The Data was parsed in Power Automate Flow and inserted to the SharePoint List. Subsequently a separate Power Automate Flow was also developed to trigger on change of status of a particular column and send Email notification to new employee and Hiring Managers with or without credentials. 

The result

 This saved a lot of manhours for the IT Team and all other departments who earlier used to co-ordinate manually. 

Tools and Platforms Used 

The Challenge

The challenge is to utilize the presence data captured by various network access points in bank branches to perform real-time analysis of customer and visitor behavior, specifically their dwell time during operational banking hours.

The Solution

Capture: Wireless access point data is collected to track customer presence.

Session Duration Determination: Defining a session duration for identifying new visits.

Filtering In-house and Staff Devices: Removing data from in-house and staff devices.

Cost-efficient Snowflake Execution: Employing methods to keep the Snowflake execution cost low despite ingesting data almost every minute.

Steps

Configuration of Cisco network devices to post minute-by-minute data to a bank webservice endpoint hosted on AWS.

Capturing JSON data from the bank webservice endpoint API calls, storing it in AWS S3, and ingesting it into Snowflake stage via SnowPipe.

Analyzing total device durations by day in business hours, excluding bank and staff devices, using the 80th percentile value as the session duration, averaging around 70 minutes.

Employing an Alteryx ETL pipeline for other network data capture in Snowflake.

Balancing data speed with Snowflake cost; allowing minute-by-minute ingestion into Snowflake stage, and performing gold layer data tasks thrice daily during business hours.

The Result

The implementation facilitated reliable ingestion of high-frequency, low-volume wireless device data without queuing. The bank can now effectively analyze dwell time, peak days, and peak timings across multiple branches, aiding in resource planning for improved customer service.

Tools and Platforms Used

The Challenge

The India-based general merchandiser faced a unique challenge in their retail stores. Customers typically spent an average of 35 minutes shopping before reaching the Point of Sale (POS) for payment. However, by the time customers arrived at the POS, they had already made their purchase decisions. This not only resulted in missed opportunities for upselling and cross-selling but also meant that valuable shopping time had been underutilized. The retailer aimed to target repeat customers for these opportunities based on their profiles while they were still shopping, well before reaching the POS for payment.

The Solution

To overcome this challenge and optimize customer engagement, a comprehensive solution was implemented:

Repeat Customer Identification: The solution initiated with the identification of repeat customers using Computer Vision face recognition technology. Cameras were strategically placed at store entry points, exit points, and POS/cash counters. OpenCV Face Detection library was utilized in edge devices to detect faces and initiate image capture.

Dwell Time Tracking: The system tracked customer dwell time at various capture points and utilized face recognition powered by AWS Rekognition. Transaction records were stored in AWS DynamoDB for future reference.

Customer Profiling: Customer profiles were gradually built by linking POS order records in SQL Server with customer records in AWS DynamoDB. This linkage was executed nightly to minimize load on the POS system during peak business hours.

Real-time Offers: When a customer entered the store, the Computer Vision program triggered the customer’s profile and retrieved personalized offers from DynamoDB. Store associates were alerted through an Android app to approach the customer for upselling and cross-selling.

The Result

The implementation of this solution led to substantial improvements for the India-based general merchandiser:

In-Depth Customer Insights: The retailer gained valuable insights into customer behavior, including repeat visits, purchase patterns, exact dwell times, purchase conversion rates, demographic profiles, and brand preferences based on demographics.

Proactive Customer Engagement: Store associates were equipped with real-time customer profiles and offers, enabling them to approach customers with personalized upsell and cross-sell opportunities based on customer segmentation.

Enhanced Sales and Customer Satisfaction: By engaging customers with targeted offers and recommendations during their shopping journey, the retailer experienced increased sales and improved customer satisfaction.

This client success story illustrates how an India-based general merchandiser leveraged Computer Vision, AWS services, and customer profiling to optimize customer engagement, drive sales, and gain valuable customer insights.

Tools and Platforms Used

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

The Challenge

The credit union sought to gain insight into its customers’ cryptocurrency transaction statistics to launch a targeted campaign aimed at raising awareness of cryptocurrency-related risks and preventing outflows of funds from the bank. However, the challenge lay in accurately identifying crypto transactions within a vast master transaction table. This required applying various filtering criteria to each individual transaction row. Additionally, the limited availability of cryptocurrency information, primarily derived from transaction comments, posed an additional hurdle. With billions of records in the transaction table, processing such a volume efficiently was a daunting task.

The Solution

To address this challenge and enable targeted campaign planning, a comprehensive solution was implemented:

Change Data Capture (CDC) Stream: A CDC Stream was developed within the Snowflake database to capture changes in the master transaction table. This stream served to reduce the volume of data to be processed each day.

Daily Data Processing Task: A Snowflake ‘Task’ was created to consume the CDC Stream daily. This task executed various filtering criteria to identify rows corresponding to crypto transactions. The identified rows were then inserted into a new table dedicated to crypto transactions, all within the Snowflake database.

BI Application Integration: The dedicated crypto transaction table was made accessible to a Business Intelligence (BI) Application, ThoughtSpot. This allowed for in-depth analysis of various metrics and Key Performance Indicators (KPIs).

The Result

The implementation of this solution delivered substantial benefits:

The credit union gained valuable insights, such as identifying the top account holders engaged in crypto transactions, the most frequently transacted cryptocurrencies, age and region distribution of crypto users, and the volume of funds flowing in and out of the bank in relation to cryptocurrencies.

Armed with this information, the credit union executed a targeted campaign to sensitize account holders about the risks associated with cryptocurrency trading, helping prevent potential financial outflows.

The solution also revealed account holders who were using credit cards to purchase cryptocurrencies, a practice contrary to the organization’s rules, enabling the credit union to take appropriate actions.

This client success story showcases how a credit union harnessed Snowflake’s CDC Stream and Task features to enhance its monitoring of cryptocurrency transactions, enabling data-driven insights and informed decision-making to safeguard the organization’s financial interests.

Tools and Platforms Used

The Challenge

The US-based bank faced a recurring challenge in the distribution of VISA card statements and defaulter notices to its extensive member base. These files, provided by the VISA FTP site, were of two types: Monthly credit card statements and defaulter notices, each in PDF format. While the individual file sizes were manageable, the combined size of these files when zipped reached gigabytes. The bank’s existing manual process involved downloading these files on the 1st of every month, unzipping them into a local folder, and subsequently transferring them to a network share. The automation workflow would then pick up these files and distribute them to individual members. However, this manual process often encountered failures during the unzip or file transfer steps, necessitating time-consuming repeats. As a result, nearly 40 man-hours were spent on this process each month.

The Solution

To address this challenge and optimize the distribution of VISA card statements, an automated solution was implemented:

Automated File Fetch and Unzipping: The solution involved automating the retrieval of files and directly unzipping them into the network share. This was achieved by invoking the command-line execution of the 7z utility, eliminating the need for manual intervention.

Workflow Automation: An Alteryx workflow was created and scheduled to run on the 1st of each month. This workflow managed the entire file fetching and unzipping process.

Tracking and Logging: Snowflake, a cloud-based data warehousing platform, was employed to log and track the downloaded and extracted files. This provided transparency into the process and facilitated error detection and resolution.

Error Handling: The program utilized 7z/CMD error codes through CMD Echo commands to verify the successful execution of the process. In case of any errors, notifications were triggered for rerunning the process.

The Result

The implementation of this automated solution yielded significant benefits:

Approximately 40 man-hours were saved each month, reducing the operational burden.

The process became more reliable, eliminating manual errors and the need for repetitive tasks.

Members experienced faster receipt of their VISA card statements, enhancing their overall banking experience.

This client success story illustrates how a US-based bank streamlined the distribution of VISA card statements, significantly reducing manual efforts, improving reliability, and expediting the delivery of essential financial information to its members.

Tools and Platforms Used

The Challenge

The credit union relied on FICS’s Loan Producer and Mortgage Servicer products for their mortgage services, which included access to several pre-built reports delivered through SAP Crystal Reports. However, this setup posed several challenges. Any modification to the reports required custom paid services and a release cycle, causing delays and additional costs. Moreover, the use of SAP Crystal Reports was redundant, given that the credit union had adopted ThoughtSpot as its organization-wide business intelligence (BI) tool. The task at hand was to migrate the existing reports from SAP Crystal Reports to ThoughtSpot, despite having only report queries and lacking a logical or physical data model.

The Solution

To address this challenge and provide the credit union with flexibility and control over their mortgage reports, a comprehensive solution was devised:

Data Model Reverse Engineering: The solution involved reverse engineering and deducing the data model from the existing report queries. This critical step aimed to understand the underlying data structure and relationships.

Data Model Migration: The physical data model was migrated from SQL Server to Snowflake, a cloud-based data warehousing platform.

Data Validation and Comparison: All 14 report queries were meticulously analyzed, and a data model was constructed accordingly. The data was temporarily loaded into Snowflake, and the same report queries were executed in Snowflake. The results were compared to ensure data accuracy and consistency.

Automated Data Loading: Alteryx workflows were developed to enable the recurring loading of data into the data model within Snowflake. This automation ensured that the data remained up-to-date and readily available for reporting purposes.

ThoughtSpot Integration: The same data model was declared within ThoughtSpot, the organization’s chosen BI tool. A ThoughtSpot LiveBoard was created to host all 14 legacy reports, providing business users with a consolidated view.

The Result

The implementation of this solution delivered significant benefits to the credit union:

Business users gained a unified view of all 14 legacy reports within ThoughtSpot.

The newfound flexibility allowed users to independently customize views, apply filters, and create reports without requiring developer intervention, a stark contrast to the previous dependence on SAP Crystal Reports.

The migration to ThoughtSpot reduced the reliance on custom paid services and release cycles, streamlining the reporting process and minimizing costs.

This client success story exemplifies how a credit union harnessed data model reverse engineering and ThoughtSpot integration to transition from SAP Crystal Reports, providing business users with self-service capabilities, enhanced flexibility, and control over their mortgage reports.

Tools and Platforms Used

The Challenge

The US-based healthcare company faces a daily influx of hundreds of provider appointments, generating a substantial volume of free-text encounter assessments. While electronic health record (EHR) data in healthcare is predominantly structured, essential information like doctor’s prescriptions and encounter assessments often remains in unstructured, free-text formats. These assessments may include critical details such as medication information, referrals, and follow-ups. Converting this unstructured data into a structured, classified format at scale and through automated means was a significant challenge. This transformation was crucial for efficient provider workload planning, medication monitoring, and faster care actions.

The Solution

To tackle this challenge, a comprehensive solution was devised, encompassing the following key steps:

Data Cleanup and Tokenization: The unstructured free-text encounter assessments of patients were retrieved from the Athena Health Encounter API. Given that the Encounter API output was in HTML code, the assessment part was extracted by cleaning the data. Tokenization and sentence detection were performed using the Spacy Python NLP library.

Machine Learning Classification: A machine learning (ML) model was built using the Keras library. This model aimed to classify raw data into multi-class, multi-label categories, including Medication, Diagnosis, ‘Notes for Pharmacy,’ ‘Internal note for Staff,’ Referral, and ‘Note to patient.’ The process involved tokenization, cleaning, vectorization, and label encoding of unstructured free text.

ML Model Optimization: Extensive experimentation was conducted with various model architectures, hyperparameters, and text preprocessing techniques to optimize the ML model’s performance.

Service Endpoint: A web API service endpoint was established in Azure Kubernetes Service, utilizing a cluster of 3 nodes with one dedicated to load balancing.

Security Measures: Authentication and authorization to run the ML model were implemented through Azure Active Directory and App Registration. Only authorized applications with appropriate secret credentials could access the ML model.

Integration with Data Workflow: The API was integrated into the data workflow using Talend Data Integration. This integration facilitated the retrieval of Athena Health Encounter data, data cleansing, and the transmission of assessment text to the API. The resulting multi-class information was stored in Salesforce for action by the care team.

The Result

The implementation of this solution delivered substantial benefits:

The care team gained the ability to act swiftly and efficiently on various encounter assessment categories, including Medication, Diagnosis, ‘Notes for Pharmacy,’ ‘Internal note for Staff,’ Referral, and ‘Note to patient.’

Manual efforts were significantly reduced, leading to operational efficiency and time savings.

Service quality was notably improved, ensuring that patients received timely and accurate care.

This client success story showcases how the US-based healthcare company harnessed machine learning and automation to transform the analysis of free-text encounter assessments, ultimately enhancing the speed and quality of care services provided to patients while optimizing operational efficiency.

Tools and Platforms used

The Challenge

The leading patientcare company plays a vital role in delivering care services to thousands of members daily. However, a significant challenge they faced was the need to verify insurance eligibility for each member before rendering any care service. The company offered various care services, conducted health surveys, planned follow-ups, scheduled appointments, and coordinated care activities. Despite categorizing members into different groups and attempting parallel checks, the process of daily eligibility verification was extremely time-consuming, taking nearly 18 hours. This lengthy eligibility checking process hindered the company’s ability to plan and deliver immediate services to patients in need.

The Solution

To overcome this challenge, a comprehensive solution was engineered leveraging Azure services. The process of checking eligibility for all members was transformed from its previous Talend-based setup to Azure.

Azure Service Bus Integration: Each member’s information was converted into an Azure Service Bus Queue message, triggering web API calls to Athena Health for eligibility checks.

Serverless Processing: Azure Logic Apps, in a serverless implementation, processed almost 800 messages per second. This included filtration logic and delays, allowing Athena Health to efficiently gather and parse the insurance eligibility information.

Optimized Database Handling: The bottleneck of inserting eligibility information messages into an Azure SQL database was eliminated. Batching the messages and inserting batches of 500 into the database significantly improved processing efficiency. The batch JSON was parsed to write individual rows in the database table.

The Result

The transformation to Azure-based processing yielded remarkable results. The eligibility checking process, now running with concurrency enabled, was completed in just 3 hours. This represented an astonishing 83% reduction in processing time compared to the previous 18-hour timeframe. As a result, the care team gained the ability to access insurance eligibility status for a member at the start of their day, enabling them to schedule same-day appointments with providers and offer timely care services to their members.

This client success story exemplifies how the adoption of Azure services revolutionized insurance eligibility checks for a leading patientcare company, significantly reducing processing time and enhancing the company’s ability to provide timely and efficient care services to its members.

Tools and Platforms Used

The Challenge

The multi-state healthcare company specializes in delivering reliable care services to its patients. However, they faced a critical challenge concerning the reliability of their data processing programs. Complex business rules and the dynamic nature of data issues often led to failures in data processing. In situations where source data was available only once, such as when a new medicine was prescribed to a patient, the data could not be retrieved if the data processing program failed. This unreliability in data severely impacted the company’s ability to make informed business decisions.

The Solution

To address this challenge and ensure data reliability, a robust solution was implemented. When dealing with source data available only once (maximum one delivery of an event), the priority was to convert it into an “at least once” delivery model. This required the persistence of data until the data processing program successfully processed it.

Athena Health provided several subscription APIs that offered new or modified data since the last subscription read. These subscriptions essentially functioned as message queues. The solution involved persisting this data in an Azure service bus topic. A straightforward job was created to consume Athena Health subscription data and store it in the Azure service bus topic. As this job did not involve complex business logic, the chances of failure were minimal. A second crucial job then read from the Azure Service Bus topic, implemented complex business logic, managed exceptional cases, and, upon successful processing, removed the data from the topic. In cases of program failure, the data underwent up to 5 configurable retry attempts and was persisted for up to 10 configurable days until all issues were resolved, and successful reprocessing occurred.

The Result

The implementation of this solution had a profound impact on data reliability for the multi-state healthcare company. It significantly improved their ability to accurately identify patient problems/conditions and access patient ordering information during patient visits or when planning appointments. With a more reliable data processing system in place, the company could make more informed decisions and deliver even more dependable care services to their patients.

This client success story showcases how Fulz Tech’s solution enhanced data reliability for a multi-state healthcare company, ultimately empowering them to provide more dependable care services and make informed patient-centric decisions.

Tools and Platforms Used