Executive Summary

A specialty retailer with 200+ store locations across United States, Canada, and London, wanted to modernize its data platform which could help the business make real-time decisions while also leveraging prescriptive analytics. They had Aptos CRM running on-premises, but were struggling with scalability, reliability and backup issues. QloudX, an AWS Advanced Consulting Partner, was approached to provide a solution to migrate CRM onto AWS, and create datalake from Customer data which can be used to create Data Warehouse and various Business intelligent reports.

The Challenge

This company has many different business critical applications, running on on-premises server. Aptos CRM was one of them, which contains customer data, however this didn’t have any backup, was not easy to scale and there was no data backup. Also, they were struggling to get historical reports from valuable customer data. Alongside CRM, they also wanted to discover the true value of data being generated from its multiple systems and understand the patterns (both known and unknown) of sales, operations and omni-channel retail performance.

The company decided to migrate their existing on-premises CRM database to AWS and build analytics on the AWS platform, using QuickSight to leverage its ease of use and sharing, unlimited storage capacity, speed and agility, security, and reliability. In addition, the analytical reports were to be extended to include a 360-degree view of their Customers and Orders with a focus on top brands amongst the customers and identifying the latest trends.

Our Solution

Initially, we started with CRM database migration to AWS cloud as a backup and DR solution. And then we started to build the Data Lake by extracting customer information from CRM and set up ETL logic to load data into Data warehouse using Redshift. We then modernized this entire solution by adding Business intelligence visualization dashboards and reports, providing visibility and helping to predict business models for the customer team. Eventually we helped them to build a complete migration and data analytics roadmap by migrating different systems and their data into AWS cloud.

Technology Used:

• S3: Data from source systems, CRM and MOA, are placed on S3
• Lambda: Lambda functions attached with triggers to initiate Glue jobs
• Glue Jobs: Lambda functions attached with triggers to initiate Glue jobs
• Redshift: Lambda functions attached with triggers to initiate Glue jobs

Data process: Implementing a 360-degree view of Customers and Orders data is being extracted from CRM and Manhattan Order Management in a scheduled and incremental manner and provided for further processing. It involved putting data on S3 buckets with historical data. Lambda triggers are attached to the S3 locations to trigger actions as soon as the data is ready.

Handling E/T/L: Glue classifiers process the data files (CSV and JSON in this case) and automate crawlers to obtain scheme-related information and store it in the data catalog. Different glue jobs handle various data, including Spark glue jobs and Python Shell glue jobs.

Data catalogue strategy: Glue crawler manages the data catalogs. The Redshift stage holds the incremental data, and the Redshift OLAP schema keeps the processed data required for the QuickSight. Staging to OLAP schema data transmission and processing is done with the help of Redshift procedures. Redshift OLAP schema views are referred to as datasets in QuickSight implementing row-level security at Redshift.

Data governance: Redshift procedures transfer the data from Redshift staging to OLAP schemas. These procedures perform an UPSERT operation on OLAP schemas to update existing data and insert new data avoiding duplication. Alerts are attached to the Glue Jobs to trigger emails to the intended users with the status of the Glue job execution. It ensures immediate attention in case of Glue job failure.

Amazon QuickSight deployed as a BI tool:

• QuickSight dashboards are accessible via published dashboards from the analysis.
• Calculated fields are used wherever required to manipulate data at QuickSight.
• Duration filter converted into on-screen control is being used to filter out data for various time duration. With this control, the user can filter data for days, weeks, months, quarters, and years.

A Solution that creates Value & Benefits

With AWS, the customer can provide a 360-degree view of customer purchases in a couple of weeks instead of years, reducing infrastructure costs, and attaining reliability. With TCO significantly reduced and time-to-market down to days instead of years, the company can leverage quick insights to help their customers identify top brands and the latest trends.

Here are some highlights of this unique AWS analytical solution:

• Retire on-premises analytics tools, thereby reducing costs.
• Data-driven decision culture.
• Increased business agility.
• Provide 360-degree view of Customer Purchases to identify top brands and latest trends.
• Cost-effective data warehouse and analytics solution on the AWS Platform.
• Ability to create reports on the fly, with the ability to use advanced filters on business parameters.
• End-to-end dashboard that visualizes business growth and key business KPIs.

Overall, the company has gained the insight to understand the true value of their data and what it can do for their business. Through migrations and additional development, they have gained a reliable, secure, scalable and performant cloud solution backed by a next-generation data platform on AWS, allowing them to ingest various application & SaaS data sources, unlocking new insights and potential.