Archive for the ‘Social Media’ Category

Mining Twitter Data with Excel and other tips for Twitter Analytics

April 23rd, 2012
by Tim Resnik

We work with several publishing clients that manage a multitude of Twitter accounts. We frequently run across the problem of spending too much time compiling a large cross section of key Twitter stats for multiple accounts. For example, we have a client that has over 20 Twitter accounts for unique brands unique brands. Trying to collect and analyze follower and status update counts is a tedious manual process, and not recommended.

There are two methods to accomplish data scraping from Twitter:

1)      Develop a web application using the Twitter API

2)      Use a mashup of tools mixed with Excel or Google Docs.

Building a web application to perform this scraping function has obvious financial and time barriers, but may be worth the effort or licensing fee in the long run.

Using Google Docs and ImportXML can handle this function nicely and has been covered thoroughly (I mean thoroughly) by Distilled in their ImportXML guide . Also, John Doherty makes good use of the API and Google Docs and has created a link prospecting tool.  What I want to cover in this blog is how to collect basic Twitter following/follower stats across several accounts using Excel and Niels Bosma’s badass plugin: SEO Tools for Excel. Since the SEO Tools plugin is doing all the heavy lifting in this example, Niels should really get all the credit.

Step 1: Install SEO Tools for Excel

There are a lot of other sweet features, but for this example we are just going to use the scraping function:

 SEO Tools for Excel

Step 2: Build a list of Twitter handles that you would like to collect via the Twitter API

First you need to figure out who you are interested in grabbing the data for. This could be your competition, your employees, different products within your company, punk bands, or whatever group of Tweeters you want to analyze. You can use services like  WeFollow and Listorious to generate ideas. Compile the list and put the handles in the first column of your spreadsheet (don’t include a preceding @). With this particular XML feed here are some of the relevant things you can pull:

  • ID: the numerical unique identifier for the account. This is a handy key to have because it will never change while the screen name can be changed by the owner of the account
  • Name: the name of the person who registered the account
  • Location: the geo-location of where the account was created
  • Description: the user-created description for the account that shows up right under the screen name on Twitter.
  • Profile img URL: the location of the profile image for that account
  • Followers count: number of followers that this account has
  • Friends count: number of accounts that this account is following
  • Created at: the date and time that this account was created
  • Status count: number of tweets since the account was created
  • Listed count: the number of times that this account has been included in another accounts lists
Or, checkout all the gory details

Here is what my column headers look like:

 Excel Column Headers

You can download the example XLS file here. The formulas are all pre-populated so all you need to do is enter the Twitter names (30 max due to API rate limiting) that you want the information for in the “Twitter Handle” column. If you want to build it out yourself, below are the details.

Step 3: Construct the API Request URL (sheet 2)

We must create the proper URL to be able to pull this information from Twitter. In this case we are using the Twitter REST API resource: GET users/show. This is one of a handful of API calls that does not require user authentication.

There are two required components and one optional to the URL.


  • Request URL:


  • Screen name: the Twitter handle you would like information for

So, it will end up looking like this:

Optional: include entities. This API requests returns the latest tweet for the user info requested. If you would like to include information about the tweet such as user mentions, hashtags or associated URLs, then also include the following to the end of the URL: &include_entities=true. The final URL would look like this:

We now have to construct the URL. In sheet 2 of the spreadsheet you can see that I am simply concatenating two fields: the Twitter handle from sheet 1 and the URL from the current sheet.

Step 4: Write Your Formulas and Populate the Array

Nothing too fancy here thanks to SEO Tools for Excel. There is a pre-built function that does all the heavy lifting. You can select the XPath (reads XML) or JSON option with the Twitter API. In this example we will use XML.

The XPath function has two inputs: the URL to call the XML file, which we constructed above and is in sheet 2, and the instructions for selecting the right information in the file (the proper node). Here is a very basic write up of how to use XPath to select nodes within an XML file.


Once you have your formula written for each column, fill in the rest of your array and wait a bit as it fills in the data. The Twitter REST API is limited to 150 requests per hour and each cell in the array you have just created is an API call. In this example we have 5 columns and 25 Twitter handles so filling out this array once will be just 25 short of the hourly cap.

Step 5: Some Basic Analysis

Now that we have an array of data for a group of Twitter users we can do some very basic analysis. In follow up blogs, I will collect the data over time and do a little deeper look at trending analysis.

In sheet 3 you will find a few graphs based on this data. It is important to note that the data in sheet 1 is formatted as text because Excel is keying off of the formula. This makes analysis difficult on that tab itself. I copied and pasted the values to sheet 3(Copy>Paste Special>Values) and then converted the text to numbers.

I wanted to compare the number of followers juxtaposed with the average number of tweets per day. Twitter returns the date as (Day of week, Month, Day, Time Stamp and Year). In order to format the date so Excel can understand it you must pull the right information out of the ‘Date Created’ :

Format Dates in Excel

Excel still can’t understand it because it is reading the formula instead of what is being visually shown in the cell. In order to get it to work, use Copy>Paste Special>Values to overwrite the formula, go to Format Cells>Date> format as 04/23/2012.Create a new column called Avg. Tweets per day. You now need to figure out how many days have passed from the time the account was created and divide into that the total number of tweets.

Average Tweets per Day

Next, you can select the data in the Followers and Avg. Tweets per Day columns to generate your graph. I won’t go over the details on chart formatting here, but here is the end result:

followers to tweets graph


Follow up post: Twitter Data Scraping and Insights Using Excel


How to Setup Google+ Pages [Guide]

November 10th, 2011
by Tim Resnik

After a few days of playing with the new Google + Pages, I am cautiously optimistic that it will provide some unique opportunities over Facebook and Twitter, specifically with Hangouts. However, I am a bit disappointed at other limitations, such as Google’s policy not to allow contests and promotions.

Here are the basic steps to getting started with Google+ Pages:

Step 1: Login to Google+ and Go here. If you want to see their promo splash page, go here instead.

Step 2: Pick a category and add your info. If you have a physical location and a Google Place page you should select “local business or place” because it will integrate some of your Places information to your Page. Google allows you to set up both a local business and a brand, but don’t do this unless you want to dilute presence on Plus and manage multiple accounts.

Google+ Pages - Create a Page

Step 3: Customize your page’s public profile. Not much you can do here, but add a short tagline and a profile photo. Don’t expect to do skyscraper image here. Unlike Facebook, the image size is fixed at 200X200 pixels. One interesting feature here is a fairly sophisticated photo editing option during the upload process.

Google+ Photo Editing

Step 4: Get the word out. Google encourages you to do four things to build your business’s Google+ presence: start posting, build your circles – in other words start adding a bunch of people to your circles, link directly to your profile and connect your website to your Google+ Page. This last piece is probably the most interesting because of the use of the rel=”publisher” link, which will “confirm ownership of a site” for Google. Google requests that you put this link which points to your + page from your website in the section of your site. 

Now it’s time to start posting, interacting and building a following. Here a few important things to note:

When you go to your Google+ home page you will notice a drop down to the right of your profile image and directly below your name. This is where you can toggle between your personal profile and your pages. Be careful. Once you switch to a page your all of your posts and comments will be from that page. Moral of the story: before posting pictures from your recent trip to Burning Man, make sure you are on the right profile/page.

Unlike your personal Google+ profile, your Page cannot add individuals to Circles if your Page is not in their Circles. Pages can however add other Pages to their Circles without reciprocation.

Pages do not appear to have the capability to be managed by multiple accounts. It took Facebook a few years to add this functionality, and I am not entirely surprised that it is not in the first release of Google+ Pages.

Google Places to Portlanders: Please Create Garbage Reviews!

June 13th, 2011
by Tim Resnik

Google continues to push Places and Offers very aggressively in the Portland area. When Google first released HotPot in Portland in December, Matt McGee  wrote about how they broke their own guidelines by offering over $13,000 in free dining to those who posted the most reviews.

Well, it appears they are at again. This time it’s a promotion centered around the Google Offers for Ground Kontrol and the only place it is being promoted is on a Facebook Events page. Interesting.   This promo incentivizes folks to go review mad. 50 reviews gets you an invite to a special event at Ground Kontrol, 100 gets you the invite plus a drink and a unfathomable 200 reviews gets you 2 free drinks, some grub, a t-shirt and a $15 gift certificate. If you have enough time to write 200 reviews for some SWAG and a couple of drinks, you either need to look for a job or move out of your parents’ house.


Google’s current policy:

Conflict of interest
Reviews are only valuable when they are honest and unbiased. Even if well-intentioned, a conflict of interest can undermine the trust in a review. For instance, do not offer or accept money or product to write positive reviews about a business, or to write negative reviews about a competitor. Please also do not post reviews on behalf of others or misrepresent your identity or affiliation with the place you are reviewing.

At the time of Matt McGee’s article the guidelines had the following sentence: “In addition, we do not accept reviews written for money or other incentives.” That sentence is now removed.

I understand that Google is trying to prime the pump by injecting the system with some reviews, but I question whether a synthetic strategy versus an organic one is wise. Google built a search empire on the foundation of quality and relevancy and shouldn’t forget its roots as it forges ahead in social.

P.S.  add a comment to this blog and be automatically entered to win a one-of-a-kind AudienceWise trucker hat (comment bots not eligible)

Crowded House: Google Offers and Facebook Deals Launch, eBay coming soon?

April 27th, 2011
by Tim Resnik

Amidst the feeding frenzy of hungry start-ups trying snag market share from Groupon and LivingSocial, Google and Facebook near-simultaneously threw their daily deals hats into the ring. The speculation and rumors that Google was building their own platform after failing to purchase Groupon for $6 (capital B) Billion in December 2010 came to fruition on April 21st when they announced Google Offers. Just 5 days later, Facebook announced Deals and launched in 4 cities. While the blog and news sites ate-up, and sometimes regurgitated, this information, another internet beast was quietly making its own move.

On April 20th eBay announced that it acquired a company called Where that provides local search, discovery and “deals” mobile applications. eBay plans to integrate Where into PayPal’s services for local businesses and it seems highly likely they will offer a group buying service. More info about Where below:



According to BIA/Kelsey, a Chantilly, Va., consulting firm the daily deals market is expected to grow to $3.93 billion in 2015 from $873 million last year. Driving the market is an insane adoption rate of smart phones, a stagnant economy that has people bargain hunting, and extremely competitive retail markets, like restaurants, looking to lay claim to dollars from tight wallets. This created an ideal environment for Groupon and LivingSocial to explode on the scene. Heck, Groupon went from 0 to 60 so quickly that they even had the opportunity to launch a tasteless ad campaign during the super bowl (sorry, had to get that one in there). Several underwhelming copy cats emerged and have been appropriately scoffed at by Groupon, but now the big boys have shown up and are posed to snag the first-movers’ milk money.

As first-movers, Groupon and LivingSocial have the benefit of a few textbook advantages, but are not entirely defensible. Most notably, they have an established operational and sales network designed to bring retailers and customers together. For several reasons, small-business owners are not an easy bunch to sell to and require “feet-on-the-ground” in order to close the deal. Groupon and LivingSocial have a proven track record of selling and delivering. Traditionally, first-mover advantage includes that of switching costs. However, in this case there are none, and in fact, the social/viral nature of group buying encourages customers to sign up for as many coupons sites as possible for FREE.

As a group, the very large, established internet-firms have two primary advantages as compared to the first-movers. First, is access to large amounts of capital; all three firms have cash in the coffers. Google has the largest advantage in this category, and because Facebook is still burning investment dollars they are in a somewhat weaker capital resource position (IPO anyone?). Second, each company has unfathomable reach and customer data. Groupon’s 10 – 20 million registered users seems quaint compared to the aforementioned.

Google Advantages:

  • Operates the largest internet ad platform and can use it in many ways to spring board Offers
  • Is the provider of the most widely adopted mobile platform, Android
  • Disadvantage: seems to have a hard time convincing users to think of them as a social site. As with my concern with +1, people perceive Google as a place to look for information, not to connect and/or share with friends. However, people like free and cheap stuff, so this could be the social platform that changes that perception for Google

Facebook Advantages :

  • 600 million socially connected people
  • Specific data on what brands users “like” and are connected with
  • Is inherently a social platform and is perfectly primed for group buying
  • Disadvantage: fewer resources (relatively)

Now that everyone wants a piece of this market, what does it mean for the two most important stakeholders: customers and retail businesses? Basic economics tells us that the higher the supply or competition the lower the prices. The group buying sites can’t directly control price, but have a few variables that they can control: the value of the offer to the consumer, the percentage of revenue they take from the retailer and the type of coupons that they offer. Ultimately, increase competition will flood the market with better and better deals for the consumer and lower margins for the providers and retailers. As group buying hits the mainstream and everyone is conditioned to be a bargain hunter, the question is: at what point do the costs outweigh the benefits for the retailers and the market is turned on its head?


By Tim Resnik

Will +1 be Google’s Social Watershed Moment?

April 4th, 2011
by Tim Resnik

Last week Google officially announced it’s new +1 button and explains it is short-hand for “this is pretty cool” and to “publicly give something your stamp of approval.” I gave it a test drive myself and provide a few thoughts about the current implementation. In addition, there is plenty of conjecture across the search industry and I have provided some links to the ones I found useful.

The first step in having a look at Google’s new +1 service is enabling it. For those having a problem finding it, you can enable +1 here.

Since I just finished a Sports Guy article that mentioned one of the NBA’s most exhilarating players, Derrick Rose, I felt inspired to +1 a link of a monster dunk. I popped “Derrick Rose Dunk” into search. Below is what I found (Side note – for basketball lovers, or those who just like to see incredible athletic feats, I have embedded the top 5 Derrick Rose Dunks at the bottom of this blog).

I want instant gratification! I fiend to see a glorious dunk and I want to see it now.

As a searcher my first goal is to find the content that I want. When I am returned results for a search I am only part of the way there. My intent is only fulfilled once I actually digest the content that I am looking for. Google +1 asks you if you want to recommend content before you even have a chance to digest it. This makes little sense and I believe they will have a hard time changing the behavior of their users. I could see using it as a quick way to bookmark links when I am doing research, but it would be nice if Google added tagging or Google Bookmarks tag integration.

The current SERP implementation of +1 seems to be an after thought

For this particular search Google did a good job of matching my intent and presenting the most relevant information at the top of the page: YouTube (of course) videos of Rose dunks. However, as you can see there is currently no way to +1 video or image boxes, only standard web search results. Also, the actual +1 button itself is pretty hard to see, however there is an animated effect upon mouse-over that makes it stands out a little more. But again, if I am looking for something that I have not yet found, my first action will be clicking on a blue link and not +1.

Google is not very social

I know they would like to be as social as Facebook, but that’s not necessarily what their users would like them to be. Sure, they have incredibly powerful data to leverage, but if your customers see you as one thing it is difficult to change their perception as well as their habits. Historically most people have used Google services unilaterally, and their wide scale attempts to wedge into the social rush has produced under-performing services such as Wave and shady forced attempts at growing Buzz.

+1 Round Up

There are a pile of articles and blogs that have been written about +1 over the last few days, here are a few highlights and summaries:

Bill Slawski from SEO By The Sea hunted down a Google patent “Dynamic Action Links for Web Content Sharing” and provides some information and thoughts.

Raven discusses future implications of +1 on SEO. The post theorizes that “the average user” doesn’t really care and will not hit the back button to +1 something. Agreed. Also talks about how it may be gamed.

SEOMoz believes +1 will have a major impact on organic results.

Here’s a good run down of +1 and some speculation on how it will affect Google Buzz and the rumored Google Me/Emerald Sea.

Greg Sterling breaks down how +1 is integrated into paid search. He gives an interesting example of how an organic +1 can automatically display a +1 on a paid result.

And, as promised, Derrick Rose throw downs:

All Roads Lead to (Jim) Rome: 5 Pillars of Social Media Engagement Strategy Borrowed from Talk Radio

March 28th, 2011
by Tim Resnik

I have a long commute to work, at least by Portland standards. Although I only drive for about 30 – 45 minutes, the fact that I pass farms, barns and stables qualifies it as a sizable commute. Cue eye rolls from NYC, LA, ATL commuters. Having three-quarters of an hour to myself leaves me with plenty of time to listen to sports talk radio and think. Well, the other morning I happened to be doing both at the same time.

I was listening to a syndicated broadcast of The Jim Rome Show and he was asking his listeners a question about what they thought about Tiger Woods and his chances of becoming the golfer and celebrity he once was. I wasn’t too interested in the question, but it got me thinking about the similarities between engaging audiences on a radio talk show and engaging audiences in social media.

Talk show producers have a pretty simple model:  create a show that attracts listeners by providing value in the form of entertainment or information and sell ads and sponsorship to companies who want to reach that demographic. They have been refining these business practices since early last century, and social media marketers could learn a thing or two.  I have identified five tried and true methods of talk radio that should be applied to engaging audiences online.

1)      Ask a lot of questions. If you are a regular listener of talk radio you have undoubtedly noticed that the host will constantly pose questions in an attempt to get people to call in and start a conversation. The host is putting the conversation in the hands of the listener and letting the audience lead. A moderator of a brand in a social media channel should do the same: moderate the conversation, don’t overpower it. If you ask the right questions, your audience will do the heavy lifting. Social media has the enormous added benefit of conversations spreading within your audiences’ personal networks. When Facebook fans actively participate on a brand’s page, their personal network knows about it via wall reposts. This is obviously not the case when someone calls into a radio show.

2)      Have an opinion. Most talk radio hosts are highly opinionated. Whether it is Jim Rome talking about sports or Rush Limbaugh politicking, there is always an opinion about the subject at hand. This is important for radio hosts and brands on social networks for two reasons. First, it establishes a position and provides context for what the brand (or host) is all about. Second, it creates a foundation for a healthy debate. People are more likely to engage if they can agree or disagree with a stated position.

3)      Create meaning and be relevant. Radio audiences choose to listen to a specific talk radio show because the subject is meaningful in some way. If the program is irrelevant or lacks meaning, they change the channel. Online audiences are even more fickle and instead of changing the channel, they will simply not engage with your content or worse un-follow or un-like. Online audiences are bombarded with information every second they are online and most people have a limited attention budget.  Its spread even thinner for each new friend or brand they follow, fan or like and relevance becomes that much more important.

4)      Give stuff away. …and if you are the 9th caller you win… People love free stuff and radio producers have used this tactic for years to engage their listeners and promote their sponsors. Brands have been doing this since the very early days of Facebook pages because audiences respond well to incentives. That being said, point number three should be considered here: the prize and the method to win the prize should be relevant and meaningful to your brand. For example, when I was at Card Player we ran a few Facebook contests giving away a trip to Vegas to play in the World Series of Poker. People who read poker magazines typically like coming to Vegas and playing poker so the prize was very meaningful. The contest itself also had meaning in that we asked people to design a cover of the magazine. Their friends and our fans then voted on the winner.

5) It’s OK to talk about yourself, but don’t overdo it. The best talk radio hosts can walk the line between relevant introspection and narcissism. Those who discuss their life experiences in context of the subject of the program have a positive impact on engagement while those who stray and just talk about themselves do not. The lesson for brands here is that your audience doesn’t just want to hear about your new product releases, blogs and accolades. They want to hear why it is relevant to them.


Back to Rome. He does a really good job  engaging his radio audience. Ironically though, he doesn’t really practice his refined audience engagement methods on the Internet. Check out his Facebook page. There is a lot of regurgitation of news and opinions, but not much else. However, he is definitely not afraid to state his opinion which is good to get the conversation going.

Although your objective for your social media strategy may be to increase leads, generate more site traffic, increase branding or provide customer support, your audience doesn’t necessarily share those same objectives. They want to participate, not be vomited on. Treat your fans as if they are listening to radio talk show about your brand, and do everything you can to prevent them from turning the dial.

Attracting Fans with UGC Promotions on Facebook

March 7th, 2011
by Tim Resnik

Every Monday releases its list of fastest growing Facebook pages for the week. I love checking out this list because it is a quick way to see what creative promotions are being deployed by page owners across Facebook. Using promotions can be a quick and inexpensive way to build your fan base on Facebook.

This week they reported that Man vs. Food, the Travel Channel program that pits Adam Richman against piles of food, added the most fans (about 550,000) to its Facebook page last week. The surge in “likes” can mostly be attributed to a user generated content promotion in where fans create a video about a spot in their town that the show should visit. The Travel Channel has since taking down the promotion on its page, but can still be seen here.

Here are three things to think about before embarking on your journey to build a large fan base through a user generated content promotion on Facebook:

1) Build your Facebook Page to a level that will sustain a promotion on its own. There is nothing worse than throwing a party that no one shows up to. User participation is key to driving any good promotion; especially user generated content promotions. This will of course provide a strong foundation for viral propagation because users are more comfortable participating when they see others doing the same. This leads to increased engagement.

2) Along the same lines as the above, make sure that your Facebook Page is ready for primetime. No one wants to join a page that is anemic with nothing going on, no matter how good your promotion is. Ensure that people are interacting on your page prior to launching a campaign that could send a lot of traffic. Having a strong welcome page, rather than just the wall, can also improve conversions. Man vs. Food did a nice job of this:

3) Don’t neglect your existing marketing channels. The Travel Channel has a highly rated cable show that can be used as a vehicle to communicate the promotion to millions of people. Most companies don’t have this advantage. However, don’t be discouraged. Although you may not be able to reach the same number of people, you still probably have a few tricks up your sleeve. Think about all the ways that you communicate with your market: your website, a newsletter, advertising, syndicated content, twitter followers. Plug your promo!

Launching a user generated promotion can be expensive if your building it from scratch and is probably not worth it unless you are driving $1 million in value; tough to do if you’re not a major brand. Luckily there are some good Facebook Page application platforms, such as Involver and Buddy Media. If you’re looking for something that can be deployed on a one-time basis and is considerably less expensive, check out Wildfire. I used them for a UGC promo for the CardPlayer Page and was pretty impressed with how easy it was to rollout.