How to Import MSN Cost Data Into Urchin

June 23, 2006 by Justin Cutroni

Everyone talks about Google Analytics and its integration with AdWords. Sure, it’s really cool. But what about other CPC systems? People seem to forget that you can track AND import ALL CPC campaigns and cost data in Urchin. Yahoo!, AdWords, MSN… You can import cost data for all these services, that’s a feature that currently isn’t available in GA.

To add cost data to Urchin you must export your cost data from your provider and them import it into Urchin. When you import the data file you must specify the format. By default, Urchin does not have an MSN Ad Center format. Luckily Urchin has a flexible architecture that we can use to almost any log format. So I created an MSN data file format. Using this format you can import your AdWords cost data into Urchin.

There is one problem that I’m still working on. It looks like MSN exports the data file (TSV) in UNICODE format. I _think_ that Urchin is choking on the UNICODE and will only process UTF-8 files. The workaround is to open the export file in Excel and then save it which changes the file to UTF-8 (I think). I’m still looking into this…

Subscribe:

More Urchin Reports for Daypart Tracking

June 21, 2006 by Justin Cutroni

Campaign Keyword Conversions per HourYesterday I posted a custom Urchin report for tracking the success of Google AdWords ad scheduling (aka dayparting). Last night I decided to take the report one step further. The new report allows you to drill down from a campaign, to the Keywords within that campaign and finally to the hour of the goal conversion. This report lets you see which keywords led to conversions and at what time. Pretty cool, huh :). Again, if you’re interested in this reports please contact me.

EDIT: Right not this is only working with manually tagged links, not links tagged with master tracking codes. Urchin’s custom report functionality is giving me fits. Grrrrrrrr…

Subscribe:

Urchin Report for Daypart Tracking

June 20, 2006 by Justin Cutroni

Last week Google announced that AdWords would support ad scheduling. This means that you, as an AdWords user, can schedule exactly when your ads display. Here’s the official post from the AdWords team:

Ad scheduling (also known as “dayparting”) lets you tell Google exactly when you want your ads to run, and more importantly — when you do not want them to run. In addition, more advanced users can automatically modify their bids based on time-of-day and day-of-week cycles in campaign performance.

Ad scheduling can also help you improve your ROI by ensuring that your ads run when it makes the most business sense. For instance, a local business may only want to run their ads during business hours, or an online retailer may want to boost their bids during their busier-than-normal lunchtime shopping period.

Urchin 5 Campaign Conversions per HourSo what’s the best way to measure this? Well, if you’re using Urchin 5, you can use my new Campaign Conversion by Hour report. This report will let you drill into a campaign and identify how many conversions you had per hour of the day. Why is this useful? Because when you configure ad scheduling in AdWords it is set at the campaign level. Having a report that identifies the Campaign Conversions per hour helps you more effectively schedule your ads. If you want to learn more about AdWords ad scheduling you can read the instructions.

Interested in my report? Contact me.

Subscribe:

Tracking Dynamic Sites With Google Analytics

June 14, 2006 by Justin Cutroni

In a previous post I mentioned that Google Analytics now offers an installation guide to help users hit the ground running. To further help GA user’s I’m writing a number of posts about common installation and configuration issues. Stuff that I encounter often.

Today I’m going to talk about dynamically generated websites. Dynamic websites use query string variables to change the content that is displayed in the browser. What’s a query string variable, you ask? Query string variables appear in a URL after the question mark. Here’s an example. The query string variables are bolded in the URLs below.

www.foo.com/index.php?aid=23
www.mysite.com/default.asp?cid=3847290&did=44jksaj9

Dynamic websites use query string variables to change the content displayed to the site visitor. The site visitor may request the index.php page 10 times but receive different content in the browser window each time the page loads because the query string variables change with each request. Dynamic sites are usually programmed in a language like PHP, .NET, ColdFusion or Java.

So why does all this matter to Google Analytics? It is important to understand what Google Analytics considers a unique website page. When a user lands on a page the urchin.js tracking code collects the value that is in the location bar of the browser. It then strips off the domain name and considers the remaining string a unique page on the website. This string includes all of the query string variables. Here’s an example.

http://www.website.com/shirts/index.php?aid=123

In the above URL Google Analytics would log a hit for /shirts/index.php?aid=123 in your reports. The query string variable aid=123 is important because it indicates the content that was shown to the visitor. If you are the site owner aid=123 will probably mean something to you.

If you have a dynamically generated site you really don’t need to do anything to your GA configuration. As described above, GA uses all of the query string variables when creating unique page names.

But what if you have a large number of query string variables that are not relevant to the content being display? You can configure each GA profile to exclude certain query string parameters when creating unique page names. This setting is found in the ‘Main Website Profile Information’ section of the profile settings. Simple list all of the query string parameters that you want GA to ignore.

Analyzing your query string parameters and excluding the irrelevant ones is a very important step in configuring your profile. Here’s why. Imagine that every visitor to your website has a unique identifier in the query string. That means that there will be a huge number of unique URLs that really aren’t unique. Your reports will be filled with useless data. Make sure you configure your profile to exclude all session identifiers from the query string variables.

Subscribe:

Check Your Spreadsheet

June 14, 2006 by Justin Cutroni

Yesterday I posted about liberating your data from your analytics application. In that post I mentioned that Microsoft Excel is a great data analysis tool to use when exporting information out of your web analytics application. Just now I sat down and read yesterday’s Wall Street Journal and the Talking Tech column by Lee Gomes is all about spreadsheet errors! Talk about timely :)

Ray Panko, a business professor at the University of Hawaii has studied spreadsheet errors for over 12 years. He offered some insight into the error that he sees most.

There are three basic types of errors. One of them is a mechanical error, where you point to the wrong cell when entering a formula or you type in the wrong number. People catch many of those errors. then there are the logic errors-either you don’t have the correct algorithm in your head or you express it incorrectly. But the most dangerous one is omitting something, leaving something out of the spreadsheet. The detection rate for these omissions is close to zero.

Pretty scary, huh? This means that people could be making decisions based on incorrect data. Just imagine that you recommend a marketing manager stop advertising on AdWords because the quality of traffic is poor only to discover there was a bug in the spreadsheet that you used for data analysis. So, what advice does Ray leave us with?

The No. 1 thing is that they’ve got to spend about a third of their spreadsheet development time testing. If they don’t do that, nothing else helps.

That sounds like a software development manager talking! He also recommended locking the spreadsheet so that users can not accidentally type a number in a formula cell. That too sounds like a software development technique. Protect your code from user input, you never know what those crazy users might do.

Subscribe:

Set Your Data Free

June 13, 2006 by Justin Cutroni

One of my rants is about web analytics data. Not how accurate it is, but where it belongs. For the majority of people the default reports in a given web analytics package are completely adequate. New visitors, returning visitors, campaign tracking, click path analysis… they’re all very helpful and can be used to optimize your advertising and site content. But, in my opinion, once you really start to dig into the data you begin to realize that no application will support the key performance indicators (KPI’s) that are specific to your business model. And that really is the key. What are the specific performance indicators for your business and how do you measure them?

What’s the solution? Set your data free! Export it from your analytics package and move it to a venue where you can perform your analysis based on the KPI’s that you’ve defined for your business and your website. You could use something as simple as Microsoft Excel. Eric T. Peterson has an outstanding book called the The Big Book of Key Performance Indicators. It comes with an Excel KPI worksheet that you can configure for your use. If you haven’t worked with analytics data outside of your analytics application give it a try, it’s very liberating! And Eric’s book is a great place to start.

Now, I know what you’re saying. “But there is no easy way to export my data from my analytics application.” Believe me, I know. We use Google Analytics & Urchin Software and neither are very friendly when it comes to exporting data. There are export features in both but they require someone to manually click on a link in a report to start the export. Not very usable, especially when you need to export data every day of the week! This shortcoming has not stopped us. We have pieced together solutions that automatically extract an XML or text export of the data so we can work with it in other applications.

For Urchin, there is u5data_extractor.pl, a Perl script on the support site that can be used to export data in text format. We use this script to automate the delivery of analytics data to the appropriate analytics. Then the analyst can import the data to another application for analysis. Depending on your needs you may be able to automate the extraction from Urchin and the insertion into your analysis application.

Getting your data out of Google Analytics is a bit more difficult. I don’t want to give away the screts, but if you know some basic web scripting (including cURL) you should be able to write a script that extracts data from the GA reports.

Do you feel empowered? I hope so. Get out there and start extracting your data!

Subscribe:

Tracking Offline Advertising With Google Analytics

June 12, 2006 by Justin Cutroni

With a little bit of programming you can use Google Analytics to track your off-line advertising activities. There are some caveats, but I believe the process below is reliable and convenient for customers. Here’s how to set it up.

First a disclaimer. I’m not going to go over the basics of how link tagging works. If you don’t know how to use GA to track your advertising campaigns stop reading this and start reading the GA support docs :)

Now we can get down to business!

The key to tracking off-line advertising with GA is link tagging. If you can tag the links in your off-line ads with the campaign variables used by Google Analytics you can measure off-line advertising success the same way you measure on-line advertising success. I know what you’re thinking. “How am I supposed to put some long, archaic URL in my ad?” The answer is, “you’re not”. There’s a trick to the implementation.

Instead of attaching the campaign variables to the URL that you place in your off-line ads, create a custom URL or a custom landing page. When a visitor requests that custom URL or landing page do a page redirect (using your application platform) and dynamically append the tracking variables.

Here’s an example. Let’s say I’m an online retailer, www.jeans.com, and I’m getting ready for a back to school sale. I decide to purchase a number of print ads in a newspaper. Within the ads I place a URL to my website. With a normal GA campaign tracking setup the URL in the ad would look like this:

http://www.jeans.com/?utm_campaign=fall_sale&utm_source=local_paper&utm_medium=print

Or, if I’m using a master tracking table the URL might look like this:

http://www.jeans.com/?utm_id=1

Those are ugly URLs! No one will remember those! Let’s use a custom URL that’s easier for the reader to remember. Then, when they land on the custom page we’ll do a redirect and dynamically append the tracking variables. Here’s the custom URL we’ll use:

http://www.backtoschooljeans.com

And here is the code that we’ll use when someone requests that URL:

< php

header(”Location: http://www.jeans.com/?utm_campaign=fall_sale&utm_source=local_paper&utm_medium=print”);

exit(0);
?>

There you have it. Your off-line ad is now tagged like your on-line ads. You’ll be able to see traffic and conversion for your on-line and off-line advertising in the same GA reports.

One thing to note. If you publicize the custom URL in other locations you’ll end up driving traffic to the page that is not from the off-line ad. This will skew your results.

Subscribe:

What is $Index?

June 9, 2006 by Justin Cutroni

Alex had a very interesting post yesterday about optimizing your site using Google Analytics. He explained how to evaluate the value of certain pages on your site using the page $Index value. But what is $Index and how is it calculated?

Here’s what the Google Analytics help documents say about $Index:

In addition, the $Index calculates the value of each page, based on the number and value of conversions occuring after a visit to that page.

Let’s explore this a bit deeper. Assume that a visitor to your website follows this path:

Page 1 >
Page 2 >
Page 3 >
Page 2 >
Page 4 >
Goal 1 Page >
Page 5 >
Transaction Page >
Exit

When Google Analytics examines the path to determine the $Index value for each page in the session it removes the duplicate hit to Page 2. So, our new session path will appear as:

Page 1 >
Page 2 >
Page 3 >
Page 4 >
Goal 1 Page >
Page 5 >
Transaction Page >
Exit

Goal 1 is defined as having a value of $10 and the Transaction has a value of $15.

In our example we can say that Page 1, Page 2, Page 3 and Page 4 all led to the user reaching Goal 1 and completing the transaction. Beacuse the user hit each of the pages on their way to completing the goal and the transaction each page is assigned a $Index value of $25 ($10 for the goal + $15 for the transaction).

Page 5 is given a value of $15 beacuse it only led to the completion of the transaction.

So, if our Google Analytics reports only contained data for the single session above then we would have the following $Index data:

Page Name $Index
Page 1 $25
Page 2 $25
Page 3 $25
Page 4 $25
Page 5 $15

Your site will have more than one session (hopefully!). To calculate $Index across all sessions Google Analytics cross eaxmines all sessions, analyzing the click paths and goals/transactions completed for each session. It them calculates $Index based on the revenue that a page helps generates and the number of sessions that the page appears in. In formula form, $Index is calculated as follows:

total revenue page X helps generate / number of sessions that includ page X = $Index

Let’s go back to our example. If there were nine more sessions that hit Page 3, and none of those sessions reached a goal or completed a transaction, then the value of $Index for Page 3 would be $2.50 ($25 revenue from Page 3 / 10 sessions including Page 3 = $2.50).

The power of $Index is that is tells you which pages lead to conversions on your website and which pages don’t. Now you can start to optimize the low value $Index pages and keep the high value $Index pages in your conversion paths.

Good Luck!

Subscribe:

Google Analytics Installation Guide

June 9, 2006 by Justin Cutroni

Today Google Analytics posted a Google Analytics Installation guide on their help site. This nine-step document provides an organized process you should follow when planning a Google Analytics installation.

To further demystify the installation and configuration process I’ll post more information about each step mentioned in the guide. My goal is to help everyone grasp the fundamental way Google Analytics works so they can understand how certain configurations affect the report data.

One thing I will suggest right now is to create an implementation plan. Use the installation guide! Before you slap the JavaScript tracking code on your website review the guide so you know how you may need to change your website to work with GA. Having an installation plan is very important. It will insure that the data in your GA reports is appropriate for your analysis needs.

Subscribe:

It’s The Trending

June 5, 2006 by Justin Cutroni

Clients often ask me to validate or reconcile the statisitcs that Google Analytics or Urchin Software provide. This is an almost impossible task that can take weeks to accomplish and usually provides inconclusive results. I advise clients to begin by focusing on trend in the data over time rather than laboring over the details of the numbers. Then, when they make specific changes to their site or advertising they can dig deeper into the data because they know exactly what changes were made.

So why do I bring this up now? I read two interesting things this morning that reminded me of this tenant.

First, Bob Cringely has a great piece about Google and their AdWords customer service. The article was actually about a friend who was fighting Google over some AdWords charges. In the article Bob references some visitation data that his friend used in his fight with Google:

Visitor Sessions per Month
Jan 06 = 386 visits (Google account active = $555.99)
Feb 06 = 328 visits (Google account active = $1,893.26)
Mar 06 = 348 visits (Google account paused)
Apr 06 = 410 visits (Google account paused)
May 06 = 916 visits (Google account active = $751.03)

These data show the number of visits and the Google AdWords cost per month. The numbers are interesting for the client because the visitation increased even when his AdWords account was paused. Those are some pretty important numbers, especially when you’re evaluating the value of your Google AdWords account. Notice they’re not some obscure statistic about the number of times a manager clicked on a blue icon. It’s just visits per month, a nice standard analytics number that can help the individual make an informed business decision.

Second, Avinash Kaushik has a fantastic blog post titled Data Quality Sucks, Let’’s Just Get Over It concerning the quality of analytics data and how to make informed decisions based on your comfort level with the data. He stresses that you will not get 100% accurate data but that doesn’t matter. You can still make informed decisions with the data that your analytics solution provides. It is a really great post and I highly recommend giving it a read.

Subscribe: