Whether you’re an advertiser trying to review campaign data or a not-for-profit working with donor files, this series will show you a few simple formulas you can use to get the most out of your data without spending hours doing manual manipulation.

In order to create pivot tables, or sort data into groupings you need to have a data file with multiple columns or data points. However, you might find the data you receive has arrived all lumped together in one column in the form of a tab deliminated csv or tab (text) file. Thus making it hard to do much of anything with your data.

The “text to column” formual in Excel allows you to take any lump sum of data and separate it into coumns.
For example, you might have received an Excel file, but your donor names are lumped together and you want/need to be able to separate them out (i.e first-name only personaliztion). Here’s how to get the data separated in less than 60 seconds

Using Excel 2007
1. Look under the Data tab and click “Text to Column”
2. Choose your data type – deliminated (separated by commas, underscores, tabs or single spaces) or fixed width (a specific amount of space separating items you want into two separate columns)
*Note: Using the above example of name separating, we would choose the first option of deliminated since we have an Excel file and the amount of characters (or space) in each name is different*
3. Choose your delimination type (we would click space for this scenario) or your column breaks if using fixed width
4. Choose your data format (text, numeric, date etc)
5. Click Finish

Your data has now been separated.

One thing to note, if you have data in the column immediately to the right of the data you’re separating you will need to insert a new blank column, otherwise you will either need to start over, or choose “ok” when prompted to replace the data in the destination cells.

The next post in this series will show you how to do the opposite of this one – combine several columns of data into one column.

How do you know if the pitch you gave sunk in and if the person who said they were going to think about it, really did decide to follow through and donate? One of the easiest and most cost effective things you can do would be to create a vanity url to help you track the success of these face-to-face campaign efforts.

A vanity url can be either a memorable web address redirecting to a specific page on your company’s website, or an extension of the current domain (e.g 123.com/xyz).

You might already create vanity urls for your DRTV or direct mail pieces, but have you thought of taking them that one step further? What about creating vanity urls for your canvassers to use that are separated by region, province, city or even events?

If you could track which teams, areas or events ended up providing web traffic, you could begin to increase your response rates and cost per donor acquisition for these tactics.

By simply creating either a ‘calling’ card for each url (team) or by inserting the urls as part of the dynamic copy changes (i.e showing regional office contact information) for your brochures and leaflets, you might suddenly begin to know whether or not your public outreach campaigns are generating awareness, and better yet, donations. Many times canvassers simply leave information behind, or speak to someone who is busy, or in today’s society, simply untrusting. A vanity url provides you an opportunity to know which potential donors decided to check out your website on their own time and terms.

By having multiple vanity urls, you would be able to review your analytics data to not only see how many people visited your site by specific group (url), but what they did while there and how that behaviour compares to other website visitors (eg donors vs. online ads vs search engine visitors etc). Is there a specific page people from the vanity urls looked at? How does that compare across the urls? You could provide this information to your canvassers so they know what topics or types of information they might need to cover in certain areas – perhaps it’s different region by region. Is there one url that provides you with a high conversion rate? If so, this data then becomes a great way for you to connect with your teams on the ground and provide them positive feedback, or have them mentor those teams that seem to have lower conversion (close) rates.

Of course vanity urls are only good if people use them and this tends to be a highly controversial topic since recent studies show that more people may recall a vanity 800-number easier than a vanity url (if listening to or watching an ad). But considering how cost effective they are (especially if you use an extension of your site), why not take a chance? You may or may not get oodles of data, but you’ll definitely get more than you have now, and enough to get a better understanding of what’s happening after your canvassers have gone away.

New moms love a few things – receiving food they can stock in the freezer for easy reheats when they are too tired because baby kept them up all night, or busy all day and guest posters so they can continue providing their readers with valuable content. Or at least this new mom loves both these things equally.

Here’s a fabulous post about real-time stats programs from Sue Clark. Sue is the author of Lighthouse News, a fun and educational (for me) blog keeping up with lighthouse news and information across the world; she’s also pretty darn talented in my personal opinion when it comes to technical aspects of blogging, including stats. Enjoy!

Using Real Time Stats Helps Leverage Traffic

If you’ve followed Rebecca’s excellent advice (Blog owner’s note: Thanks for the compliment Sue!), you’ve already learned how to use the free and excellent Google Analytics to your advantage. But one of its limitations is that it’s not real time, and you can be missing some important traffic. You don’t get your statistics until the next day. For this, I recommend you install, alongside Google’s tool, a real time traffic monitor.

Some reasons you might want to leverage real time traffic (and some not so obvious ones) include:
*You see traffic coming in on particular search term to a specific page and might want to update information that may be out of date.
*Someone accidentally stumbled the wrong page (yes, that happens quite often) and you may want to put a link to the correct page.
*You’re running a specific campaign and want immediate feedback on its success.
There are many more reasons, including just plain curiousity about how many people are online at the moment. But those are the most common.

One example of when it was useful for me was when I noticed a large amount of traffic coming in from Google on the search term, “Scotland’s tallest lighthouse.” Now, I have no post referring to Scotland and its tallest lighthouse (Skerryvore, in case you’re wondering), but I do have a slightly humorous post about the World’s Tallest Lighthouse. And that was apparently showing up in the Search Engine Result Pages right near the top. But it really wasn’t about the world’s tallest lighthouse.

However, with all of that traffic coming in, I edited the post to add the actual tallest lighthouse, just so I wouldn’t annoy the readers when they found out it wasn’t what they expected. And although I don’t sell anything, or promote anything except saving lighthouses, I don’t want to annoy anyone. They might come back and visit again, or better yet, subscribe to my feed or newsletter.

If you’re wondering, there is no issue in running another stats collector program alongside of Google Analytics. There are numerous real time stats packages available, both free and paid. The list is by no means complete, but should give you a start.

*Stat Counter (free)
>Feature rich, looks good. Demo available. No ads on your site.

*HiStats (free)
>I personally use this one. I like the visitor tracking in the “last 20,000 visitors” section.

*Woopra (free)
>Sometimes slows down sites, but great features. Has a WordPress plugin.

*Sitemeter (Free and Paid)
>Even the free version is good.

*Feedjit (Paid)
>Has a ten day free trial, and some widgets to use on your site.

For more, here is a quick search from (who else?) Google. Check them out, try them on for size, it’s easy. And the benefits to your campaign are instantaneous. In most cases it’s simply adding some code to your footer, or a widget to your sidebar.

My daughter in mid-wail

What do crying babies and your website visitors have in common with one another? A lot more than you think.

Being the mother of a newborn infant (two months old this past Saturday), crying is something I’ve been learning about. Babies are not able to communicate with words yet, so they cry to tell you something is wrong. Visitors to your website don’t cry (at least not that you know of), but they do something else; they leave your website.

When a baby cries you try to understand what they are asking for – are they hungry, do they need to be changed, do they have gas in their tummy? You can go through each of these options (and more) to try and remedy the problem until you find the solution. Unfortunately you can’t do that with a website visitor, once they are gone, they’re usually gone for good.

This is why it’s so important to understand patterns and behaviours of visitors to your site and to try and identify why people are leaving the site and fix it before they leave. Previously, I wrote a post encouraging you to look at your fall out reports available to you from your web analytics software as it can tell you valuable information. This report can be very helpful if you know what you’re looking for and if visitors are going more than one page deep in your site, or are using some sort of registration.

But what if they aren’t? What if visitors aren’t getting past your front door? They come to your site, but leave right away? What do you do? Like a crying baby they can’t tell you why they are leaving – so how do you know?

Here’s a few simple questions you can ask yourself about your website to see if you’re able to change the bounce rate of your site.

Look to see where visitors are coming from

 If they are coming from search engines, what keyword is it they arrived on, this will give you a clue as to what kinds of information they were expecting to find. If it is wasn’t as a result of organic search, then what site was it? What likely made them visit your site?

How easy is it to find what they are looking for?

Now that you know where your visitors came from and what they might be looking for, ask yourself if your site caters to their needs? Can you easily find the information they might have been looking for in eight seconds or less?. If you answer no to this question, you need to seriously consider the content and layout of your site.

Is your call to action apparent and easy to follow?

What is the number one thing you want a visitor to your site to do? Do you gently guide them towards that path without any other obstacles in their way? Or do you potentially overwhelm them by giving them too much choice and too many options? Is your call to action clear, concise and most importantly – above the fold and enticing enough to make your visitors take that action, not now, but right now?

We all lead busy lives, it’s easy to start a task and abandon it halfway because the baby started crying, or the boss asked you to drop everything you were doing for another project.  So make sure your website is designed to make it easy for people in these situations to find and finish whatever it is you want them to do so they can go on their merry way.

One of my dogs underwent knee surgery a couple of weeks ago to repair a torn cruciate ligament; when my husband and I picked him up, we knew right away something wasn’t right and he shouldn’t have been released from the vet’s. But it was a weekend, and we were told that everything we were concerned about could also be normal in a recovering pooch. Our guts kept telling us something was wrong, and sure enough when we finally saw the surgeon again several days later, he took one look at our dog and agreed with us. Our poor guy had to have a second surgery and ended up suffering needlessly for several days.

On the professional front, I’ve been comparing some advertising results for a client for several different sites. While I’ve previously discussed the difference between clicks and click-throughs and why you will see discrepancies, sometimes the discrepancies jump out at you enough to question them.

This is the case with one particular site. The client’s tracking system and the vendor’s show very different numbers for clicks and click-throughs, as high as 150% or more when you start to break down the data week by week. A discrepancy of this size immediately set off alarm bells in my head… and my client’s.

We’ve been back and forth with the vendor numerous times, and after iniitally discounting us, they are now taking us seriously. Given this is the only site of many we’re on showing this discrepancy, it’s clear that something isn’t working. Whether it’s the client’s or vendor’s tracking system remains to be seen, but both parties now agree that this must get resolved because something is amiss.

The moral of this story? Trust your judgment. You have good instincts when it comes to things you know – like your website. If something doesn’t feel right, question it. Keep on doing so until you feel satisfied with the answers you’re receiving. My husband and I should have pushed more (and sooner) with our dog, but we were out of our element – we didn’t know enough about the subject, so just trusted what others said. At the end of the day, he’s our dog, we know him best, and while we may not have known what exactly was wrong, we knew something was, and it turns out we were right.

The same can be said for my client who knows the number of click-throughs they normally get from advertising campaigns, and the types of discrepancies they normally see between their tracking system and a vendor one. They aren’t giving up until this is sorted out because the discrepancy is large enough to make or break this campaign (on this one site) for them. It will determine whether or not they advertise with them again in the future. So until we know for sure, we’re going to keep digging and keep questioning.

As G.I. Joe says.. “Knowing is half the battle.”

Photo Credit: Stuant63; Flickr

While the post is geared towards non-profits, the points raised really are transferrable across almost every industry.

In this age of information overload, microblogging (i.e Twitter) and other social media tools, email newsletters need to be freshened up. Gone are the days of creating long-winded newsletters with all the articles and information right there in the body.

Newsletters should be short and provide clear direction for what action you want people to take. They should also provide options; while you can segment your audience and send targeted newsletters to certain groups (clients vs. prospects, etc.), it doesn’t mean everyone is still interested in the same articles or information.

Writing your newsletter topics like Twitter posts or Facebook statuses – giving a description in small chunks with a link to view more for those interested – is a way to not overwhelm your audience.

As Kivi pointed out in her post, “everyone has a desire to achieve inbox 0 these days,” so the less overwhelming you can make an email, the better. The easier it is for people to click through on a link and then take some sort of action (like bookmarking or sharing the story) brings your newsletter in the social media age and provides valuable insight.

If you’re tracking your newsletters, links, word of mouth mentions (using alerts and searches and so on) you’ll be able to see how far and wide some of your newsletter articles go. This will help you get a better sense of what kinds of articles you should be including in your newsletter and ultimately make your newsletter even more attuned to your subscribers.

Here’s three tips for campaign optimization that don’t involve creative changes to help keep things fresh and your budget in check.

Using your Analytics systems, check click and conversion metrics for:

1. Best performing sizes
Sometimes certin sizes or types of ads may perform differently than you expect. For example, box ads may be placed at the top, middle or bottom of the page on the site. If you know your ads are on the bottom, there’s a chance that the box ad may not perform as well for you as another ad unit (or vice versa). Shift impressions to the ad unit that performs the best from a combined click-through and conversion standpoint. Keep in mind not all ads units will cost the same price, so you will need to weigh that in your decision but if you notice a signficant enough difference in response rates, fewer impressions with more conversions wouldn’t necessarily be a bad thing.

2. Best performing placement
While Run of Site or homepage placements may be cheaper, perhaps they get lost in the noise and don’t get the kind of click-through (or conversion) rates you’re seeing for more targeted placements (on the same site). Really review each placement to see if there is a difference. The more targeted the placement, the more it often costs. Maybe your ad gets great response rates without the targeting – shifting those impressions to ROS would end up stretching your dollar and your campaign as you could potentially run your ad longer. But you’ll never know unless you’re tracking individual placement responses for both click and conversion rates.

3. Frequency caps
Unless you’re sponsoring a placement, running a roadblock or have 100% share of voice, you may want to consider capping the number of times a user can see your ads. This can be particularly effective when you have ROS placements or are running on sites that get a lot of repeat visitors. Working with your agency or the site, get them to place a cap between 3-5 views per user on your ads. Over time, compare the click-through rates and see if they increase, you might be surprised at what you find. I’ve had clients who have suddenly seen their click-through rates jump enough to continue running advertisements with the site in question. You’ll want to use your analytics system to review results and you may need to play with that 3-5 range to find the number that works best for you. Frequency capping shouldn’t cost any extra money and it’s a way to improve the life span of your campaign, and potentially reach more unique eyeballs.

Some of the above mentioned optimizations are difficult to do if you have just sent one creative tag for tracking purposes. It’s important to think about all the different ad placements and sizes on each site up front and to create separate ad tags for each one before the ads are trafficked to the website.

While this creates more work up front, it will allow you to track your campaign accurtely and really learn which site, placement, size or execution is working for you. Relying only on site reporting may not give you the most accurate results as every site and ad server may track things differently. You’ll want to make sure you’re using one tool to benchmark and compare the data equally.

You’ll often notice that when looking at the data, the results vary by site. This is to be expected and as a result of minute differences in visitor profiles and behaviour. You should make sure that before making any blanket decisions you have looked at the data by site as well to make sure any performance generalizing statements you make aren’t the result of one or two sites in particular.

Understanding what works well for each site will help you run the right kind of ads to the right kind of audiences, thus increasing your clicks and your conversions, without spending huge sums of money.

Photo Credit: Woodsy Stock.Xchng

Are you happy you’ve met the traffic goals at the end of the month? Hold on, the data displayed on your Analytics dashboard also includes the internal traffic (visits from staff/internal office network) on your web site. You may have hired some web developers living on the other side of the globe working on your web site. What about their visits while they work on your site? Their visits are also counted in your traffic. So, are you getting the true picture of actual number of genuine visitors coming to your site?

Here comes the role of filters that can be used to exclude visits from specific sources on your web site. Power users are able to harness the true power of filters with the help of custom regular expressions. In our case, we will see how to exclude visits from selective sources so that they do not count in our traffic statistics.

Excluding visits from a network

Let’s assume your company has two offices in different locations. One network has the IP range of 3.67.232.1 to 3.67.232.189 and the other has the IP range of 10.17.115.1 to 10.17.115.137. Now you want to exclude all visits from these two networks to get a better estimate about the actual number of visitors coming to your web site.

To achieve this, simply create a filter as shown below. I’m assuming you are currently in your Analytics dashboard.

1. Go to Analytics Settings -> Filter Manager -> Add Filter.
2. Give an appropriate name to the filter (for exp: ABC Inc Internal Traffic).
3. Select ‘Exclude all traffic from an IP address’ from the drop down list of Filter type option.
4. Now for first IP range our regular expression will be:

^3\.67\.232\.([1-9][1-9][0-9]1([0-8][0-9]))$

For second IP range, the regular expression will be:

^10\.17\.115\.([1-9][1-9][0-9]1([0-2][0-9]3[0-7]))$

For specifying more than one range, we have to separate both expressions with a ” pipe symbol. So the final expression becomes:

^3\.67\.232\.([1-9][1-9][0-9]1([0-8][0-9]))$^10\.17\.115\.([1-9][1-9][0-9]1([0-2][0-9]3[0-7]))$

Write this regular expression in the IP address field. If you find it difficult to create these expressions for your IP range, here is a handy tool to create regular expressions for a given IP range.

Note: In Google Analytics, the maximum allowable length of a regular expression is 255 characters. If your company has large number of networks, you have may have to create several filters to accommodate all IP ranges.

5. Next, you have to select the web site profiles on which this filter should be applied. Select the sites from the left side table and click Add >>. Click Save Changes to complete the filter creation process.

Now all visits from these networks will not be counted in your traffic and you will be able to get a better and clearer picture about the actual performance of your web site.

Excluding visit from a single IP

To reduce the complexity, I’m not discussing about excluding traffic from dynamic IP addresses. The process of excluding traffic from a single IP is same as shown above. All the steps are the same except the regular expression. Suppose, you want to exclude all traffic from IP address 10.31.119.45.

The regular expression for this IP will be 10\.31\.119\.45

Regular Expression Primer

The table shown below will help you in experimenting and creating your own regular expressions.

? -match zero or one of the previous item
. (dot) -match any single character
() -remember contents of parenthesis as item
- -create a range in a list
* -match zero or more of the previous item
-or
^ -match to the beginning of the field
$ -match to the end of the field
[] -match one item in this list
+ -match one or more of the previous item
\ -escape any of the above

If you are hungry for more, here is an excellent tutorial to learn regular expressions for Google Analytics. So what are you waiting for? Go ahead and use this extremely powerful feature of Google Analytics.

A recent article in Internet Marketing News states that Omniture is going to start tracking Twitter conversations for their customers.

From what I gather, it will work similarily to something like Google alerts, allowing you to create a report alerting you to talk of simple words and phrases. It can be set up to send real-time alerts to email or SMS services so that you can be well aware as to what people are saying about your brand as it happens.

What I like about this is that it can be incorporated into their reporting, allowing you the user the ability to take it all in context. You can compare the number of conversations (good or bad) that occured on a given day or time, against the number of traffic you get from the Twitter site.

Not only are you now beginning to get a one-stop shop for all your tracking and monitoring needs, as the article further points out, users will have an unlimited number of keywords to track, which means besides your brand, you could track your competitors as well. Gathering almost real-time intelligence and being ready to jump on any kind of feedback that occurs is vital in such a competitive market, and kudos to Omniture for being the (one of – if not the) first to attempt it in this capacity.

You see, I’m 21 weeks now and already had my second-trimester ultrasound at around 19 weeks. Unfortunately, the baby played shy and they didn’t get a good enough view of all the things they needed to, so I had to go back for another ultrasound. What a difference a couple of weeks make! The baby was much more developed, and thankfully much more cooperative.

Now you might be wondering what redoing an ultrasound has to do with your data – but I promise you there is method to my madness.

You see, perhaps you’ve just launched your site, or a new campaign, and you eagerly go in to your analytics system and check your data. Perhaps the early results you look at suggest things are going swimmingly – or pehaps they suggest the opposite.

Just like my first ultrasound was a little too early to get really good pictures, sometimes a first look at your data can be too early to know the full story. The first data check can give you an indicaition of how things are going, but maybe you haven’t given the data enough time to mature and tell you that story.

If you’re doing an advertising campaign, then chances are you will get a good sense of how things are progressing sooner rather than later, but if you’ve just launched a site and perhaps don’t have a huge marketing budget to promote it, you might need to wait a little longer. You might find the first check shows minimal results, but then a mention somewhere gives you an unanticipated traffic boost.

Here are five simple tips to better analyze your data:

1. Set timeframes for data checks (daily/weekly/monthly – depends on what it is you’re doing and how much traffic you anticipate) and create benchmarks or goals for each data check.

2. Don’t be afraid to adjust the set goals once you’ve got some results under your belt.
 
3. Loweing your goals isn’t a bad thing if that’s what it calls for – but try to find the answers as to why you need to do so (too aggressive, unrealistic, smaller niche, not getting as many ad impressions as originally thought, etc.)
 
4. Look for a story – Data always has a story to tell, good or bad. You just need to look hard enough to figure out what that story is and how it is relevant to your site or business.
 
5. Learn from the results. Don’t just look at them and leave them alone, optimize where you can, or if you can’t optimize currently, write out points for the future of what you will do differently (or keep the same) based on the results.