Cypher Scripts for Neo4j Fraud Blog Post

data.cypher

CREATE (john:User {name:"John"})
CREATE (sheila:User {name:"Sheila"})
CREATE (robert:User {name:"Robert"})
CREATE (karen:User {name:"Karen"})
CREATE (m1:Merchant {name:"Computer Store"})
CREATE (m2:Merchant {name:"Gas Station"})
CREATE (m3:Merchant {name:"Jewelry Store"})
CREATE (m4:Merchant {name:"Furniture Store"})
CREATE (m5:Merchant {name:"Optometrist"})
CREATE (m6:Merchant {name:"Coffee Shop"})
CREATE (m7:Merchant {name:"Corner Store"})
CREATE (m8:Merchant {name:"Online Gift Card Merchant"})
CREATE (m9:Merchant {name:"Online Audio Book Merchant"})
CREATE (m10:Merchant {name:"Gas Station #2"})
CREATE (m11:Merchant {name:"Electronics Store"})
CREATE (m12:Merchant {name:"Vehicle Maintenance Merchant"})
CREATE (m13:Merchant {name:"French Restaurant"})
CREATE (m14:Merchant {name:"Mexican Restaurant"})
CREATE (m15:Merchant {name:"Chinese Restaurant"})
CREATE (m16:Merchant {name:"Indian Restaurant"})
CREATE (m17:Merchant {name:"Japanese Restaurant"})
CREATE (m18:Merchant {name:"Sports Bar"})
CREATE (m19:Merchant {name:"Pool Hall"})
CREATE (m20:Merchant {name:"Bowling Alley"})

CREATE (tx1:Transaction:Fraudulent {amount: 2000.00, date:datetime()})
CREATE (tx2:Transaction {amount: 35.00, date:datetime() - duration('P1D')})
CREATE (tx3:Transaction {amount: 25.00, date:datetime() - duration('P2D')})
CREATE (tx4:Transaction {amount: 12.00, date:datetime() - duration('P3D')})
CREATE (tx5:Transaction {amount: 87.00, date:datetime() - duration('P4D')})
CREATE (tx6:Transaction {amount: 10.00, date:datetime() - duration('P5D')})
CREATE (tx7:Transaction {amount: 30.00, date:datetime() - duration('P6D')})
CREATE (tx8:Transaction {amount: 25.00, date:datetime() - duration('P7D')})
CREATE (tx9:Transaction {amount: 62.00, date:datetime() - duration('P8D')})
CREATE (tx10:Transaction {amount: 4.00, date:datetime() - duration('P9D')})
CREATE (tx11:Transaction {amount: 90.00, date:datetime() - duration('P10D')})
CREATE (tx12:Transaction {amount: 73.00, date:datetime() - duration('P11D')})
CREATE (tx13:Transaction {amount: 26.00, date:datetime() - duration('P12D')})
CREATE (tx14:Transaction {amount: 108.00, date:datetime() - duration('P13D')})
CREATE (tx15:Transaction {amount: 50.00, date:datetime() - duration('P14D')})
CREATE (tx16:Transaction {amount: 40.00, date:datetime()})
CREATE (tx17:Transaction {amount: 38.00, date:datetime() - duration('P1D')})
CREATE (tx18:Transaction {amount: 49.00, date:datetime() - duration('P2D')})
CREATE (tx19:Transaction:Fraudulent {amount: 3000.00, date:datetime() - duration('P3D')})
CREATE (tx20:Transaction {amount: 66.00, date:datetime() - duration('P4D')})
CREATE (tx21:Transaction {amount: 12.00, date:datetime() - duration('P5D')})
CREATE (tx22:Transaction {amount: 3.00, date:datetime() - duration('P6D')})
CREATE (tx23:Transaction {amount: 40.00, date:datetime() - duration('P7D')})
CREATE (tx24:Transaction {amount: 8.00, date:datetime() - duration('P8D')})
CREATE (tx25:Transaction {amount: 2.00, date:datetime() - duration('P9D')})
CREATE (tx26:Transaction {amount: 76.00, date:datetime() - duration('P10D')})
CREATE (tx27:Transaction {amount: 33.00, date:datetime() - duration('P11D')})
CREATE (tx28:Transaction {amount: 24.00, date:datetime() - duration('P12D')})
CREATE (tx29:Transaction {amount: 17.00, date:datetime() - duration('P13D')})
CREATE (tx30:Transaction {amount: 22.00, date:datetime() - duration('P14D')})
CREATE (tx31:Transaction {amount: 37.00, date:datetime()})
CREATE (tx32:Transaction {amount: 78.00, date:datetime() - duration('P1D')})
CREATE (tx33:Transaction {amount: 23.00, date:datetime() - duration('P2D')})
CREATE (tx34:Transaction {amount: 10.00, date:datetime() - duration('P3D')})
CREATE (tx35:Transaction {amount: 19.00, date:datetime() - duration('P4D')})
CREATE (tx36:Transaction {amount: 37.00, date:datetime() - duration('P5D')})
CREATE (tx37:Transaction {amount: 44.00, date:datetime() - duration('P6D')})
CREATE (tx38:Transaction {amount: 18.00, date:datetime() - duration('P7D')})
CREATE (tx39:Transaction {amount: 62.00, date:datetime() - duration('P8D')})
CREATE (tx40:Transaction {amount: 32.00, date:datetime() - duration('P9D')})
CREATE (tx41:Transaction {amount: 76.00, date:datetime() - duration('P10D')})
CREATE (tx42:Transaction {amount: 6.00, date:datetime() - duration('P11D')})
CREATE (tx43:Transaction {amount: 31.00, date:datetime() - duration('P12D')})
CREATE (tx44:Transaction {amount: 36.00, date:datetime() - duration('P13D')})
CREATE (tx45:Transaction {amount: 65.00, date:datetime() - duration('P14D')})
CREATE (tx46:Transaction {amount: 40.00, date:datetime()})
CREATE (tx47:Transaction {amount: 23.00, date:datetime() - duration('P1D')})
CREATE (tx48:Transaction:Fraudulent {amount: 1500.00, date:datetime() - duration('P2D')})
CREATE (tx49:Transaction {amount: 35.00, date:datetime() - duration('P3D')})
CREATE (tx50:Transaction {amount: 12.00, date:datetime() - duration('P4D')})
CREATE (tx51:Transaction {amount: 25.00, date:datetime() - duration('P5D')})
CREATE (tx52:Transaction {amount: 40.00, date:datetime() - duration('P6D')})
CREATE (tx53:Transaction {amount: 8.00, date:datetime() - duration('P7D')})
CREATE (tx54:Transaction {amount: 12.00, date:datetime() - duration('P8D')})
CREATE (tx55:Transaction {amount: 3.00, date:datetime() - duration('P9D')})
CREATE (tx56:Transaction {amount: 36.00, date:datetime() - duration('P10D')})
CREATE (tx57:Transaction {amount: 15.00, date:datetime() - duration('P11D')})
CREATE (tx58:Transaction {amount: 19.00, date:datetime() - duration('P12D')})
CREATE (tx59:Transaction {amount: 27.00, date:datetime() - duration('P13D')})
CREATE (tx60:Transaction {amount: 42.00, date:datetime() - duration('P14D')})

CREATE (tx1)-[:AT_MERCHANT]->(m1)
CREATE (tx2)-[:AT_MERCHANT]->(m8)
CREATE (tx3)-[:AT_MERCHANT]->(m12)
CREATE (tx4)-[:AT_MERCHANT]->(m7)
CREATE (tx5)-[:AT_MERCHANT]->(m20)
CREATE (tx6)-[:AT_MERCHANT]->(m16)
CREATE (tx7)-[:AT_MERCHANT]->(m11)
CREATE (tx8)-[:AT_MERCHANT]->(m9)
CREATE (tx9)-[:AT_MERCHANT]->(m10)
CREATE (tx10)-[:AT_MERCHANT]->(m2)
CREATE (tx11)-[:AT_MERCHANT]->(m9)
CREATE (tx12)-[:AT_MERCHANT]->(m17)
CREATE (tx13)-[:AT_MERCHANT]->(m12)
CREATE (tx14)-[:AT_MERCHANT]->(m7)
CREATE (tx15)-[:AT_MERCHANT]->(m8)
CREATE (tx16)-[:AT_MERCHANT]->(m20)
CREATE (tx17)-[:AT_MERCHANT]->(m13)
CREATE (tx18)-[:AT_MERCHANT]->(m10)
CREATE (tx19)-[:AT_MERCHANT]->(m3)
CREATE (tx20)-[:AT_MERCHANT]->(m10)
CREATE (tx21)-[:AT_MERCHANT]->(m2)
CREATE (tx22)-[:AT_MERCHANT]->(m5)
CREATE (tx23)-[:AT_MERCHANT]->(m9)
CREATE (tx24)-[:AT_MERCHANT]->(m14)
CREATE (tx25)-[:AT_MERCHANT]->(m2)
CREATE (tx26)-[:AT_MERCHANT]->(m16)
CREATE (tx27)-[:AT_MERCHANT]->(m20)
CREATE (tx28)-[:AT_MERCHANT]->(m7)
CREATE (tx29)-[:AT_MERCHANT]->(m6)
CREATE (tx30)-[:AT_MERCHANT]->(m9)
CREATE (tx31)-[:AT_MERCHANT]->(m8)
CREATE (tx32)-[:AT_MERCHANT]->(m17)
CREATE (tx33)-[:AT_MERCHANT]->(m13)
CREATE (tx34)-[:AT_MERCHANT]->(m14)
CREATE (tx35)-[:AT_MERCHANT]->(m18)
CREATE (tx36)-[:AT_MERCHANT]->(m19)
CREATE (tx37)-[:AT_MERCHANT]->(m20)
CREATE (tx38)-[:AT_MERCHANT]->(m11)
CREATE (tx39)-[:AT_MERCHANT]->(m12)
CREATE (tx40)-[:AT_MERCHANT]->(m7)
CREATE (tx41)-[:AT_MERCHANT]->(m13)
CREATE (tx42)-[:AT_MERCHANT]->(m2)
CREATE (tx43)-[:AT_MERCHANT]->(m15)
CREATE (tx44)-[:AT_MERCHANT]->(m14)
CREATE (tx45)-[:AT_MERCHANT]->(m1)
CREATE (tx46)-[:AT_MERCHANT]->(m5)
CREATE (tx47)-[:AT_MERCHANT]->(m8)
CREATE (tx48)-[:AT_MERCHANT]->(m4)
CREATE (tx49)-[:AT_MERCHANT]->(m18)
CREATE (tx50)-[:AT_MERCHANT]->(m1)
CREATE (tx51)-[:AT_MERCHANT]->(m17)
CREATE (tx52)-[:AT_MERCHANT]->(m20)
CREATE (tx53)-[:AT_MERCHANT]->(m18)
CREATE (tx54)-[:AT_MERCHANT]->(m5)
CREATE (tx55)-[:AT_MERCHANT]->(m2)
CREATE (tx56)-[:AT_MERCHANT]->(m8)
CREATE (tx57)-[:AT_MERCHANT]->(m14)
CREATE (tx58)-[:AT_MERCHANT]->(m12)
CREATE (tx59)-[:AT_MERCHANT]->(m6)
CREATE (tx60)-[:AT_MERCHANT]->(m8)

CREATE (john)-[:MAKES]->(tx1)
CREATE (john)-[:MAKES]->(tx2)
CREATE (john)-[:MAKES]->(tx3)
CREATE (john)-[:MAKES]->(tx4)
CREATE (john)-[:MAKES]->(tx5)
CREATE (john)-[:MAKES]->(tx6)
CREATE (john)-[:MAKES]->(tx7)
CREATE (john)-[:MAKES]->(tx8)
CREATE (john)-[:MAKES]->(tx9)
CREATE (john)-[:MAKES]->(tx10)
CREATE (john)-[:MAKES]->(tx11)
CREATE (john)-[:MAKES]->(tx12)
CREATE (john)-[:MAKES]->(tx13)
CREATE (john)-[:MAKES]->(tx14)
CREATE (john)-[:MAKES]->(tx15)
CREATE (sheila)-[:MAKES]->(tx16)
CREATE (sheila)-[:MAKES]->(tx17)
CREATE (sheila)-[:MAKES]->(tx18)
CREATE (sheila)-[:MAKES]->(tx19)
CREATE (sheila)-[:MAKES]->(tx20)
CREATE (sheila)-[:MAKES]->(tx21)
CREATE (sheila)-[:MAKES]->(tx22)
CREATE (sheila)-[:MAKES]->(tx23)
CREATE (sheila)-[:MAKES]->(tx24)
CREATE (sheila)-[:MAKES]->(tx25)
CREATE (sheila)-[:MAKES]->(tx26)
CREATE (sheila)-[:MAKES]->(tx27)
CREATE (sheila)-[:MAKES]->(tx28)
CREATE (sheila)-[:MAKES]->(tx29)
CREATE (sheila)-[:MAKES]->(tx30)
CREATE (robert)-[:MAKES]->(tx31)
CREATE (robert)-[:MAKES]->(tx32)
CREATE (robert)-[:MAKES]->(tx33)
CREATE (robert)-[:MAKES]->(tx34)
CREATE (robert)-[:MAKES]->(tx35)
CREATE (robert)-[:MAKES]->(tx36)
CREATE (robert)-[:MAKES]->(tx37)
CREATE (robert)-[:MAKES]->(tx38)
CREATE (robert)-[:MAKES]->(tx39)
CREATE (robert)-[:MAKES]->(tx40)
CREATE (robert)-[:MAKES]->(tx41)
CREATE (robert)-[:MAKES]->(tx42)
CREATE (robert)-[:MAKES]->(tx43)
CREATE (robert)-[:MAKES]->(tx44)
CREATE (robert)-[:MAKES]->(tx45)
CREATE (karen)-[:MAKES]->(tx46)
CREATE (karen)-[:MAKES]->(tx47)
CREATE (karen)-[:MAKES]->(tx48)
CREATE (karen)-[:MAKES]->(tx49)
CREATE (karen)-[:MAKES]->(tx50)
CREATE (karen)-[:MAKES]->(tx51)
CREATE (karen)-[:MAKES]->(tx52)
CREATE (karen)-[:MAKES]->(tx53)
CREATE (karen)-[:MAKES]->(tx54)
CREATE (karen)-[:MAKES]->(tx55)
CREATE (karen)-[:MAKES]->(tx56)
CREATE (karen)-[:MAKES]->(tx57)
CREATE (karen)-[:MAKES]->(tx58)
CREATE (karen)-[:MAKES]->(tx59)
CREATE (karen)-[:MAKES]->(tx60)

CREATE (john)-[:PREV_TX]->(tx1)
CREATE (tx1)-[:PREV_TX]->(tx2)
CREATE (tx2)-[:PREV_TX]->(tx3)
CREATE (tx3)-[:PREV_TX]->(tx4)
CREATE (tx4)-[:PREV_TX]->(tx5)
CREATE (tx5)-[:PREV_TX]->(tx6)
CREATE (tx6)-[:PREV_TX]->(tx7)
CREATE (tx7)-[:PREV_TX]->(tx8)
CREATE (tx8)-[:PREV_TX]->(tx9)
CREATE (tx9)-[:PREV_TX]->(tx10)
CREATE (tx10)-[:PREV_TX]->(tx11)
CREATE (tx11)-[:PREV_TX]->(tx12)
CREATE (tx12)-[:PREV_TX]->(tx13)
CREATE (tx13)-[:PREV_TX]->(tx14)
CREATE (tx14)-[:PREV_TX]->(tx15)
CREATE (sheila)-[:PREV_TX]->(tx16)
CREATE (tx16)-[:PREV_TX]->(tx17)
CREATE (tx17)-[:PREV_TX]->(tx18)
CREATE (tx18)-[:PREV_TX]->(tx19)
CREATE (tx19)-[:PREV_TX]->(tx20)
CREATE (tx20)-[:PREV_TX]->(tx21)
CREATE (tx21)-[:PREV_TX]->(tx22)
CREATE (tx22)-[:PREV_TX]->(tx23)
CREATE (tx23)-[:PREV_TX]->(tx24)
CREATE (tx24)-[:PREV_TX]->(tx25)
CREATE (tx25)-[:PREV_TX]->(tx26)
CREATE (tx26)-[:PREV_TX]->(tx27)
CREATE (tx27)-[:PREV_TX]->(tx28)
CREATE (tx28)-[:PREV_TX]->(tx29)
CREATE (tx29)-[:PREV_TX]->(tx30)
CREATE (robert)-[:PREV_TX]->(tx31)
CREATE (tx31)-[:PREV_TX]->(tx32)
CREATE (tx32)-[:PREV_TX]->(tx33)
CREATE (tx33)-[:PREV_TX]->(tx34)
CREATE (tx34)-[:PREV_TX]->(tx35)
CREATE (tx35)-[:PREV_TX]->(tx36)
CREATE (tx36)-[:PREV_TX]->(tx37)
CREATE (tx37)-[:PREV_TX]->(tx38)
CREATE (tx38)-[:PREV_TX]->(tx39)
CREATE (tx39)-[:PREV_TX]->(tx40)
CREATE (tx40)-[:PREV_TX]->(tx41)
CREATE (tx41)-[:PREV_TX]->(tx42)
CREATE (tx42)-[:PREV_TX]->(tx43)
CREATE (tx43)-[:PREV_TX]->(tx44)
CREATE (tx44)-[:PREV_TX]->(tx45)
CREATE (karen)-[:PREV_TX]->(tx46)
CREATE (tx46)-[:PREV_TX]->(tx47)
CREATE (tx47)-[:PREV_TX]->(tx48)
CREATE (tx48)-[:PREV_TX]->(tx49)
CREATE (tx49)-[:PREV_TX]->(tx50)
CREATE (tx50)-[:PREV_TX]->(tx51)
CREATE (tx51)-[:PREV_TX]->(tx52)
CREATE (tx52)-[:PREV_TX]->(tx53)
CREATE (tx53)-[:PREV_TX]->(tx54)
CREATE (tx54)-[:PREV_TX]->(tx55)
CREATE (tx55)-[:PREV_TX]->(tx56)
CREATE (tx56)-[:PREV_TX]->(tx57)
CREATE (tx57)-[:PREV_TX]->(tx58)
CREATE (tx58)-[:PREV_TX]->(tx59)
CREATE (tx59)-[:PREV_TX]->(tx60)

queries.cypher

// The last week of John's transactions using the MAKES relationships
MATCH p = (n:User {name:"John"})-[:MAKES]->(tx)
WHERE tx.date > datetime() - duration('P7D')
RETURN p;

// The last week of John's transactions using the PREV_TX relationships
MATCH p = (n:User {name:"John"})-[:PREV_TX*]->(tx)
WHERE NONE (tx IN nodes(p) WHERE COALESCE(tx.date, datetime()) <= datetime() - duration('P7D'))
RETURN p;

// All the transactions marked fraudulent in the last week and the transactions that came before them up to two weeks ago.
MATCH p = (fraud:Fraudulent)-[:PREV_TX*]->(tx)
WHERE fraud.date > datetime() - duration('P7D')
  AND NONE (tx IN nodes(p) WHERE COALESCE(tx.date, datetime()) <= datetime() - duration('P14D'))
RETURN p;

// Top 5 common merchants from fraudulent transaction chains up to two weeks ago.
MATCH p = (fraud:Fraudulent)-[:PREV_TX*]->(tx)
WHERE fraud.date > datetime() - duration('P7D')
  AND NONE (tx IN nodes(p) WHERE COALESCE(tx.date, datetime()) <= datetime() - duration('P14D'))
WITH nodes(p) AS transactions
UNWIND transactions AS tx
WITH DISTINCT tx
MATCH (tx)-[:AT_MERCHANT]->(merchant)
RETURN merchant.name, COUNT(*) AS txCount
ORDER BY txCount DESC
LIMIT 5;

MATCH (fraud:Fraudulent)<-[:MAKES]-(user)
WHERE fraud.date > datetime() - duration('P7D')
WITH COLLECT(user) AS users
// Find the transactions that happened before the reported fraud in the last 2 weeks
MATCH p = (fraud:Fraudulent)-[:PREV_TX*]->(tx)
WHERE fraud.date > datetime() - duration('P7D')
  AND NONE (tx IN nodes(p) WHERE tx.date <= datetime() - duration('P14D'))
WITH users, nodes(p) AS transactions
// Find out at which merchant these transactions took place
UNWIND transactions AS tx
WITH DISTINCT users, tx
MATCH (user)-[:MAKES]->(tx)-[:AT_MERCHANT]->(merchant)
// Use the number of distinct users who made transactions as a score
WITH users, merchant, COUNT(DISTINCT user) AS userCount
ORDER BY userCount DESC
// Find out who else may be at risk
MATCH (merchant)<-[:AT_MERCHANT]-(tx)<-[:MAKES]-(user)
WHERE tx.date > datetime() - duration('P14D')
  AND NOT user IN users
// Which merchants may have been the first sign of fraud
WITH DISTINCT user, COLLECT({merchant: merchant.name, userCount: userCount}) AS potentials, AVG(userCount) AS averageuserCount
WITH user, averageuserCount, potentials
UNWIND potentials AS potential
// Return those with higher than average 
WITH user, potential, averageuserCount
WHERE potential.userCount > averageuserCount
RETURN user, potential.merchant, potential.userCount
ORDER BY user, potential.userCount DESC