Amnesty International only uses data towards the universal recognition of human rights.
The purpose of this project is to pull publicly available data from social media sites relevant to Amnesty International. This data will be used to;
- Track the success of campaigns
- Determine which issues are important to our supporters
- Responds quickly to changes in the social media sphere
These instructions are designed for Ubuntu 12.04 running Ruby 1.9.1 and SQL Server 2008
Create a user to run the Borg scripts.
$ sudo useradd --create-home --shell /bin/bash --user-group <username>
CREATE LOGIN <username> WITH PASSWORD = '<password>', DEFAULT_DATABASE = <dbname>
GO
USE <dbname>;
CREATE USER <username> FOR LOGIN <username>;
GO
EXEC sp_addrolemember db_datareader, <username>
GO
EXEC sp_addrolemember db_datawriter, <username>
GO
USE <dbname>;
CREATE TABLE fb_link_count
(
seqn INT IDENTITY PRIMARY KEY,
url VARCHAR(32),
share_count INT,
like_count INT,
comment_count INT,
created datetime default CURRENT_TIMESTAMP,
updated datetime default CURRENT_TIMESTAMP
)
USE <dbname>;
CREATE TABLE fb_page_post
(
post_id VARCHAR(50) PRIMARY KEY,
message VARCHAR(MAX),
photo BIT,
video BIT,
created_time datetime,
updated_time datetime,
permalink VARCHAR(128),
type INT,
parent_post_id VARCHAR(32),
actor_id VARCHAR(64)
)
CREATE TABLE fb_page_post_stat
(
seqn INT IDENTITY PRIMARY KEY,
post_id VARCHAR(50),
share_count INT,
likes_count INT,
comments_count INT,
created datetime default CURRENT_TIMESTAMP,
updated datetime default CURRENT_TIMESTAMP
)
USE <dbname>;
# Tables
CREATE TABLE Tweets
(
id BIGINT PRIMARY KEY NOT NULL,
usr_id INT NOT NULL,
coordinates GEOGRAPHY NULL,
text VARCHAR(160) NOT NULL,
created DATETIME NULL,
imported DATETIME DEFAULT CURRENT_TIMESTAMP
)
CREATE TABLE TwitterUsers
(
id INT PRIMARY KEY NOT NULL,
screen_name VARCHAR(32) NOT NULL,
name VARCHAR(32) NULL,
location VARCHAR(32) NULL,
protected BIT NULL,
verified BIT NULL,
followers_count INT NULL,
friends_count INT NULL,
statuses_count INT NULL,
time_zone VARCHAR(32) NULL,
utc_offset FLOAT NULL,
profile_image_url VARCHAR(128) NULL,
created_at DATETIME,
imported_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
CREATE TABLE TweetsAnatomize
(
tweet_id BIGINT NOT NULL,
term VARCHAR(32) NOT NULL,
CONSTRAINT pk_TweetsAnatomize PRIMARY KEY (tweet_id,term)
)
CREATE TABLE TweetUserMentions
(
tweet_id BIGINT NOT NULL,
usr_id INT NOT NULL,
CONSTRAINT pk_TwitterUserMentions PRIMARY KEY (tweet_id,usr_id)
)
CREATE TABLE TweetHashtags
(
tweet_id BIGINT NOT NULL,
hashtag VARCHAR(32) NOT NULL,
CONSTRAINT pk_TweetHashtags PRIMARY KEY (tweet_id,hashtag)
)
CREATE TABLE TweetUrls
(
tweet_id BIGINT NOT NULL,
url VARCHAR(256) NOT NULL,
CONSTRAINT pk_TweetUrls PRIMARY KEY (tweet_id,url)
)
CREATE TABLE TweetRegions
(
tweet_id BIGINT NOT NULL,
region VARCHAR(32) NOT NULL,
CONSTRAINT pk_TweetRegion PRIMARY KEY (tweet_id,region)
)
# Views
CREATE VIEW vAI_Tweets AS
SELECT
T.id,
TU.screen_name,
TU.name,
TR.region,
TU.location,
T.coordinates,
TU.profile_image_url,
T.text,
T.created,
T.imported,
TU.followers_count,
TU.friends_count,
TU.statuses_count 'tweet_count',
DATEDIFF(DAY, TU.created_at, GETDATE()) 'days_active',
CASE WHEN (DATEDIFF(DAY, TU.created_at, GETDATE())) = 0 THEN
TU.statuses_count
ELSE
(TU.statuses_count / (DATEDIFF(DAY, TU.created_at, GETDATE())))
END 'tweets_per_day'
FROM
Tweets AS T
INNER JOIN
TwitterUsers AS TU
ON T.usr_id = TU.id
INNER JOIN
TweetRegions AS TR
ON T.id = TR.tweet_id
CREATE VIEW vAI_CanadianTweets AS
SELECT *
FROM vAI_Tweets
WHERE
region IN ('GTA', 'London', 'Calgary', 'Montreal', 'Victoria', 'Ottawa', 'Winnipeg', 'Oshawa', 'Edmonton', 'St Catharines', 'Kitchener', 'Vancouver', 'Halifax', 'Quebec City')
USE <dbname>;
CREATE TABLE ENsupporters
(
supporter_id INT PRIMARY KEY NOT NULL,
imis_id INT NULL,
first_name VARCHAR(32) NULL,
last_name VARCHAR(32) NULL,
preferred_salutation VARCHAR(32) NULL,
title VARCHAR(8) NULL,
supporter_email VARCHAR(64) NULL,
address VARCHAR(32) NULL,
city VARCHAR(32) NULL,
postal_code VARCHAR(10) NULL,
province VARCHAR(16) NULL,
phone_number VARCHAR(16) NULL,
supporter_create_date DATE NULL,
supporter_modified_date DATE NULL,
imported DATETIME DEFAULT CURRENT_TIMESTAMP,
updated DATETIME DEFAULT CURRENT_TIMESTAMP
)
CREATE TABLE ENsupportersAttributes
(
seqn INT IDENTITY PRIMARY KEY,
supporter_id INT NOT NULL,
attribute VARCHAR(32) NOT NULL,
value VARCHAR(32) NOT NULL,
imported DATETIME default CURRENT_TIMESTAMP,
updated DATETIME default CURRENT_TIMESTAMP
)
CREATE TABLE ENsupportersActivities
(
seqn INT IDENTITY PRIMARY KEY,
supporter_id INT NOT NULL,
type VARCHAR(16) NOT NULL,
id VARCHAR(64) NULL,
datetime DATETIME,
status VARCHAR(16) NULL,
data1 VARCHAR(MAX) NULL,
data2 VARCHAR(MAX) NULL,
data3 VARCHAR(MAX) NULL,
data4 VARCHAR(MAX) NULL,
data5 VARCHAR(MAX) NULL,
data6 VARCHAR(MAX) NULL,
data7 VARCHAR(MAX) NULL,
data8 VARCHAR(MAX) NULL,
data9 VARCHAR(MAX) NULL,
data10 VARCHAR(MAX) NULL,
data11 VARCHAR(MAX) NULL,
data12 VARCHAR(MAX) NULL,
data13 VARCHAR(MAX) NULL,
data14 VARCHAR(MAX) NULL,
data15 VARCHAR(MAX) NULL,
data16 VARCHAR(MAX) NULL,
data17 VARCHAR(MAX) NULL,
data18 VARCHAR(MAX) NULL,
data19 VARCHAR(MAX) NULL,
data20 VARCHAR(MAX) NULL,
imported DATETIME default CURRENT_TIMESTAMP,
updated DATETIME default CURRENT_TIMESTAMP
)
USE <dbname>;
CREATE TABLE Articles
(
url VARCHAR(256) PRIMARY KEY NOT NULL,
title VARCHAR(128) NULL,
source VARCHAR(32) NULL,
type VARCHAR(8) NOT NULL,
description VARCHAR(MAX) NULL,
published DATETIME NULL,
imported DATETIME DEFAULT CURRENT_TIMESTAMP,
updated DATETIME DEFAULT CURRENT_TIMESTAMP
)
CREATE TABLE ArticlesAnatomize
(
url VARCHAR(128) NOT NULL,
term VARCHAR(32) NOT NULL,
count INT,
CONSTRAINT pk_ArticlesAnatomize PRIMARY KEY (url,term)
)
$ cd /path/to/dir
$ git clone https://github.com/AmnestyInternational/Borg.git
$ bundle install
$ sudo chown -R <username>:<groupname> Borg/
Update
- yaml/api_tokens.yml
- yaml/db_settings.yml
Add cronjobs for the social pull user
$ sudo su -- <username> -c 'crontab -e'
It is necessary to include the variable paths in the cron table.
GEM_HOME=/usr/local/lib/ruby/gems/1.9.1
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
# Social media api scripts
*/10 * * * * cd /srv/Borg; ruby fb_page_post_stats.rb;
*/10 * * * * cd /srv/Borg; ruby fb_link_count.rb;
45 * * * * cd /srv/Borg; ruby twitter.rb;
15 00 * * * cd /srv/Borg; ruby engaging_networks.rb;
55 * * * * cd /srv/Borg; ruby articles.rb;
Campaign Type Codes
| Type of Activity | Default code |
|---|---|
| email to target | ET |
| data capture | DC |
| tell-a-friend | TAF |
| broadcast email | B |
| jamii registration | J |
| jamii custom form | JF |
| question checkbox | Q |
| question multiple | QM |
| donation page | TAF |
| donation page – credit/debit card single payment | CREDIT/DEBIT_SINGLE |
| donation page – credit/debit card recurring payment | CREDIT/DEBIT_RECURRING |
| donation page – bank single payment | BANK_SINGLE |
| donation page – bank recurring payment | BANK_RECURRING |
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/.