Marketing Data Consolidation and Analytics Enhancement

Our customer is a fashion giant with a robust presence in both traditional retail and e-commerce. With an extensive 80-year history, they operate 250+ stores across the UK and Europe, managing the entire cycle from manufacturing and design to distribution through offline and online channels.

The client was managing customer data across several disparate systems, with no consolidated view of their customers.

The major pain points were:

• Customers with multiple accounts were often counted as separate individuals, resulting in inaccurate business metrics.
• The reliance on external tools for metric calculations added unnecessary costs and slowed down decision-making, negatively impacting overall business efficiency.

That we had: The existing data infrastructure lacked the flexibility and agility needed to handle complex datasets. Without an effective deduplication process, the client couldn’t get a clear view of their actual customer base, which hampered their ability to make informed marketing and business decisions. The absence of a multi-layered data storage solution and streamlined data processing further compounded the problem, limiting their ability to extract valuable insights in real-time.

Our goal was to create a unified, efficient, and cost-effective data infrastructure that would empower the client to derive accurate insights, improve decision-making, and optimise their business processes.

• Building a Centralised Data Warehouse (DWH) with Historical Entities in Snowflake. We created a multi-layered Data Warehouse (DWH) in Snowflake, organising the data into structured stages such as raw data, transformed data, and business-ready data layers. This centralised system housed both current and historical customer and order data. By consolidating all customer information into a single customer attributes table, the client gained a unified and comprehensive view of each customer. This enabled deeper insights into customer behaviour, especially for those with multiple accounts.
• Custom Business Metrics Development and In-House Calculation Logic. We replaced the client’s costly external tools with custom-built logic to calculate business metrics. This transition enabled the creation of analytical tables for retention and marketing metrics based on the client’s order data. By using Snowflake SQL and Python scripts, we provided the marketing team with accurate, real-time data, significantly improving their ability to make data-driven business decisions. The exclusion of third-party tools not only reduced operational costs but also improved the flexibility and scalability of the solution.
• Establishing a Full ETL Process between AWS, Snowflake, and BigQuery. We implemented a full ETL (Extract, Transform, Load) process to automate data flows between AWS, Snowflake, and BigQuery. While Snowflake served as the primary Data Warehouse, BigQuery was used for specific analytics use cases. AWS S3 was employed for storage, and AWS Glue orchestrated the transformation process. Jenkins was integrated for CI/CD to automate deployments. This streamlined integration of data ensured that both current and historical data were available for analysis in real-time, enhancing the client’s ability to respond quickly to business insights.

• Customer Deduplication Logic with Snowflake and AWS Glue. Addressing the issue of customers with multiple accounts, we developed customer deduplication logic orchestrated by AWS Glue and processed using Snowflake SQL. This ensured that individuals with multiple accounts were accurately counted as single entities. This significantly improved the accuracy of the client’s customer metrics, giving the marketing team clearer insights into retention, loyalty, and engagement.
• Advanced RFM Models for Customer Segmentation. To deepen customer insights, we developed an advanced RFM (Recency, Frequency, Monetary) model using SQL in Snowflake for customer segmentation. The model allowed the marketing team to better understand customer behaviour, identify high-value customers, and tailor marketing campaigns accordingly. BigQuery was used for additional performance querying, helping to fine-tune these insights for personalised customer engagement strategies.
• Optimising Account/Customer Status Logic. We implemented a logic-based system to determine customer and account statuses in Snowflake. This logic helped automate business processes, including customer interactions and communication. The status system kept the client’s operations aligned with real-time customer data, ensuring efficient and personalised interactions.
• CRM Integration and Process Automation. We integrated the calculated business metrics and customer insights into the client’s CRM system, automating the data flow using AWS Glue for transformation and Jenkins for CI/CD pipeline deployment. This automation allowed the sales and marketing teams to engage with customers based on up-to-date information, enabling more personalised and timely communication across the organisation.