One of the most common mistakes in web analytics is assuming that all numbers can simply be added together. This works in many cases but there are a few common metrics where you can easily run into a duplication problem. Some common ones are:

Visits/Visitors
Lets say that a visitor (in a singe visit) comes to your site and performs a search for “dress” and then for “skirt.” If you were then to look at your Internal Search report you would expect to see one visit for “dress” and one visit for “skirt.” This is correct because, in fact, each of these keywords got a visit. However, what if you wanted to know simply how many visits performed a search? It sounds simple but, in the absence of any other reporting, some are tempted to add the one visit for “dress” and the one visit for “skirt” in order to get a grand total of two. With this information they can then report to their boss that of the one visit to their site a total of two visits engaged in search. Doesn’t make much sense does it? In this case the one visit for “dress” and the one visit for “skirt” should still be one because it it the same visit. This example is a little absurd but keep this in mind because with real data, where only a portion of your visits engage with search, the mistake may not be as obvious.

Orders
This is another one that sometimes gets people. Let’s say that I’m looking at a products report and there are two products (A and B) that were purchased on my site, each with one order. What would my total orders be? Someone that is too quick to add may say two. Someone that read the Visits/Visitors example may be too quick to say one. In the end, though, you really don’t know by just looking at this report. These products may have been each purchase in two separate orders or in the same order. To get to this information you will need to have a different report that gives you de-duplicated orders (a total orders report.)

Rule of thumb
The easy way to determine if the metrics that you are using are additive or not is to ask yourself if the metric that you are viewing (revenue, orders, page views, visits …) could potentially span multiple values of the dimension (product names, keywords, page names …). For example, In the case of products and orders, the question would be “Could a single order potentially span multiple products?” The answer here is most likely “Yes” (not always, though) and you need to be careful to not fall into the trap of duplicating metrics.

Another interesting case to think about is visits, entries, or single-page visits in a pages report. If you were to just sum the visits in this report then you would have a serious duplication issue being that a single visit can span multiple pages. However, you are safe summing entries and single-page visits because these metrics are limited to only a single page. If you were limited to just this report you could, though, get a de-duplicated visit count by using the sum of entries across all pages. This is because the ratio of total visits and total entries is always one and summing the entries does not put you at risk of duplication. Really, though, if you want this number it’s easiest to just use a totals report but it’s something to think about.

I was once a pretty decent blogger; however, the last post that I did was more than a year ago. Pretty pathetic. It’s ironic how the more I have to blog about the less I blog. Oh well. I am going to try to pick up the pieces and produce some more interesting content. Not now, though. I think I’m going to hit the sack. G’night.

Coming up with the last day of the month in Excel is simple but not readily apparent. By using the DATE function in combination with the YEAR, MONTH, and DAY functions, all you have to do is take the next month (MONTH(reference)+1) and then set the day to zero (DAY(0)). The final formula would look like this:

 =DATE(YEAR(reference),MONTH(reference)+1,DAY(0))

Here’s an example. You are responsible for managing your marketing budget. You are given a budget forecast for the next year that lists the budget in monthly amounts. Let’s also suppose that for your purposes you need to know your budget on a daily basis and your budget varies from month to month (you can’t just divide the total by 365). Using the method above to find the last day of the month you can build out a worksheet like this:

The first two columns are the dates and the budget which was given to you. The third column gives us the days in the month that we can then use as the denominator with the budget as the numerator to get the Daily Budget. The formula for this column would look like this:

=DATE(YEAR(A2),MONTH(A2)+1,DAY(0))-A2+1

What this does is take the last day of the month, subtract the first day of the month, and then adds one (because we subtracted dates up to and including the first day). Dragging the formula down will then provide the appropriate information for all months.

For the longest time I would always sort my data by using the the sort feature under the Excel Data menu. The thing that always bothered me when doing this is that it’s not automatic. If I am working with data that is constantly being updated then I will have to constantly be running this sort. Here’s a way that you can create sorted data that is based on unsorted data.

You can start by getting some numbers (using the RANDBETWEEN function) into column B. These will be the numbers that we want to sort into column C. We can do this by using the LARGE(array,k) function which will return the n^th (or in this case the “k^th“) largest number in the array that you select.

For the array we’ll reference the numbers in column B but we still need something to put into the k parameter to designate which place we are at in the sort. To accomplish this I use the ROW function. Doing this avoids having to create another column with the numbers needed for k. To get the number we need we can take the row of cell C3 and subtract the row of cell C2. For C3 this would give us 1, which, when used in the large function, will give us the 1st largest number in the range.

The formula in cell C3 will come to look like this:

=LARGE($B$3:$B$12,ROW()-ROW($C$2))

You can then drag this down to fill the rest of the rows and you now have your data sorting automatically no matter what is in column B.

What if I have multiple columns that need to be automatically sorted?

Likely if you have a big block of data that you are sorting by one column you’ll want the other columns to move with the sorted column. To do this you can create one column that does the initial sort as described above and then you can use an INDEX MATCH combination below to take the value returned by the LARGE function and use that to find the rest of the information that should be on the same row.

=INDEX([array to return],MATCH([lookup value],[lookup array],[match type]))

Here’s an example workbook:Auto Sort

This idea behind the Google quality score is a funny thing. The name implies that it is something unquestionably good and and that you should definitely want it. It is an interesting concept because by having a superior quality score you could actually bid less then someone else but still get placed in a higher position. Sounds like a great deal but this may not be what you really want.

The way that Google figures your quality score is simple. Whoever will provide the most total revenue will get first position and then it is ranked in descending order. This means that if you are bidding 10 percent less than the next guy but people like your ad and are clicking on it 50 percent more  then you will get the first spot. Doing this will not give Google more per click but it will be more as far as total revenue. 

If this happens for a profitable keyword then you are in good shape (as long as it isn’t crowing out an even more profitable keyword). However, if your quality score is good but then the keyword doesn’t convert (let’s call it the conversion score) then Google will keep serving up that ad regardless of what would be better for you.

This means that if you ever receive some promotion from Google, Yahoo, or any other search engine that will give you some sort of click optimization package then be careful. This is a good thing for profitable keywords but could conflict with your best interests when the quality score is good but the conversion score is bad. So, beware! Use a quality analytics package in connection with your PPC campaigns to optimize around conversion and yield.

I read in MarketingVox that 96 percent of Travelocity’s bookings come from branded keywords. You would think that is too high to be true but I wouldn’t say that it is unrealistic. I would call it the high end of the norm but not unbelievable. It’s a little strange though when they say that the superior performance of the branded keywords “casts the practice of buying non-branded industry or product-related terms in a suspect light.” Branded keywords are so much different from non-branded keywords that it is almost an apple to celery comparison.

The biggest difference between the two is where the customer is at on the sales cycle. How does it go? Something like awareness, acquisition, conversion, and then retention. Well if someone is coming to your site off of a branded keyword then they are definitely past the awareness stage and you are exactly what they are looking for. The may even be a life long customer that makes a purchase every week and simply likes to use a search to navigate to your site rather than going through the agony of typing a .com on the end of your name.

On the other hand, people who come in off of non-branded keywords are just getting familiar with you, or, perhaps you aren’t managing you negative keywords and you are nothing like what they want. Comparing the two is much like saying “that’s strange, people inside my store convert a lot better than those that are hanging out across the streets.” Of course they are going to convert better.

Another big difference is the space your content is taking up on the search results page. If someone types in a branded keyword then you are going to appear in the sponsored listings and you are likely going to have another result high in the organic results. This gives you twice the chances of others to be noticed.

Do non-branded keywords have this advantage? Not for most sites. If you do a Google search for flowers then you will see that the top three sponsored links are also the top three organic sites. After that, though, there is quite a difference. Those that are in the top organic spots aren’t only fortunate in that they get the organic traffic but they are reinforcing their position with the paid links and visa verse. That just means a little more work (more like “a lot”) for everyone else.

So, I guess the idea behind this post is to say that bidding on branded keywords isn’t some genius idea. It is the most obvious and ideal spot to start your bidding. It doesn’t mean that you should stop there but you should definitely invest into branded keywords before any other.

If you’re looking for a good way to understand how information is being transferred to your computer from the web then I would recommend looking into the Charles Web Debugging Proxy. It can tell you things about how a site works better than just by observing with the natural eye. For example, I was looking into the way that Commission Junction handles the affiliate links that it manages and I found that Charles HTTP monitor very useful. By setting it to record while I was poking around I found that Commission does a triple redirect before landing you on the page of the advertiser. I also wanted to know the value of the document.referrer browser property. Would it be empty? Would it have the last redirect down as the referrer? Would it maintain the original referrer? Well, through viewing the information captured by Charles I was able to see a summary that answered all of my questions.

This is a very useful tool in connection with web analytics and JavaScript web beacons. You can learn the whole story of what is happening on the page load or even if some image request is fired off after the page has loaded.

I just tried the “call” link for the first time while doing a search on Google Maps. I was looking for a Little Caesars Pizza and I thought the process was pretty slick. Initially I clicked on the link because I was thinking “this link is worthless. Maybe if I was on my cell phone then I could…wait a sec.” I then was presented with a prompt to enter my phone number. Google then called me with the business’s number showing up on my phone. When I answered I heard a voice say “connecting” and shortly I was greeted by the pizza guy on the other end.

The best thing about it was that Google then stored my number so that I don’t have to type it in again. Now, not only are online phone books easier to search and broader in scope, but now you don’t even have to go through the gruelling effort of dialing your number. Thank goodness for that.