Preparing to Measure Social Media from the Database (Part 1)

Why Add my Social Media Data to a Database?

There are plenty of good reasons to dump your Social Media data into a database.

1.  Additional functionality

Like all web apps Radian6’s data is constrained by the UI, and while the team works fervently to provide new features and functions, a lot of questions I receive benefit from the flexibility that a database provides. For example, one report I like to do measures the repeats of a headline, something which is currently unavailable and provides some very interesting insights. In terms of added features, Radian6 does not allow you to e-mail a specific person if a Blog post on say Engadget hits 40 comments or more related to your company.  Once you control the data, this is possible.

2. Speed and automation

A UI just can’t compete with queries for speed, and, in the case of repetitive tasks, simple PHP scripts can provide you with almost instantaneous reports that would otherwise have required hours of work.

3.  Integration

Once you have the data in your hands, you can begin to integrate it with your other data sources.  Web analytics data is the most obvious use case for me, but there are many others I’m sure you can think of.

4.  Broadening the data mining pool

In short, there are a lot of people who are absolutely brilliant data miners.  If your organization is of sufficient size, you probably have a business intelligence unit with just such experts.  By putting the data into a format they are comfortable with, you can harness their expertise and see what others can come up with.

5.  Accessibility

With a bit of work, you can build a frontend that can literally allow anyone, no matter how unversed with Social Media, get the answers they want.  I find this concept very powerful, though it is a topic unto itself.  In essence, if you do it right, someone should be able to go to your web front end, type in a word or two, and get everything they need about the topic.

Setting up the Database

The Process

This method is very manual, but it is the simplest and quickest way to begin adding and manipulating Radian6 data from a database. If you are interested in writing PHP scripts to automate the process, ping me and I will share the code/work with you to get it set up on your end.

What you will need

1.  A MySQL 5 database (I use features that to my knowledge only 5+ has)
2.  A basic understanding of how to create tables
3.  Limited knowledge of the command line
4.  A copy of my table schema– download that here

Nice to have

1. phpMyAdmin for setting up the tables/general administration

Database and table creation

It should be noted that I am no database expert, so the way I designed my database may be horrifically inefficient.  Due to the volume of content that our Radian6 setup draws in, I split the database into multiple tables for each profile, broken down by medium.

So lets create our example database using the downloaded demo.sql as our template.  From the command line, type the following, obviously adjusting for the path of demo.sql (or run this command from the directory that contains it).

mysql -h server -u login -ppassword < demo.sql

Now login to mysql

mysql -h server -u login -ppassword

Lets look at our new database

SHOW databases;

You should see the database demo.  Lets use it.

USE demo;


Now lets look at the available tables.

SHOW tables;

Finally, lets take a look at our table.

DESCRIBE demoblogs;

I use this schema for all my Radian6 stuff since their CSV export contains the same headers, with data populated depending on the medium. For instance, given that we’re going to be looking at blog data here, ForumThreadSize will obviously not be particularly relevant (but the CSV still contains the field).

If you want to use this schema and move it to another database, or create one for say, forums, use the CREATE table LIKE Command which follows a CREATE TABLE tablename LIKE database.table syntax.  Thus..

CREATE TABLE forums LIKE demo.demoblogs;

Would create an exact replica of the demoblogs table for you to play around with.

Still with me?  Good.  In the next chapter, we’ll download some Radian6 data and learn how to insert it into the database.

Tags: Database, Social Media, Web Analytics

This entry was posted on Tuesday, October 27th, 2009 at 6:38 pm and is filed under Social Media, Uncategorized, Web Analytics. You can follow any responses to this entry through the RSS 2.0 feed.