Facebook Page Listener Custom Graph Template

Implement real time Facebook Page tracking with Kitewheel. This allows your brand to link up customer data to power automated real time engagement with customers on Facebook via your Page's feed or Facebook Messenger. Create a messenger bot that integrates NLP with with the central hub bringing context conversation context enriching real time automated responses.

See here for Facebook Messenger API and here for the Facebook Pages API.

Kitewheel listens as a Webhook subscribed to events for your Facebook page(s) and uses the Facebook graphAPI via the webservice adaptor to fetch user details and send messages.

Package contents

Graphs:
- Facebook Event Listener (API Listener - webhook)
  - Process FB Entries
    - Process FB Messages
    - Process FB Changes
- Application Parameters Graph Template
  - Used to store Facebook Page IDs
Nodes:
- Web Services
  - getFacebookProfileMetadata - fetch first name, surname, id type (e.g. user, page, messengerpagescopedid) by Facebook ID
  - sendFBMessage - send a message: raw text or template (buttons, images, attachments)
Schema: transaction:{}, interaction:{}, profile:{}

Setup

Create a Facebook Application

Note: You will need rights to access the Facebook Page as an administrator

Create a Facebook application https://developers.facebook.com/apps/ (or use existing one)
Add the Messenger and Webhook Products to your app
- You need to add BOTH products in order to receive a page feed
Link to a Facebook Page you want to track - recommended guide: Facebook Application Quick Start
Get the access token for the page - copy this to notepad
Don't fill out the webhook section yet

Deploy the template

Create the Facebook Event Listener template
Create the following tables

Create appParams

CREATE TABLE appParams (
  paramKey varchar(100) NOT NULL,
  paramValue text,
  paramType varchar(45) DEFAULT NULL,
  description varchar(200) DEFAULT NULL,
  PRIMARY KEY (paramKey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create chan2Profile

CREATE TABLE chan2Profile (
  channelId varchar(100) NOT NULL COMMENT 'The cookie, twitter handle etc',
  cidType varchar(20) NOT NULL COMMENT 'The channel ID type "web", "twitter" etc ',
  pId varchar(60) NOT NULL COMMENT 'The unqiue profile id - foreign key to kwProfile',
  PRIMARY KEY (channelId,cidType,pId) COMMENT 'Composite key',
  UNIQUE KEY kwChan2Profile_UNIQUE (channelId,cidType,pId),
  KEY pidIndex (pId),
  KEY channelIndex (channelId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create chanFacebookFeed

CREATE TABLE chanFacebookFeed (
  kwInteractionId int(11) NOT NULL,
  field varchar(50) DEFAULT NULL,
  valueRaw text,
  edited_time datetime DEFAULT NULL,
  is_hidden tinyint(4) DEFAULT NULL,
  link varchar(2000) DEFAULT NULL,
  message varchar(2000) DEFAULT NULL,
  photo varchar(2000) DEFAULT NULL,
  photo_ids varchar(2000) DEFAULT NULL,
  photos varchar(2000) DEFAULT NULL,
  post_id varchar(45) DEFAULT NULL,
  sender_name varchar(100) DEFAULT NULL,
  comment_id varchar(200) DEFAULT NULL,
  created_time datetime(3) DEFAULT NULL,
  event_id varchar(100) DEFAULT NULL,
  item varchar(100) DEFAULT NULL,
  parent_id varchar(100) DEFAULT NULL,
  photo_id varchar(100) DEFAULT NULL,
  reaction_type varchar(100) DEFAULT NULL,
  published varchar(45) DEFAULT NULL,
  inReplyToUserId varchar(45) DEFAULT NULL,
  sender_id varchar(45) DEFAULT NULL,
  share_id varchar(45) DEFAULT NULL,
  verb varchar(45) DEFAULT NULL,
  video_id varchar(45) DEFAULT NULL,
  PRIMARY KEY (kwInteractionId),
  UNIQUE KEY kwInteractionId_UNIQUE (kwInteractionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create chanFacebookMessenger

CREATE TABLE chanFacebookMessenger (
  kwInteractionId int(11) NOT NULL,
  epochTs datetime DEFAULT NULL,
  senderId bigint(20) DEFAULT NULL,
  recipientId bigint(20) DEFAULT NULL,
  text varchar(45) DEFAULT NULL,
  mid varchar(45) DEFAULT NULL,
  seq int(11) DEFAULT NULL,
  attachments text,
  timestamp datetime(3) DEFAULT NULL,
  deliveredFlag smallint(6) DEFAULT NULL,
  deliveredTs datetime(3) DEFAULT NULL,
  readFlag smallint(6) DEFAULT NULL,
  readTs datetime(3) DEFAULT NULL,
  PRIMARY KEY (kwInteractionId),
  UNIQUE KEY id_UNIQUE (kwInteractionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create interaction

CREATE TABLE interaction (
  kwInteractionId int(11) NOT NULL AUTO_INCREMENT,
  pId varchar(60) NOT NULL,
  channel varchar(30) DEFAULT NULL,
  interactionType varchar(20) DEFAULT NULL,
  ts datetime DEFAULT CURRENT_TIMESTAMP,
  direction varchar(45) DEFAULT NULL,
  PRIMARY KEY (kwInteractionId),
  KEY idx_pid (pId)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Create profile

CREATE TABLE profile (
  pId varchar(60) NOT NULL,
  statusCode varchar(10) DEFAULT NULL,
  firstName varchar(45) DEFAULT NULL,
  lastName varchar(145) DEFAULT NULL,
  country varchar(145) DEFAULT NULL,
  city varchar(145) DEFAULT NULL,
  industry varchar(45) DEFAULT NULL,
  loyaltyPoints int(11) DEFAULT '0',
  favoriteProduct varchar(45) DEFAULT NULL,
  productsInterestedIn varchar(145) DEFAULT NULL,
  lastUpdated timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (pId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create profileFacebook

CREATE TABLE profileFacebook (
  pId varchar(60) NOT NULL,
  firstName varchar(100) DEFAULT NULL,
  surname varchar(100) DEFAULT NULL,
  lastInteractionTs datetime(3) DEFAULT NULL,
  lastInboundMsgTs datetime(3) DEFAULT NULL,
  lastOutboundMsgTs datetime(3) DEFAULT NULL,
  metaRaw text,
  PRIMARY KEY (pId),
  KEY pId (pId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert into appParams:
- The fbPageAccessToken - you copied it above
- Update this URL to get your page name and page id https://graph.facebook.com/v2.10/me?access_token={your_fbPageAccessToken_here}

Creating Connections

Set the database connection
Set the facebook API connection
Run the graph in listen mode with 10 iterations

Verify the webhook in Facebook

Create the webhook
This makes a call to your process to validate with a hub.challenge value that it expects to be echoed in plain text with a 200 response.
- Tip: if using the European hub, ensure your Callback URL contains api-eu.
- The Verify Token can be any value.
- Once validated, you can stop the graph
Set the subscription to Page
- Subscribe to Feed and Messages
- Once validated, you can stop the graph
On the Messenger product, subscribe to the following events
If you have a wit.ai account (free and recommended) then add this to the messenger product for real time Natural Language Processing
Complete the Facebook Messenger Bot Approval steps - essentially you just need to set up 2 standard responses and FB app testers will confirm your app works.

Using

Run the listener and test by posting on your brand page (you can use the privacy settings to restrict who can see the post)

Notes:

What is likely to go wrong

Verify Webhook fails
- Ensure the graph is running when you click verify - the graph must echo back the hub.challenge value as plain text
I receive duplicate messages
- Receiving the same message twice is common as FB tries to ensure all messages are received by your webhook. However if >2 duplicates are being received then there might be a problem with your graph completing resulting in an HTTP 404 to Facebook who will then try send the message again. Check for unhandled errors stopping the transaction bubbling up to the top process and returning the response object.
- This is common. The graph handles duplicates

Typical Deliverables Plan

Use this plan to agree the requirements from the Kitewheel User and your client/ brand

Deliverables Plan

KW user to deploy Facebook Event Listener graph template and required environments
1. Deliver webhook listener URLs to the client
Create SQL Assets:
1. If using your own database
  1. KW user to create SQL assets
2. If using the client's database
  1. Provide the SQL creation code above for the brand to create
  2. KW will need access to the database - see here for DB connections
KW User to deploy the graph
A Facebook user with a Developer account (free) that has access to the brand's FB page to set up the Facebook Application
1. Use the provided webhook listener URLs to set up the webhooks for the brand's FB page - liaise with the KW user to ensure the Kitewheel graphs are deployed
2. If desired, set up a wit.ai account (free) to integrate NLP
If setting up a messenger bot, Client to spec out an example story board for the KW user to build. This is used by official FB engineers when the messenger bot is submitted for approval to go live