Small Business Data Lake for Customer Insights

Summary

A small grocery chain in Lima, Perú, struggled to understand the impact of their sales promotions. Their sales, loyalty, and invoice data were siloed across multiple systems, making it nearly impossible to track customer behavior or campaign effectiveness. This project unified their data sources into an S3-based data lake, enabling real-time insights and smarter promotion strategies.

Business Challenge

With three stores in working-class districts, the store aimed to compete with traditional corner shops by offering affordable staples and loyalty incentives. However, their data was fragmented:

• POS system: Exported daily sales data as CSV files.
• Vendor invoices: Arrived via email as PDFs—many handwritten.
• Loyalty program: Stored in a local MySQL database.

The owners couldn’t determine if a promotion actually increased revenue or engagement. For example, a “Free Oil with 5 Visits” campaign caused unintended losses—customers redeemed oil but skipped other higher-margin products. No one realized the impact for weeks.

Solution and Architecture

I designed and implemented a unified data platform using AWS services. All incoming data was stored in an Amazon S3 data lake with clearly defined raw zones. Glue and Spark jobs handled cleaning, joining, and extracting structured data—including using NLP for text extraction from invoice PDFs.

• S3 Raw Zones: Separate folders for POS CSVs, vendor PDFs, and MySQL loyalty dumps.
• Glue: Performed standardization, removed nulls, and normalized formats.
• Spark SQL: Joined POS and loyalty data to track redemptions and customer patterns.
• Spark NLP: Extracted line-item data from invoices—including handwritten entries.
• Athena: Queried unified datasets to track signup trends, campaign lift, and margin shifts.

Steps Taken

• Established raw S3 folder structure and ingestion policies.
• Built Glue jobs to clean POS files and deduplicate loyalty phone numbers.
• Implemented OCR and Spanish NLP pipeline using Spark NLP for vendor PDFs.
• Standardized promo names and linked transactions to customer IDs using phone matching logic.
• Created Athena queries to generate key business insights on promotion effectiveness.

Impact and Results

The store transitioned from reactive guesses to data-driven promotion planning. Key outcomes:

• Discovered that “2x1 Rice” drove a 15% increase in new loyalty signups.
• Identified a 10% margin drop on oil promotions due to cannibalization of higher-margin items.
• Enabled the accounting team to stop merging spreadsheets manually—saving 10+ hours weekly.
• Now balances aggressive discounts with cross-promotion of profitable items like snacks and beverages.

This project helped a small Latin American business leverage cloud analytics and natural language processing to compete smarter and optimize every peso spent on promotions.