Wednesday
Jan132010

Enhanced Campaign Tracking and Analysis in Yahoo Web Analytics using Bulk Upload Worksheet

Background

This document is intended for Yahoo Web Analytics power users who want to be able to create rich drill down analysis capabilities for campaigns. It assumes you understand the concepts of relational data, and the importance of creating hierarchical data sets. It would also help if you were familiar with Omniture SAINT functionality, which this document is showing you how to replicate in Yahoo Web Analytics, though this is not essential.

I wrote this document because I wanted to replicate the campaign classification and hierarchy approach that Omniture have done such a great job with through their SAINT engine. This document is based on the approaches we have developed for our clients as a YWA partner.

In this document you’ll learn how to create a rich campaign hierarchy based on campaign metadata, which will allow you to easily analyse your campaigns with a powerful drilldown capability.

*This document is pretty long (so apologies for this) - I’ve saved a version of this for download from Slideshare.

Some basics around campaigns in YWA

The out-of-the-box campaign setup (for every form of media except paid search) process for Yahoo Web Analytics is quite tedious for anyone who wants to launch agency-size campaigns and to have the ability to analyse a campaign and drilldown into various properties of a campaign (for example by placement).

The way that Yahoo allow you to create campaigns is based on three different criteria:

  • Properties – basic name / description / dates / visibility and cost type
  • Categories – up to ten different categories are available for each campaign (but each campaign cannot have multiple categories)
  • Settings – criteria for detecting the campaign through landing pages or url parameters (noting that only one campaign code is permitted per campaign)

These criteria become rather limiting when you want to analyse large campaigns, or components of campaigns. For example, a typical campaign in the Yahoo language would be something like “Banner Campaign – January – Facebook” whereas in reality a typical media buy is going to involve a number of sites, placements and ads in rotation – from my experience in working agency side we might have 5-10 sites, maybe 2-3 placements per site and perhaps 3 ads in rotation. Using this simple example a ‘typical’ campaign might have 90 or so combinations of sites / placements / ads all under the same campaign umbrella.

The objective of this document is to show you how to successfully replicate the content of your media plan, to create campaigns with multiple categories and to create formulas in Excel to help create a structured process for ongoing campaign management. As with any instructions in this field, we would encourage you to adapt this process for your own needs – making sure you document the process for your clients / agencies etc.

Exclusions

This setup does not include setup for paid search and integration with paid search engines. The YWA user guide contains in-depth instructions on this. 

This document only shows how to setup a rich campaign tracking environment within the YWA data centre, it does not show you how to analyse the data!

Introducing the process

Through trial and error we have created a six step process to enable rich campaign tracking and multi-dimensional analysis

Step 1: Setting up campaign categories

The first thing we need to do is setup your campaign categories from the ten that YWA makes available. You will need to use at least 2 of these categories to enable the correct relationship between campaign and properties, and all other categories can be used to describe your campaign in more detail:

In the example above we have shown an example using all 10 categories – the 2 you absolutely must setup are #2 Campaign Name (used to create a central container for campaigns in the system) and #10 Unique Identifier (a helper field used in the excel workbook to create tracking codes). Please review the example categories set here to give you a feel for how you might be able to setup categories.

Step 2: Download the bulk upload template

Next step is to download the template that YWA provides for offline campaign management. Access this file by going to ‘Campaign management’ and clicking on ‘Enter bulk campaign management’.

From the next screen download the bulk campaign management template by clicking on the ‘Download campaign settings (xls)’ link.

This will download the YWA campaign template in Excel format to your desktop. From here, open up the file and take note to review the document by reviewing the notes in Row 7. If you have already setup campaigns in your account, these will be included as rows in the spreadsheet – they will have an ID in column A and a checksum in column W created automatically by Yahoo – do NOT edit these columns for existing campaigns!

You’ll notice from column R you can see the campaign categories you have created.

Step 3: Setup some unique id formulas

The first thing we are going to do inside your workbook is add some simple formulas that will help automate the process of creating unique campaign names (column B) and tracking codes (column K).

If you weren’t following this guide, you would be able to setup new campaigns by filling out the various columns, making sure that you use the dash – character for any empty columns before uploading to YWA., if you recall what we described earlier this way would give you one row per campaign.

In our ‘advanced’ approach we will be using a unique campaign name in column B and using the new campaign name (created as a category) to allow ‘friendly naming’ of campaigns.

In column B you will need to add a formula to create a unique campaign identifier:

=LOWER(CONCATENATE(IF(C9="","ERROR",IF(C9="Paid Search","sem",IF(C9="Affiliate Program","aff",IF(C9="Email Campaigns","eml",IF(C9="Banner Campaigns","ban","oth"))))),".",LEFT(R9,3),".",LEFT(S9,3),".",TODAY(),".",Z9))

What this is doing is using a bit of simple string manipulation and concatenation to populate a string constructed as follows:

[campaignType].[first3charsofCampaignName].[first3charsofMediaOwner].[todaysDateasSerial].[uniqueIDfromCategory]

Which when built looks something like:

ban.jan.yah.40191.016

The second formula we’ll be using will simply take this campaign identifier and use it to populate the campaign code key for the querystring (this string is what you’ll need to use with your adserver to correctly track the campaign from click). This is a much simpler formula and will be added to column K, so in row 8 (assuming you have an empty spreadsheet) add:

=CONCATENATE("?cid=",B8)

You’ll then drag both of these formulas into all rows you’re populating in your spreadsheet. At this stage you might see some strange things – because you haven’t yet populated your metadata (descriptions) for your campaign.

Step 4: Populate your metadata

Now that the formulas are in place we’ll want to fill out the columns of the spreadsheet, I won’t run through everything in depth, but will show you how to ensure the querystring tracking is setup correctly:

A few things you’ll need to bear in mind:

  • There are a number of undocumented combinations of costs that cannot be entered – for example an Affiliate Program cannot have a FIXED cost setup, and must be setup as cpa/cpc
  • Column M for cost must be formatted as text (yes this is weird, but if you don’t do this you’ll get an error)
  • Same for Column P for quantity – format as text
  • For the date columns you’ll need to enter the date using a text entry format prefixing with an apostrophe, otherwise YWA will throw an error with the incorrect date format – so instead of 01-13-10 (or 13-01-10 for my American readers) you’ll enter ‘2010-01-13     

Step 5: Upload and note for errors

Next step is to review your workbook, making sure that the cell formats are correct (see above) and that any empty cells contain an apostrophe ‘ to indicate they are empty. Save the file and go back to YWA and try to upload it.

If it works, congratulations! You’ll get a green success message telling you how many new campaigns you’ve uploaded (remember we have a campaign name category so we haven’t ‘actually’ uploaded this many campaigns).

If it fails, I’m sorry! You’ll get a rather cryptic error message that doesn’t really make sense. My experience shows me the errors you’ll typically receive are because of incorrect cell formats or because you’ve go invalid cost / quantity combinations setup. Ideally YWA would have good error messages here, at this time you’ll have to experiment I’m afraid.

Step 6: Review your setup with Custom Reporting

If you’ve successfully uploaded, the immediate benefit is that all of your campaign metadata will be available for review (even without any click or conversion data). This is quite cool – especially for debugging purposes.

If you go to the ‘Marketing > Campaigns > Campaign summary’ report you’ll be able to drilldown into your data, per your category setup – this will allow you to simulate the relationships between the data and allow you to start planning for custom reports and segmentation exercises.

Word of warning: with 10 categories setup your campaign summary report is going to be pretty messy (see below) so I’d recommend using custom reporting to set this up in a way that is a little more legible and easier to make sense of:

Conclusion

With your campaign setup process in place, you’ll now have a campaign analysis tool that is getting as close to the depth you can provide with Omniture Site Catalyst (Omniture allows much deeper classification but without the drilldown analysis capabilities) and more flexible than you could achieve with the long querystrings required by Google Analytics (although setting up in Google might be much easier for many organisations).

Additional Thoughts

If you’re using Atlas as your adserver, getting into this level of setup is quite easy with the ability to upload traffic sheets, if however you’re using DFA then you’ll probably not want to get into this kind of depth. An alternative for businesses on the DFA platform would be to use the DFA api to pull placement data and upload into YWA through the upload api. As Yahoo are currently updating the api’s this is not something we’re exploring at this time. 

 

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
All HTML will be escaped. Textile formatting is allowed.
« Setting up traffic source bounce rate in Yahoo Web Analytics | Main | Year of the Tiger - Gong Xi Fa Cai! »