Neo4j Graph Database Use-Case Project: Acme Online Retail

Neo4j Desktop is a client application to help you work with Neo4j, whether you are just getting started or have prior experience. It is designed to help you as a new user to learn and experiment with Neo4j locally by including everything you need to get started. Once you know Neo4j, Desktop becomes your local development environment for projects where you will use Neo4j. With Neo4j Desktop, you can create any number of local databases as supported by the resources of your machine.

The resource for installing Neo4j is the Neo4j Desktop Installation Guide located at https://neo4j.com/docs/desktop-manual/current/installation/.

Once you have Neo4j Desktop installed, you will need to add a local DBMS instance to the desktop and start it, then add a database to it, select and open it with the Neo4j Browser. The database can be named whatever you like. For the purpose of this use case, I have named my database acmeonlineretail.

Acme Online Retail Use-Case Model

Acme Online Retail database is a simple fictitious online store which I developed using Neo4j Desktop. The graph database mimics an online store with inventory (product nodes) which the store orders and receives from its suppliers (supplier nodes), and which is purchased by customers (customer nodes) who view the store’s inventory online.

The language Neo4j uses to create and manipulate a graph database is called cypher, and it is cypher query syntax that accomplishes this. The cypher language is much simpler and intuitive to use than the traditional SQL syntax you would use if you were creating a relational database. But don’t worry. You won’t need to learn cypher before you create the Acme Online database as all cypher queries will be provided for you.

Products are categorized into category nodes through a relationship and identified to a supplier or suppliers of that category through a relationship. For example: cameras can be a category of product with multiple camera models supplied by one or more suppliers of the product.

Customers then view products, add products to their wish list, or purchase products through respective relationships. A customer order generates an customer order detail node in the database to provide additional information about each customer’s order.

As customer orders decrement product inventory, the on-hand quantity and reorder point of each product determines if a reorder should be generated. Receipts from suppliers generate receipt nodes that serve to increment the store’s product inventory to a state that does not trigger a reorder when reorders are run.

Twenty-percent Promotional offer nodes can also be generated. A promotional offer promotes a product or products which a customer may wish to purchase. The products promoted are any available products in the same category as products the customer showed interest in (either viewed or added to his/her wish list) but didn’t buy having a retail cost ranging from +/- 20% of the retail cost of those products the customer was only interested in.

Setting Up The Acme Online Database

There is a required order for the creation of nodes within the Acme Online Retail database for it to work properly. That order is:

  1. Suppliers
  2. Categories
  3. Products
  4. Customers

Once these nodes are in place, customer orders may be created for product in the online inventory. For purposes of demonstration, our online store will carry only two categories of product: smartphones and cameras.

Creating Our Suppliers

So, let’s get started creating the database.

The first thing we need to do is create a few Suppliers for our categories.

The cypher code to use to create suppliers is as follows:

// create supplier // enter supplier name, contact, freight_cost, zip and email create (:Supplier {name:'', Contact:'', Freight_Cost:0.00, Zip:99999, email:''});

Just copy and paste this code into the code input window of your acmeonlineretail database, then fill in the information indicated for a supplier before running the code with Ctrl + Enter or by pressing the blue right-pointing triangle on the right side of the code input line. In cypher code, string data is enclosed in single quote (‘ ‘) and numbers are entered without quotes.

Let’s create a supplier for Apple Smartphones. Here is the information you need to fill in the code:

supplier name: ‘Phones R Us’
Contact: ‘Mark Peterson’
Freight Cost: 1.15
Zip: 90210
email: ‘mpeterson@gmail.com’

Your input window should look like this before you run the code:

// create supplier
// enter supplier name, contact, freight_cost, zip and email
create (:Supplier {name:'Phones R Us', Contact:'Mark Peterson', Freight_Cost:1.15, Zip:90210, email:'mpeterson@gmail.com'});

Now run the code with Ctrl + Enter and you should see the following message with the creation of your first supplier.

Added 1 label, created 1 node, set 5 properties, completed after ? ms.

In order to see the graphical representation of your supplier node, type the following in the input window and run it, then click on the Graph icon on the left side of the large output pane:

match(n) return n

Here is what you should see when you run the code:

Supplier Node Created

Let’s enter another supplier for Nikon Cameras. Copy and paste the create supplier cypher into the code input window. Here is the information you need to fill in the code:

supplier name: ‘Cameras Plus’
Contact: ‘Linda Mayweather’
Freight Cost: 0.75
Zip: 81450
email: ‘lmayweather@camerasplus.com’

Your input window should look like this before you run the code:

Again, your code input window should look like this before you run it:

// create supplier
// enter supplier name, contact, freight_cost, zip and email
create (:Supplier {name:'Cameras Plus', Contact:'Linda Mayweather', Freight_Cost:0.75, Zip:81450, email:'lmayweather@camerasplus.com'});

Now run the code with Ctrl + Enter as before.

After typing in the following code,

match(n) return n

and running it, you should see the following graph containing two nodes:

Two Supplier Nodes Created

Note: There is no need to click the Graph icon this time as Neo4j Desktop remembers a graphical output is what you requested before.

The next thing we will do is create the product Category nodes which will be associated with our products we create after that.

Creating Our Categories

The cypher code to use to create categories is as follows:

// create category // enter name create (:Category {name:''});

There are two categories we need to create: one for smartphones and another for cameras.

As before, just copy and paste this code into the code input window of your database, then fill in the information indicated for a category before running the code.

Here are the two cypher codes that you need to run one at a time.

// create category // enter name create (:Category {name:'Smartphones'});
// create category // enter name create (:Category {name:'Cameras'});

Once you have run both of these code blocks, run

match(n) return n

to view the graphical database. What you should see are four nodes: two supplier nodes and two category nodes:

Two Supplier Nodes and Two Category Nodes

Now, we are going to create some products our suppliers have in their inventory. We’ll be ordering and receiving products from our suppliers later on in the database creation.

Let’s start by creating smartphone products supplied by Phones R Us.

Creating Our Products in the Smartphones Category

The cypher code we will use to create product in Category: Smartphones is as follows:

// create product in category // enter supplier name, product name, Qty_On_Hand (if not zero), Reorder_Point, // Unit_Cost and markup match (s:Supplier {name:''}) create (s)-[:SUPPLIER_OF]->(p:Product) set p.name='', p.Qty_On_Hand=0, p.Qty_On_Order=0, p.Reorder_Point=0, p.Reorder_Qty=p.Qty_On_Order, p.Unit_Cost=0.00, p.markup=0.00, p.Retail_Cost=(p.Unit_Cost * toFloat(1+p.markup)), p.Ship_Cost=toFloat(p.Unit_Cost) + toFloat(s.Freight_Cost) with p match (cat:Category {name:''}) merge (cat)<-[:IS_IN]-(p);

We will create four smartphone products that Phones R Us carries. Copy and paste the above cypher code and fill in the data items requested before running the code.

Here is the data for the first product node we’ll create:

Supplier name: ‘Phones R Us’
Product name: ‘iPhone 13 Pro’
Qty_On_Hand: 3 (Note: This represents the quantity we have on-hand)
Reorder_Point: 2
Unit_Cost: 999.00
p.markup: 0.10
Category name: ‘Smartphones’

Note: Don’t change p.Qty_On_Order=0. This will be updated when reorders are placed later on.

Now, run this code with Ctrl + Enter.

Let’s pause here before entering the remaining three products and have a look at the database.

As before, to view the database enter this code and run it:

match(n) return n
Product Node Created

What you see are four nodes: two supplier nodes, two category nodes, one product node with two relationships: one points from the product to its Category with the IS_IN relationship, and the other points from the supplier to the product with the relationship SUPPLIER_OF.

Now, let’s repeat the above steps to add the remaining three products with the data you need to supply before running the code, as follows:

Supplier name: ‘Phones R Us’
Product name: ‘iPhone 13’
Qty_On_Hand: 4
Reorder_Point: 3
Unit_Cost: 699.00
p.markup: 0.10
Category name: ‘Smartphones’

Supplier name: ‘Phones R Us’
Product name: ‘iPhone 12’
Qty_On_Hand: 4
Reorder_Point: 3
Unit_Cost: 599.00
p.markup: 0.05
Category name: ‘Smartphones’

Supplier name: ‘Phones R Us’
Product name: ‘iPhone SE’
Qty_On_Hand: 4
Reorder_Point: 1
Unit_Cost: 399.00
p.markup: 0.05
Category name: ‘Smartphones’

After running each product code block, go ahead now and view the database once more by running,

match(n) return n

What you should see looks like this:

Four Product Nodes Created

What you see may appear differently, but you should have four product nodes supplied by Phones R Us in Category Smartphones with their respective relationships, as well as the Cameras Category node and Cameras Plus supplier node.

Next up, we will create four products in the Cameras Category supplied by Cameras Plus.

Creating Our Products in the Cameras Category

The cypher code we will use to create product in Category: Cameras is the same as the code we used for creating product in the Smartphones Category, namely:

// create product in category // enter supplier name, product name, Qty_On_Hand (if not zero), Reorder_Point, // Unit_Cost, markup and Category name match (s:Supplier {name:''}) create (s)-[:SUPPLIER_OF]->(p:Product) set p.name='', p.Qty_On_Hand=0, p.Qty_On_Order=0, p.Reorder_Point=0, p.Reorder_Qty=p.Qty_On_Order, p.Unit_Cost=0.00, p.markup=0.00, p.Retail_Cost=(p.Unit_Cost * toFloat(1+p.markup)), p.Ship_Cost=toFloat(p.Unit_Cost) + toFloat(s.Freight_Cost) with p match (cat:Category {name:''}) merge (cat)<-[:IS_IN]-(p);

Run the following cypher code blocks to create our four camera products:

Supplier name: ‘Cameras Plus’
Product name: ‘Canon EOS M50 Mark II’
Qty_On_Hand: 3
Reorder_Point: 1
Unit_Cost: 699.99
p.markup: 0.10
Category name: ‘Cameras’

Supplier name: ‘Cameras Plus’
Product name: ‘Canon EOS R5’
Qty_On_Hand: 2
Reorder_Point: 1
Unit_Cost: 3899.00
p.markup: 0.15
Category name: ‘Cameras’

Supplier name: ‘Cameras Plus’
Product name: ‘Canon EOS R6’
Qty_On_Hand: 4
Reorder_Point: 1
Unit_Cost: 2499.00
p.markup: 0.15
Category name: ‘Cameras’

Supplier name: ‘Cameras Plus’
Product name: ‘Canon EOS RP RF24’
Qty_On_Hand: 3
Reorder_Point: 1
Unit_Cost: 1299.00
p.markup: 0.10
Category name: ‘Cameras’

When you view the database again, what you will see is something that looks like this:

Eight Product Nodes Created in Two Categories

Now that we have our Suppliers, product Categories and Products created, it’s time to create a couple Customers who will be interacting with our inventory.

Creating Our Customers

We can imagine the online system will add our customers to our database by running the following cypher code:

// create customer // enter customer name, address, city, state, zip and email create (:Customer {name:'', Address:'', City:'', State:'', Zip:'', Email: ''});

Let’s create three customers with the data as follows:

Customer name: ‘Thomas Coleman’
Customer Address: ‘1678 Grey Fox Farm Road’
Customer City: ‘Houston’
Customer State: ‘TX’
Customer Zip: ‘77063’
Customer email: ‘tcoleman@gmail.com’

Customer name: ‘Deborah Gorman’
Customer Address: ‘202 Barnes Street’
Customer City: ‘Orlando’
Customer State: ‘FL’
Customer Zip: ‘32808’
Customer email: ‘dgorman@gmail.com’

Customer name: ‘Keli Bohler’
Customer Address: ‘4280 Mount Street’
Customer City: ‘Birch Run’
Customer State: ‘MI’
Customer Zip: ‘48415’
Customer email: ‘kbohler@gmail.com’

Viewing the database once again, we see something that looks like this:

Three Customer Nodes Added

We now have our three customers loaded in the database. It’s time for them to interact with our product.

Creating Our Customer Orders

Thomas Coleman likes the iPhone 12, and so he buys it. We can imagine the online system will add Thomas’ order to our database by running the following cypher code:

// create customer order // enter customer name, product name, ord_num, COD Order_Qty, and date match (cu:Customer {name:''}) match (p:Product {name:''}) merge (cu)-[:PLACED_ORDER]->(o:Order {ord_num:1000})-[:ORDER_FOR]->(p) merge (cod:CustOrdDetail {name:'COD', Order_Qty:0, date:'', Customer: cu.name})-[:PURCHASED]->(p) set p.Qty_On_Hand=(toInteger(p.Qty_On_Hand)-toInteger(cod.Order_Qty)) return cu, cod, p, o;

Here are the data entries we need to run to post his order to the database:

Customer name: ‘Thomas Coleman’
Product name: ‘iPhone 12’
ord_num: 1000 (this is the first sequential order number in our system)
Order_Qty: 1 (this is the Customer Order Detail quantity ordered)
COD date: ‘20220211’ (Feb 11, 2022)

The following is what we should see when we run the order for Thomas:

Customer Order 1000 Created

In the above screenshot we see the order placed by Thomas Coleman for an iPhone 12 and the COD detail information in the right hand pane.

Now, let’s create another purchase. Keli Bohler orders a camera. Her data for the purchase is as follows:

Customer name: ‘Keli Bohler’
Product name: ‘Canon EOS M50 Mark II’
ord_num: 1001 (this is the next sequential order number in our system)
Order_Qty: 1
COD date: ‘20220214’

The following is what we should see when we run the order for Keli:

Customer Order 1001 Created

Viewing Customer Orders by Order Number

We have a couple orders, so let’s create a tabular view of them. Let’s say we want to a listing view of order number 1000. The cypher code we can use for this is the following:

// view customer order number total // enter customer order ord_num match (cu:Customer)-[:PLACED_ORDER]->(o:Order {ord_num:1000})-[:ORDER_FOR]->(p:Product)<-[:PURCHASED]-(cod:CustOrdDetail) return cu.name as Customer, p.name as Product, cod.Order_Qty as Quantity, cod.date as Ordered, p.Retail_Cost*cod.Order_Qty as Order_Total;

Upon running this code we see the details of the order place by Thomas Coleman:

Customer Order 1000 Order Detail

Viewing Customer Order History

If we want to view a history of the products ordered by a particular customer, we can run the following cypher code, supplying the name of the customer. Let’s look at the order history for Keli Bohler:

// view customer order retail history // enter customer name match (cu:Customer {name:'Keli Bohler'})-[:PLACED_ORDER]->(o:Order)-[:ORDER_FOR]->(p:Product)<-[:PURCHASED]-(cod:CustOrdDetail) return cu.name as Customer, o.ord_num as Order, p.name as Product, cod.Order_Qty as Quantity, p.Retail_Cost*cod.Order_Qty as Order_Total order by Order;
View Customer Order History

Creating Product Reorders

At the end of the business day we would like to review our inventory to see if we need to reorder product that has reached its reorder limit we set earlier for each product. We can run the following cypher code to generate necessary reorders with our Suppliers, supplying the date for our reorder run. The cypher will set the reorder quantity to 1 for any product identified as needing to be reordered, but that can be changed if desired. Let’s leave the reorder quantity at the default value.

// create reorders
// enter Reorder date and Reorder Quantity (if desired)
match (p:Product) where toInteger(p.Qty_On_Hand)+toInteger(p.Qty_On_Order)<=toInteger(p.Reorder_Point) match (s:Supplier)-[:SUPPLIER_OF]->(p) merge (s)<-[:REORDER_WITH]-(re:Reorder {name:"Reorder", date:'YYYYMMDD'})-[:REORDER_OF]->(p) with p,s,re set p.Qty_On_Order=(toInteger(p.Reorder_Point)+1)-toInteger(p.Qty_On_Hand) set p.Reorder_Qty=p.Qty_On_Order set re.Reorder_Qty=toInteger(p.Qty_On_Order) set re.Unit_Cost=p.Unit_Cost set re.Total_Cost=toFloat(re.Unit_Cost)+toFloat(s.Freight_Cost) set re.Reorder_Cost=(re.Reorder_Qty*re.Total_Cost) set re.Received='N' return p, s, re;

Here we see one product, the iPhone 12, that needs to be reordered. This is because the purchase made by Thomas Coleman reduced the On_Hand quantity of this product to its Reorder_Point.

Creating Product Reorder

If we want to see how much our cost outlay will be for this reorder, we can run the following cypher code which provides a cost outlay listing by Supplier. There is only one product to be reordered here.

// view reorder cost outlay by supplier match (re:Reorder)-[rel:REORDER_WITH]->(s:Supplier) with re,rel,s return s.name as Supplier, re.date as Reorder_Date, re.Reorder_Qty*re.Reorder_Cost as Cost_Outlay order by Supplier, Reorder_Date;

This is what we see when we run the code:

Reorder Cost Outlay

The cost outlay is the total shipping cost.

Updating a Reorder Quantity After Reorder Has Run

If we decided we wanted to change the reorder quantity from the default of 1 to something else, we can run the following cypher code. Here, we will increase the reorder quantity of the iPhone 12 from 1 to 3.

// set reorder quantities to amount other than 1 // enter product name and Qty_On_Order match (p:Product {name:'iPhone 12'}) match (re:Reorder)-[:REORDER_OF]->(p) set p.Qty_On_Order=3, p.Reorder_Qty=p.Qty_On_Order, re.Reorder_Qty=p.Reorder_Qty return re, p;

Upon running this code, we see the following:

Updating Reorder Quantity After Reorder Run

The reorder quantity has been updated from 1 to 3 (see right hand pane).

Viewing Our Outstanding Reorders

We can view all our outstanding reorders with by running the following cypher code:

// view outstanding reorders match (re:Reorder) where re.Received='N' match (re)-[:REORDER_WITH]->(s:Supplier) match (re)-[:REORDER_OF]->(p:Product) where p.Reorder_Qty<>0 return distinct s.name as Supplier, id(re) as Reorder_ID, re.date as Reorder_Date, re.Reorder_Qty as Quantity, p.name as Product, re.Reorder_Qty*re.Reorder_Cost as Total_Reorder order by Reorder_ID, Reorder_Date, Product;

We only have one product in the reorder which hasn’t been received, so here is what we see when we run the code:

View Outstanding Reorders

Now we can see our outstanding reorders are for one product, and the Total Reorder cost $1800.45 is our new cost outlay for the reorder.

Creating Our Product Receipts

A few days have passed since we placed our reorder for the iPhone 12 with our Supplier, Phones R Us, and when we check the mail on Feb 18, 2022 we see our reorder for 3 phones has been received. So, we need to record the receipt to our inventory.

If we run outstanding reorders once again, we see the reorder id matching our receipt is 4.

The following cypher code will be used to create the Receipt node and update the product data to reflect it. Entering the reorder id along with the date of receipt and quantity received (which may differ from the order) into the cypher, we can run the code that follows:

// create receipts // enter reorder id, receipt date and quantity received match (re:Reorder) where id(re)=4 match (re)-[:REORDER_OF]->(p:Product) merge (rect:Receipt {name:'Receipt', date:'20220218', quantity:3})-[:RECEIVED_FROM]-(re) with rect,re,p set p.Qty_On_Hand = toInteger(p.Qty_On_Hand)+toInteger(rect.quantity) set p.Qty_On_Order = toInteger(p.Qty_On_Order)-toInteger(rect.quantity) set p.Reorder_Qty = toInteger(p.Reorder_Qty)-(rect.quantity) set re.Received='Y' return p,re,rect;

Below, we see a receipt node has been created for the receipt of 3 phones, and our inventory for the iPhone 12 now reflects an on-hand quantity of 6 and zero on-order quantity.

Creating Product Receipt Node

Creating Customer Viewing a Product

Now, Debora Gorman has viewed a smartphone from our online store but did not buy it. We can imagine the online system will add her interaction to our database by running the following cypher code:

// create customer viewing interest with a product // enter customer name and product name match (cu:Customer {name:''}) match (p:Product {name:''}) merge (cu)-[:VIEWED]->(p) return cu, p

Her data for this product interaction is as follows:

Customer name: ‘Debora Gorman’
Product name: ‘iPhone 13’

When we run this cypher code with this data inserted, we see the following:

Customer Viewing Product

Creating Customer Adding Product to Their Wish List

Also, Debora Gorman adds the iPhone 13 Pro to her wish list. We can imagine the online system will add her interaction to our database by running the following cypher code:

// create customer added a product to wish-list // enter customer name and product name match (cu:Customer {name:'Debora Gorman'}) match (p:Product {name:'iPhone 13 Pro'}) merge (cu)-[:ADDED_TO_WISH_LIST]->(p) return cu, p;

and what we see when the code runs is as follows:

Customer Adding Product To Wish List

View Customers Interested in Product But Nothing Ordered

At some point we want to see our customer’s interaction with our online product for which the customer(s) didn’t buy anything. The following cypher code will give us that information which we can use to create a product promotion for the customer.

// view customers interested not ordered match (cu:Customer)-[:VIEWED|:ADDED_TO_WISH_LIST]->(p:Product) where not (cu)-[:PLACED_ORDER]->() return cu,p;

Running this code produces the following results:

Customer Interest In Product Not Ordered

Here we see our customer, Debora Gorman’s interaction with two phones: she viewed the iPhone 13 and added the iPhone 13 Pro to her wish list, but she didn’t buy a phone.

Creating a Promotion to Customer(s)

What we would like to do is promote phones to Debora that she didn’t interact with. The criteria for our promotion is as follows:

Any promotion candidate products:

  • must be in the same category the customer had interaction with
  • must have a retail cost + or – 20% of the products she interacted with
  • must be available for purchase (on-hand quantity not equal to 0)
  • must not have been ordered by the customer previously

The cypher code we wlll use to create a promotion is as follows:

// create 20% promotional offer // enter customer name MATCH (cu:Customer {name: 'Debora Gorman'}) MATCH (target_product:Product) WHERE NOT ((cu)-->(target_product)) MATCH (p:Product) WHERE ((cu)-->(p)) MATCH (target_product)-[:IS_IN]->()<-[:IS_IN]-(p) WHERE ((p.Retail_Cost - p.Retail_Cost * 0.20) >= target_product.Retail_Cost <= (p.Retail_Cost + p.Retail_Cost * 0.20)) WITH target_product, cu MATCH (target_product) WHERE NOT target_product.Qty_On_Hand=0 CREATE(offer:PromoOffer {type: 'personal_replacement_offer', content: 'Personal replacement offer for ' + cu.name}) WITH offer, target_product, cu MERGE(offer)-[rel:USED_TO_PROMOTE {name: cu.Email}]->(target_product) RETURN offer, target_product, rel;

When we run this code, we see the following:

Creating Customer 20% Promotion Node

As we can see, there are two products in the Smartphones category which meet all of our above criteria for promotion to Debora Gorman.

Let’s take a final look at our database now that we have created Suppliers, Categories, Products and Customers, Customers have placed orders and interacted with Product, we have generated a Reorder and a Receipt for Product; and finally, we have generated a 20% Promotional Offer for a customer.

match(n) return n
Acme Online Retail Graph Database

This concludes the Acme Online Retail Use-Case demo. I hope you have enjoyed this presentation and will take the time to create your own Acme Online Retail database.

Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published.