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

Setup

Create a Facebook Application

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

Deploy the template

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 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 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 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 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 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 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;


Creating Connections 

Verify the webhook in Facebook

Using  

Notes: 

What is likely to go wrong

Typical Deliverables Plan

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

  1. KW user to deploy Facebook Event Listener graph template and required environments
    1. Deliver webhook listener URLs to the client
  2. 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
  3. KW User to deploy the graph
  4. 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
  5. 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