Creating a Week Label in Excel

I just love Excel.  I remember when I was introduced to pivot tables—it was like the clouds of data ignorance had parted to reveal golden-robed angels singing of cross-tabbed insights.  And I wasn’t even drunk!  Except on the beauty of Excel, of course.

Just recently I was looking at some daily performance data, and I decided I wanted to group it by week.  To start I just applied the WEEKNUM formula:

=WEEKNUM(date)

which renders “6/22/11”, for example, into “26”.

This worked fine for grouping, but “26” doesn’t really mean anything intuitively to me.  When I wanted to figure out why the numbers jumped in a particular week, I had to go back to the daily sheet, find the week number in question, and see what the associated dates were—then I could say, “Oh, yes, 9/2, that’s when we had a piece run in the Wall Street Journal.”

So instead I decided to create a label that would display the first and last days of the week in question.  After a bit of tinkering I hit on using the WEEKDAY function, which delivers a number for the day of the week a date falls on.  I figured I could use that number to calculate how far from the beginning or end of the week the date is, then subtract or add the right number of days to deliver the two numbers. I used TEXT to make them labels:

=TEXT(date-(WEEKDAY(date)-1),”MM/DD”)&”-”&TEXT(date+(7-WEEKDAY(date)),”MM/DD”)

This yielded an output like this:

Date	Week
6/22	06/19-06/25
6/23	06/19-06/25
6/24	06/19-06/25
6/25	06/19-06/25
6/26	06/26-07/02
6/27	06/26-07/02
6/28	06/26-07/02
6/29	06/26-07/02
6/30	06/26-07/02
7/1	06/26-07/02
7/2	06/26-07/02
7/3	07/03-07/09

I formatted the labels to include the “0” in front of single-digit numbers because otherwise they don’t sort properly—October (10) comes before February (2).

Did I mention that I did all this on a Saturday morning?  And in fact considered it to be a bit of a treat to myself for doing some other work?  Yep, I party hard.

Who Will Be The Next Google?

You know, sometimes my own powers of prediction are truly amazing.  Back in 1998, for example, I thought, “Why would you want to work at a search engine?  They are so boring.  Email, that’s where the excitement is!”  It’s that kind of effortless market acumen that’s kept my family in ramen and envy through the years.

So I had to giggle when I re-read this old 2008 post of mine about how the iPhone was changing the mobile search market:

“To me it appears the writing is on the wall in terms of the direction mobile search is going, and it is away from the separate ecosystem of mobile-only ads and landing pages. This very fact will only increase the iPhone’s dominance as sites stop investing in mobile-only design.”

So, okay.  I guess if I’m to be generous to myself I got that maybe 50% correct: it’s true that the need for mobile-specific search ads and accompanying WAP-compatible landing pages has all but disappeared.  And of course, the iPhone was anything but a flash in the pan, and even if Android is coming on strong I’d still say that the iPhone was the driver of current smartphone design.  But the idea that people would just use their phones to interact with web pages they way that they were without any need for dedicated mobile sites—bzzzzt.  Wrong and wronger—although they aren’t calling them “mobile sites” but rather “apps”, the end result is that marketers must create mobile versions of their content—and on multiple platforms, too.  The market has actually become more fragmented and the effort has increased along with the opportunity.

In a way it feels like 1998 all over—just as beautiful, functional sites back then were vastly outnumbered by brochureware and sites that broke all aesthetic and taste boundaries, so the current crop of mobile apps contains a few gems along with a lot that are clunky, lame, or derivative.  It is now de rigeur for a major web property to have its mobile apps, and independent apps abound, too; the iPhone App store now has something like 300,000 offerings.  And as in 1998, the available systems for cataloging, presenting, and discovering apps leave something to be desired.  On top of that, a lot of search done on mobile is local, and while Google is moving hard to own that space through its Places pages (and of course through its squelched attempt to purchase Groupon), there is still room for new players to emerge.

Basically, these are exciting times.  There is a lot of value being created right now and The Next Google is still out there.  If you want some ideas in advance of who the winners in this new market might be, let me know.  I’ll tell you what companies I think are lame, and you can send your resume to them immediately, get in early, and then enjoy a good laugh when you drive by me in your Tesla five years from now.

 

People Are Funny Things

My blog, of course, reaches a pretty wide audience, numbering some days all the way into the very lowest reaches of the double digits.  With that broad of a readership of course I keep my eye on the stats so I can make sure I’m serving people well and providing them the information they want.  And based on the top searches that people are using to get to my blog, what they appear to want is for me to be a MILF.

Yes, the MILF Beth Morgan is still a pretty common way for people to get to my blog, and I honestly do not get this.  I just did a search for “beth morgan milf” myself, and indeed my blog does show up in the first page of results– under the enticing heading “Beth Morgan’s Blog O’ Marketing” and after an entire page with of listings promising hot pics of MILF Beth, personal conversations with MILF Beth, and seriously far more enticing-looking options.  I just can’t understand how someone genuinely interested in the topic of hot mamas could ever be tempted to click on a listing that says right there on the results page, “Sadly I have nothing for you.”  Maybe they think someone who has a career as a nontraditional erotic model might also have a side-business in marketing consulting and they want to see the other side of her (eww)?

And the sad thing is that having now written a post with “MILF Beth” in it 15 times, it will probably only serve to move me up in the search results.  Google’s a funny thing, too.

You Know Who Loves Google’s Ad Buy? eHow.

Oh my gosh.  I can’t believe I haven’t posted in so long.  The hangnail situation from last time?  Well, it got even more out of hand and it’s really been taking all my time.  The good news is I have some new people in place and things are looking really squared away now– nice trim cuticles, shiny nails, smooth skin.  I’m kind of starting to get veiny old lady hands like Madonna, but that just comes with the territory of impending middle age.  My overall plan for all things related to aging has been to try to redefine beauty standards so that age spots are desirable.  We’ll see how that goes.

So!  Some men in tiny pants played a game on Sunday to great acclaim and huge audience (I say that all casually as if I wasn’t down on my knees the entire 3 hours praying to the gods of jambalaya, voodoo, and whoever else would listen for a Saints win).  In fact, preliminary results say that the broadcast became the most-watched program in US history, with 106 million viewers.  And, being the marketing geek that I am, I of course looked up from my prayers at each break to check out the ads.  This won’t be a “Best and Word Ads” post, although suffice it to say that I’m certainly never purchasing a Dodge Charger nor doing any business with GoDaddy any time soon.  But my heart did an embarrassing pitter-patter when I saw the Google logo and realized they had done a Superbowl ad buy.

Because Google, of course, does not do ad buys.  Seeing a Google ad is like seeing J.D. Salinger out at the grocery store (and I’m keeping that in the present tense on purpose, because Salinger lives 4eva).  I was also intrigued to see that it was an ad for what most would assume they do not need to advertise at all, their core search product.  I’ve seen their billboards for other products, which make sense as they try to extend their tentacles across your entire online experience.  But core search product is what they pushed, which tickled my special geek place even more.  I yelled out to my husband to draw his attention to the ad, which was completely unnecessary as he was sitting right beside me.  The excitement could not be held back.

It also helped that the ad itself was excellent, telling the story of a Parisian romance in about one minute, all through search terms from “impress a french girl” to “how to assemble a crib”.  It stayed true to Google’s clean and simple aesthetic while quickly showcasing a variety of capabilities from translation to mapping to flight tracking.  It also packed real emotional impact into a tiny space, reminding me of what I always love about doing keyword research, which is thinking about the real people and real stories behind the queries.  I’m a romantic, dammit, and the ad made my eye glisten a little.

So today when I hopped onto eHow to do a little research, the top tip of the day really stood out to me:

Look!  There it is!  How to assemble a crib!  Aww.

Now, there’s nothing that indicates why this is a “top tip”– it is very likely that the clever editors of eHow just took advantage of the Google ad buy to boost traffic.  But they wouldn’t be doing that if they weren’t getting some good traffic on the article, I would think, and the eHow listing does come up tops when you put that phrase into Google.   This is another listing on the home page:

Which is also a top Google search result if you enter “impress a French Girl” into Google.

Are there millions of new hits coming to eHow because of Google’s ad buy?  Doubtful.  But increased traffic is increased traffic, and it’s even better when someone else paid for it.

Man, I gotta figure out how to get on that plan.

Powerset: Worthy Of The Hype?

Whew, I haven’t posted in months.  Sorry, I was doing my nails, and I had a tricky hangnail situation so it just took ages.

So, Google.  You’ve heard of them, right?  Apparently they make a dollar or two here and there by selling ads alongside search results.  And anything that makes a dollar or two is going to spawn a host of other businesses looking to carve off a little pie for themselves.  The past couple of years have seen a brave army of search engines trying to capture just a little bit of that Googly magic for themselves; here, for example, is a list of over 40 social search engines.  There are image search engines, people search engines, and capturing a lot of late-breaking buzz, natural language search engines.  In particular, tech reporters across the country have been getting a tingly feeling in their special places over the premier of Powerset, a search engine that promises to truly understand what a web page is about and thus to find answers to queries quicker.  The site they have just premiered indexes Wikipedia only as a way to showcase their user experience; more in-depth indexing is to follow.

So is this the company that will lure the searching public away from Google and spawn the next set of millionaires?  That all depends on if it truly offers a different and better result.  As you’ll see from the link above, lots of people have tried Powerset out and reported back.  I personally like to test new engines with questions that Google has either performed really well or really poorly on in the past, so I gave it a try on a few phrases of my own, comparing Powerset, Google, and a site search of Wikipedia:

1) Which god gets his liver eaten out each night?

Powerset returns a reference to the correct answer in one below-the-fold result.  Google returns a correct answer in 3 of the top 5 results.  Wikipedia nails it in the second result, meaning that for some reason Powerset wasn’t able to correctly grok the results of the only data source it looked at.  Google was the winner here.

2) My friends and I were trying to recall if there was an earthquake in Manhattan around the time of 9/11.  So which search method does best on “New York City Earthquake”?

Neither Wikipedia nor Powerset had anything helpful at all to offer here.  Google nailed it in both its first and second results.  So once again, Google was the winner.

3) On a cross country road trip you start to see the same names over and over.  Search engines, can you reveal the “most popular town name in america”?

Wikipedia offered nothing at all for that search term.  Powerset offered nothing useful.  Google’s #5 listing points to an article on, interestingly, Wikipedia, called “list of the most common US place names”.  Google, you clever bandit!  You win again!  (It’s Franklin, which pleases me, as he has always been my favorite Founding Father.)

So my own experience with Powerset does not have me excited yet.  It wasn’t as effective as Google in returning the information I wanted, even when that info was actually on Wikipedia.  Wiki’s own site search didn’t do as well either.  So for now I’m going to stick with Google when Very Important Questions such as these arise.

Google + Performics = Conflict Of Interest

Tech Crunch notes in this story about Google’s acquisition of DoubleClick that “many have missed one part of the deal that may raise eyebrows: Google now owns SEO firm Performics.” Welp, as an employee of another firm that offers SEO and SEM services, I can say we sure didn’t miss that element. I’ve already felt that Google’s aggressive promotion of its free paid search strategy services offered enough of a conflict of interest, but as Danny Sullivan points out at length, they will now be accepting payment from firms wishing to rank well in their own natural search results. Google has long touted that they don’t have any connections to SEO firms…well, now they do. I’m sure they will pull out their “Don’t Be Evil” flag and wave it around, as if merely saying that that is their motto means they can do anything they want and it’s still okay. But selling SEO strategy is a clear conflict of interest and I agree with Danny that they should divest themselves of Performics as soon as they can.

Microsoft To Shun Last-Click Attribution. Searching Public To Shun Microsoft.

Microsoft, oh Microsoft. So full of clever search marketing ideas you are. Your keyword research tools are actually much richer than others on the market. And your newly announced idea to distribute conversion to all the clicks that contributed (search and not)– we likey, we likey. Now if only you had the volume to make it worthwhile.

iPhone Changes The Mobile Search Conversation

Two recent related stories point to the continued flux in the mobile search market:

1. To the surprise of no one who has paid the least attention to the hype, the iPhone has increased its share of the US smartphone market quite rapidly for such a new entrant into the market. It is now the number 2 smartphone in the US, with a market share of 28% (compared to market leader RIMM/Blackberry with 41%). It also has a reasonably strong share of the global market; it comes in #3 with 6.5% (market leader Nokia has 53%). The iPhone is popular and its popularity is increasing; more importantly, the clamor over its user-friendly interface is having a big effect on overall smartphone design. And that brings us to:

Yahoo Users Tend To Spend Less: No Kidding!

A study released last Friday by Hitwise is causing lots of discussion: graphing search engine use against some of their demographic data, they assert that people who use Yahoo tend to buy less stuff online. To which anyone working in search replies, no duh!  Client after client, our data shows that while impressions and even clicks from Yahoo might be fairly even with Google, Yahoo pretty much never equals Google in what really matters: conversions.  People who use Yahoo just don’t buy stuff like Google users do.  Our intuition has always been that these users are younger (and thus less likely to be affluent); data like this backs up that intuition.

Beth, You Naughty Thing

Well. A sudden new entrant is taking the Beth Morgan scene by storm. Although Beth Morgan news tends to be dominated by cricket Beth Morgan, the chatter these days is about a very, very naughty Beth Morgan in Australia who approved controversial real estate developments in exchange for sex and cash. Beth! That is shocking! And I thought that porno Beth was bad for the Beth Morgan brand. This sounds like something soap opera Beth Morgan might do. I’m sure it’s been amusing to cricket Beth, who has been in Australia cricketing away while all the sordid details of naughty Beth have been making headlines there.

Update: Big shout-out to Sex & Cash Beth for causing an explosion of traffic to the blog!  As with the people seeking MILF Beth, I sadly don’t have much for you.  I’ve never even been in a position powerful enough that I *could* demand sex and cash from anyone.  Even with the husband it’s more of a negotiation than a demand.