Using apoc.load.jsonParams to load data from Zendesk into Neo4j to learn about article subscribers


(Dana Canzano) #1

The following document describes how to utilize the Zendesk API
to load data from Zendesk into Neo4j, specifically data about users who have choosen to subscribe/follow Knowledge
Base section(s). This document attempts to solve the issue described by the following questions from Zendesk[
Question & Answers]. Although the Zendesk UI allows users to subscribe/follow a Knowledge Section it does not provide an equivalent
UI for a Zendesk Administrator to see what users are subscribed to each section.

The Cypher below, will create supporting indexes and then iterate overr every section, request all the user_ids with said section,
create the relationship between the user_id and section and then populate the user_id with more identifying details.

create index on :Section(id);
create index on :User(id);
create index on :Organization(id);

<!-- // get all sections -->
CALL apoc.load.jsonParams("",{Authorization:"Basic base64Encoded_username:password"},null)
  yield value as sectionvalue
  with sectionvalue
  unwind sectionvalue.sections as section_item
    Merge (n:Section {,, created_at:section_item.created_at, updated_at:section_item.updated_at,url:section_item.html_url})
    with as secid
    // foreach section then find the subscribers
    CALL apoc.load.jsonParams(""+secid+"/subscriptions.json?per_page=200",{Authorization:"Basic base64Encoded_username:password"},null)
       yield value as subscribervalue
       with subscribervalue, secid
       unwind subscribervalue.subscriptions as subscription_item
            // create the relationship from the User to the Secction through the :Follows relationship
            match (s:Section {id:secid}) with s,subscription_item
            merge (n:User {id: subscription_item.user_id})
            merge (n)-[:Follows {subscribed_on: subscription_item.created_at}]->(s)
            with subscription_item.user_id as s_userid
            CALL apoc.load.jsonParams(""+s_userid+".json",{Authorization:"Basic base64Encoded_username:password"},null)
                 yield value as userRecord
               with userRecord, s_userid
               unwind userRecord.user as uid
               match (n:User {id:s_userid})
match (n:User) where exists(n.organization_id)
      with n,n.organization_id as organization_id
      CALL apoc.load.jsonParams(""+organization_id+".json",{Authorization:"Basic base64Encoded_username:password"},null)
      yield value as orgRecord
      unwind orgRecord.organization as orgid
      with n,orgid
      merge (o:Organization {id:, name:, created_at: orgid.created_at})
      merge (n)-[:IS_MEMBER_OF_ORG]->(o);                     

and to load 140 nodes (81 Users, 7 Sections, 52 Organizations) and associated relationships took 54 seconds.

In the above Cypher code, you will need to replace all occurances of

  `your_domain`  with the actual domain your Zendesk is hosted under
  `base64Encoded_username:password` with the base64 encoding ( of a Zendesk
   Admin user and password who has Admin rights in Zendesk

Additionally, to use basic authentication, you must enable
password access in the Zendesk Support
admin interface at Admin > Channels > API.

Finally, per the Zendesk API, if you expect to have more
than 100 results per API call you will need to consider Pagination.

By default, most list endpoints return a maximum of 100 records per page. You can change the number
of records on a per-request basis by passing a per_page parameter in the request URL parameters. 
Example: per_page=50. However, you can't exceed 100 records per page on most endpoints.

When the response exceeds the per-page maximum, you can paginate through the records by
incrementing the page parameter. 
Example: page=3. List results include next_page and previous_page URLs in the response
body for easier navigation:

Copying the above Cypher into a shell script file, for example build_zd.cql, will then allow for it to be run through
cypher-shell by running

cat build_zd.cql | bin/cypher-shell

And the resultant graph model is thus defined as

The entire graph thus appears as

To which we will see there are 4 Sections (i.e. green nodes/circles) which have no subscribers (i.e the 4 green nodes
in the upper left corner).
Three other sections have subscribers, though the Section on the right has the most subscribers (i.e blue nodes/circles)
Additionally some subscribers/users have choosen to follow multiple sections.

Each Node is defined with the following properties

*Section:*  name
*Organization:*  name

Usefull Cypher statements to query the graph

  1. Find # of users subscribed by Section
match     (n:Section) 
           size (  (n)<-[:Follows]-() ) as subscribers
order by   subscribers desc;
  1. Find users and associated organization, per section and when the user subscribed subscribed
match (s:Section)<-[r:Follows]-(u:User)-[:IS_MEMBER_OF_ORG]->(o:Organization)
	    r.subscribed_on as DateWhenSubscribed
order by,