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.
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 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
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
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
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 –
Thanks for reading!