Share via

Analytics for Twitter 2013

  • Article

Analytics for Twitter 2013 version 1.3 (Updated on November 15, 2013) is now available for download at http://husting.com/twitter-analytics-for-excel/ 
This version has been updated based on changes to twitter's api. This includes functionality for downloading user profile images and was built for Office 2013 with PowerView.

As a reminder, you can download the Office 2010 version of Analytics for Twitter here.

Analytics for Twitter 2013 

Analytics for Twitter enables you to create tweet dashboards inside Microsoft® Office Excel 2013 by searching on any combination of terms including #hashtags, @user mentions, and keywords.  The workbook comes with several sheets that provide valuable insight into the search terms and are an easy way create your own customized Twitter analytics.

Sheet Overview 

• Topics – identify key topics and mentions, frequency of tweets by time of day, and top tweeters.
• People – insight into the key tweeters for your search queries including mentions and retweets.
• Tweet Map – a map of where are your tweeters are located and a distribution of when they tweeted.
• Mentions – the most talked about tweeters for your search terms.
• Tweeter Profiles – explore details about tweeters and the most talked about tweeters for your search terms.
• Details – dive into every tweet, filtering on key items. 

Quick Start Guide

  1. Open the “Analytics for Twitter 2013.xlsx” file on your desktop.
  2. Click the “Analytics for Twitter” tab in the ribbon.
  3. Click the “New Query” button in the “Analytics for Twitter” tab.
  4. In the authorization dialog enter your Twitter credentials and click “Authorize app” to generate a PIN.
  5. Enter the generated PIN in the field at the bottom of the authorization dialog and click “Authorize.”
  6. In the search dialog enter a maximum of five comma delimited searches. See the Twitter Search Query Syntax section below for more information on the search syntax. For more control over your search options use the steps outlined in the Advanced Search section below.
  7. Click the Search button to begin gathering tweets for analytics.  

Note: During the searching process if your search queries return too many results in a short amount of time you may have to wait a few minutes before Twitter will provide you more tweets. Analytics for Twitter will automatically continue gathering tweets after this waiting period, but if you don’t want to wait click the “Stop & Process Now” button to generate the reports with the tweets you already have. 

  1. Once your queries have been processed switch to the "Summary" worksheet (if you aren't already there). Click the DATA tab in the ribbon and click the Refresh All button. This will refresh the data model and build your new reports.
  2. Save a copy of your Excel workbook so you can add new tweets at a later time.
  3. When you are ready to add more tweets to your existing query click the “Refresh Query” button.

  

To fully harness the power of Analytics for Twitter we recommend using advanced search. By specifying a small amount of search information such as location, type of tweets, and language you can generate a much more targeted analysis of Twitter. In advanced search each of your five queries use their own configuration enabling you to compare the same query in different areas or languages.

  1. Follow steps 1-6 in the Quick Start Guide above to set up and authorize Analytics for Twitter 2013.
  2. While in the basic search dialog click “Use advanced search” to switch to advanced search mode.
  3. In advanced search each query has its own configuration tab. Click the “Add Query” tab at the top to add up to five separate queries and configurations.
  4. Enter your search query in the “What are you searching for” field. You can use all of the standard Twitter query syntax.
  5. Give your query a, short, friendly name by entering it in the “What should we call this search” field. This name is used in the report slicers.
  6. Enter a geocode and distance in the “Near where should we search” field. The format is “Latitude, Longitude, Distance” (without the quotes) so a hundred mile radius around Redmond, Washington would be “47.677019, -122.130900, 100mi.” You can use “mi” and “km” for distance units.  

Note: A very easy way to get a geocode for a given location is to search for it on bing maps. If you right click on the map it will give you the latitude and longitude for the clicked location. 

  1. Choose recent, popular, or mixed in the “Which type of tweets you want to search” drop down.
  2. If you only want results from a given language choose it in the “Which language do you want results in” drop down.
  3. If you want to limit the results to a specific locale chose it from the “Which locale you want tweets from” drop down.
  4. Follow steps 7-10 in the Quick Start Guide to finish your search.

  

Twitter Search Query Syntax

Analytics for Twitter has been designed to support up to five concurrent search queries on #hashtags, @mentions, keywords and Tweeters.  The search operators' syntax follows Twitter Search syntax. For more information see Twitter’s documentation on advanced searches.  The following examples outline several sample search scenarios. 

Searching Hashtags

•   #microsoft, #msbi, #sql, #sqlserver, #powerpivot

•   #microsoft AND #bi, #sql OR #sqlserver  

Searching Mentions

•   @microsoft, @microsoftbi, @powerpivot

•   @microsoft AND @bi, @sql OR @sqlserver 

Searching Keywords (i.e. freeform text search)

•   microsoft, powerpivot, excel

•   excel AND bi, powerpivot or sql  

Tweeters

•   from:MicrosoftBITV, from:MicrosoftBI

•   from:MicrosoftBITV OR  from:MicrosoftBI  

Combo Search

•   #msbi, @microsoftbi, MicrosoftBI, from:MicrosoftBI

•   #msbi AND @microsoftbi, MicrosoftBI OR from:MicrosoftBI 

 

Troubleshooting Silverlight and PowerView

When you open the Analytics for Twitter workbook it will prompt you to install the Silverlight Plugin if it is not present on your system. If the Silverlight Plugin fails to load or install properly while you are in Excel it will keep prompting you to install the plugin. To troubleshoot this problem follow the following steps.

Verifying Silverlight Plugin Installation

  1. Close all open Excel workbooks.
  2. Open Internet Explorer and connect to http://www.microsoft.com/silverlight/case-studies/ This page should load the Silverlight Plugin. If the page fails to load properly you will need to reinstall the Silverlight Plugin from http://www.microsoft.com/silverlight/ while using Internet Explorer. Ensure that the Silverlight Plugin loads properly before continuing.
  3. Open Excel 2013 and create a blank workbook.
  4. Click the INSERT tab in the ribbon and then click the PowerView button to insert a blank PowerView report. This will load the Silverlight Plugin and create a blank PowerView report. If PowerView loads properly try opening the Analytics for Twitter workbook. If PowerView doesn't load properly try disabling and enabling the PowerView Add-In by following the steps below.

Disabling and Enabling the PowerView Add-In

  1. In Excel 2013 click the FILE tab in the ribbon and select Options to bring up the Excel Options dialog.
  2. In the Excel Options dialog click Add-Ins to manage your Add-Ins.
  3. At the bottom of the Manage Add-Ins pane click the Manage drop down to select "Com Add-ins" and click Go...
  4. In the COM Add-Ins dialog verify that there is only one entry for Power View and that the Analytics for Twitter Add-In is enabled.
  5. Deselect the check box next to Power View.
  6. Click OK to close the COM Add-Ins dialog then close all open Excel workbooks.
  7. Open Excel 2013 and create a blank workbook.
  8. Follow steps 1-4 above to open the COM Add-Ins dialog
  9. Click the checkbox next to Power View to enable the Add-In.
  10. Click OK to close the COM Add-Ins dialog then close all open Excel workbooks.
  11. Open Analytics for Twitter by launching the shortcut on your desktop.

 

Revision History 

**Version 1.2 (July/2013) **
Improved exception handling for when Twitter servers are over loaded and returns an Over Capacity error during the query and data refresh processes. 
Updated to Twitter API v1.1 call for downloading user profile images.
Added additional devices and apps to meta data table.

Version 1.1 (April/2013) 
Updated exception handling for errors during the query and data refresh processes. Includes updated rate limit processing and mitigation of incomplete or corrupted tweet data. Added a check to ensure tweet data does not exceed excel table row limitations.

Known Issues

  • Microsoft Office 2013 or greater needs to be installed for this installation to continue: To ensure that Analytics for Twitter functions properly the installer checks for the presence of the "Office15" folder up to five folders deep in your "Program Files" or "Program Files (x86) directories. If you have installed Office 2013 in a non-traditional folder a work around is to create an "Office15" folder in your "Program Files (x86)" folder.
  • Reports do not automatically refresh after searching or refreshing tweets: This is a known issue and we are working with Microsoft to enable this feature in the near future. To work around this issue first switch to the "Summary" worksheet. On the DATA tab in the ribbon click the "Refresh All" button. This will manually refresh the data model and rebuild the reports.
  • Silverlight fails to load in Excel if it was installed within a third party browser and not Internet Explorer: If you used a third party browser such as Mozilla Firefox or Google Chrome the Silverlight Plugin may not install properly. Try installing Silverlight by browsing to http://www.microsoft.com/silverlight/ with Internet Explorer.
  • Blank data in PowerView reports: If you run into problems with your PowerView reports a good first step is to try refreshing your data. On the POWER VIEW tab in Excel, click “Refresh.”
  • Exception occurred while searching: #term  Please verify your search query: Do to some changes in information returned with query results Analytics for Twitter was no longer properly determining if the user could receive more tweets. This was causing an exception to be thrown during the query process. We are actively developing a workaround to this unforeseen change.
  • Circular Dependency was Detected: If your query fails during processing exit Excel and try your query again. This is a known issue with table processing.
  • Unresponsive PowerView reports after a new query or data refresh: If your PowerView report sheets do not load and only show a waiting spinner after a new query or data refresh you need to close and reopen the Analytics for Twitter workbook. After reopening the workbook switch to a PowerView report sheet, wait for it to load normally, and then perform your new query or data refresh while the PowerView report sheet is still open.      
  • Location filters do not work as expected: Currently the tweet map uses the user specified location field from their profile. This is because presently there are very few geocoded tweets. If you want to use geocoded tweets you can build your own report by using the latitude and longitude fields in tblTweets.