Scripts to Import Zendesk into Neo4j

0_indexes.cypher

create index on :ZendeskOrg(id);
create index on :ZendeskUser(id);
create index on :ZendeskTicket(id);
create index on :ZendeskTicket(initial_severity);
create index on :Hour(hour);
create index on :Quarter(quarter);

1_import_orgs.cypher

:param url: "https://neotechnology.zendesk.com/api/v2/"
:param zd_identifier: "Basic ZGFuYSTlnVlVDNnQ="

MERGE (import:Import {id: 1})
SET import.page = $url + 'organizations.json?page=1', import.token = $zd_identifier;

CALL apoc.periodic.COMMIT('
 match (import:Import {id:1})
 CALL apoc.load.jsonParams(import.page,{Authorization: import.token},null)
  YIELD value as orgs
  WITH orgs, orgs.next_page as next_page
  UNWIND orgs.organizations as oneorg
  UNWIND oneorg.organization_fields as orgf
  MERGE (n:ZendeskOrg {id:oneorg.id})
   SET n.name=oneorg.name,
       n.url=oneorg.url,
       n.created=oneorg.created_at,
       n.domain_name=oneorg.domain_names
 WITH next_page,
 CASE WHEN next_page is null then 0 ELSE 1 END AS count
 FOREACH(_ in CASE WHEN count = 0 THEN [] ELSE [1] END |
     MERGE (import:Import {id:1})
        SET import.page = next_page
 )
 RETURN count', null);

2_import_users.cypher

MERGE (import:Import {id: 2})
SET import.page = $url + 'users.json?page=1', import.token = $zd_identifier;

CALL apoc.periodic.COMMIT('
MATCH (import:Import {id:2})
CALL apoc.load.jsonParams(import.page,{Authorization: import.token},null)
YIELD value AS users
WITH users,users.next_page as next_page
UNWIND users.users as oneuser
MERGE (n:ZendeskUser {id:oneuser.id})
 SET n.name=oneuser.name,
     n.email=oneuser.email,
     n.url=oneuser.url,
     n.last_login=oneuser.last_login_at,
     n.organization_id=oneuser.organization_id,
     n.suspended=oneuser.suspended,
     n.created=oneuser.date_created

     with next_page,
 CASE WHEN next_page is null then 0 ELSE 1 END AS count
 FOREACH(_ in CASE WHEN count = 0 THEN [] ELSE [1] END |
     MERGE (import:Import {id:2})
     SET import.page = next_page
 )
 RETURN count ', null);

3_import_tickets.cypher

MERGE (import:Import {id: 3})
SET import.page = $url +
'/incremental/tickets.json?start_time=1293840000&include=metric_sets',
    import.token = $zd_identifier;

CALL apoc.periodic.COMMIT('
  MATCH (import:Import {id:3})
  // so as to not exceed ZD throttle mechanism
  CALL apoc.util.sleep(10000)
  CALL apoc.load.jsonParams(import.page,{Authorization: import.token},null)
  YIELD value as tickets
  WITH tickets, tickets.next_page as next_page, tickets.count as APICount
   unwind tickets.tickets as oneticket
   MERGE (n:ZendeskTicket {id:oneticket.id})

   SET n.user_id=oneticket.requester_id,
        n.org_id=oneticket.organization_id,
        n.url=oneticket.url,
        n.ticket_id=oneticket.id,
        n.status=oneticket.status,
        n.org_id=oneticket.organization_id,
        n.ticket_subject=oneticket.subject,
        n.ticket_description=oneticket.description,
        n.date_created=oneticket.created_at,
        n.date_updated=oneticket.updated_at,
        n.assigned_id=oneticket.assignee_id,
        n.priority=oneticket.priority

   WITH n,oneticket, next_page, APICount
   UNWIND oneticket.fields as oneticketfields

   // take oneticketfields as a json value similar to
   // fields:[{id:24134566,value:severity_2},{id:80611248, ... ... ....
   // unwind it so it becomes
   //  {id: 24134566, value: severity_2}
   //  {id: 80611248, value: ... .... ....
   //
   // ticket severity          id=24134566
   SET n.severity = CASE WHEN oneticketfields.id=24134566 THEN oneticketfields.value ELSE null END
   // ticket cause             id=24134536
   SET n.cause = CASE WHEN oneticketfields.id=24134536 THEN oneticketfields.value ELSE null END
   // ticket category          id=24087433
   SET n.category = CASE WHEN oneticketfields.id=24087433 THEN oneticketfields.value ELSE null END
   // ticket version           id=20781041
   SET n.version = CASE WHEN oneticketfields.id=20781041 THEN oneticketfields.value ELSE null END
   WITH n,oneticket, next_page, APICount

   UNWIND oneticket.metric_set as metrics

   SET n.reopens=metrics.reopens,
       n.date_solved=metrics.solved_at,
       n.assignee_stations=metrics.assignee_stations

   WITH n, next_page, APICount,
        metrics.first_resolution_time_in_minutes as first_res_time,
        metrics.requester_wait_time_in_minutes as req_wait_time,
        metrics.full_resolution_time_in_minutes as full_res_time,
        metrics.reply_time_in_minutes as reply_time
   // need to divide by ##.0 so as to force float division thus 59/60 will return 0.9833 and not 0
   SET n.first_resolution_time_in_business_days=
         first_res_time.business/1440.0
   SET n.first_resolution_time_in_calendar_days=
         first_res_time.calendar/1440.0

   SET n.requester_wait_time_in_business_hours=
         req_wait_time.business/60.0
   SET n.requester_wait_time_in_calendar_hours=
         req_wait_time.calendar/60.0

   SET n.full_resolution_time_in_business_days=
         full_res_time.business/1440.0
   SET n.full_resolution_time_in_calendar_days=
         full_res_time.calendar/1440.0

   SET n.reply_time_in_business_hours=reply_time.business/60.0
   SET n.reply_time_in_calendar_hours=reply_time.calendar/60.0

 WITH next_page, APICount,
 CASE WHEN (APICount<1000) then 0 ELSE 1 END AS count
 WITH count, next_page, APICount
 MERGE (import:Import {id:3}) SET import.page = next_page, import.APICount=APICount
 RETURN count ', null);

4_import_helpcenter_kb.cypher

MERGE (import:Import {id: 4})
SET import.page=$url + '/help_center/sections.json?page=1', import.token = $zd_identifier;

MATCH (import:Import {id:4})

CALL apoc.load.jsonParams(import.page,{Authorization: import.token},null)
YIELD value AS sections

WITH import, sections, sections.next_page AS next_page

UNWIND sections.sections AS section_item
// create the Section Node
MERGE (n:ZendeskSection {id:section_item.id})
ON CREATE SET n += {name:section_item.name, date_created:section_item.created_at,
                    date_updated:section_item.updated_at,url:section_item.html_url}
WITH import, section_item,
     $url + '/help_center/sections/'+section_item.id+'/subscriptions.json?per_page=200' AS url2

// foreach section then find the subscribers
CALL apoc.load.jsonParams(url2,{Authorization: import.token},null)
YIELD value AS subscribervalue
WITH subscribervalue, section_item
UNWIND subscribervalue.subscriptions AS subscription_item
// create the relationship from the User to the Secction through the :Follows relationship
MATCH (s:ZendeskSection {id:section_item.id}) WITH s,subscription_item
MATCH (n:ZendeskUser {id: subscription_item.user_id})
MERGE (n)-[f:Follows]->(s)
SET f.subscribed_on=subscription_item.created_at;

5_add_relationships.cypher

// connect user to org
MATCH (u:ZendeskUser) WITH u, u.organization_id AS uorg
MATCH (o:ZendeskOrg) WHERE o.id=uorg
MERGE (u)-[:IS_MEMBER_OF_ORG]->(o);
// connect ticket to user and org
MATCH (t:ZendeskTicket)
WITH t, t.user_id AS tsubmitter, t.assigned_id AS towner
MATCH (u:ZendeskUser) WHERE u.id=towner
MATCH (u2:ZendeskUser)-[:IS_MEMBER_OF_ORG]->(org:ZendeskOrg) WHERE u2.id=tsubmitter
MERGE (t)-[:IS_ASSIGNED_TO]->(u)
MERGE (t)<-[:CREATED_ZENDESK_TICKET]-(u2)
MERGE (t)-[:ZENDESK_TICKET_ORG]->(org);
// connect to open quarter
MATCH (t:ZendeskTicket)
WITH t, substring(t.date_created,0,4) AS zdyear, toInteger(substring(t.date_created,5,2)) AS zdmonth
WITH t, toString(((zdmonth-1)/3)+1) AS zdquarter, zdyear
WITH t, 'q'+zdquarter+zdyear AS total, 
 zdyear+'q'+zdquarter AS total2
MERGE (zdq:Quarter {quarter:total2})
MERGE (t)-[:ZENDESK_TICKET_OPENED_QUARTER]->(zdq)
RETURN COUNT(*);

// connect to closed quarter
MATCH (t:ZendeskTicket)
  WHERE t.status='closed'
WITH t, substring(t.date_solved,0,4) AS zdyear, toInteger(substring(t.date_solved,5,2)) AS zdmonth
WITH t, toString(((zdmonth - 1)/3)+1) AS zdquarter, zdyear
WITH t, 'q'+zdquarter+zdyear AS total, zdyear+'q'+zdquarter AS total2
MERGE (zdq:Quarter {quarter:total2})
MERGE (t)-[:ZENDESK_TICKET_CLOSED_QUARTER]->(zdq)
RETURN COUNT(*);

// connect to "open" hour nodes
MATCH (t:ZendeskTicket)
WITH t, time(datetime(t.date_created)).hour AS total2
MERGE (zdq:Hour {hour:total2})
MERGE (t)-[:ZENDESK_TICKET_HOUR]->(zdq)
RETURN COUNT(*);

6_zd_queries.cypher

// inactive users
MATCH (u:ZendeskUser)-[:IS_MEMBER_OF_ORG]->(o:ZendeskOrg)
  WHERE NOT u.suspended
    AND (NOT exists(u.last_login)
     OR duration.inDays(datetime(u.last_login),datetime()).days>180)

RETURN u.name, u.email,  o.name, u.last_login, u.suspended,
       duration.inDays(datetime(u.last_login),datetime()).days;


// who follows which section
MATCH (s:ZendeskSection)<-[:Follows]-(u:ZendeskUser)
RETURN s.name,u.name;

// support member tickets
MATCH (t1:ZendeskTicket)
WHERE date(datetime(t1.date_created)).year=2018
WITH count(t1) AS ttotal

MATCH (t2:ZendeskTicket)-[:IS_ASSIGNED_TO]-(u:ZendeskUser)
WHERE date(datetime(t2.date_created)).year=2018
WITH  ttotal,u.name AS supportmember, count(t) AS utotal

RETURN supportmember, utotal,
       (utotal/toFloat(ttotal))*100 AS PercentClosed
ORDER BY utotal DESC;


// quarterly reporting per region
MATCH (zdq:Quarter)
WITH zdq ORDER BY zdq.quarter DESC SKIP 1 LIMIT 5

MATCH (h:Hour)<-[:ZENDESK_TICKET_HOUR]-(ticket:ZendeskTicket)-[:ZENDESK_TICKET_OPENED_QUARTER]->(zdq)
WITH *, CASE WHEN h.hour IN range(0,6) THEN 'APAC'
        WHEN h.hour IN range(7,14) THEN 'EMEA' ELSE 'AMER' END AS region

WITH region, zdq.quarter AS quarter, count(ticket) AS Total_Opened_Tickets
ORDER BY quarter ASC, region ASC

WITH region, collect(quarter) AS quarters, collect(Total_Opened_Tickets) AS tix
RETURN region, tix[0] AS q1, tix[1] AS q2, tix[2] AS q3, tix[3] AS q4, tix[4] AS q5;