This project presents an end-to-end data analysis of the "Shefa Issachar" supermarket branch in Yavne. By integrating raw Geolocation signals (GPS/WiFi) with Point-of-Sale (POS) transaction logs, I conducted a deep-dive investigation into consumer behavior and store operations.
The primary objective was to transform fragmented data into actionable business strategies—ranging from workforce management to market expansion. This project demonstrates the application of advanced SQL techniques, statistical modeling, and data visualization to solve real-world retail challenges.
- The Challenge: Distinguishing between staff, suppliers, and customers within raw geolocation data, and further segmenting customers by purchasing power and payment habits.
- Technical Solution: * Developed a Classification Engine to identify roles based on dwell time (>3 hours) and access to operational zones.
- Applied IQR (Interquartile Range) analysis to define high-value "Whale" customers.
- Key Findings: * Identified that "Whale" transactions (above ₪1,139) are driven exclusively by returning customers.
- Payment Trends: 87% of customers use physical cards, while only 6.4% utilize mobile payments, suggesting a gap in digital adoption.
- The Challenge: Addressing long wait times and staffing mismatches during peak demand.
- Technical Solution: Performed a Gap Analysis comparing real-time customer demand against active cashier devices.
- Business Impact: Detected a critical shortage on Thursdays and Fridays, where demand requires up to 56 registers vs. the 15 available. Recommended Self-Checkout systems to handle extreme peaks.
Cashier Gap Analysis: Red indicates a staffing shortage, green represents a labor surplus, and bar height quantifies the headcount delta.
- The Challenge: Quantifying the financial impact of customer dwell time and ensuring data reliability.
-
Technical Solution: Built a linear regression model (
$R^2 = 0.758$ ) to correlate visit duration with basket size. - Data Reliability: The analysis confirmed an App Capture Rate of 43.8%, exceeding the 40% target for data validity.
- Business Impact: Proved that "Time = Money." Every additional minute in-store correlates with higher revenue, justifying investments in Sensory Marketing to encourage longer sessions.
- The Challenge: Navigating a highly competitive market with a current low market share of 2.7% of local households.
- Technical Solution: Integrated CBS (Lamas) demographic data with competitor location mapping.
- Business Impact: * Market Expansion: Identified significant untapped potential in Yavne, necessitating targeted marketing to increase the 2.7% market share.
- Retention Maximization: Recommended a dual strategy: maximizing existing customer value while aggressively acquiring new ones.
- Advanced SQL (BigQuery): Window Functions,CTE's, Complex Joins, Statistical Quantiles etc.
- Looker Studio: Professional dashboarding for operational KPIs and traffic trends.
- Sessionization: Custom algorithm to define unique "visits" based on signal gaps.
The final phase of this project involved building a comprehensive dashboard in Looker Studio to allow management to monitor these KPIs in real-time. 👉 View the Live Interactive Dashboard here
This project was completed as part of the Data Analyst program at Google and Reichman Tech School.