Max De Marzi

If you have started or are thinking about starting a Graph project, you ought to get in touch with me. I’ve been involved in hundreds of graph database backed projects and chances are I can point you in the right direction. It doesn’t cost anything to get on a goto meeting for an hour and talk about it. Contact me at max@neo4j.com to schedule it. If you are very serious and have a little budget allocated then I recommend you sign up for one of our bootcamps. You’ll be amazed at what we can accomplish together in a very short time. I’ll even make you a deal, if you sign up for a bootcamp and end up buying a commercial license, we’ll give you a week of professional services absolutely free.

That’s my offer, but today I also want to talk about your offers. Neo4j has many retailers as clients and one of their use cases is making offers to their customers. I was with a client today who had seen my boolean logic rules engine and decision tree blog posts and they were considering going that route for their offers, but threw down the challenge of being able to do offers by just using Cypher. Their requirements were that offers can be of three types: “AllOf” offers require that the customer have all the requirements in order to be triggered, “AnyOf” offers which required just one of the requirements to be met, and “Majority” which required the majority of requirements to be met. The model could look like this:

Let’s go ahead and create some sample data:

 
CREATE (o:Offer { name: "Offer 1", type:"Majority",
             from_date: date({ year: 2018, month: 5, day: 1 }),
               to_date: date({ year: 2018, month: 5, day: 30 }) }),
(req1:Requirement {id:"Product 1"})<-[:REQUIRES]-(o),
(req2:Requirement {id:"Product 2"})<-[:REQUIRES]-(o),
(req3:Requirement {id:"New Customer"})<-[:REQUIRES]-(o),
(req4:Requirement {id:"In Illinois"})<-[:REQUIRES]-(o),
       (o2:Offer { name: "Offer 2", type:"AnyOf",
              from_date: date({ year: 2018, month: 5, day: 1 }),
                to_date: date({ year: 2018, month: 5, day: 30 })}),
(req5:Requirement {id:"Existing Customer"})<-[:REQUIRES]-(o2),
(req6:Requirement {id:"Last Purchase > 30 Days Ago"})<-[:REQUIRES]-(o2),
(req7:Requirement {id:"In California"})<-[:REQUIRES]-(o2),
       (o3:Offer { name: "Offer 3", type:"AllOf",
              from_date: date({ year: 2018, month: 5, day: 1 }),
                to_date: date({ year: 2018, month: 5, day: 30 })}),
(req1)<-[:REQUIRES]-(o3),
(req2)<-[:REQUIRES]-(o3),
(req3)<-[:REQUIRES]-(o3)

Which looks like this in the Neo4j Browser:

Now we are ready to write our query. It needs to return offers that are valid today, they need to be relevant to the customer so they need to have at least one requirement in common with the customer. We must return the offer, the requirements we meet, all of the offers requirements, the missing requirements and wether or not we meet those requirements. That sound pretty complicated, but let’s see the finished query and then we can walk through it in steps:

 
MATCH (req:Requirement)<-[:REQUIRES]-(o:Offer)
WHERE o.from_date < date() < o.to_date
  AND req.id IN ["Product 1", "Product 2", "In Illinois", "Existing Customer"]
WITH o, COLLECT(req.id) AS have
MATCH (o)-[:REQUIRES]->(reqs:Requirement)
WITH o, have, COLLECT(reqs.id) AS need
RETURN o, have, need, 
CASE o.type 
	WHEN "AnyOf" THEN ANY(x IN need WHERE x IN have)
	WHEN "AllOf" THEN ALL(x IN need WHERE x IN have)
	WHEN "Majority" THEN SIZE(have) > SIZE(need)/2.0
END AS qualifies, FILTER(x IN need WHERE NOT x IN have) AS missing

Not bad right? If you have never used the Cypher CASE statement or FILTER statement, click on those links to learn more about them. So what’s our query doing. First thing we want to do is use the “date()” function from Neo4j 3.4. to get today’s date and compare it to the from_date and to_date of our offers. The offers need to have at least one requirement that the user has, so we MATCH and use an “IN” clause to find them and collect them in to a list by offer that we call “have”.

 
MATCH (req:Requirement)<-[:REQUIRES]-(o:Offer)
WHERE o.from_date < date() < o.to_date
  AND req.id IN ["Product 1", "Product 2", "In Illinois", "Existing Customer"]
WITH o, COLLECT(req.id) AS have

Next we find all of the requirements for our Offer and collect them in a list we call “need”.

 
MATCH (o)-[:REQUIRES]->(reqs:Requirement)
WITH o, have, COLLECT(reqs.id) AS need

Next we return the Offer, the have and need lists, and we use a CASE statement to figure out if we meet the requirements of the offer. If the offer is of type “AnyOf” we just need to make sure that any requirement that we have is in the requirements that we need. If the offer is of type “AllOf” we need to make sure ALL the requirements are met. These ANY and ALL keywords are predicates in cypher that return TRUE or FALSE.

 
RETURN o, have, need, 
CASE o.type 
	WHEN "AnyOf" THEN ANY(x IN need WHERE x IN have)
	WHEN "AllOf" THEN ALL(x IN need WHERE x IN have)

If the offer is of type “Majority” then we make sure the size of the have list is greater than half the size of the need list. Majority requires 50% + 1, if we wanted “at least 50%” we could make that a greater than or equal to comparison instead. Finally, we want to return the missing requirements as well. We use a FILTER to get the list of missing requirements by checking each requirement in need and seeing if they are missing in the list of have.

 
	WHEN "Majority" THEN SIZE(have) > SIZE(need)/2.0
END AS qualifies, FILTER(x IN need WHERE NOT x IN have) AS missing

and there we have it:

So give it a shot, try changing the requirements passed in the array and see how the results change. Remember you will need Neo4j 3.4.0 or higher because of the use of the new date datatype. So go get it.

Before we end this, there are other ways to write this query… for example we could have written the case statement in this way:

 
RETURN o, have, need, 
CASE o.type 
	WHEN "AnyOf" THEN true
	WHEN "AllOf" THEN SIZE(have) = SIZE(need)

It works because “AnyOf” is always true since we wouldn’t have gotten to the offer if none of the requirements matched. Instead of using the ALL predicate we could simply compare the sizes of the two lists for AllOf. You may have been tempted to write “have = need” but the order of the items in the lists are not guaranteed and out of order lists are not equal even if they contain the same values.