In today's digital landscape, businesses often struggle with managing data spread across multiple systems and platforms. This was the challenge our client faced—data scattered across Excel sheets, Mailchimp audiences, different Bexio accounts, and various legacy systems, each having isolated contact information. The lack of integration between these systems led to duplicated contacts, inconsistent data, and inefficiencies in operations. Our task was to consolidate these data silos, eliminate duplicates, and build an interactive solution that would improve CRM (Customer Relation Management)and operational efficiency.

Analyzing Systems and Data Quality

The first step in this project was a comprehensive analysis of the client’s existing systems and the data they contained. We conducted an audit of each data source, including Excel lists, Mailchimp, Bexio, and the legacy systems that the client had relied on for years. The goal was to understand the structure of the data, identify inconsistencies, and assess the overall data quality.

The audit revealed significant challenges. Not only were there numerous duplicate contacts across systems, but the data also varied in quality—some records were incomplete, others outdated. Additionally, the lack of integration between these systems meant that the same contact information could differ from one platform to another, leading to mistakes and inefficiencies.

Before:

Merging Duplicates and Enhancing Data Quality

With a clear understanding of the client’s data landscape, we proceeded to consolidate the information. This process involved the careful merging of duplicate contacts across all systems. We developed and implemented algorithms to identify and match duplicates, taking into account variations in name spellings, email addresses, and other contact details.

As we merged the data, we also took steps to improve its overall quality. This involved standardizing address formats, filling in missing information, and removing outdated or irrelevant records. This allowed the final dataset to not only be consolidated but also accurate, up-to-date, and ready for scale.

Building a Custom ERP System with Airtable

The next phase of the project was to import the cleaned and consolidated data into Airtable, a flexible database platform known for its user-friendly interface and powerful automation capabilities. Airtable was chosen because it could serve as the backbone of a custom ERP (EnterpriseResource Planning) system tailored to the client’s needs.

In the next phase, we will design and build automatic interfaces in Airtable to connect it with the client’s key systems: the website for managing user profiles and course attendance, Mailchimp for email marketing, and Bexio for invoicing and order management. This integration will transform Airtable into a central hub where all data can be accessed, managed, and updated in real-time across all systems. The result will be an automated flow of data, eliminating the need for manual entry and significantly reducing the risk of errors.

‍After: