June Dershewitz on Web Analytics

How to Compare 2 Lists of URLs Using Excel

Sometimes I need to compare 2 lists of URLs and find out how much they overlap. Which URLs are only in list #1? Which URLs are in list #2? Which URLs are in both lists?

Here’s an example: say I’ve got one list of URLs from my Web analytics tool (like Content>Top Content in Google Analytics) and another list of URLs from a sitemap-generating spider. Comparing the 2 lists lets me see which pages are actually getting traffic on my site versus which pages are reachable by spider. Although most URLs that get traffic are also spiderable, it’s also likely that some URLs get traffic but aren’t spiderable (like thank you pages) and some URLs are spiderable but get zero traffic.

I've developed a quick and easy way to do this kind of comparison using Excel. You can download it here. I’ve included step-by-step instructions for you to follow. There are 8 steps:

1. Paste in your first list of URLs
2. Standardize them
3. Pivot to remove duplicates
4. Paste in your second list of URLs
5. Standardize them
6. Pivot to remove duplicates
7. Concatenate the standardized, deduped lists and add a "magic" code
8. Pivot again to get results

All the details are in my example spreadsheet.

If you're a programmer I’m sure you have a fancier way to compare lists. However, the beauty of my method is that anyone can do it as long as they have a copy of Excel and a basic understanding of pivot tables.

Speaking of pivot tables, if you've never created one I encourage you to learn right this minute. It's something that every single Web analyst should know how to do. If I ever interview you for an analyst job, even an entry-level one, I will quiz you on pivot tables.

What do you think? Do you have an even easier method? And, as a Web analyst, why have you needed to compare lists of URLs?

Career Advancement for Veteran Web Analysts (Article and Slides)

Whether you got your start in Web analytics just recently or ages ago, you will inevitably go through the “newbie” stage and come out the other side. You break into the field, gain some skills and eventually find your niche. So now you’re a veteran Web analyst, what’s your approach to career advancement?

That's the focus of an article I've contributed to Online Behavior, Daniel Waisberg's wonderful new marketing measurement portal: Career Advancement for Veteran Web Analysts.

The article was based on a presentation I gave at eMetrics San Jose earlier this year. If you just can't get enough of this topic, here are the slides from my talk:

Career Advancement for Veteran Web Analysts

Thoughts on Mobile Paid Search (and My SMX Slides)

Last week I participated in a panel discussion on Mobile Paid Search at SMX Advanced in Seattle; I have just posted my slides on SlideShare. As a practice, mobile PPC is still in its infancy and it was an honor to share the stage with mobile marketing thought leaders such as Cindy Krum and Greg Sterling.

When I wasn't speaking I was taking notes. Here are 3 of my favorite take-aways from the session:

• Use AdWords? Your PPC ads might show up on mobile devices already. You are opted in unless you have explicitly opted out. In my own presentation I gave an example of a paid search listing that ran in identical form on PC and mobile - and failed miserably on mobile. If you're going to take part in mobile search, make sure it's an intentional choice and cater to the medium.

• Mobile PPC is full of open questions, such as: Are mobile searchers closer to conversion point than PC? Are mobile search phrases shorter? Do short vs. long phrases convert better? Comparisons between traditional PPC and mobile PPC surfaced frequently during the panel, but the panelists' conclusions were often contradictory. We have a long way to go before mobile PPC benchmarking reaches a mature state.

• Trying to decide whether to jump in and start doing mobile PPC? These verticals already get a lot of mobile search activity:
  
  • Retail, entertainment, travel (& any business w/local component)
  • Financial
  • Mobile-specific goods & services

If you'd like to read more, check out my presentation.