Using Excel to its Full Potential – Part 2 Separating Data

Tuesday, September 29th, 2009

Table of contents for How to use Excel to its Full Potential For Data Analysis

  1. How to Create Pivot Tables and Why Marketers Should Use Them
  2. Using Excel to its Full Potential – Part 2 Separating Data

Excel is a powerful tool when you have to evaluate data – if you know how to use all the bells and whistles it offers. The idea for this series comes from a post (which I’m labelling as the first part of this series) onhow to create and use pivot tables. I got (and still get) a lot of on and offline questions and feedback about it. Most of us know the basics of Excel, but unless you’ve take a training course, you don’t know it’s true power. I know I don’t know all the tips and tricks, but I do know a few things that should help make some of the things you might want to do a little bit easier.

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.

Previous in series

29.09.2009 analytics tools No Comments

What Vanity URLs Can Tell You

Wednesday, September 23rd, 2009

If you’re a not-for-profit using canvassing techniques – whether it is traditional door-to-door tactics, presence at a community event, or some other form of human connection – it can be difficult to track your success if you don’t capture donations on the spot.

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.

23.09.2009 Branding and Marketing, Direct Marketing, Integrated marketing, Web analytics 2 Comments

Using Real Time Stats Helps Leverage Traffic

Wednesday, September 9th, 2009

suec_thumbnailNew 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.

09.09.2009 Web analytics, analysis, analytics tools 3 Comments

Crying Babies & Website Visitors Are More Alike Then You Think

Tuesday, September 8th, 2009
My daughter in mid-wail

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.

Meredith in a better mood   MJ7weeks  Waving

08.09.2009 analysis 2 Comments