Petra Jewellery's CRM Synchronisation

January 14, 2025. Reading time: 6 minutes.

Background

Petra Jewellery, a distinguished retailer known for its exquisite jewellery, currently employs an on-premises POS System to manage customer details, sales transactions, and stock control. As part of their digital migration strategy, Petra Jewellery have commissioned GenieAI, a cloud-based CRM solution, to capitalise on its advanced sales, marketing, and AI capabilities. Consequently, there is a need to synchronise data between the on-premises POS system and GenieAI to fully harness the potential of both systems.

Problem Statement

The initial solution, developed in-house, involved exporting data from the on-premises POS system and Squarespace, transforming it, and importing it into GenieAI. This process comprised several manual steps, including exporting multiple reports, reimporting these files into a staging system built in Microsoft Access, initiating a data cleansing routine, manually exporting the data to a file, and finally importing it into GenieAI. This procedure would need to be performed daily.

During the latter stages of testing, it was discovered that the process was very slow and that there were numerous validation issues with the GenieAI import routines. Identifying the exact rows containing invalid data was difficult, complicating the issue further. Additionally, commas in text fields corrupted the integrity of the source data files.

At this stage Petra Jewellery decided to seek external help on resolving these issues.

Desired Outcome

Following Petra Jewellery’s initial consultation with Smarter Business Tech, the following high-level requirements were established:

1. Correction of invalid data: Resolve validation issues to ensure valid data is passed to GenieAI.

2. Automated, efficient, and robust data synchronisation: The process should run daily, be performant and robust, operate with minimal user intervention, and provide exception reporting.

Proposed Solution

To address these challenges, a slightly different solution was proposed, promising quicker implementation, lower costs, improved automation, and greater maintainability. The key components of the proposed solution are:

1. Utilise API’s Instead of Manual Processes

   The current process heavily relied on manual intervention. Given the source data is located in a block box monolith application hosted locally, it was feasible to access this directly instead of relying on data exports. It was discovered that the on-site CRM database used the FireBird database platform, accessible using the correct ODBC (Open DataBase Connectivity) driver. Furthermore, both Squarespace and GenieAI have APIs (Application Programming Interfaces) that allow data to be imported automatically.

2. Pre-empt GenieAI Validation

   Utilise GenieAI’s API documentation to ensure that source on-premises data is compliant before posting it to GenieAI. This proactive approach ensures data quality upfront and minimises unexpected issues at the last minute.

3. Utilise a Better Staging Platform

   Replace Microsoft Access with Microsoft SQL Server Express Edition (SQL Express) for several advantages:

   • Free to use with a database up to 10GB.
   • Not reliant on Microsoft Office, which requires licensing and a large installation footprint not designed for server use.
   • Better tools for validation and ensuring referential integrity.
   • Greater performance capabilities.
   • Easier to code against using the .NET Core framework instead of VBA (Visual Basic for Applications).
   • Feature-rich administration console.

4. A New Controller Application

   Develop a small application using Microsoft .NET Core Framework (C#) to orchestrate the process. This will handle data retrieval, transformation, validation, and insertion between the on-premises POS system, Squarespace, and GenieAI. It will also report validation issues and exceptions to a predefined set of users for correction in the on-premises POS system.

5. Tying It All Together

   The proposed solution will be triggered using Windows Server Task Scheduler on a server hosted at Petra Jewellery premises. This will start the new controller application, which will extract data directly from the source on-premises POS system, call the Squarespace API to retrieve and merge data, transform the data, and send the differences to GenieAI. Any issues will be reported to users for remediation.

Project Delivery

The project was delivered within the quoted timescales, satisfying all functional and non-functional requirements. Furthermore, the solution was designed so that the new staging database could eventually be repurposed into a more functional and capable replacement for the current on-premises POS solution. Mitch, Owner of Petra Jewellery, commented:

“… Dan stood out for his ability to understand our needs as users and explained concepts in a way that we understood. I trusted him from the start to deliver the right outcome for my business, he managed us, costs and timescales brilliantly too.” - Mitch, Owner, Petra Jewellery.

Conclusion

By implementing the proposed solution, Petra Jewellery has achieved a seamless, automated, and robust data synchronisation process. This has enhanced sales and marketing activities, improved data quality in the source on-premises POS System, and potentially provided a scalable platform for future business growth.