Contributer(s): Tyler Gustafson
DISCLAIMER: AGM is a fictional business in the Bay Area please note that this project was completed to showcase my skills NoSql database skills and analytics.
Overview
Unlocking Client's Vision: The Role of NoSQL Tech
-
Client: High-end kitchen that produces fresh-frozen meals at booths in local natural & organic grocery stores
-
Task: Innovate and optimize delivery logistics that support rapid growth in the Bay area
Objective
AGM's executive leadership wanted us to:
-
Increase Accessibility (i.e. reach more customers)
-
Innovative Delivery (BART partnerships and in the case of the future delivery drones / robots)
-
Apply NoSQL technologies to expand their data science and machine learning team
AGM's Vision for the Future
Increase Accessibility
Additional customer pick up locations
Innovative Delivery
BART
Delivery drones
Self-driving vechiles
NoSQL Refresher
NoSQL databases are designed to handle a wide variety of data models and are optimized for specific workloads and data access patterns. They provide a flexible schema model, allowing for efficient storage and retrieval of data without the constraints of traditional relational databases. Here are the key characteristics and types of NoSQL databases:
-
Schema-less: No predefined schema, allowing for dynamic addition of fields.
-
Horizontal Scalability: Designed to scale out by adding more servers easily.
-
Distributed Architecture: Data is distributed across multiple nodes for redundancy and availability.
-
Flexible Data Models: Supports various data models like key-value, document, column-family, and graph.
-
High Performance: Optimized for high read/write throughput and low latency.
There are three key types of NoSQL databases that will be important for AGM: 1) Graph-Based, 2) Documented-Oriented and 3) In-memory / Key-Value Stores.
Neo4j Refresher
The first tool that comes to mind when having to deal with a relationship network such as the BART is Neo4j. Neo4j is a type of database designed to store and handle data in the form of connected graphs rather than tables, making it ideal for representing relationships between data points. Benefits include Enhanced Relationship Handling including the application of advanced algorithms, Flexibility in Data Modeling and Performance Efficiency vs traditional relational databases.
The first step required building out the BART network in Neo4j. These connections were built through a series of SQL queries that you can see in the code. You can see the vast network this creates in the web mapping. This then translates to the everyday map. Now it was time to start answering business questions.
The Approach
Business Case 1:
Where is the optimal place to situate AGM’s kitchen if we’re leveraging BART lines for distribution?
The approach to answer this was in two steps:
(1) Determined the centralized BART location by applying a Harmonic Centrality algorithm.
The harmonic centrality algorithm in Neo4j measures a node's closeness to all others by averaging the reciprocals of shortest path distances.
Top Three Locations:
-
West Oakland* (0.23)
-
Embarcadero (0.22)
-
Lake Merritt (0.21)
(2) Cross-reference locations with current customers with Geodesic Fencing (zipcode radius algorithm).
This algorithm calculated the percentage of current customers within a 7-mile radius of proposed stations.
Top Three Locations:
-
West Oakland* (56.45%)
-
North Berkeley (56.39%)
-
MacArthur (56.18%)
Outcome: Shift the kitchen to a new location in West Oakland (but transform our old Ashby kitchen into a delivery hub):
-
Considering our current customer base, it confirms a preference for West Oakland as our primary kitchen location to centralize along the BART lines
Business Case 2:
Where should we position our distribution hubs along the BART line to expand pick-up locations and last-mile delivery, ensuring access to SF, Oakland, and San Jose?
The approach to answer this was in a few steps:
(1) Identify and group zones (Louvain Modularity)
Apply Louvain Modularity to define zones along BART lines, uncovering clusters for improved planning. Seen in the 7 zones in the updated BART map.
(2) Determine a Hub within each of these zones
Apply Geodesic Fencing by focusing on maximizing population coverage within a 2-mile radius for each station with their respective zones.
Selected as Distribution Hub since largest population radius in zone
Additional strategic considerations were taken into account for some hub locations, for example:
-
Coliseum offers critical access to the OAK airport, highlighting a key trade-off in decision-making.
-
West Oakland is confirmed as kitchen location, eliminating the need for an additional hub in group 2
Outcome: Kitchen + 5 Distribution Hubs Model
-
With West Oakland as the new kitchen and Ashby, Civic Center, Daly City, Coliseum, and Berryessa stations as delivery hubs to expand customer reach and reduce delivery times.
-
We can optimize route planning using a Neo4j Shortest Route Algorithm that when implemented can help determine the most efficient delivery routes between hubs or for last mile delivery
The Recommendation
1
2
3
4
Select optimal location for AGM Local Kitchen using neo4j algorithms
Create four additional BART hubs to maximize delivery efficiency including last mile and create more pickup locations
Create an advanced analytical database capable of empowering company decision makers
Provide real-time customer delivery updates with Redis for last mile delivery.
Further analysis can be conducted to refine this recommendation such as BART ridership by station and proximity to other high-end natural / organic grocery stores
Check out the code and more on my GitHub
