Keeping track of customer details and policy data can quickly become overwhelming, especially when you’re juggling multiple tools and systems. Manual updates take time and leave plenty of room for error.
Thankfully, there’s a smarter way to handle this and it’s called RPA.
Imagine having a system that automatically updates customer information the moment someone submits a form, and at the same time, synchronizes policy data from other platforms, so everything stays up to date without lifting a finger.
By connecting tools like JotForm, Airtable, and QQCatalyst through seamless API integrations, businesses can create an efficient workflow that keeps all critical data in sync, ensuring that teams have access to the most accurate information at any given moment.
In this article, we’ll walk you through how this automated system was built for our client and the benefits it brings to managing customer and policy data.
Client’s Pain Points in Customer Data Management
Our client, a leading insurance company, faced significant challenges in managing customer data submissions and policy creation. The company received customer information via JotForm, and employees had to manually process each submission, upload attachments, and create policies in the QQCatalyst platform.
The core problem was the manual nature of data management, which involved several repetitive tasks. Employees spent a significant amount of time:
- Extracting Data: Manually pulling information from each JotForm submission, which consumed a lot of time.
- Checking for Duplicates: Review customer profiles in QQCatalyst to ensure no duplicates were created, leading to potential errors.
- Creating Profiles and Policies: Entering new customer information and creating policies in the QQCatalyst platform, which was tedious and error-prone.
- Uploading Attachments: Manually uploading and organizing necessary documents and attachments associated with each submission.
- Sending Confirmation Emails: After processing the data, employees had to send confirmation emails to customers, adding more steps to an already lengthy process.
This manual workflow resulted in significant delays in processing customer data, reduced overall productivity, and frequent errors, such as duplicate records or incorrect policy information. With all these headaches piling up, it was obvious they needed an automated solution to make things run smoother and get their operations back on track.
Bitcot’s RPA-Based Policy and Customer Data Management System
To address these inefficiencies, the client needed a solution that could automate the entire process – from data extraction to policy creation – without sacrificing accuracy. The solution also needed to ensure seamless integration between JotForm and QQCatalyst, eliminating the need for manual intervention.
The goals of the project were to:
- Automate the processing of customer data submitted through JotForm.
- Seamlessly integrate this data into the QQCatalyst platform.
- Ensure the accuracy and consistency of new policies without manual intervention.
To achieve these goals, an RPA (Robotic Process Automation) workflow was developed using Power Automate, leveraging both cloud and desktop flows. The workflow managed API interactions, data extraction, authentication, and tasks that required desktop operations.
The solution was structured as follows:
Cloud Flow (Power Automate Cloud)
The cloud flow handled most of the high-level operations:
- Trigger: The flow is triggered whenever an email containing customer data from JotForm is received in Gmail.
- File Management: Attachments, such as customer documents and policies, are automatically downloaded and stored in
SharePoint in a well-organized folder structure based on date and customer details.
- Data Extraction and Storage: Customer details, such as names, addresses, and policy details, are extracted from the JotForm PDF and saved into a SharePoint Excel file, categorized by customer name, ensuring centralized storage and easy retrieval.
- Customer Validation via API Using a Search API, the system checks if the customer already exists in the QQCatalyst database. If the customer exists, the workflow pulls existing data from QQCatalyst and proceeds to update policy details; if not, a new customer record is created via API.
- Policy Creation and Data Integration: After the contact is created or validated, the system creates a new policy directly in QQCatalyst using the contact’s details. This step ensures that policy creation aligns with the company’s insurance management system, keeping all data consolidated.
- Updating Airtable with Policy Data: Airtable is integrated via API to update customer details, triggered by its connection to JotForm. The newly created policy information, including the Policy ID from QQCatalyst, is synchronized with Airtable. This allows tracking and managing policies in real-time.
- Automation with Desktop Flows: In this phase, Desktop Flows are used to automate the creation of three separate folders in SharePoint for organizing policy-related files. The system also moves attachments to designated marketing folders, keeping everything organized for future use.
- Adding Customer Details and Property Information: The system enriches customer data by adding additional information such as Date of Birth (DOB), property address, and a secondary contact. These updates are made both in QQCatalyst and through Desktop Flows.
- Final Notifications: Once the policy is successfully created and all data is updated, a final email notification is sent out to notify the relevant stakeholders that the process is complete.
Desktop Flow (Power Automate Desktop)
For tasks that couldn’t be managed by the cloud, a desktop flow was created to:
- File Operations: Create new folders, move files, and handle file management tasks that require interaction with the local system.
- Profile Updates: In cases where additional information, such as Date of Birth, was missing from JotForm submissions, the desktop flow was responsible for adding these details to the QQCatalyst profiles.
The Process Behind Our RPA Solution
First up, we’ve set up a flow that triggers whenever we receive an email with a specific subject line. This email should also have attachments in PDF or PNG formats. If these conditions are met, we perform our operations on the PDF files. This ensures that we’re only processing relevant information.
Once the email comes in, we check for PDF files that follow a specific naming convention – essentially, the contact’s name followed by the effective date, ending with “.pdf”. If everything checks out, we create a file to extract data from that PDF and store it in an Excel file.
Next, we check if the required file already exists. If not, we proceed to create an Excel file for any API error records. We ensure that this file is created only if it doesn’t already exist in the folder. We have templates ready for both API error records and data extraction, neatly organized in our factory-made folder.
Now, let’s move on to data extraction. We pull mailing addresses directly from the email body since it comes in a perfectly comma-formatted value. We then parse this information in JSON, gathering various details including the mailing address, physical address, property address, secondary applicant on loan, and date of birth.
Now, we iterate over the attachments we receive. If any of the attachments match our naming convention, we proceed to process them. If they don’t match, we simply save them into a designated folder.
As we handle the PDFs, we ensure they’re labeled correctly. For instance, if we receive a PDF in October, it will include the current date. We use a unique identifier based on the naming convention to save the attachments.
If the file name matches our conditions, we process it further and save the file into the appropriate folder. This entire process is aimed at extracting data efficiently from JotForm.
After data extraction, we get the access token needed for our API interactions. Once we have that, we search the QQ Catalyst portal to see if the contact already exists. If they do, we filter out the existing records. If not, we note that the contact is not present in the system.
We also have an API for looking up state codes to ensure we’re matching values correctly when creating a new contact.
If the contact doesn’t exist, we proceed to create it in the QQ Catalyst portal using its API. If the contact is created successfully, we get a successful response, which allows us to move forward with the next steps. If not, we maintain error logs for further review.
Once the contact is successfully created, we add a policy for that contact using the “Add Policy” API from QQ Catalyst. If the policy is created successfully, we receive a success response, and based on this, we check the overall workflow to ensure everything is proceeding smoothly.
Airtable Update Record Subflow
Finally, if the policy is created without any issues, we call a subflow related to Airtable records, ensuring that all data is seamlessly integrated and managed.
This subflow is designed to efficiently update records in Airtable while handling various operations such as data retrieval, filtering, and uploading attachments.
1. Setting Up the Subflow
The subflow begins by initializing fixed variables that will be used throughout the process. This sets the groundwork for the operations we’ll perform.
2. Retrieving Data from Airtable
Next, we retrieve data from Airtable. We filter records based on a specific view, focusing on those in the “quad pending queue.” This allows us to narrow down our data for more efficient processing.
3. Filtering Records
Using a filter array, we check whether the desired records are already present in Airtable. If a record exists, we proceed to upload the relevant attachments and update the queue accordingly. If a record is not found, we simply return a response indicating that the contact is unavailable in Airtable.
4. Uploading Attachments
If the upload is successful, the subflow will return a status code of 200, confirming that the attachment has been uploaded to Airtable successfully.
5. Updating Airtable Status
Upon completion of the upload, we revisit the flow to set the Airtable status based on the results of the previous steps. This ensures that the information in Airtable is always up-to-date.
6. Running the Desktop Flow
The next step involves executing a desktop flow where we pass several input variables, including the mailing address, physical address, property address, and the SharePoint full path URL. This is crucial for maintaining time zone accuracy.
7. Main Flow Operations
Within the main flow, we create lists to establish folders in the QQ portal. We also get the current date in the required format to manage our files effectively.
8. SharePoint File Management
A key component of our subflow is managing files stored in SharePoint. We check for the existence of necessary folders and create them if they do not already exist. Additionally, we retrieve all files from SharePoint and verify that we have data to work with.
9. Logging into QQ Portal
We perform logins on the QQ portal to facilitate file uploads and folder creation processes. This authentication step is critical to ensure secure access to our data management systems.
10. File Upload Process
After authentication, we initiate the folder creation and file upload flow. This step allows us to create folders associated with newly created policies and upload files into designated marketing folders.
11. Updating Personal Information
As part of maintaining accurate records, we update the date of birth for newly created contacts in the personal information section on the portal. This ensures that all relevant data remains current and accessible.
12. Handling Secondary Applicant Details
Finally, we manage secondary applicant details as part of the overall data update process. This ensures that all necessary information for contacts is captured and stored in Airtable accurately.
Subflow for Property Address Management
Now let’s explore the subflow designed to manage property addresses, including physical and mailing addresses, as well as handling additional contact details. The primary purpose of this subflow is to maintain comprehensive records of various address types – physical, mailing, and property addresses.
We begin by passing the relevant address details based on specific conditions that dictate how these addresses are organized and stored.
Address Handling
Upon receiving the address data, the subflow splits the values and stores them in the appropriate fields within the portal. This ensures that all relevant address information is accurately captured and can be easily accessed when needed.
The address values are stored in a hidden address field, allowing for seamless integration into the overall data management system without cluttering the user interface.
Managing Additional Contact Information
In addition to address management, the subflow includes functionality for maintaining secondary contact information. This involves checking whether the secondary applicant’s name and date of birth (DOB) fields are not empty, false, or null. This validation step is crucial to ensure that only valid data is processed.
Once confirmed, we update the portal with the secondary applicant’s details, including their first name, last name, and DOB. This ensures that all pertinent information related to secondary contacts is accurately recorded.
Flow Structure
The desktop flow handles the core processes, while the cloud flow oversees the integration and communication between systems. This dual structure allows for efficient data management and enhances the overall performance of the subflow.
The Key Features Behind Our RPA Solution
Our RPA solution is designed to tackle the pain points of manual data management head-on, streamlining the entire process with some key features. These features come together to create a smooth and efficient experience, transforming how our client manages customer data and policies.
Automated Data Processing
Customer information submitted via JotForm was automatically extracted, processed, and formatted before being sent to QQCatalyst. The cloud flow handled this operation seamlessly, ensuring all required customer data was accurate before creating or updating profiles.
Duplicate Profile Check
To prevent duplicate entries, the workflow integrated QQCatalyst’s Search API, which automatically verified whether a customer profile already existed. If a customer was found, the profile was updated; if not, a new profile was created.
Policy Creation
Once the customer’s profile was either updated or newly created, the flow utilized QQCatalyst’s Create Policy API to generate a new policy based on predefined customer data, eliminating the need for manual policy entry.
Attachment Upload and File Organization
Attachments received via email, such as policy documents, are automatically downloaded and stored in SharePoint. The workflow ensured that files were organized in a structured folder system based on current date and submission details, making it easier to retrieve and manage documents later.
Final Notifications
After each workflow cycle, a final confirmation email was sent to the team and the customer to notify them that the data has been successfully processed and updated in QQCatalyst.
API Integrations
- JotForm: Extracted customer data from submitted forms.
- QQCatalyst: Created new customer contacts and policies using QQCatalyst’s API.
- Airtable: Used for updating customer details as part of the data processing workflow.
- SharePoint: Managed the storage and organization of customer documents and attachments.
Error Handling
The solution included built-in error handling to ensure that if any data extraction or API call failed, the flow would notify the operations team and provide a detailed error log. This helped in resolving issues quickly and minimizing disruptions.
The Client’s Major Impact and Success With Our RPA Solution
By automating this complex workflow, each key role at our client’s organization benefits from tailored solutions that address their specific needs, ensuring an efficient and accurate process:
- Customer Data Managers no longer need to manually upload data into QQCatalyst. The system automatically extracts and processes customer data from JotForm submissions, streamlining the workflow.
- System Administrators can rely on the system to check for existing customers before creating new profiles, preventing duplication and keeping the database clean and accurate.
- Operations Leads benefit from automated file management, with attachments being uploaded and organized into appropriate folders, ensuring a streamlined and consistent process without manual intervention.
- Policy Managers enjoy automated, error-free policy creation, as the system generates policies based on extracted data, eliminating the need for manual entry and ensuring greater accuracy.
- Account Managers receive a final confirmation email detailing the newly created contact and policy, ensuring they can easily track the completion of the workflow without any missed steps.
The RPA solution delivered several impactful outcomes to the client:
Time Savings
The automated workflow reduced the time spent on manual data entry and policy creation by over 80%. Employees no longer needed to manually process submissions or create policies, freeing them up to focus on higher-value tasks.
Improved Accuracy
The elimination of manual data entry significantly reduced the risk of human error. Customer profiles and policies were now created with consistent accuracy, ensuring that information was always correct and up-to-date.
Seamless Data Integration
By automating the integration between JotForm, Airtable, QQCatalyst, and SharePoint, the solution enabled seamless data transfer across platforms, ensuring that all systems were synchronized without manual intervention.
Better Document Management
The automated file organization process ensured that customer documents were consistently stored in the correct SharePoint folders, making retrieval fast and easy.
Increased Operational Efficiency
With the automation of repetitive tasks, employees were able to improve overall productivity and focus on customer service, rather than administrative work.
Enhanced Customer Experience
By speeding up the data processing and policy creation steps, PacPremier was able to provide faster and more accurate service to their customers, leading to increased satisfaction and trust.
Final Thoughts
The integration of JotForm, Airtable, and QQCatalyst, along with the implementation of Power Automate, has made a big difference in our client’s customer data processing workflow, resulting in significant improvements in efficiency, accuracy, and time management.
Automating those tedious, error-prone tasks has not only saved time but also boosted efficiency and accuracy across the board, letting the team focus on delivering faster, more reliable service to customers.
This project shows how powerful automation can be when combining RPA and API integrations in modernizing business processes. Not only did it solve the client’s immediate challenges, but it also laid the foundation for future scalability.
As the company grows, this automated solution will enable it to handle larger volumes of customer data without adding extra work or overhead.
If you’re curious about how automation could work for your business, feel free to reach out to our team. We’re happy to discuss and explore the possibilities together.
