One of the cool things about Facebook is that there’s lots of publicly available data and you don’t need Admin access to get it.

For example, our Competitive / Comparative Analysis Worksheet shows lots of valuable data:

With this simple Excel dashboard you can the following information about anybody’s page.

• The number of Facebook Fans
• The number of Facebook shares
• The number of Facebook likes
• The number of Facebook comments
• The number of Twitter followers
• The number of Tweets

You can get that spreadsheet here: Competitive+analysis+worksheet+retail.xls

This is very useful information especially since if it is your competitors you are tracking.

But it’s not enough.  You need more than snapshot in time

You need to know how they are trending, are there any unusual values, does it correlate to any outside events or campaigns, is there a pattern?

I’m sure you’d agree it would be more useful to be able to go back in time and see what the values were last month and what the trends were day by day or even hour by hour or minute by minute (if you were, for example, tracking tweets about a TV broadcast tied to commercials being shown).

In short, do these steps:

1. Change your Facebook and Twitter queries to create a data file for each day (rather than having a single query that fetches many days)
2. Change your analytics to load the data files, and not to query Facebook or Twitter.
3. Use our PowerPack Scheduler to get fresh data each day and
4. Change the reports to use the new data files.

This blog article is a detailed walk-thru on how to do it.  This lets you gather competitive intelligence historically.

From Facebook, you can also get get:

• Names of commentors (to a status message)
• Listing of their comment
• Number of likes on the comments

If you have a busy site, you know the pain Fast Access Mode is causing you. Have sympathy for Google though. It is, after all, a free service, and you are making their servers work exponentially (factorially?) harder than most other businesses.

Some of you might be asking — What’s “Fast Access” mode. This mode alters your results if you are running a busy site. If you know anything about statistics, think of it as sampling, though I am not sure what Google’s algorithm is so I would rather not put a non-Google sponsored label on it.

One customer yesterday said they get 10 million hits a day. The size of the download was lengthy, causing timeouts, and they had to deal with sampled data. Yikes — In a way, I wish I had that problem!

Fortunately, I have the solution! The answer is to run a smaller queries, potentially one for each day, and build your analytics based off the results of those.

We have a technical blog article that explains how to do it. In short, there’s four steps:

1. Change your queries to create a data file for each day (rather than having a single query that fetches many days)
2. Change your analytics to load the data files, not query Google Analytics. These data files are on your own pc, not on some server somewhere!
3. Implement a scheduler to fresh data is obtained each day and
4. change the dashboards and reports to use the new data, and not perform queries to the data servers.

The big benefit is that there is no single large time-consuming error-prone download.

No Fast Access mode, no data sampling.

The dashboards refresh instantly because they’re not waiting for internet data transfers.

In fact, you pack the whole thing up on a memory stick and take it on a plane train or automobile.  Mobile GA, I like it!

The Twitter API is wide open.  You can get data not just on yourself, not just on your friends and followers, but also your competitors and peers.

Most Twitter tools give you stats as-of-the-moment.  Very few if any let you build history for longer term analysis of trends and outliers, patterns, and correlation.  Maybe some do, but at what cost?  Next Analytics is just $199 per year — they probably cost that per month to do what I’m about to show you how to do.

Next Analytics lets you do keep history on all those people, build your own library of data and create your own analytics, all from your own PC.

Basically, you can track historically. Basic information includes:

• Frequency of tweets, retweets, number of followers, influence of followers, hashtags (memberships), urls (sources), mentions, and ratios
• These can be trended, outliers discovered, and activity by day in month, day in week, hour in day
• Lists of names and some profile information.  The lists can be processed using Basket Analysis (aka joiners and leavers) so you can focus on the changes and new information. You can get very targeted lists of those who saw a tweet, retweet, hashtag or keyword.
• Get a list of the people that comment or retweet.  Those are their advocates and critics.  Find out the influence of their advocates, find out what they’re saying, find out what they’re tweeting about.

Our advice is to build a history of level of activity  as the first step to learning more about them.

• get a list of their recent tweets and any links they shared (to learn where they get their content)
• frequency of their tweets,
• frequency of their distinct tweets,
• how often their tweets get retweeted (old and new style),
• how many people are following them
• any hashtags they used (to get their memberships/interests)
• any links they used (to learn what they are recommending, where they’re getting info and it’s originality and influence)

Of the retweets,

• get counts and a list of screennames who saw the retweet (can be recursive, down the tree)

Of the followers,

• get a list and count of their recent tweets
• get a list of their recent hashtags and other screennames mentioned in their tweets (to get their memberships/interests)
• get a list of their recent tweets and any links they shared (to get where they get their content)

If you provide a list of keywords,

• get a list of mentions of that keyword (from entire twitter sphere or filtered by list of screennames).

To track influence:

• how often their screenname gets mentioned.
• The ratio of following to followers, because it’s known they are often proportional due to twitter courtesy practices.
• How many followers they have.

There’s plenty of actionable data in getting answers to those questions. Folks, this is not rocket science. Next Analytics can deliver this.  And the dashboards are automatically refreshable – no touch, just let it run, and enjoy the data!

• Use our software to link this to Facebook and LinkedIn.  What are their hobbies, interests, location? Tie your social media together.
• Are they mentioning your brand? Are they mentioning keywords you track?  Are they mentioning keywords you should be tracking?
• Become friends with their friends and followers, Follow them in Twitter, and even get them into conversations.

What can you do with this kind of information? Here’s some ideas:

• Add new keywords to your pay-per-click campaigns
• Add them to your in bound marketing web content, perfect for long tail content generation
• Contact and become friends with potential brand advocates, monitor what they’re saying

Most of what I’ve talked about are done with custom queries that you can build using our user interface in the Windows PowerPack.

If you are looking for something more turnkey, for rapid ROI, here’s some sample Excel Twitter dashboards:

Tweets-Retweets-Mentions.xls
Tweets-Retweets-Mentions.xlsx
Twitter-Friends-And-Followers.xls
Twitter-Friends-And-Followers.xlsx
TwitterFollowersAnalysis.xls
TwitterFollowersAnalysis.xlsx
TwitterReferencesAnalysis.xls
TwitterReferencesAnalysis.xlsx
TwitterTagCloud.xlsm

The more often you engage in social media, the more popular you get.

Sadly, this is also true for your competitors.

That’s why you should track the social activity of competitors.

The problem is that Facebook and Twitter do not give historical data (unless you are the administrator).

So we created two simple spreadsheets we are calling “Trackers.”

These two small Tracker spreadsheets automatically fetch data and store it on your computer.

These let you build up your own historical view of your competitors engagement and popularity in Facebook and Twitter!

Unless they are customers of ours, they probably don’t even have this information themselves !!!

Here’s what you get:

• number of Twitter Followers
• number of Twitter Status Messages and
• number of Facebook Likes.

You can get it by day, week, monthly basis — anything you want — you control the frequency.

The important thing is: The data gradually builds up on your computer and you can monitor it.

To get the dashboards and advice on how to set it all up, here are some useful links.

Explanation:

Automatically-Track-Fan-Counts-of-Competitors-Facebook-Pages

How-to-Track-Tweets-and-Followers-of-175-of-your-closest-friends

The spreadsheets:

facebook-competitive-page-tracker.xls

twitter-people-tracker.xls

Track the behaviour of people as they visit pages in your web site. You can choose up to six pages in an imaginary sequence (call it a funnel).  This report presents details of where they landed from, and which page they went to next, not just “in your funnel”.  It also knows what they were searching for when they landed.

Get a copy of the dashboard here. It’s a spreadsheet that shows a dynamic list of URLs, and tells you the flow-thru from one stage to next, or where they went to instead. Also shows you what they were searching for when they first came.) There’s no advance setup required — it’s really easy to setup and even works back in time.

You can setup multiple workbooks, each with combinations of pages that you want to the track the flow-thru for.  Find out the combinations of pages that are the most successful.

You can even have multiple workbooks on the go that look back historically. This analysis works even if you didn’t set up the funnel tracking in advance.

If you can identify which funnels are leading to the most conversions, it’s pages are the ones you can optimize, the ones you promote.

1. For each segment of visitors, look for the patterns of how they go from page to page. Are the proportions what you expect?
2. When leaving a certain page, are the exits higher than you want them to be? Where did they go to?
3. Assign names to sets of pages as “conversion funnels” – match the funnel to the segment? Which Funnels are working better? Those are your best segments? What can you do to optimize?

Your Visitors are telling what you need to know, their Actual paths and why they took them.

Funnel tuning becomes actionable.

1) How your visitors found your site (direct, search, referral, paid)

2) Where they came from (from what Sources, Mediums, Referral Paths, Campaigns)

3) What they were searching for (keywords)

4) Where sequences of pages looked at after they landed

By their actions, your prospects are TELLING YOU WHAT THEY WANT.  Google Analytics tells you what they were searching for, how they got there, what they saw, and where they went. You can plan improvements with that information. Use the information to optimize your site exactly suited to each visitor segment needs and behavior.

• Better  more-specific content
• Better navigation
• Call-To-Actions that work

Maybe you’re not an analyst, maybe you do not create the content.  At least now, you can make informed recommendations and prove WHY you are making them. For example:

• If they’re exiting, ask why? What were they searching for?  Is something driving wrong traffic for the wrong reason? You might be able to save on Pay per click costs.
• If they’re going to a support page, then they are seeking more information.  Provide it, and less people will interrupt their conversion cycle.
• If they’re going to a corporate page, then in the “Who are you stage?” sooner than you’d expect.  Find a way to share it then, or make it easy to continue the conversion.  Make it part of the conversion cycle.
• If they’re going to a link you don’t want them to, then add new content and reduce the linkage to the un-wanted places.
• If they’re going to a page you would like to encourage, then you can improve.

Our Funnel Report

This is a spreadsheet report. Just open it, click a button, and your data shows. It’s easy to run each week, make changes, and see what happened the following week. Gradually you can improve your web site to get the more conversions.

1. Choose any combination of any pages.
2. You will see the volume of traffic from page to page, as well as other places they go to if they don’t follow your funnel.
3. Copy the spreadsheet, and run this over a large number of combinations, one for each customer profile.
4. There’s no advanced preparation required.
5. You can add / change or remove pages pages .
6. If you add or remove a page, you don’t lose anything, go back and analyze historically if you want.
7. You can apply advanced segments, even retroactively.
8. Unlike Google Analytics Funnel Report, this does not rely on goals being set up in advance.

The Funnel report gives you this immediately actionable information:

1)      Knowing the popular pages gives you a priority list of where to focus your efforts

2)      Dynamically explore the navigation paths, see which ones visitors are actually using

3)      Knowing where visitors come from is important gives you an idea of the best content to convert them.

4)      Knowing what they were searching tells you what kind of content you can write that will drive conversions.

5)      Knowing what they were searching for shows you trends, how your prospects are seeing your business, and which keywords are driving traffic.

6)      The pages the went to after the landing page tells you about content that’s lacking or what their real interest was.

7)      What they’re searching for is useful because it tells what message would be useful for different sources of traffic.

In addition to the Funnel view, there are complementary additional views of your web site data:

1) Your Top URLs

2) Landing Page Analysis: What proportion the traffic is from Direct, Search, Referral, and Paid

Conclusion

With this kind of information, you have a better idea of what to say on your site.

You can design internal links and call to actions that are highly targetted.

The great thing is, you get it all in one place, at the click of a button, in a spreadsheet that’s nicely laid out.

If you review and repeat this process weekly and gradually your web site will be a highly targetted and effective selling tool.

And, there’s no complex setup. Just identify your key URLs, and the rest is automatic.  It even works historically.

Here’s a request by a customer for a highly focused dashboard that gave them answers to specific questions. They wanted to use this report (after customizing its look by themselves) to use each week in their board meetings. We thought you might find it useful.

It’s a zero-footprint dashboard – nothing to install – just setup an account at cloud.nextanalytics.com and click the refresh button in the spreadsheet. Get it here. If you have an account at cloud.nextanalytics.com, the button to push to see your own numbers is on Main_actions worksheet. If you have our Windows PowerPack, you can simply press the Refresh button on the ribbon bar.  Remember to customize the report first by editing cell A3.

The first view shows the trends of visitors to your web site. Current month over previous month, same month last year, and annual trend.

Then it shows visitors and visits by City, by Referrer, and top 500 keywords over the past month.

It also lets you see important results for a specific page. See the day by day count of visitors, visits, bounces, and new visits. See the counts from each type of Source, and what page they went to from there.

The trouble is, Google’s search engine gives no thought to truthfulness when it scores someone else’s words higher than your own.

The need to sensationalize is not a new phenomena. If someone is announcing anything, it will probably be sensationalized.  They will say your brand is AMAZING and world-class or that it’s a big disappointment, rarely will be a moderate comment. When others retweet or add comments, the exaggeration gets a life of its own.

Words live forever on the web, any words, no matter how exaggerated they are. Then people find and read it while making a purchasing decision. Whatever was said is what you’re going to be judged by.  It can drive valuable organic traffic, leads , and conversions or make prospects will lose all interest.

The effect is compounded when it is a highly rated poster.  The search engines rank their words higher than your own. This, in effect, becomes what general people will discover when they search your brand.

From the point of view of a marketer, this is not good.  When a random message gets visibility, then your key messaging loses prominence in the purchasing decision. This is a big problem for marketers!

What do you do about it? Well, first, you need to know about it.  You can’t fix what you don’t know about.  The problem with that is, keeping tabs on what’s being said on the internet is very time consuming and you have other things to do in your day.

If you know about something, then you can nurture and encourage the enthusiasts.  You can pre-emptively mitigate and improve relationships, minimizing a potential detractor. And, you can improve upon good messaging and dilute poor messaging by jumping into a thread and adding your own content. You can even create new content with the same keywords as the other article, promote it, and hope that you show up in the same long tail searches over and above the other article.

Tracking Social media can be a time-sucking vortex. The problem is that social media itself doesn’t let you work efficiently. Facebook, Twitter, LinkedIn all want you to spend your time perusing their content and engaging with their services. They tease you with a wealth of data, but they don’t make it easy to get at. What you need, you can’t get:

• Influential people and their friends and followers.
• The influence of your fans, their followers and fans.
• The influence and connections of who you follow, who’s in your network, and fans/friends.
• Real-time trends of your own content’s popularity and
• A quick way to scan through messages, posts, and tweets, looking for brand mentions and sentiments

In other words, you need to quantify depth of influence, and breadth of influence, and be alerted to scenarios where your brand is mentioned. There are various tools that help, but they under-deliver.

• Just reading all the tweets, posts, comments is going to distract you and waste your time. From the way your boss sees it, it’s going to consume too much of your workday.
• Some search tools work by searching for keywords you specify but they don’t help you assess things you didn’t anticipate, you miss the level of influence, and you are not seeing trends (since you have to choose the alerts). You will miss important things, people, messages, and emerging trends.
• Semantic analytics are expensive and hard to configure to get to the point where they actually save you time and money, net after costs. There’s just too many factors such as context, culture, language, and temporal shifts to consider and setup before they’ll truly save you time.

Summary

The information you need to do your job is:

• Keeping track of the influence of friends and followers
• Monitoring what’s being said and knowing when to intervene and pre-empt

After all is said and done, you need to:

• Be efficient.
• reinforcing your brand messaging.
• build relationships with your audience.

This maximizes the number of free qualified-leads to your sites and social media, and reinforces your key messaging. A very desirable goal.

The Rest of this Article explains our approach, some of the reports we offer, and the benefits

Before we start, the high level benefits are:

• Our reports are delivered as Excel spreadsheets, so they’re easy to install and use – just open them!
• Data refresh can be fully automated and delivered each morning.
• The results are portable, you can take read them while you commute.
• You will detect mentions of your brand
• You will be aware of the influence of your following/followers/friends/fans/network
• You will know when to engage on content that has the wrong message
• You will know when to nurture advocacy, and mitigate brand assassins.

Let’s focus on the big three in Social Media: Twitter, Facebook, and LinkedIn.

Important note: To establish your security creditentials for Facebook, Twitter, LinkedIn, and Google Analytics, you should install our Addin and connect to each service before running one of the following dashboards. n.b. the cloud thin-client version does not support Twitter or LinkedIn.  For more explanation, see this article: http://excel.nextanalytics.com/pre-built-dashboards/

Tracking Twitter

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/TwitterFollowersAnalysis.xls

• You can quantify influence by see people’s top twenty followers, and how many followers they have.
• You can quantify the depth and breadth of their reach by seeing their distribution of their followers, grouped into buckets by the number of followers each has.
• You can quantify the virality of their tweets by looking at the distribution of Followers, grouped into buckets of the number of tweets they produce.

Tweets-Retweets-Mentions.xlsx

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/Tweets-Retweets-Mentions.xls

Use Excel to search and scan through tweets which mention your brand and favorite keywords.  Unlike in native Twitter, you can save and go back in history. If you see a mention, or retweet something you wrote, there’s a potential relationship to be nurtured or mended. Discover brand advocates and assassins.

See the distribution of tweets, retweets, and mentions for the last rolling month.

Build ad hoc reports that show a count of keyword mentions over time, to ensure your results are being distributed in a manner consistent with your other marketing objectives.

Twitter-Friends-and_Followers.xlsx

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/Twitter-Friends-And-Followers.xls

You need to measure the influence of people you discover. Your goal is to know what the effect would be if ten percent of these people tweeted something, what would be the footprint on the internet?  In this capacity,

• Of the people who are following you, what is the distribution of their followers and following.
• Of the people you follow, what is the distribution of their followers and following.
• Find out who is no longer following you
• Find out who you follow but don’t follow you.

Get their name,description, location, and url.  Find them in other social media. These might be influential people, or people who have lost interest.

Detailed information can be useful for designing a targeted message.  It helps to know who they are and what their interests are.

TwitterReferencesAnalysis.xls

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/TwitterReferencesAnalysis.xls

To help measure the influence of people, you can see a trend of how frequently they mention a screename.  This is a report that’s trended over time.

Twitter Tag Cloud

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/TwitterTagCloud.xlsm

To get a sense of what keywords are hot, try this report. You can provide a screen name, and it will assess the frequency of words in all their posts for the past 7 days (up to 1500) and it will tell you mainly what they tweeted about.

Tracking Facebook

As the name implies, this gets message and comments. It shows the count of likes, comments, and impressions. This gives you a sense of how popular something that was posted was.

Getting the names is the first step to knowing your brand enthusaists and detractors.

Being able to read the comments easily, without the painfully slow scrolling of the Facebook UI, is a boon to scanning for comments which you need to deal with.

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/FacebookMessagesAndCommentsWithNames.xls

Facebook-VisitorDemographics.xlsx

To get a sense of who’s interested in your brand messaging, you can study the demographics of visitors to your Facebook page.

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/FB-VisitorDemographics.xlsx

This report shows the data by age group, gender, country, and the totals (for comparison).

Facebook-Insights-fan-overview.xlsx

You need to be measure and report on your success. For this, use the Fan Overview spreadsheet.

http://apps.nextanalytics.com/downloads/NextAnalyticsDashboards/FB-Insights-Fan-Overview.xls

Get a sense of your reach demographically, trended by time.  You can see where your Likes came from, and the countries, cities, and languages of of your active users.  You can see the trend of daily active users, likes, unlikes, and the lifetime totals.

Tracking LinkedIn Reports

• Get a list of your LinkedIn connections in Excel
• You can search for people by name and company
• You can get profile details about any person
• You can get a spreadsheet with changes to their profiles
• You can see when they answered a question, asked a question, and shared a link
• You can track through to Twitter, and see who they’re following and being followedby

Gone are the days when you can run single large and unsegmented campaigns and hope for success.  On the plus side, web and PPC methodology allow you to have a laser-focus on segments and they provide you the data you need to create great campaigns, if you use it.  Google Analytics, Facebook Graph API, Facebook Insights, and Twitter API all have a tremendous wealth of information – it’s a gold mine waiting to be tapped.

So that brings me to the a particular point. Everybody knows that smart phone sales are through the roof.  You need to think about how your prospects and customers are using mobile because it affects your SEO and PPC  campaigns, or even the layout of your pages, and the content of those pages.

Here’s why: Being mobile means extreme changes in consumer behavior. People are operating in real-time doing Facebook updates, foursquare check-ins, tweets, and more. They expect your sites to service what they want,  and they demand you react.  They want an immediate reaction.  They want to be social with their network, and they share because they crave feedback, and reward.  Unlike traditional mediums of desktop and laptop, people embrace their mobile social activities with a passion. They share, they expect validation, and they expect reward.

Unlike never before, if you execute well, you encourage advocacy.  The competition to be the lead advocate is fierce and people modify their behavior beyond what you’d hope for, they actually compete to be the best advocates, to reap the rewards.  If you don’t execute well, you turn people into brand assassins.

In short, customers and prospects contribute, they expect a reaction, and they expect progress in the relationship.  Don’t forget to reward your advocates.  Being mobile, they expect immediacy. Your campaigns must engage with mobile users in a special way.  Traditional approaches will not work, they’re too slow, they’re not location aware, and there’s no sense of a relationship.

Here’s some useful reports that will help while planning your next campaign.  I’ve picked these ones for general applicability but with a bit of effort, you can go much further and in more detail.

First, know what portion of your business is coming from mobile.

Screen Resolutions of people coming to your site

If you drive advocates to key landing web site pages, then monitor those pages in particular.

Who Visited Your Most Important Pages

Measure the trends  in shares and likes and tweets.

Facebook Insights Fan and Twitter Overview

Count and compare counts of comments on your posts and tweets.  Get a sense of which of your posts generate the most buzz.

Facebook Like And Comment Sources.xlsx

Facebook Messages And Comments With Names

Monitor engagement metrics such as frequency, duration, recency, and depth.  Some combination of these show the behavir of your Adocates. Once you see them, how the interact, then optimize your site for them.

duration versus depth

frequency versus depth

frequency versus duration

frequency versus recency

Engagement Analysis Report

Find out what Time of Day, Day of Week they’re coming in. With a bit of effort, you could segment this by screen size.

Calendar View

Each day, monitor what segments are coming to your web site.  Catch trends in real time.

Daily Leads Report

Let them tell you what they want. These reports tell you what they were they searching for when they come to your site.

Keyword Tag Cloud Report

Search Terms That Sell Report

As you can see, the information you need is there.  So I  hope I’ve got you thinking about how your next campaign could be improved.  You might be considering some sort of offer based on location, popular content, comments, and engagement.

A typical analysis might start with: Give me a list of Facebook users who commented on an event or liked a post in a certain city, tell me how my landing pages did for visits from that city.  Tell me the engagement metrics of people from that city.  Based on all that, create a campaign that makes an offer to people from that city.  And, during and after the campaign, remember to monitor engagement both on Facebook (through shares, likes, and comments by city) and in Google Analytics with the the range of reports that are avaialable (above).