The Direct Approach

Using Excel to its Full Potential – Part 2 Separating Data

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

How to Create Pivot Tables and Why Marketers Should Use Them
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.

: Choosing Your Delimination Type

: How your data is deliminated

: Formatting options

: Your finished data

Previous in series

Share and Enjoy:

29.09.2009 analytics tools No Comments

What Vanity URLs Can Tell You

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.

Share and Enjoy:

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

Using Real Time Stats Helps Leverage Traffic

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.

Share and Enjoy:

09.09.2009 Web analytics, analysis, analytics tools 3 Comments

Crying Babies & Website Visitors Are More Alike Then You Think

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.

Share and Enjoy:

08.09.2009 analysis 2 Comments

Learn To Trust Your Instincts

Recent personal and professsional experiences have inspired this post, which may slightly resemble a rant.

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.

Share and Enjoy:

04.05.2009 Web analytics, online advertising 4 Comments

Why Shorter Newsletters are a Win-Win for Everyone

Skimming a new site that launched today – Association Jam – which basically is a niche Digg-style website for associations and non-profits (a brilliant idea from the folks at Wild Apricot!) – I found a favourited post discussing three trends that beg for shorter emails.

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.

Share and Enjoy:

23.04.2009 Web analytics No Comments

How to Optimize Your Online Ad Campaign On a Budget

Often when the topic of optimizing your online advertising campaign comes up, you might think of it in terms of changing or tweaking the actual ad creative which can sometimes be costly.

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.

Share and Enjoy:

14.04.2009 Web analytics, campaign tracking, online ad measurement 2 Comments

How To Use Filters In Google Analytics To Remove Your Own Data

The following guest post is from Rajeev Edmonds, also known as mintblogger. His blog was one of my favourites long before I got to know him through the Authority Blogger Forum. His vast technical knowledge of what you can do with Google Blogger and Google Analytics definitely surpasses my own. Today he discusses how to properly use filters in GA to exclude your own internal data (among other things). Enjoy!
************************************

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?

Share and Enjoy:

20.03.2009 Google Analytics 2 Comments

Omniture to Add Twitter Tracking Options to SiteCatalyst

I’m probably a little late to the party on posting this news, but because I’ve not always been kind to Omniture, and since I think this could be a great new tracking tool, I’ve got to share it.

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.

Share and Enjoy:

16.03.2009 Web analytics, brand advertising 3 Comments

What an Ultrasound Can Teach You About Your Data

As per my last post, yup, I’m pregnant, and it’s funny how going for something as simple as an ultrasound can trigger an idea for a blog post.

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.

Share and Enjoy:

10.03.2009 Web analytics 2 Comments

Subscribers

Using Excel to its Full Potential – Part 2 Separating Data

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

What Vanity URLs Can Tell You

Using Real Time Stats Helps Leverage Traffic

Crying Babies & Website Visitors Are More Alike Then You Think

Learn To Trust Your Instincts

Why Shorter Newsletters are a Win-Win for Everyone

How to Optimize Your Online Ad Campaign On a Budget

How To Use Filters In Google Analytics To Remove Your Own Data

Omniture to Add Twitter Tracking Options to SiteCatalyst

What an Ultrasound Can Teach You About Your Data

Previous Posts

About Me

Archives