Using Datazen with Plus One to visualize your social media data

The idea for this post came from having spent the last year or so playing with a app called Plus One. Check out their site to learn much more about it, but in short, it’s an app you download onto your desktop, enter the keywords you want to search across different social networks (Twitter, Facebook, etc.), and get back the results. And in the FAQ, it states – “You are not limited to just the reports found within Plus One Social. Because we store the data in a friendly desktop database, Microsoft Access, you can connect your favorite self serve bi tool . . .”

Naturally, I wanted to see if Datazen would work with Plus One, which meant I had to do a few things I hadn’t done before to get started –

– Install the Plus One application on Windows Server and
– Use Microsoft Access as a data provider

Installing Plus One is pretty straightforward – you go to this link, enter in your information, and they’ll send you the download link. Download and install the app on the Windows Server you’re running Datazen on, and the Plus One icon will appear on the desktop.

This next part is tricky – BEFORE you try to open the app, you have to install two Access ACE ODBC drivers. If you try to do it before that, you’ll get an error message. The problem is, Datazen requires the 64-bit ODBC driver, while Plus One requires the 32-bit driver. So how do we work around this?

1. Follow the instructions in this blog post to install the driver and setup the data source that Datazen needs. However, use this data provider schema file as opposed to the one in the post because you need to leverage Access as your data source, not Excel.

2. Once that’s done, install the 32-bit Access Driver from here. It’s from the 2007 version of Office, so there’s only one item to download and install. That should allow you to open Plus One Social to get started.

Open the app now, and you’ll see the search bar where you can enter your search terms. I’m going to do a search for Twitter data related to Datazen (surprise, surprise), so I type in datazen.

I’m asked to authorize the app on my Twitter Account (you need accounts with the different social networks you want to search across)

I authorize the app, and it grabs the results from Twitter. It’ll then go and grab the last seven days of tweets (Plus One is limited to one week of data, so you’ll need to save it on a rolling basis to keep older data), and I can even setup an auto-refresh to get the latest data.

Now that I’ve got my Twitter data, I want to view it in Datazen. The default Access database sits in the following folder location on the machine –

C:\Users\UserName\Documents\Plus One Social\PlusOneDB.accdb

If you were to open the file in MS Access, you’d see five tables with data – Hashtags, Mentions, Messages, Queries, and Users. You can now write SQL queries against those tables in the Datazen server to pull back the data you need for your dashboard(s). Let’s do one together.

I’ve got the Microsoft Access data provider all setup

I want to see what platform people are using on my dashboard, whether it’s iOS, Android or something else. So I’m going to write a simple case statement query to bring back the results. Something like this –

SELECT Source = CASE WHEN Messages.Source like ‘%iPad%’ THEN ‘iOS’ When Messages.Source like ‘%iPhone%’ THEN ‘iOS’ WHEN Messages.Source like ‘%Android%’ THEN ‘Android’ ELSE ‘Other’ END,
Count(Messages.MessageID) AS CountOfMessageID
FROM Messages
Group By CASE WHEN Messages.Source like ‘%iPad%’ THEN ‘iOS’ When Messages.Source like ‘%iPhone%’ THEN ‘iOS’ WHEN Messages.Source like ‘%Android%’ THEN ‘Android’ ELSE ‘Other’ END

Except I get an error –

The issue here is you have to write the SQL Query like you would in Access, when doesn’t use case statements. It does use switch statements, so I try that instead –

SELECT Switch (Messages.Source like ‘%iPad%’, ‘iOS’, Messages.Source like ‘%iPhone%’, ‘iOS’, Messages.Source like ‘%Android%’, ‘Android’,True, ‘Other’ ) as Source, Count(Messages.MessageID) AS CountOfMessageID
FROM Messages
GROUP BY Switch (Messages.Source like ‘%iPad%’, ‘iOS’, Messages.Source like ‘%iPhone%’, ‘iOS’, Messages.Source like ‘%Android%’, ‘Android’, True, ‘Other’ )

And that works without issue. I went ahead and created seven different queries in total –

and set them up to refresh every hour, making sure I set it to run 10 minutes after I know my refresh on Plus One will finish.

With that all setup, it was easy to create my dashboard (thanks to Datazen) and always stay on top of our social media activity. This was the end result –