The challenge
The company aimed to develop a standardized daily satisfaction survey process for Net Promoter Score (NPS). However, the “relational” Net Promoter Score (rNPS) was outsourced and lacked the flexibility needed for the business. The targeted monthly survey response rates were not being met. Additionally, the quarantine and blacklisting processes did not produce the expected results.
To address the needs of various data sources, the solution should support both “transactional” NPS (tNPS) and “product” NPS (pNPS). Stratified sampling becomes essential for both rNPS and pNPS.
Furthermore, at least seven different touchpoints are necessary for tNPS. To improve data collection, the database must include valid email addresses and phone numbers to ensure contacts and reduce waste. For managing survey links, a third-party provider (Medallia) will oversee the process, streamlining operations and increasing overall efficiency.
Why AWS
BTC successfully integrated all data sources and table generations into the AWS environment, creating a centralized data lake for querying and analysis by the analytics team. The most straightforward solution for building a Standardized Daily Survey Process for NPS was found in AWS’s versatile tool suite. Leveraging Strata’s expertise in AWS, a streamlined data pipeline was established, efficiently handling new data, preprocessing, and generating targeted client samples.
In sum, the combination of BTC’s vision and AWS’s prowess, coupled with Strata’s expertise, led to a remarkable solution that empowered the organization’s data-driven initiatives.
Why the Customer Selected the Partner
In a previous project, Strata partnered with BTC to develop a comprehensive Data Lake on AWS. We also created processes to map various data sources, establish mechanisms for data uploads, verify data consistency, and build analytical tables within the AWS environment. The successful deployment of this AWS-based solution laid a strong foundation for our expertise in managing the requested current model.
Selecting Strata as the preferred partner for this project was well justified. Our experience designing complete AWS solutions across the telecommunications sector and beyond makes us the ideal choice for this task. We bring extensive knowledge and skill, ensuring outstanding results on this project as well.
Partner Solution
The proposed solution is based on extracting information from the DNA of Postpaid, Fixed, and Prepaid B2C clients to build a comprehensive dataset. The conditions for creating the dataset vary between tNPS, rNPS, and pNPS. While tNPS is based on clients’ actions from the previous day (7 days ago), rNPS and pNPS are not; all DNA-active clients are included.
This solution adheres to a daily priority order: clients are first reserved for high-priority touchpoints like t-onboarding, t-install, and others. Next, rNPS is addressed, then lower-priority touchpoints such as t-change, and finally, pNPS is evaluated.
The solution employs a multi-stage approach. Here’s the sequence in which it was executed:
1. Sampling Business Intelligence Filters: This process involves taking the dataset created for the entire NPS process and applying filters based on the database of quarantined clients and the blacklist. The output is the “Sample Space” database. Only customers with at least one contact method, such as email or cell phone, are selected.
2.a. For tNPS: Sampling Customer Care Filters: Starting with the Sample Space file, customers are further filtered, keeping only those who meet specific eligibility criteria for each touchpoint. The result is a database of potential customers to survey.
2.b. For rNPS and pNPS: Sampling Customer Care Filters Sampling: Building on the previous step’s results, customers are filtered again to keep only a specific subset. Client eligibility is determined by the target number of customers to be identified by the end of the month, according to the “Autoscaling” process outlined later.
3. The selected customers from different processes (rNPS, tNPS, and pNPS) are combined, and relevant variables of interest are added. Subsequently, this file is sent to Medallia to conduct the surveys.
For rNPS and pNPS, a process called Autoscaling is created to automatically adjust the number of surveys that will be sent for the rest of the month based on the number of responses received in the previous days.
The Autoscaling process is defined by setting a fixed number of responses to be received per product each month, with the goal of reaching that target by the end of the month. Surveys are sent from the first business day of the month; however, for obvious reasons, not all customers respond. This is where the autoscaling process comes into play, as it works to obtain the required responses by the end of the month by determining daily how many surveys need to be sent.
HL Architecture
A distributed data processing system was required due to the high daily data volume, and AWS Glue for Apache Spark was the best service to meet the customer’s requirements.
The data sources mentioned above are processed through a scheduled event, and an AWS Glue ETL job runs to filter and carefully clean the data, discarding incomplete or clearly erroneous entries. Additionally, filters from clients on the blacklist and quarantine (in that order) are applied. The blacklist database is generated by Amazon Athena from received feedback surveys and updated daily.
The Sample Frame, which is the first “clean” dataset in the staging area, is created by the AWS Glue Job Process and serves as input for the next Sampling CC Filters activity. Following the priority order, the generated dataset acts as a master sample, and high-priority touchpoints, rNPS, low-priority touchpoints, and pNPS are processed sequentially. Both pNPS and rNPS processes are subject to Autoscaling. The Autoscaling process, along with generating outputs for tNPs, pNPS, and rNPS, is executed using AWS Glue for Apache Spark Jobs, connected to the AWS Glue Data Catalog, and stores partitioned data in the project bucket. Similar to the blacklist, the Autoscaling process is driven by feedback received, adjusting the number of users to contact based on a simple mathematical algorithm.
Once the dataset to be contacted is created, an AWS Step Functions state machine is triggered to coordinate the subsequent process, which completes each survey with additional information, mainly using AWS Lambda and Amazon Athena. Once finished, output files are generated in CSV format and stored in a designated Amazon S3 bucket with a lifecycle policy. This script triggers an event via Amazon Simple Notification Service (SNS). These files are sent daily and recorded in the AWS Glue Data Catalog.
After the company conducting the surveys completes the NPS process, the resulting CSV is published in a specific Amazon S3 bucket. The related Amazon SNS topic activates an AWS Lambda function that updates the survey results and simultaneously creates a file in the AWS Glue Data Catalog to add the information to the DNA. This serves as feedback for the entire cycle and will provide the dataset that Amazon Quicksight will use in the future.
Results
The implemented solution is fully automatic; when new information becomes available, it triggers the entire process, generating an output in approximately 45 minutes. The solution was designed to be flexible, allowing potential modifications to execution order and priorities in the future without significant inconvenience.
Next Steps
The following steps are aimed at generating a near real-time orchestration similar to the one involving shipments three times a day for the touchpoints: t-Installs (installations), t-Install self-service (installations made by the client), and t-Truck rolls (repairs). This will be achieved by directly utilizing the real-time updates of Service Orders (SO).
