Richard Kraneis Blog

Pivot Tables and Web Analytics

Each year I use web analytics for each customer to describe the last year. Maybe you can call it a progress report.

In January 2008 I crunched some numbers for one client (confidential, of course).

Let’s say my client’s hypothetical website name was www.superdupersrollerskates.com . It isn’t but let’s say that’s the case.

Let’s also say, that the majority of their website visitors come because they remember the website name: www.superduperrollerskates.com

And finally, hypothetically, understand that www.superduperrollerskates.com makes the finest molybdenum roller skates in the world. Last year, a pound of molybdenum cost about $35. But I believe it’s sold by the ton. So these are pricey roller skates.

The client sells its share of fancy molybdenum roller skates. But that’s exactly the problem.

When I did my analysis for the roller skate company, I took traffic logs from www.ConversionRuler.com , aggregated the data into one big Excel spreadsheet, and then sliced and diced the data with an Excel pivot table.

Guess what?

Even though my client makes terrific molybdenum roller skates, I found that hardly any sales were attributable to users searching for molybdenum roller skates. And let’s also assume that for my hypothetical client, that many people search for molybdenum roller skates on www.Google.com .

If only my client would build a custom web page with the HTML title: Molybdenum Roller Skates. At least hundreds or more of visitors a day would find the company. You bet, more sales.

Well, like I said, www.superduperrollerskates.com is a mythical company. But my client is real. I told them Monday morning that even though they make the best XXX widgets in the world, nobody ever finds them on the Internet searching for XXX widgets.

So my client authorized building a web landing page that can be found by people searching for molybdenum roller skates (again, I can’t tell you the actual name of the company or their product.

So, Excel analysis of www.ConversionRuler.com data for my client was the key. And using Excel pivot tables was critical. If you’d like to learn more about using pivot tables, this advanced Excel tutorial will help.

PPC Ads and Matching Landing Pages

“I hate when the Internet ad doesn’t show me what I want.”

That’s what the lady said.

I don’t think it can be any clearer than that. All the books you read, all the Internet articles, they tell you to match your landing page to the “Internet ad” you created.

So you create a PPC ad and then point it to a matching landing page.

If you have an ad selling the new famous Super Duper Golf Putter, you don’t want to point the ad to a home page about golf equipment. You should point to the Super Duper Golf Putter web page.

But don’t take my word for it. Find two ladies on their lunch break in a cafeteria, answer them when they ask you, “What do you do for a living?”. Chat a while and ask them about PPC ads and landing pages.

The lady in the cafeteria is a lot smarter than some managers I’ve met who claim to be Internet savvy.

Happy Birthday to Abraham Lincoln

Since this blog is partially based on Abraham Lincoln, I certainly need to say “happy birthday”.

Here’s a first paragraph from Wikipedia regarding Abraham Lincoln.

Abraham Lincoln was born on February 12, 1809, to Thomas Lincoln and Nancy Hanks, two uneducated farmers, in a one-room log cabin on the 348-acre (1.4 km²) Sinking Spring Farm, in southeast Hardin County, Kentucky (now part of LaRue County).

I’ve read my share of Abraham Lincoln biographies. Perhaps no more than 20 books. That’s a good start. Currently I am reading Carl Sandburg’s biography on Lincoln.

In reading Lincoln things begin to bubble to the surface. Sometimes great things, sometimes small things.

For example, Lincoln loved cats. There’s a story about how when Lincoln stayed overnight in someone’s home, a cat meowed sadly during the night. Lincoln found the kitten, fed and held it through the night.

Lincoln also was known to walk the hospitals and hospital tents of the Civil War visiting both Union and Confederate wounded. One week President Lincoln visited the Confederate wounded, chatting with the men, giving them his companionship and support. The next week Lincoln was assassinated. Many of those same southern soldiers wept upon hearing of Lincoln’s death.

There’s so much we don’t know about Abraham Lincoln. What we do know is that he was born February 12, 1809.

Enjoy the day and remember. Abraham Lincoln changed the United States irrevocably. And although our country may not be perfect, we are an imperfect beacon of hope to many in this world.

Happy birthday Abe.

Homeless Bicyclist in snowy Deerfield, Illinois

Today driving in to work, passing into Deerfield, IL, I made a mistake.

As I was driving on busy Waukegan Road, northbound through the forest preserves just before the toll way crossing, I passed a homeless bicyclist in a snow drift.

How did I know he was homeless? He was African-American, had shopping bags hanging from his handlebars, was in a snowdrift, coming out of the forest. I’m from Chicago, I unfortunately know what a homeless person looks like.

Sure, there was lots of traffic, I couldn’t stop.

Sure, what could I do even if I had stopped? Take him to where?

Now, 90 minutes later, as I sit in my warm office, I just made a cell phone call to my wife. Gee, a cell phone.

I’ve done heroic things over the years, small heroism. I broke up a fight on a Chicago train once. Another time I stopped a man from beating another man with a baseball bat in Chicago’s Uptown neighborhood. Around midnight once in my taxi, I used the car to stop two men from beating a man on Broadway in Chicago, IL.

But today, I did nothing. I am so sorry.

Next time, if I can’t physically render help, I’ll pick up that cell phone, give my name, and call 911.