Zeppelin and Spark: Ad Hoc Twitter Feedback Analysis

Introduction

A convenient screen in Zeppelin for performing ad-hoc analysis on twitter data for entity (brand or show) mentions. Paragraphs:

1. Load data in parquet and view schema
2. View breakdown by language
3. View tweet distribution by twitter handle
4. View tweet distribution by search keywords

Ad-Hoc Analysis in Zeppelin

Loading the data

%pyspark

import locale
locale.setlocale(locale.LC_ALL, 'en_US')

path="/data/output/parquet/tweets"

df_fb = sqlContext.read.parquet(path)
df_fb.registerTempTable("df_fb")

df_fb.limit(1).show()

Output:

+----------+--------------------+--------+-------------+-------------+------------------+--------------------+----+-------------+--------------+
|    userid|         posted_time|category|       parent|       entity|           tweetid|             content|lang|search_object|       thandle|
+----------+--------------------+--------+-------------+-------------+------------------+--------------------+----+-------------+--------------+
|2217980506|2014-01-27 22:27:...|    show|grammy awards|grammy awards|427930900505329665|@DionneJames are ...|  en|      grammys|louisemorse123|
+----------+--------------------+--------+-------------+-------------+------------------+--------------------+----+-------------+--------------+

Breakdown by Language

This paragraph gives a breakdown by language. Zeppelin provides a useful pie chart to quickly get a sense of the distribution between the languages involved.

%sql

select
    lang, count(lang)
from
    df_fb
where
    entity=lower('${entity}')
group by
    lang

Output:

Distribution by Twitter Handle

This is a useful paragraph to get a sense of distribution across twitter handles. In use cases where individual buzz is desirable, spam and/or official and/or fan accounts can be spotted quickly.

%sql

select
    thandle, count(thandle) as total
from
    df_fb
where
    entity=lower('${entity}')
and
    lang=lower('${lang}')
group by
    thandle
order by
    total desc

Output:

This paragraph can be further refined by adding this additional WHERE clause:

and
    lower(thandle) like lower('%${entity}%')

This will output the distribution across all twitter handles that are similar to the entity name:

 It's not unlikely that a hard-core fan of a show or brand might take that name and make it part of their own username.  Having said that, if the use case requires differentiation between individual buzz and corporate/spam buzz, this becomes an easy way of identifying the fat tail (e.g. the top 10).

Distribution by Search Objects

The search patterns used to find the tweets can be analyzed with a similar distribution. If a given search object is wrong or ambiguous (like using "friends" to find instances of the sitcom Friends), this distribution can show the impact. It's worth quickly investigating the fat tail to look for evidences of ambiguity or anything that might have been used incorrectly.

%sql

select
    search_object, count(search_object) as total
from
    df_fb
where
    entity=lower('${entity}')
and
    lang=lower('${lang}')
group by
    search_object
order by
    total desc

Output: