Analysis of call detail records (CDR) provides greater insights into the telecommunications activity like inbound calls, outbound calls, dropped and abandoned calls, and unanswered calls. This helps organizations track details on individual extensions, toll fraud, multiple sites/locations, and make necessary changes to improve productivity and efficiency on all levels.
CDR data can reach big data proportions for some Enterprise Businesses.Updating very large tables in SQL server can be a time taking task and sometimes it might take hours to finish. In addition to this, it might also cause blocking issues.
Neo4j has been used in telecom industry: churn reduction (https://neo4j.com/news/0227-reducing-telecom-churn/) and event log data( https://vimeo.com/79390660). These applications are at the telecom provider level.
Here I use Neo4j at the business (corporations) level that consume the services provided by the telecom provider.
Call detail records data file comes with about 120 columns. For this gist, I selected call time, calling number, called number, call forwarded number, call duration, call type(incoming/outgoing/voicemail), call answered flag, call forwarded flag, company name.
Incoming calls: Answered or went to voicemail.
Incoming calls: Forwarded, answered or went to voicemail.
Outgoing calls (Extension to Extension): Answered or went to voicemail.
Outgoing calls (External): Called
Direct to voicemail: Voicemail
This graph model stores call detail records (CDR) in nodes, relationships and properties. Call date is modelled as integer following the article: https://www.menome.com/wp/neo4j-modelling-dates/
Here is a sample of data in the .csv file:
TME: Epoch time, CLNG: Calling number, CLED: Called number, FCLED: Call forwarded number (80000 is voicemail), TYP: Call type, ANS: Answered or Voicemail, FWD: Forwarded number, CPNY:Company name
TME,CLNG,CLED,FCLED,DUR,TYP,ANS,FWD,CPNY 1449158574,4045551212,4041212555,80000,70,Incoming,VM,FWD,AmeyaSoft 1449159132,9195551212,8191212555,8191212555,458,Outgoing,Ans,Orig,AmeyaSoft 1449159135,8045551212,5401212555,80000,304,Incoming,VM,FWD,AmeyaSoft 1449159146,2705551212,8151212555,8151212555,451,Incoming,Ans,FWD,AmeyaSoft
A Cypher query to setup the database (used only 50 call records)
CREATE (company:Company {name: "AmeyaSoft"})
CREATE (calldate:CallDate {id: 20151203})
CREATE (company)-[:CALL_DATE]->(calldate)
CREATE (connect:Connect {id: 1449158574})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect)
CREATE (called:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm:IncomingVM {id: "4045551212", duration: 70})
CREATE (connect)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm)
CREATE (callingvm)-[:CALL_TO]->(called)
CREATE (connect1:Connect {id: 1449159132})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect1)
CREATE (called1:CallTo {orig: "8191212555", fwd: "8191212555"})
CREATE (calling1:CallFrom {id: "9195551212", duration: 458})
CREATE (connect1)-[:OUTGOING {ans: true, Abandon: false}]->(calling1)
CREATE (calling1)-[:CALL_TO {fwd: false}]->(called1)
CREATE (connect2:Connect {id: 1449159135})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect2)
CREATE (called2:CallTo {orig: "5401212555", fwd: "80000"})
CREATE (callingvm2:IncomingVM {id: "8045551212", duration: 304})
CREATE (connect2)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm2)
CREATE (callingvm2)-[:CALL_TO]->(called2)
CREATE (connect3:Connect {id: 1449159146})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect3)
CREATE (called3:CallTo {orig: "8151212555", fwd: "8151212555"})
CREATE (calling3:IncomingFrom {id: "2705551212", duration: 451})
CREATE (connect3)-[:INCOMING {ans: true, Abandon: false}]->(calling3)
CREATE (calling3)-[:CALL_TO {fwd: true}]->(called3)
CREATE (connect4:Connect {id: 1449159149})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect4)
CREATE (called4:CallTo {orig: "8121212555", fwd: "8121212555"})
CREATE (calling4:CallFrom {id: "9735551212", duration: 384})
CREATE (connect4)-[:OUTGOING {ans: true, Abandon: false}]->(calling4)
CREATE (calling4)-[:CALL_TO {fwd: false}]->(called4)
CREATE (connect5:Connect {id: 1449159162})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect5)
CREATE (called5:CallTo {orig: "8121212555", fwd: "8121212555"})
CREATE (calling5:CallFrom {id: "2145551212", duration: 695})
CREATE (connect5)-[:OUTGOING {ans: true, Abandon: false}]->(calling5)
CREATE (calling5)-[:CALL_TO {fwd: false}]->(called5)
CREATE (connect6:Connect {id: 1449159198})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect6)
CREATE (called6:CallTo {orig: "6151212555", fwd: "6151212555"})
CREATE (calling6:IncomingFrom {id: "3525551212", duration: 480})
CREATE (connect6)-[:INCOMING {ans: true, Abandon: false}]->(calling6)
CREATE (calling6)-[:CALL_TO {fwd: true}]->(called6)
CREATE (connect7:Connect {id: 1449159229})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect7)
CREATE (called7:CallTo {orig: "8191212555", fwd: "8191212555"})
CREATE (calling7:CallFrom {id: "5615551212", duration: 668})
CREATE (connect7)-[:OUTGOING {ans: true, Abandon: false}]->(calling7)
CREATE (calling7)-[:CALL_TO {fwd: false}]->(called7)
CREATE (connect8:Connect {id: 1449159334})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect8)
CREATE (called8:CallTo {orig: "5181212555", fwd: "80000"})
CREATE (callingvm8:VoiceCall {id: "5185551212", duration: 361})
CREATE (connect8)-[:VOICEMAIL {call: "Outgoing"}]->(callingvm8)
CREATE (callingvm8)-[:CALL_TO]->(called8)
CREATE (connect9:Connect {id: 1449159420})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect9)
CREATE (called9:CallTo {orig: "9091212555", fwd: "9091212555"})
CREATE (calling9:IncomingFrom {id: "8175551212", duration: 881})
CREATE (connect9)-[:INCOMING {ans: true, Abandon: false}]->(calling9)
CREATE (calling9)-[:CALL_TO {fwd: true}]->(called9)
CREATE (connect10:Connect {id: 1449159488})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect10)
CREATE (called10:CallTo {orig: "8161212555", fwd: "8161212555"})
CREATE (calling10:CallFrom {id: "5615551212", duration: 314})
CREATE (connect10)-[:OUTGOING {ans: true, Abandon: false}]->(calling10)
CREATE (calling10)-[:CALL_TO {fwd: false}]->(called10)
CREATE (connect11:Connect {id: 1449159492})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect11)
CREATE (called11:CallTo {orig: "8181212555", fwd: "8181212555"})
CREATE (calling11:CallFrom {id: "8155551212", duration: 352})
CREATE (connect11)-[:OUTGOING {ans: true, Abandon: false}]->(calling11)
CREATE (calling11)-[:CALL_TO {fwd: false}]->(called11)
CREATE (connect12:Connect {id: 1449159570})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect12)
CREATE (called12:CallTo {orig: "8181212555", fwd: "8181212555"})
CREATE (calling12:CallFrom {id: "7135551212", duration: 653})
CREATE (connect12)-[:OUTGOING {ans: true, Abandon: false}]->(calling12)
CREATE (calling12)-[:CALL_TO {fwd: false}]->(called12)
CREATE (connect13:Connect {id: 1449159623})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect13)
CREATE (called13:CallTo {orig: "8161212555", fwd: "8161212555"})
CREATE (calling13:CallFrom {id: "6155551212", duration: 485})
CREATE (connect13)-[:OUTGOING {ans: true, Abandon: false}]->(calling13)
CREATE (calling13)-[:CALL_TO {fwd: false}]->(called13)
CREATE (connect14:Connect {id: 1449160268})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect14)
CREATE (called14:CallTo {orig: "7171212555", fwd: "80000"})
CREATE (callingvm14:VoiceCall {id: "7175551212", duration: 351})
CREATE (connect14)-[:VOICEMAIL {call: "Outgoing"}]->(callingvm14)
CREATE (callingvm14)-[:CALL_TO]->(called14)
CREATE (connect15:Connect {id: 1449160627})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect15)
CREATE (called15:CallTo {orig: "8141212555", fwd: "8141212555"})
CREATE (calling15:CallFrom {id: "4075551212", duration: 471})
CREATE (connect15)-[:OUTGOING {ans: true, Abandon: false}]->(calling15)
CREATE (calling15)-[:CALL_TO {fwd: false}]->(called15)
CREATE (connect16:Connect {id: 1449160671})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect16)
CREATE (called16:CallTo {orig: "8161212555", fwd: "8161212555"})
CREATE (calling16:CallFrom {id: "2145551212", duration: 308})
CREATE (connect16)-[:OUTGOING {ans: true, Abandon: false}]->(calling16)
CREATE (calling16)-[:CALL_TO {fwd: false}]->(called16)
CREATE (connect17:Connect {id: 1449160860})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect17)
CREATE (called17:CallTo {orig: "8451212555", fwd: "8451212555"})
CREATE (calling17:IncomingFrom {id: "8455551212", duration: 324})
CREATE (connect17)-[:INCOMING {ans: true, Abandon: false}]->(calling17)
CREATE (calling17)-[:CALL_TO {fwd: true}]->(called17)
CREATE (connect18:Connect {id: 1449160936})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect18)
CREATE (called18:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm18:IncomingVM {id: "6781212555", duration: 64})
CREATE (connect18)-[:VOICEMAIL {call: "Incoming"}]->(callingvm18)
CREATE (callingvm18)-[:CALL_TO]->(called18)
CREATE (connect19:Connect {id: 1449160976})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect19)
CREATE (called19:CallTo {orig: "2011212555", fwd: "2011212555"})
CREATE (calling19:IncomingFrom {id: "5125551212", duration: 376})
CREATE (connect19)-[:INCOMING {ans: true, Abandon: false}]->(calling19)
CREATE (calling19)-[:CALL_TO {fwd: false}]->(called19)
CREATE (connect20:Connect {id: 1449161939})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect20)
CREATE (called20:CallTo {orig: "9091212555", fwd: "9091212555"})
CREATE (calling20:IncomingFrom {id: "9515551212", duration: 540})
CREATE (connect20)-[:INCOMING {ans: true, Abandon: false}]->(calling20)
CREATE (calling20)-[:CALL_TO {fwd: false}]->(called20)
CREATE (connect21:Connect {id: 1449162007})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect21)
CREATE (called21:CallTo {orig: "9091212555", fwd: "9091212555"})
CREATE (calling21:IncomingFrom {id: "7145551212", duration: 371})
CREATE (connect21)-[:INCOMING {ans: true, Abandon: false}]->(calling21)
CREATE (calling21)-[:CALL_TO {fwd: true}]->(called21)
CREATE (connect22:Connect {id: 1449162054})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect22)
CREATE (called22:CallTo {orig: "4101212555", fwd: "4101212555"})
CREATE (calling22:IncomingFrom {id: "2025551212", duration: 369})
CREATE (connect22)-[:INCOMING {ans: true, Abandon: false}]->(calling22)
CREATE (calling22)-[:CALL_TO {fwd: false}]->(called22)
CREATE (connect23:Connect {id: 1449162215})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect23)
CREATE (called23:CallTo {orig: "7021212555", fwd: "7021212555"})
CREATE (calling23:IncomingFrom {id: "7855551212", duration: 418})
CREATE (connect23)-[:INCOMING {ans: true, Abandon: false}]->(calling23)
CREATE (calling23)-[:CALL_TO {fwd: false}]->(called23)
CREATE (connect24:Connect {id: 1449163024})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect24)
CREATE (called24:CallTo {orig: "9541212555", fwd: "80000"})
CREATE (callingvm24:VoiceCall {id: "9545551212", duration: 314})
CREATE (connect24)-[:VOICEMAIL {call: "Outgoing"}]->(callingvm24)
CREATE (callingvm24)-[:CALL_TO]->(called24)
CREATE (connect241:Connect {id: 1449163781})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect241)
CREATE (called241:CallTo {orig: "4041212555", fwd: "4041212555"})
CREATE (calling241:IncomingFrom {id: "4045551212", duration: 349})
CREATE (connect241)-[:INCOMING {ans: true, Abandon: false}]->(calling241)
CREATE (calling241)-[:CALL_TO {fwd: false}]->(called241)
CREATE (connect25:Connect {id: 1449164479})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect25)
CREATE (called25:CallTo {orig: "8001212555", fwd: "80000"})
CREATE (callingvm25:VoiceCall {id: "9545551212", duration: 379})
CREATE (connect25)-[:VOICEMAIL {call: "Outgoing", Abandon: false}]->(callingvm25)
CREATE (callingvm25)-[:CALL_TO]->(called25)
CREATE (connect26:Connect {id: 1449165245})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect26)
CREATE (called26:CallTo {orig: "9091212555", fwd: "80000"})
CREATE (callingvm26:IncomingVM {id: "9091212555", duration: 362})
CREATE (connect26)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm26)
CREATE (callingvm26)-[:CALL_TO]->(called26)
CREATE (connect27:Connect {id: 1449169344})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect27)
CREATE (called27:CallTo {orig: "8001212555", fwd: "80000"})
CREATE (callingvm27:VoiceCall {id: "3035551212", duration: 447})
CREATE (connect27)-[:VOICEMAIL {call: "Outgoing", Abandon: false}]->(callingvm27)
CREATE (callingvm27)-[:CALL_TO]->(called27)
CREATE (connect28:Connect {id: 1449169392})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect28)
CREATE (called28:CallTo {orig: "9091212555", fwd: "80000"})
CREATE (callingvm28:IncomingVM {id: "7021212555", duration: 151})
CREATE (connect28)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm28)
CREATE (callingvm28)-[:CALL_TO]->(called28)
CREATE (connect29:Connect {id: 1449169887})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect29)
CREATE (called29:CallTo {orig: "9091212555", fwd: "80000"})
CREATE (callingvm29:IncomingVM {id: "5591212555", duration: 108})
CREATE (connect29)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm29)
CREATE (callingvm29)-[:CALL_TO]->(called29)
CREATE (connect30:Connect {id: 1449172878})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect30)
CREATE (called30:CallTo {orig: "6151212555", fwd: "80000"})
CREATE (callingvm30:IncomingVM {id: "6151212555", duration: 171})
CREATE (connect30)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm30)
CREATE (callingvm30)-[:CALL_TO]->(called30)
CREATE (connect31:Connect {id: 1449173459})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect31)
CREATE (called31:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm31:IncomingVM {id: "6781212555", duration: 62})
CREATE (connect31)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm31)
CREATE (callingvm31)-[:CALL_TO]->(called31)
CREATE (connect32:Connect {id: 1449173679})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect32)
CREATE (called32:CallTo {orig: "8001212555", fwd: "80000"})
CREATE (callingvm32:VoiceCall {id: "9735551212", duration: 311})
CREATE (connect32)-[:VOICEMAIL {call: "Outgoing", Abandon: false}]->(callingvm32)
CREATE (callingvm32)-[:CALL_TO]->(called32)
CREATE (connect33:Connect {id: 1449174539})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect33)
CREATE (called33:CallTo {orig: "6151212555", fwd: "80000"})
CREATE (callingvm33:IncomingVM {id: "2701212555", duration: 137})
CREATE (connect33)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm33)
CREATE (callingvm33)-[:CALL_TO]->(called33)
CREATE (connect34:Connect {id: 1449175373})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect34)
CREATE (called34:CallTo {orig: "5401212555", fwd: "80000"})
CREATE (callingvm34:IncomingVM {id: "5401212555", duration: 100})
CREATE (connect34)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm34)
CREATE (callingvm34)-[:CALL_TO]->(called34)
CREATE (connect35:Connect {id: 1449176076})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect35)
CREATE (called35:CallTo {orig: "7021212555", fwd: "80000"})
CREATE (callingvm35:IncomingVM {id: "6021212555", duration: 114})
CREATE (connect35)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm35)
CREATE (callingvm35)-[:CALL_TO]->(called35)
CREATE (connect36:Connect {id: 1449176132})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect36)
CREATE (called36:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm36:IncomingVM {id: "4701212555", duration: 66})
CREATE (connect36)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm36)
CREATE (callingvm36)-[:CALL_TO]->(called36)
CREATE (connect37:Connect {id: 1449177292})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect37)
CREATE (called37:CallTo {orig: "8001212555", fwd: "80000"})
CREATE (callingvm37:VoiceCall {id: "9515551212", duration: 300})
CREATE (connect37)-[:VOICEMAIL {call: "Outgoing", Abandon: false}]->(callingvm37)
CREATE (callingvm37)-[:CALL_TO]->(called37)
CREATE (connect371:Connect {id: 1449177501})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect371)
CREATE (called371:CallTo {orig: "4071212555", fwd: "80000"})
CREATE (callingvm371:VoiceCall {id: "4075551212", duration: 364})
CREATE (connect371)-[:VOICEMAIL {call: "Outgoing", Abandon: false}]->(callingvm371)
CREATE (callingvm371)-[:CALL_TO]->(called371)
CREATE (connect38:Connect {id: 1449177594})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect38)
CREATE (called38:CallTo {orig: "7141212555", fwd: "80000"})
CREATE (callingvm38:IncomingVM {id: "8011212555", duration: 165})
CREATE (connect38)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm38)
CREATE (callingvm38)-[:CALL_TO]->(called38)
CREATE (connect39:Connect {id: 1449179416})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect39)
CREATE (called39:CallTo {orig: "8001212555", fwd: "80000"})
CREATE (callingvm39:VoiceCall {id: "8175551212", duration: 323})
CREATE (connect39)-[:VOICEMAIL {call: "Outgoing", Abandon: false}]->(callingvm39)
CREATE (callingvm39)-[:CALL_TO]->(called39)
CREATE (connect40:Connect {id: 1449180507})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect40)
CREATE (called40:CallTo {orig: "4101212555", fwd: "80000"})
CREATE (callingvm40:IncomingVM {id: "2401212555", duration: 111})
CREATE (connect40)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm40)
CREATE (callingvm40)-[:CALL_TO]->(called40)
CREATE (connect41:Connect {id: 1449180703})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect41)
CREATE (called41:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm41:IncomingVM {id: "2141212555", duration: 91})
CREATE (connect41)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm41)
CREATE (callingvm41)-[:CALL_TO]->(called41)
CREATE (connect42:Connect {id: 1449181970})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect42)
CREATE (called42:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm42:IncomingVM {id: "4041212555", duration: 65})
CREATE (connect42)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm42)
CREATE (callingvm42)-[:CALL_TO]->(called42)
CREATE (connect43:Connect {id: 1449182480})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect43)
CREATE (called43:CallTo {orig: "8001212555", fwd: "80000"})
CREATE (callingvm43:VoiceCall {id: "7605551212", duration: 530})
CREATE (connect43)-[:VOICEMAIL {call: "Outgoing", Abandon: false}]->(callingvm43)
CREATE (callingvm43)-[:CALL_TO]->(called43)
CREATE (connect44:Connect {id: 1449183491})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect44)
CREATE (called44:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm44:IncomingVM {id: "4041212555", duration: 81})
CREATE (connect44)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm44)
CREATE (callingvm44)-[:CALL_TO]->(called44)
CREATE (connect45:Connect {id: 1449186481})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect45)
CREATE (called45:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm45:IncomingVM {id: "7701212555", duration: 66})
CREATE (connect45)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm45)
CREATE (callingvm45)-[:CALL_TO]->(called45)
CREATE (connect46:Connect {id: 1449189294})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect46)
CREATE (called46:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm46:IncomingVM {id: "7701212555", duration: 62})
CREATE (connect46)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm46)
CREATE (callingvm46)-[:CALL_TO]->(called46)
CREATE (connect47:Connect {id: 1449192673})
CREATE (calldate)-[:CONNECT_TIME {date: 20151203}]->(connect47)
CREATE (called47:CallTo {orig: "4041212555", fwd: "80000"})
CREATE (callingvm47:IncomingVM {id: "9191212555", duration: 95})
CREATE (connect47)-[:VOICEMAIL {call: "Incoming", Abandon: false}]->(callingvm47)
CREATE (callingvm47)-[:CALL_TO]->(called47)
;
Get all incoming calls recieved by each employee:
MATCH path=(c:Company)-[:CALL_DATE]->(n0:CallDate)-[:CONNECT_TIME]->(n:Connect)-[r:INCOMING]->(n1)-[]->(n2)
RETURN path;
Gel all unasnwered calls by each employee.
MATCH path=(c:Company)-[:CALL_DATE]->(n0:CallDate)-[:CONNECT_TIME]->(n:Connect)-[r:VOICEMAIL]->(n1)-[]->(n2)
RETURN path;
Total incoming, unanswered, and outgoing calls:
MATCH (n:Connect)
WITH n,
size((n)-[:INCOMING]->()) as Incoming,
size((n)-[:OUTGOING]->()) as Outgoing,
size((n)-[:VOICEMAIL]->()) as Voicemail
RETURN count(*) as Total, sum(Incoming) as Incoming,
sum(Outgoing) as Outgoing, sum(Voicemail) as Voicemail;
Total incoming, voicemail, and outgoing calls by each employee. This way employees performance can be evaluated by managers.
//Total calls by phone number
MATCH (n:Connect)-[]->()-[:CALL_TO]->(n2:CallTo)
RETURN "Total" as Type,n2.orig as PhoneNbr, COUNT(*) as Count ORDER BY Count DESC
UNION
MATCH (n:Connect)-[r]->(n1)-[:CALL_TO]->(n2:CallTo)
RETURN type(r) as Type, n2.orig as PhoneNbr, COUNT(*) as Count ORDER BY Count DESC;
10 out of 11 calls to 4041212555 went to voicemail, a signal for poor performance by the employee?.
In a multi-site organization, there will be a central location which receives the call detail records from every site. The data from each site will have a field to store a code that identifies the site.
For a single site organization, CDR contains one record for each call time. For multi-site organization there can be multiple records (one from each site) for one call time. The following query displays this multi-site scenario (with a bigger dataset).
MATCH (n:Connect)-[r:VOICEMAIL {call: 'Incoming'}]-(n1:VoiceCall)
WITH DISTINCT n
MATCH (n1:Connect)-[r1:VOICEMAIL {call: 'Outgoing'}]-(n2:VoiceCall)
WHERE n1.id = n.id
WITH DISTINCT n1
MATCH path = (x:Company)-[:CALL_DATE]->(n0:CallDate)-[:CONNECT_TIME]->(n2:Connect)-[r2:INCOMING|VOICEMAIL|OUTGOING]->(n11)-[:CALL_TO]->(n21)
WHERE n2.id = n1.id
RETURN path;
Call detail records provide wealth of information on telecommunications activity for the entire organization. Modeling the data with Neo4j provided orders of magnitude improvement (performance) in generating statistics of all call types compared to SQL server. In this gist, I covered only a few metrics. There are many other metrics like Conference Calls, Toll-free Calls, Returned Voicemail messages, etc., that I did not cover here.
Created by {Kamal Murthy}
Email: ameyasoft@gmail.com
Kya es tool se kisi ki bhi call details dekh sakte hai. Obhi kitne dino ki