Handling ‘dirty data’ is a common obstacle, and in this particular project, the primary challenge revolved around formatting, particularly with respect to phone numbers. With no direct access to the core data source, we decided to tackle the issue through Python-based data cleansing methods.
Our primary goal was to establish seamless communication via SMS notifications for customers when their ISP provider’s tickets were received. This task entailed bridging the gap between ISP providers’ ticket generation system and our client’s ticket management platform. The process required quick, accurate, and effective communication flow. The source data, originating from ISP providers, contained a single field encompassing various contact information formats. Ranging from complete contact details to partial entries like just name and phone number or phone number and email, this mishmash posed a significant challenge. With multiple ISP providers, each featuring numerous agents, the situation demanded a strategic approach.
Our approach comprised a series of well-thought-out steps to address the formatting issue:
Initial Data Cleansing: The first step involved stripping away special characters from the contact information field, paving the way for consistent data processing.
Data Chunking: We proceeded to segment the data into discrete “chunks,” eliminating spaces between the chunks to streamline the subsequent analysis.
Number Identification Function: Each chunk of data was subjected to a specialized function. This function aimed to identify the chunk most likely to represent a valid Swedish mobile phone number. Key parameters guiding this assessment were a minimum of nine digits and specific prefixes like 46, 00, 07, or 7. This step was further simplified due to the prior removal of special characters.
Email Identification: In addition to phone number identification, we incorporated a function that utilized regex to identify potential email addresses within the data.
Optimizing for Twilio: Upon identifying the most probable Swedish mobile number chunk, we extracted the last nine digits and added +46 as the country code, making it ready for integration with Twilio.
Conditional Outputs: The resulting code provided valuable outputs including a boolean indicator for successful number identification, the cleaned phone number, and any found email address.
Workflow Integration: The boolean output acted as a pivotal point in the workflow. When integrated with Zapier, it determined the next steps based on whether a valid Swedish number was identified or not.
Interactive Customer Interaction: For cases where a valid number was identified, an SMS was dispatched using Twilio. This message contained a link to a Zapier interface—a form that customers needed to engage with.
The Zapier interface facilitated customer engagement, capturing necessary information while providing links to terms, conditions, and pricing. By incorporating the Freshdesk ticket ID into the URL, we established a seamless connection between customer information and ticket data.
By addressing data formatting issues, we ensured accurate communication, enhanced customer satisfaction, and maintained up-to-date client databases within Freshdesk.
This is what we do, we specialize in turning data challenges into innovative solutions, paving the way for optimized communication and improved business processes.
