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. Xponentbeginners 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 Xponent 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 Xponent 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  

1. 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.
   3. Set the Twitter Listener Mode to 'Keywords'.
   4. Fill in the desired Keywords to track.
   5. Save the listener.
2. To make the database nodes valid either:
   1. Complete the kwDB database connection - the template creates 'kwDB' for you.
      or
   2. Delete the kwDB connection and update the database nodes to your own database connection.
3. Deploy the Listen Twitter (Keyword) graph.

Possible Issues

If no tweets appear, it is possible that either your keyword criteria are too explicit or that your twitter connection properties have not been configured correctly.

Typical Deliverables Plan

Use this plan to agree on the requirements of the Xponent User and your client/brand.

Deliverables Plan

1. Xponent user to deploy Twitter Listener graph template and required environments.
2. The client or Xponent User to set up the Twitter application and deliver the required Twitter connection tokens.
3. Create SQL Assets:
   1. If using your own database:
      1. Xponent 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. Xponent will need access to the database - see here for DB connections.
4. Data capture scope
   1. If the client wishes more data to be tracked, such as real-time natural language processing, please contact your Xponent account manager.
   2. Xponent User to deliver if the client wishes more data to be derived by Xponent by.