Visualizing Diaper-Beer Relationship in Graph database

Bryan Soong
Dec 13, 2015 · 4 min read
credit: https://bigdatabigworld.wordpress.com/tag/beer-and-nappies/

Wal-mart: People who bought diaper also buy beer. Therefore, they placed beer beside the diapers rack and it increased beers’ sales.

My friend Kelvin Lim just wrote an introductory article about using Neo4J to visualize categories map of iFlix.com movies. Inspiring from that, I would like to see if I could explore some insights about buyer behavior from Biztory data using Neo4J.

Biztory is a cloud based business management app that helps small business owner to manage their day-to-day business tasks from billing, inventory, customer, purchase to report and GST submissions, in a smart and simplified way.


Customer purchase history

Biztory runs on Laravel and LAMP stack. Extracting the sales track records is just a matter of a few JOIN queries. Following Kelvin’s guide, I firstly need to export the data in CSV format.

Since I’m exploring the relationship between products sold in a single invoice, the nodes I need to create are:

MySQL query to join the relationship between product A and product B sold in the same invoice
USING PERIODIC COMMIT 1000
LOAD CSV WITH HEADERS FROM “file:/Users/bryan/Desktop/product-product-rel.csv”
AS csvline
WITH csvline
MERGE (p1:Product {sku: csvline.code1, name: csvline.name1})
MERGE (p2:Product {sku: csvline.code2, name: csvline.name2})
MERGE (p1)-[:Also_Bought{invoice:csvline.ref_num}]-(p2)
####
When you join the tables to see relationship between product A and B, you will get 2 relationships between the same products, A->B and B->A. In our case, what we concern is just A<->B. Therefore, I run a MERGE statement when creating the relationship instead of CREATE to combine the duplicates.
####

Analyze it

Say given a product “KN-BM 3x5”, I want to see what are the products that also being bought along.

MATCH (p1:Product{sku:’KN-BM 3x5'})
MATCH (p1)-[r:Also_Bought]-(p2:Product)
RETURN p1, p2, count(r) AS thickness
Graph visualization for products that also being bought when someone buy 3x5" BINTANG REBOND MATTRESS [KN-BM 3x5]

Given that, let’s see if we can strategize a good promo bundle package that can cross sell each other, and also trigger a chain sales.

Marketing Idea

Scenario 1: Top selling product

Applying the same query, below illustrates the network graph for one of the top selling product: Big Cotton Pillow.

The top part of the graph are mainly sponge mattress and reborn mattress (mattress). On the right side, are double decker and metal bed (bed). On the left and center part, bolster, blanket and bed sheet (bedding accessories). So far, these are related products for a pillow (the diaper).

Interesting, the bottom part of the graph shows a list of irrelevant products (the beer), such as kitchen stool, gas rack, plate rack, shoe cabinet and wardrobe.

From the finding, we can propose to bundle Big Cotton Pillow together with Single Bed Sheet and Blanket, and from the graph it is very likely it will lead to additional chain sales such as mattress and bed, or some kitchen stuffs.

Single bed sheet & blanket have high correlations with pillow sales
list of related products of pillow sort by occurrence most at top

Scenario 2: Low selling product with plenty of remaining stocks

Another common problem is store owner may pile up some stocks but not selling well, and wish to do some clearance sales.

Graph below shows Japanese Coffee Table mostly sell along in two branches, either Sofa (right) or a bunch of inter-related products (left). Obviously, it is more potential to strategize along the left path as it gives us better chaining. We may hard to see this if we do not visualize it with a graph network and merely by looking at the excel spreadsheet.

In this case, I would propose to bundle with 2-Door Plastic Shoe Cabinet as it has high linkage and it is also one of the popular product in the store. This bundle will give good money value to customers and at the same time helps the owner to clear the accumulating stock, hopefully it will also trigger some chain sales from there, or give some up-sell offer to boost the sales.

graph shows the two main linked sales branches for Japanese Coffee Table

Conclusion

By applying big data analysis technique, it extends Biztory from an ordinary billing app to a smart business management app with some BI integrated within. It helps the business owner to have a better idea how to improve or resolve the business problem when diving into the insight of it.

Having anything to share? Please leave your comment below :)

Bryan Soong

Written by

CEO & Founder @ Biztory.com.my Cloud Accounting, digitalizing SMEs business workflow.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade