Twitter Listener Graph Template

This template provides an out of the box graph for tracking tweets by keyword combined with customer profile building. With LUDA (Listen, Understand, Decide, Act) in mind, this business logic focuses on the Listen and Understand steps. Kitewheel beginners should be able to set up this graph within 1 hour running in a production environment tracking tweets and building up customer profiles.

Please see our Kitewheel Twitter Listener article for details on setting up the Twitter Listener connection.

Interaction Schema

{
	"interaction": {
		"ts": "2017-01-01 00:00:00.000",
		"chanId": "1234-asdf-1234-asdf",
		"channel": "twitter",
		"direction": "inbound",
		"interactionId": "41234",
		"interactionType": "twitterTweet",
		"tweet": {
			"id": 839858557000515600,
			"geo": null,
			"lang": "en",
			"text": "RT @Thomas1774Paine: I have concerns about #Trump",
			"place": {...},
			"id_str": "839858557000515584",
			"source": "Twitter for iPhone",
			"entities": {...},
			"favorited": false,
			"retweeted": false,
			"truncated": false,
			"created_at": "Thu Mar 09 15:20:58 +0000 2017",
			"coordinates": null,
			"contributors": null,
			"filter_level": "low",
			"timestamp_ms": "1489072858463",
			"quoted_status": {...},
			"retweet_count": 0,
			"favorite_count": 0,
			"is_quote_status": true,
			"quoted_status_id": 839609744025350100,
			"retweeted_status": {...},
			"in_reply_to_user_id": null,
			"quoted_status_id_str": "839609744025350145",
			"in_reply_to_status_id": null,
			"in_reply_to_screen_name": null,
			"in_reply_to_user_id_str": null,
			"in_reply_to_status_id_str": null
		}
	}
}

Profile Schema

{
	"profile": {
		"channelIdentifiers": [{
			"chanId": "",
			"cidType": ""
		}],
		"profileWeb": {
			...
		},
		"profileCommon": {
			"pId": "",
			"email": "",
			"lastName": "",
			"firstName": "",
			"channelIds": ""
		},
		"profileTwitter": {
			"id": 275276082,
			"url": "http://TruePundit.com",
			"lang": "en",
			"name": "Thomas Paine",
			"id_str": "275276082",
			"location": "Chief Muckraker -- True Pundit",
			"verified": false,
			"following": null,
			"protected": false,
			"time_zone": "Eastern Time (US & Canada)",
			"created_at": "Thu Mar 31 23:54:17 +0000 2011",
			"utc_offset": -18000,
			"description": "*Gerald Loeb Award Recipient ...*2-Time Pulitzer Prize Investigative Reporting Nom, *George Polk Award Political Reporting Nom... @true_pundit",
			"geo_enabled": false,
			"screen_name": "Thomas1774Paine",
			"listed_count": 4,
			"friends_count": 70031,
			"is_translator": false,
			"notifications": null,
			"statuses_count": 11184,
			"numTweetsTracked":5,
			"default_profile": false,
			"followers_count": 73103,
			"favourites_count": 6217,
			"profile_image_url": "http://pbs.twimg.com/profile_images/839189391515975687/lAjVOY83_normal.jpg",
			"profile_banner_url": "https://pbs.twimg.com/profile_banners/275276082/1481130602",
			"profile_link_color": "B20000",
			"profile_text_color": "000000",
			"follow_request_sent": null,
			"contributors_enabled": false,
			"default_profile_image": false,
			"profile_background_tile": false,
			"profile_image_url_https": "https://pbs.twimg.com/profile_images/839189391515975687/lAjVOY83_normal.jpg",
			"profile_background_color": "000000",
			"profile_sidebar_fill_color": "000000",
			"profile_background_image_url": "http://abs.twimg.com/images/themes/theme1/bg.png",
			"profile_sidebar_border_color": "000000",
			"profile_use_background_image": false,
			"profile_background_image_url_https": "https://abs.twimg.com/images/themes/theme1/bg.png"
		}
		"profileFacebook": {
			...
		}
	}
}

Package contents

Graph:
- 'Listen Twitter (Keyword)' (Twitter Listener)
- Process Tweet
  - getProfile
  - getTwitterProfile
  - updateTwitterProfile
  - insertInteractionEvent
Schema Location:
- {Schema}/transaction
- {Schema}/profile
- {Schema}/interaction

Setup

Deploy the 'Listen Twitter (Keyword)' template to create all the required Kitewheel resources
Run the following SQL to create all the required SQL assets

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),
  UNIQUE KEY channelId_UNIQUE (channelId,cidType) COMMENT 'No duplicate keys for the same channel',
  KEY pidIndex (pId),
  KEY channelIndex (channelId)
) DEFAULT CHARSET=utf8;

Create chanTweet

CREATE TABLE chanTweet (
  kwInteractionId int(11) NOT NULL,
  tweet_id varchar(20) NOT NULL,
  user_screen_name varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  user_id_str varchar(30) DEFAULT NULL,
  tweet_text varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  created_at varchar(50) DEFAULT NULL,
  in_reply_to_screen_name varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  in_reply_to_status_id varchar(50) DEFAULT NULL,
  in_reply_to_user_id varchar(50) DEFAULT NULL,
  lang varchar(10) DEFAULT NULL,
  source_ varchar(150) DEFAULT NULL,
  filter_level varchar(30) DEFAULT NULL,
  media text,
  user_mentions text,
  urls text,
  hashtags text,
  geo text,
  place text,
  retweeted_id varchar(18) DEFAULT NULL,
  retweeted_created_at varchar(50) DEFAULT NULL,
  retweeted_source varchar(150) DEFAULT NULL,
  retweeted_geo text,
  retweeted_place text,
  retweeted_user_screen_name varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  retweeted_user_id_str varchar(30) DEFAULT NULL,
  retweeted_user_name varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  retweeted_user_description varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  retweeted_user_location varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  retweeted_user_created_at varchar(50) DEFAULT NULL,
  PRIMARY KEY (tweet_id)
) 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)
) 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)
) DEFAULT CHARSET=utf8;

Create profileTwitter

CREATE TABLE profileTwitter (
  pId varchar(60) NOT NULL,
  id_str varchar(30) NOT NULL,
  screen_name varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  name varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  description varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  lang varchar(10) DEFAULT NULL,
  location varchar(150) DEFAULT NULL,
  favourites_count int(11) DEFAULT NULL,
  followers_count int(11) DEFAULT NULL,
  friends_count int(11) DEFAULT NULL,
  statuses_count int(11) DEFAULT NULL,
  verified int(11) DEFAULT NULL,
  created_at varchar(50) DEFAULT NULL,
  geo_enabled varchar(20) DEFAULT NULL,
  url varchar(150) DEFAULT NULL,
  utc_offset varchar(30) DEFAULT NULL,
  time_zone varchar(50) DEFAULT NULL,
  listed_count int(11) DEFAULT NULL,
  numTweetsTracked int(11) DEFAULT '0',
  lastUpdated timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (pId)
) DEFAULT CHARSET=utf8;

Using

Create the required graphs from the 'Twitter Listener (Keyword)' graph template
1. Follow the steps here to set up your Twitter Connection
2. Open 'Listen Twitter (Keyword)' graph and view the listener properties
4. Set the Twitter Listener Mode to 'Keywords'
5. Fill in the desired Keywords to track
6. Save the listener
To make the database nodes valid either:
1. Complete the kwDB database connection - the template creates 'kwDB' for you
2. or delete the kwDB connection and update the database nodes to your own database connection
Deploy the Listen Twitter (Keyword) graph

Possible Issues

Things that are likely to go wrong:

No tweets appear - either your keyword criteria is too explicit or you have not set your Twitter connection properties correctly

Typical Deliverables Plan

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

Deliverables Plan

KW user to deploy Twitter Listener graph template and required environments
Client or KW User to set up the Twitter application and deliver the required twitter connection tokens
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
Data capture scope
1. If the client wishes more data to be tracked such as real time natural language processing, please contact your Kitewheel account manager
2. KW User to deliver if the client wishes more data to be derived by Kitewheel