Onclusive Social Media Counts Sample

Onclusive is the data science company for marketing and communications. We provide Media Monitoring services underpinned by our proprietary, global news crawler, which captures over 6 million pieces of digital content every day. We provide the most comprehensive media coverage available, including digital, newswire, print, broadcast and radio coverage. Onclusive PR Analytics provide brands with visibility into earned media performance. We invented PR Attribution (TM) , enabling brands to understand earned media ROI.

The Onclusive Social Media Counts Sample dataset consists of article urls published in a given time period (2019/10/1 - 2019/10/5) and the number of Facebook likes each article received.

Files are JSONL and gzipped, Athena-compatible

field	type	description	example
url	string	canonical url of article	"https://foo.com/news/2019/10/some-article"
fb_likes	int	number of Facebook likes	479
date	string	date of article publication	"2019-10-01"

Steps to access social counts data via Amazon Athena:

1. Download the data set to your S3 bucket.
2. Set all required IAM permissions for Athena.
3. You can run following DDL to create Athena Table.

CREATE EXTERNAL TABLE <table_name> ( url string, facebook_likes int, date string ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION '<Your S3 Bucket>';

4. Sample Queries:

SELECT date, count(*) FROM <table_name> GROUP BY date LIMIT 10; SELECT * FROM <table_name> LIMIT 10; -- Top 10 URL with most facebook likes -- SELECT url, sum(facebook_likes) AS facebook_likes FROM <table_name> WHERE date >= 'from_date' AND date < 'to_date' GROUP BY url ORDER BY facebook_likes DESC LIMIT 10;