Days of Week or Month Number in Excel / Google Sheets

Here are some easy ways to convert dates into the day of the week (Mon, Tue, etc.)

=HLOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7;"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},2,FALSE)

=TEXT(A1,"dddd")

Here's how to convert Month names to numbers

=HLOOKUP(A2,{"January","February","March","April","May","June","July","August","September","October","November","December";1,2,3,4,5,6,7,8,9,10,11,12},2,FALSE)

Custom Formula for Conditional Formatting to Highlight Duplicate Values in Google Sheets

=countif($A$1:$A$1000,A1)>1

How to Add Twitter Card Information Previews to Your Web Site

Twitter lets you add previews of your web pages to tweets. Twitter calls these previews "cards."

If you or anyone else Tweets a link to your site, you can add title, description, a picture, and your Twitter account. The examples shown here are for a "summary" card type. Twitter offers other card types as well, including: summary with large image, photo, gallery, app, player, and product. Twitter claims photo cards have the highest click-through at 1.09% of Tweet impressions, and summary cards are 2nd highest at 0.95%.

To enable previews of your pages, add the following meta tags. Twitter provides a handy interactive web tool that lets you see how to use these tags. I divided the tags into sections based on how most sites construct pages, but you can ignore those distinctions if they do't apply to your site.

Site-Level Information

Site: Use your main Twitter account's screen name. For example:

<meta name="twitter:site" content="@TwitterUserName" />

Site ID: This optional tag includes Twitter's unique numeric ID for your Twitter account. If you don't know your numeric Twitter ID, you can skip this tag.

<meta name="twitter:site:id" content="1234567890" />

Page Type-Level Information

Card Type: This tag lets you specify which kind of preview you want. You may want to use different cards by page type (e.g. blog posts or news vs. videos, vs. images), or a simply "summary" for your whole site.

<meta name="twitter:card" content="summary" />

Image: This lets you specify which logo or image to user in the previews.

<meta name="twitter:image" content="http://www.domain.com/path/image.png" />

Page Level Information

Title: This generally contains the same text as your page's title tag. However, it lets you customize the preview title that appears on Twitter.

<meta name="twitter:title" content="Title of Your Web Page Goes Here" />

Description: This is also generally the same text as your page's description tag. Again, you can customize it for Twitter previews if you like.

<meta name="twitter:description" content="Summary of your web page goes here." />

URL: This is the fully-qualified URL to your web page. I find that if this is missing or is not fully qualified (meaning including the http://www.domain.com/" part, Twitter won't display a preview.

<meta name="twitter:url" content="http://www.domain.com/path/file.html" />

Creator: This can be either your main Twitter account screen name, or the Twitter screen name of the page's author.

<meta name="twitter:creator" content="@AuthorTwitterName" />

Creator ID: This optional tag can be either your main Twitter account's numeric ID, or the Twitter numeric ID of the page's author.

<meta name="twitter:creator:id" content="9876543210" />

Approval

Once you have these tags set up on your pages, you must request that Twitter approve previews on links to your site. To apply for approval, run your page through the Validate & Apply tab on Twitter's Card Validator page.

July Bots Are In

The July web crawler indexing bots stats are in. Here are the top bots for a small site I run.

Top 20 Obvious Bots

These bots are nice enough to include "bot", "spider", or "crawl" in their user agent string, or access the robots.txt file. Here are the top 20, representing 89% of obvious bot hits:

1. 18% - Mozilla/5.0 (compatible; Ezooms/1.0; ezooms.bot@gmail.com)
2. 13% - Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
3. 12% - Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)
4. 7% - Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_1 like Mac OS X; en-us) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8B117 Safari/6531.22.7 (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)
5. 5% - Mozilla/5.0 (compatible; YandexImages/3.0; +http://yandex.com/bots)
6. 5% - Mozilla/5.0 (compatible; AhrefsBot/5.0; +http://ahrefs.com/robot/)
7. 5% - Mozilla/5.0 (compatible; WBSearchBot/1.1; +http://www.warebay.com/bot.html)
8. 4% - Twitterbot/1.0
9. 3% - Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)
10. 3% - Mozilla/5.0 (compatible; AhrefsBot/4.0; +http://ahrefs.com/robot/)
11. 2% - ShowyouBot (http://showyou.com/crawler)
12. 2% - Mozilla/5.0 (compatible; TweetmemeBot/3.0; +http://tweetmeme.com/)
13. 2% - Aboundex/0.3 (http://www.aboundex.com/crawler/)
14. 2% - Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)
15. 2% - Mozilla/5.0 (compatible; SISTRIX Crawler; http://crawler.sistrix.net/)
16. 2% - msnbot/2.0b (+http://search.msn.com/msnbot.htm)
17. 1% - Mozilla/5.0 (compatible; PaperLiBot/2.1; http://support.paper.li/entries/20023257-what-is-paper-li)
18. 1% - Mozilla/5.0 (compatible; SearchmetricsBot; http://www.searchmetrics.com/en/searchmetrics-bot/)
19. 1% - Mozilla/5.0 (compatible; Dow Jones Searchbot)
20. 1% - Sogou web spider/4.0(+http://www.sogou.com/docs/help/webmasters.htm#07)

Top 20 Developer Packages or Proprietary Bots

These bots are built on developer packages, but don't specifically identify themselves as a bot. The top 20 represent 92% of hits from these bots.

1. 19% - checks.panopta.com
2. 16% - NING/1.0
3. 13% - UnwindFetchor/1.0 (+http://www.gnip.com/)
4. 10% - FeedBurner/1.0 (http://www.FeedBurner.com)
5. 7% - JS-Kit URL Resolver, http://js-kit.com/
6. 5% - UniversalFeedParser/5.0.1 +http://feedparser.org/
7. 4% - PycURL/7.19.5
8. 3% - Java/1.6.0_26
9. 3% - TwitterFeed 3
10. 2% - HTMLParser/2.0
11. 2% - Ruby
12. 2% - Mozilla/5.0 (Digg/1.0; support@digg.com)
13. 1% - Java/1.7.0_21
14. 1% - Crowsnest/0.5 (+http://www.crowsnest.tv/)
15. 1% - curl/7.24.0
16. 1% - Opera/7.11 (Windows NT 5.1; U) [en]
17. 1% - MetaURI API/2.0 +metauri.com
18. 1% - Jakarta Commons-HttpClient/3.1
19. 1% - InAGist URL Resolver (http://inagist.com)
20. 1% - Mozilla/5.0

Plus these other notables:

1. Feedfetcher-Google; (+http://www.google.com/feedfetcher.html; subscribers; feed-id=)
2. Mozilla/5.0 (compatible; Embedly/0.2; +http://support.embed.ly/)

Top 20 Sneaky Bots

These bots either don't identify themselves, mask their identity using a common real user agent, or don't include a user agent. I identify these by hits from same or similar IP addresses, complete lack of any referring URLs, or too many hits from the same IP address.

1. From IP 168.62.192.113 (Microsoft) with user agent "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.163 Safari/535.19".
2. More coming soon

View Click Stats on Bitly or Goo.gl Short URLs

You may already know that you can view click stats on Bitly short URLs by adding + to the end of the URL, like this:

http://bitly.com/LH9pqK+

But did you know adding + also works for any other service that shortens their URLs through Bitly, like Amazon?

http://amzn.to/wZsYV5+

You can also view data on Google short URL clicks by inserting the shortened ID of a goo.gl URL into a longer Google URL like this (I find that I have to click this link twice to see the metrics):

http://goo.gl/#analytics/goo.gl/9bHF1/week

How to Get More Twitter Followers Legitimately

The best way to get more Twitter followers is to follow more Twitter accounts. People who may not be aware of your account or who wouldn't find it any other way will be notified that you have followed them. They then become aware of your account any may choose to follow you - or block you if they suspect you are a spammer. If too many people block your account too fast, Twitter may suspend your account. The trick is to find accounts that will follow you back and not block you.

I tested two methods to identify Twitter accounts who are more likely to follow your account, and less likely to block you when you follow them. I call the first method Collaborative Following: accounts that follow several accounts similar to yours. This method resulted in a 49% follow back rate in a test of 495 follows. I call the second method Mutual Following: accounts that follow and are followed back by an account similar to yours. This method resulted in a 31% follow back rate in a test of 491 follows.

Method 1: Collaborative Following

1. Use Twitter's APIs to download list of your followers and people you follow. You can do this in Excel with data connections, or with web server scripts and a database.
   
   Download Twitter IDs of your followers (up to 5,000 IDs at a time):
   https://api.twitter.com/1/followers/ids.xml?screen_name=YourScreenName
   
   Download Twitter IDs of people you follow (aka friends, up to 5,000 IDs at a time):
   https://api.twitter.com/1/friends/ids.xml?screen_name=YourScreenName
2. Import the lists of your followers and friends into Excel or a database. Augment the IDs you get from those APIs with full information about the accounts. You can do this by pasting REST URLs in your browser, saving the results, and opening the files in Excel; of with web server scripts and a database.
   
   Download details about accounts (up to 100 at a time):
   https://api.twitter.com/1/users/lookup.xml?include_entities=false&user_id=1234,2345,3456
3. Identify 10 accounts you trust with between 4,000-10,000 followers each. If you don't know which accounts to choose, you can go with mutual follows (accounts you follow that follow you back), or with accounts from your competition or for similar interest groups. For example, if your account is about cats, you might use other accounts about cat lovers. These will be your targeted accounts.
4. Identify a few accounts with 4,000-10,000 followers each whose followers would not be interested in following your account. For example, if you account is about cats, you might choose accounts about dog lovers. These will be your anti-targeted accounts and will help you exclude users who are not interested in your subject area, or spammers who follow everyone.
5. Identify accounts that are willing to follow other accounts. Use the APIs described above to download IDs of accounts that follow your targeted and anti-targeted accounts. These are your prospects.
6. Generate counts of how frequently each prospect ID appears in the lists of those who follow your targeted accounts. You can do this in Excel with a pivot table, or in a database with SQL. If you identified 10 targeted accounts, this will be a number from 1-10.
7. Exclude anti-targeted account IDs from your targeted account IDs.
8. Exclude any IDs who follow fewer than 4 of your targeted accounts. These accounts have not expressed enough interest in your subject area.
9. Use the lookup API described above to augment the remaining IDs with full account details.
10. Remove IDs for any account that hasn't tweeted in the past 7-14 days. These accounts are not active enough to read your updates or follow you back.
11. Remove IDs for any account whose friends count is less than 90% of their followers count. These accounts don't show enough willingness to follow others.
12. Twitter only lets people follow up to the greater of 2,000 accounts or 10% more accounts than their own followers count. Remove IDs for any account who will be allowed to follow fewer than 10 more people. These accounts are going to be stingy with choices of whom to follow.
13. Remove IDs for any account that follows more than 10,000 accounts. These accounts get so many Tweets, yours will be lost in the noise. These accounts are more likely to be spam or brand accounts who may not really read Tweets anyway.
14. Identify a few negative words that identify accounts who would not be interested in your subject area. For example, if your account is about cats, you may want to avoid people who mention "dog" in their profile. Remove IDs of accounts who use these negative words in their descriptions.
15. These exclusions generally remove 42-48% of prospects. Of the accounts that remain, follow about 100 per day. This moderate rate of following will help keep the number of any account blocks per day at a low level.

Method 2: Mutual Following

1. Identify an account similar to yours with 4,000-10,000 followers that also follows 4,000-10,000 accounts. Accounts with comparable counts of followers and friends may indicate that the account owner has done some pre-filtering of follower quality for you.
2. Use the followers and friends APIs described above to download IDs of the targeted account's followers and friends.
3. Identify IDs that appear in both the followers and friends lists. These are your prospects.
4. Use The lookup API described above to augment prospect IDs with full account details.
5. Follow the same exclusion steps described above to remove anti-targeted accounts, inactive accounts, low following rates, limited following, high following counts, and negative words.
6. Of the accounts that remain, follow about 100 per day.

More SEO and Social META tags for Twitter and Google

Here are some more HTML META tags I just discovered:

Twitter Cards

Twitter Cards (announced in June 2012) are extended tweets that can show more than just 140 characters. Here are META tags to add to your page HEAD section to help Twitter pick the right parts your site's content.

The twitter:card tag can be summary (for a news article, blog post, or text-based page), photo (for an image or picture), or player (for video).

<meta name="twitter:card" content="summary" />

The twitter:site and twitter:site:id tags let you identify your own Twitter username, and official Twitter user ID. Most people won't know their Twitter user ID. If you don't, you can omit that META tag.

<meta name="twitter:site" content="@YourTwiterScreenName">
<meta name="twitter:site:id" content="1234567890" />

The twitter:url tag is for a link to your page.

<meta name="twitter:url" content="http://www.YourSite.com/path/pagename.html" />

Other tags are pretty self-explanatory:

<meta name="twitter:title" content="Your Page Title" />

<meta name="twitter:description" content="Your page description." />

<meta name="twitter:image" content="http://www.YourSite.com/image.jpg" />

Google+ OpenGraph

OpenGraph (og) META tags help Google Plus pull the right information from your page, in case anyone ever shares it on Google+. These are pretty self-explanatory.

<meta property="og:title" content="Your Page Title" />

<meta property="og:image" content="http://www.YourSite.com/image.jpg" />

<meta property="og:description" content="Your page description." />

<meta property="og:site_name" content="Name of Your Site"/>

Google Search Thumbnails

Google also uses a thumbnail META tag to present small images next to its search results from your site.

<meta name="thumbnail" content="http://www.YourSite.com/image.jpg" />

How to add a Pinterest button to your web site

Pinterest is all the rage right now, and of course they have their own sharing button!

If you don't want to use their preferred code (or if you don't want to put their JavaScript on your site), you can also create a simple link using this format:

http://pinterest.com/pin/create/button/?url=PageURL&media=ImageURL&description=Optional+Description

• url is your HMTL page's link (not the image, but the page it's on)
• media is the URL of the image you want people to share
• description is optional text you can include so people don't have to write their own

If you want to put that in a pop-up, just use this link to let people click a little Pinterest icon:

<a href="" onclick="window.open('http://pinterest.com/pin/create/button/?url=PageURL&media=ImageURL&description=Optional+Description', 'pinterest', 'toolbar=0, resizable=1, status=0, width=626, height=430');return(false);"><img src="http://passets-cdn.pinterest.com/images/about/small-p-button.png" width="16" height="16"></a>

Music Sales Leveling Off?

Here's a story from TechCrunch covering SoundScan news that music sales may have finally hit bottom. My favorite part is where they call out the labels that "acted like a petulant child in the face of the digital revolution".

Google+1 Button Documentation

Here's where to get the Google+1 social sharing button for your web site.

Here's a function I use in my global site JavaScript file to also track events in Google Analytics when people turn the +1 button on or off. To use this, add googlePlusOneCallback as "JS Callback function" under the "Advanced options" when you configure your button. Update the last 3 values for the way you track events on your site. (The values I used below will track a Google Analytics Event with Category "share", Action "googleplus-on" or "googleplus-off", and the Label will be the URL of the page the button was on.)

function googlePlusOneCallback(obj){
_gaq.push(['_trackEvent','share','googleplus-'+obj.state,obj.url]);
}

As more companies encourage their customers or users to share more, people are beginning to notice and worry.

Twitter Advanced Search Operators

I just noticed Twitter documents its advanced search operators. The :) and :( searches are intriguing for brand research.

Operator	Finds tweets...
twitter search	containing both "twitter" and "search". This is the default operator.
"happy hour"	containing the exact phrase "happy hour".
love OR hate	containing either "love" or "hate" (or both).
beer -root	containing "beer" but not "root".
#haiku	containing the hashtag "haiku".
from:alexiskold	sent from person "alexiskold".
to:techcrunch	sent to person "techcrunch".
@mashable	referencing person "mashable".
"happy hour" near:"san francisco"	containing the exact phrase "happy hour" and sent near "san francisco".
near:NYC within:15mi	sent within 15 miles of "NYC".
superhero since:2010-12-27	containing "superhero" and sent since date "2010-12-27" (year-month-day).
ftw until:2010-12-27	containing "ftw" and sent up to date "2010-12-27".
movie -scary :)	containing "movie", but not "scary", and with a positive attitude.
flight :(	containing "flight" and with a negative attitude.
traffic ?	containing "traffic" and asking a question.
hilarious filter:links	containing "hilarious" and linking to URLs.
news source:twitterfeed	containing "news" and entered via TwitterFeed

Here's their Advanced Search form. You might also be interested in handy search bookmarklets from one of my previous posts.

Helpful Web Page HTML Meta and Link Tags

Web pages can include extra HTML tags that your site visitors don't necessarily see, but that help search engines like Google and Bing, other web sites link Facebook, and applications like the iPhone browser know more about your page. This blog entry describes the most common and some other useful tags. All these tags, except for JavaScript, belong in the "head" HTML section of your web pages. Note that some of these are "meta" tags with "name" and "content" attributes, and some are "link" tags with "rel" and "href" attributes.

Basic Meta Tags

Title - The Title tag contains text that shows up at the top of the browser when someone visits your page. Search engines also use your title as the main link to your page when it shows up in search results. An example Title tag looks like this:

<title>This is where your page title goes</title>

Description - A description tag can contain more text about your site. Visitors don't see this information when visiting your page, but search engines generally show it just below the page title in search results. This is a good place to include additional keywords and a call to action. An example Description tag looks like this:

<meta name="description" content="Click on this site for more information on what you're searching for." />

Keywords - Though most search engines ignore contents of the Keywords tag, including it may be helpful if your site has its own search engine. Use it to include additional words not necessarily on your page that visitors might search by. Here's an example Keywords tag:

<meta name="keywords" content="web page editing authoring header meta tags HTML CSS JavaScript JS" />

Robots - This tag lets you tell search engines whether or not to index or cache your pages. If you don't want search engines to store a copy of your page, use a tag like this:

<meta name="robots" content="noarchive" />

Canonical - If your page can be reached at several different URLs, this tag lets you tell search engines which link you prefer them to use. See my other blog post for more information about this tag. Here's an example:

<link rel="canonical" href="http://www.yourdomain.com/yourpage.html" />

Including Site CSS and JavaScript Libraries

It's good practice for your site to use a common CSS stylesheet and JavaScript file that the browser can cache and reuse. Browsers will store these files so your visitor doesn't have to download them again with every page. Moving common formatting and scripts out of each page makes your page size smaller which means they'll load faster.

Stylesheet - This tag tells the browser where to find the general formatting for your site. Here's an example Stylesheet tag:

<link rel="stylesheet" type="text/css" href="/sitestylesheet.css" />

JavaScript - This tag tells the browser where to get your site's general script library containing functions that are reused on several pages. This tag belongs near the top of the "body" section of your web page. Here's an example JavaScript tag:

<script type="text/javascript" src="/sitejavascript.js"></script>

Browser Icons, and Apple iPhone and iPad Icons

These tags let you tell browsers and other applications which icon to use to represent your site when a visitor bookmarks your page.

Shortcut Icon - Web browsers will show a small icon, generally 16x16 pixels, in the address bar and bookmarks. To tell the browser where to find the icon, include the "shortcut icon" tag. Here's an example:

<link rel="shortcut icon" href="/favicon.ico" />

Apple Touch Icon - Apple iPhones and iPads will show a 57x57 icon when a visitor bookmarks your site. Here's how to tell Apple where to find your icon:

<link rel="apple-touch-icon" href="/logo57x57.png" />

Viewport - When the iPhone browser displays your page on its small screen, it can't always figure out just how wide or narrow to show it. Use the "viewport" tag to tell the iPhone Safari browser exactly how wide to display your page.

<meta name="viewport" content="width=650" />

Facebook

If you've ever shared a link on Facebook, you probably noticed that Facebook sometimes shows an irrelevant image from the page. To let Facebook know which image you prefer to use, include a "medium" and "image_src" tag.

Medium - This tag helps Facebook know how to display a shared link to your site. You can specify news, blog, image, video, audio, and mult for the "content" attribute based on your page's content. Here's an example "medium" tag for textual content.

<meta name="medium" content="news" />

Image_Src - This tag tells Facebook which image to use when someone shares a link to your page. If you would like to let the Facebook user select from more than one image, you can include this tag any number of times with different "href" image URLs. Here's an example:

<link rel="image_src" href="http://www.yourdomain.com/image1.png" />
<link rel="image_src" href="http://www.yourdomain.com/image2.png" />

RSS Feeds

Alternate - If your site has an RSS feed, most browsers will show an RSS icon near the address bar that visitors can click to subscribe to your RSS feed. To tell the browser where your RSS feed is, include a link to it like the one below. If your site has more than one RSS feed, you can include more than one "alternate" link.

<link rel="alternate" type="application/rss+xml" title="Your Feed Name" href="http://www.yourdomain.com/feed1.rss" />
<link rel="alternate" type="application/rss+xml" title="Your Feed Name" href="http://www.yourdomain.com/feed2.rss" />

Advanced Topic: Site Search

Modern browsers like Firefox or Microsoft Internet Explorer version 7 and higher let users add custom searches to search your site even when they're not on your site. If your site has its own search feature, you can set up a small XML file that tells the browser where to find your search.

First set up an XML file, generally named "opensearch.xml", like the example below and upload it to your server.

<OpenSearchDescription xmlns="http://a9.com/-/spec/opensearch/1.1/">
<ShortName>Site or Search Name</ShortName>
<LongName>Your Site or Search Engine Name</LongName>
<Description>A longer description of your site or search engine.</Description>
<Image type="image/vnd.microsoft.icon" height="16" width="16">http://www.yoursite.com/favicon.ico</Image>
<Url type="application/opensearchdescription+xml" rel="self" template="http://www.yourdomain.com/opensearch.xml" />
<Url type="text/html" rel="results" template="http://www.yourdomain.com/yoursearchscript?yourquerytag={searchTerms}" />
<Query role="example" searchTerms="Example" />
</OpenSearchDescription>

If your search engine supports real-time search-as-you-type suggestions, add a line like this to your XML file:

<Url type="application/x-suggestions+json" template="http://www.yourdomain.com/yoursuggestionscript?yourquerytag={searchTerms}" />

Then include a "search" tag like the one below in the header of all your pages with a link to the search XML file your uploaded:

<link rel="search" type="application/opensearchdescription+xml" href="http://www.yourdomain.com/opensearch.xml" title="Your Site or Search Engine Name" />

Make Search Engines Use Your Keywords with the Canonical Tag

It used to be that search engines would index keywords listed in web page meta keywords header tags like this:
<meta name="keywords" content="Schools Out Forever Maximum Ride" />

However, so may sites overloaded that tag with spam that search engines started ignoring it entirely. The challenge for site owners became where else to put keywords that search engines would still see. People noticed that Google not only looked for keywords in text, but also in domain names and URLs. So the trick became how to get keywords into your URLs.

Usually an URL includes a one-to-one mapping to a file name on the web server (or database-driven sites may use IDs in query strings). So webmasters could include keywords in file and directory names, but that gets tedious because generally anything between / characters is also a physical sub-directory, and it just doesn't work for database-driven sites. Using physical file and directory names would mean your web servers would have files in tons of individual sub-directories that would become impossible to maintain.

The good thing is there's no law that says an URL has to exactly equal a physical file name. So one solution is to set up your web server to rewrite URLs to come up with the real file name.

For example, all these URLs render the same content:
http://www.amazon.com/dp/0446618896
http://www.amazon.com/Schools-Out-Forever-Maximum-Ride/dp/0446618896
http://www.amazon.com/asdf-asdf-asdf-asdf-asdf/dp/0446618896

But how do you tell Google what your preferred URL is, since it could find any of those URLs? That's where the canonical tag comes in.

If you look at the source code for the pages at any of those URLs and find the canonical tag, you'll see that they all use the same value, no matter what the actual URL was:
<link rel="canonical" href="http://www.amazon.com/Schools-Out-Forever-Maximum-Ride/dp/0446618896" />

So Google should generally link to http://www.amazon.com/Schools-Out-Forever-Maximum-Ride/dp/0446618896 from its index, no matter what URL its spider really found the page at.

The trick Amazon does to make all those pages render the same thing probably utilizes web server URL-rewriting to ignore anything between "http://www.amazon.com/" and "/dp/0446618896" and simply serve whatever content is at location 0446618896 (or in their case, whatever's in the database with that ID). URL rewriting is an arcane topic, but should be familiar to system administrators who manages web servers.

Since Amazon can then include any keywords in their URLs, the other thing they probably do is ensure consistency in how they link each product. So no matter where they have their links (sitemaps, site search, product listing pages, etc.), they always use a single preferred canonical URL.

In Praise of the Ugly - But Useful

Wired has an article about Why Craigslist is Such a Mess. A wonderful mess. Craigslist delivers customer value without the burden of overmessaging. Serendipity plays a large part of the experience there too. You know you've missed a great deal because you didn't reload the page every hour, but you still look for deals anyway.

The craigslist article reminds me immediately of the 37signals blog post Why the Drudge Report is one of the best designed sites on the web. It's ugly, but it's consistent and it's fearless.

Craigslist drives the classified advertising market (and is driving local newspapers into the ground). Drudge drives the news cycle. All with minimal or no design budgets. Both with just the facts, ma'am. No baloney, no hype, just value.

Court Gets it Right

Here's some good news: a federal appeals court ruled that preference-based playlists are not interactive!

Just as anyone can tune the radio dial until he finds a station he likes, he can tell online services what music he likes and hear that kind of music - all under the US compulsory license, which means the online service doesn't have to negotiate special on-demand rights or prices.

This is a victory not only for Yahoo and Launch, but Pandora and every other preferences-based streaming service out there!

Bookmarklets for Search

Here are some handy bookmarklets I use that let me select text on a web page, then click the bookmarklet to look it up directly on Wikipedia, Google and other places. You may find them useful.

In most browsers, you can just drag these links to your Bookmarks toolbar or favorites. In Internet Explorer, you'll need to right click links and select "Add to Favorites", then add them to your "Links" or other folder of your choice. The 2nd link is the same as the first, but with a shorter name to save space on your bookmarks bar.

• Find on Google | Google
• Find on Wikipedia | Wiki
• Find on Google News | GNews
• Find on Amazon.com| Amazon
• Find on Google Images | GImages
• Find on Google Books | GBooks
• Find on Yahoo | Yahoo
• Find on Yahoo News | YNews
• Find on YouTube | YouTube
• Find on Bing | Bing
• Find on Bing News | BNews
• Find on Twitter | Twitter
• Find on LyricsTime | Lyrics- Select an artist and/or song name, or lyrics on a web page, then use this bookbarklet. It opens in a new window so you can continue to listen to music while you read the lyrics.

The sound a dinosaur makes when it dies

Ironically the online arm of a print publication, BusinessWeek, reports that the Associated Press wants to launch its own online news portal to compete with Google News. As Time-Warner found out with AOL (or vice versa), readers don't want a single source for their news or entertainment. The AP even claims Google is essentially destroying the newspaper business because, of all things, Google makes it easy for readers to find and click through to stories they want to read. Yet the AP licenses its content to Google!

Newspapers should be knocking down Google's door trying to get their stories posted higher on Google News, yet here they are deploying "coding to lock out Google robots" (actually nothing more technological than putting a small, commonly-used text file on your site that tells Google to ignore your pages).

The only strategy that could possibly save the AP and its member papers from extinction is actually to stop licensing its content to online-only outlets like Google, Yahoo!, MSN, and AOL. The AP should also ban its offline subscribers from posting any AP content on their online outlets, or at least require it to be accessible only to signed-in subscribers who pay a certain minimum monthly charge. The only way the AP can save print is to boycott online. Subscriptions are a hard sell, and won't be popular. The market size is several orders of magnitude less than free.

If the AP is not ready to make the leap to a full-fledged boycott or require paid access, and my guess is it doesn't have the guts to, it needs to stop complaining about honest sites like Google News sending readers to its stories on its member's sites.

Why newspapers are dead

The demise of printed newspapers is getting a lot of ink lately. People's primary psychological reaction to this news is nothing more than nostalgia. Journalism will survive the death of the medium.

The main news wires are ironically most responsible for the wholesale migration of newspaper readership to online. You can get an AP or Reuters story on Yahoo! as soon as it hits the wire. Why wait until tomorrow when the story is old news?

Newspapers' top remaining niche was local. However, their local revenue has been destroyed by Craigslist and will be further eroded by effective local mobile advertising.

Another niche will be quality reporting and investigation for time-starved intellectuals that summarizes important stories and filters the online thrash and bias. Think The Economist (though it's free online now too). Another niche will be underreported foreign news and analysis for expats and sophisticated readers. Think a more engaging Foreign Affairs. But these will be weeklies, not dailies.

My personal rebellion against newsprint has 3 primary sources: the unmanageable broadsheet (they should have gone tabloid style long ago - it's even cheaper to print), inky fingers and stains (yes, even with modern inks), and the AP's insulting repetitive inverted pyramid style (and single sentence paragraphs) designed to make it easier for an editor to cut a story short to fit column inches than for readability. A secondary complaint is the "continued on page 5" phenomenon. That's probably OK for the front page (get more headlines in front of the reader), but once inside, stop making me jump all over the place to finish a story.

Wrapping your head around Web 2.0

Just as some declare that Web 2.0 is now passe, others are still trying to figure out what it is, and how to employ it.

For marketers, Web 2.0 means you get to have a conversation with your customers and learn more about their passions. For advertisers, it means you can no longer pitch products with one directional messages. For brand managers, it means your customers own your brand and are talking about it in ways that you might or might not like. For direct marketers, low response rates means Web 2.0 (at least the social networking and widgets part of it) hasn't lived up to the hype.

For software engineers, Web 2.0 meant you could easily get your hands on lots of great data and make it do what you wanted, or make other things out of it - but this posting is for the marketers out there.

I've found one of the road blocks to expanding into a Web 2.0 strategy is educating management on what exactly Web 2.0 is, and how it can help the company. Engaging directly with communities of customers is a new and scary concept for old school executives and PR agencies.

Here are a few resources that might help you educate others on what Web 2.0 even is, and how it can be leveraged (I'll add to this list from time to time as I find more sources):

• The book Wikinomics by Don Tapscott is an intense introductory course on how collaboration is changing everything.


• A McKinsey study Six ways to make Web 2.0 work by Michael Chui, Andy Miller, and Roger P. Roberts explains Web 1.0, Web 2.0, and covers some basic strategies to encourage participation in Web 2.0 solutions.


• The article The Secrets of Marketing in a Web 2.0 World by Salvatore Parise , Patricia J. Guinan and Bruce D. Weinberg has rules of thumb for marketing departments, including the very scary words "Don't control, let it go."

SoundExchange reaches web royalty deal with NAB, but not webcasters

SoundExchange collects royalties under the US Government's compulsory licensing of music for radio stations. How much to charge for a web stream, and whether royalties for performance rights are included along with SoundExchange's traditional publisher royalties has been a point of contention for about a decade.

From what I can find in the press (which leave some questions unanswered), the National Association of Broadcasters (NAB, which represents terrestrial radio stations) has agreed to $1.50 per song per thousand online listeners, ramping up to $2.50 per thousand by 2015. (No news what stations pay for their airways broadcast listeners.) That deal is for radio stations that simulcast their programming over the Internet. It's also for any online-only programming those broadcasters might also provide. That $1.50 rate will more than eat up any online advertising the stations can sell, which typically sells for less than $1.50 CPM (thousand impressions) these days - and that's assuming they can sell a display ad for every song, not a single ad for every 3-5 (or more) songs typical of terrestrial audio ads inserted into broadcasts.

So it looks like the deal with pure online broadcasters got hung up on the question of programming interactivity and what appears to be an irrational insistence by SoundExchange on a percentage of a company's revenue, rather than a simple per-song fee. Some also blame RealNetworks for screwing up a deal reached in November 2008 by at least twice seeming to agree to terms, then pulling out at the last minute. (Sounds like Rob is being Rob again.)

US law grants an automatic license to play music as long as the broadcaster follows certain rules. The main rule is the music can't be played "on demand": a listener can't push a button and hear a specific song. Online services like Pandora skirt the on demand rule by allowing listeners to program their own stations by listing some artists they like. The listener is not guaranteed what song will be played, but it's likely something she or he wanted to hear. SoundExchange deems this outside the compulsory license law and wants extra money for that interactivity. They seem to forget that I can change my car radio from a country station to a rock station to a Latino station to an urban rap station whenever I want. Though I'm not selecting the music or affecting what an individual station plays, I'm still interacting with what I'm listening to.

True on-demand streaming services like Rhapsody, Napster, Zune, and others must negotiate separately with recoding owners (labels) to play music as US compulsory license law was not written for on-demand performances.

Microsoft reorgs Zune

This can't be good. Microsoft is splitting the Zune hardware team from the software and programming team.

The article also mentions Zune's device has merely cannibalized other non-iPod devices that used Microsoft's DRM under the "Plays for Sure" logo program. It's apparently made no dent in Apple's MP3 player market share.

I subscribe to Zune, but find their marketplace useless and their software client confusing. There's never anything I want to listen to on the main Zune page, and so far I haven't figured out any way to personalize it, or to automatically "recharge" my Zune with music I will like. Zune also infuriates me by showing me music in search results that they don't have. What's the point of that? To tease me, or to piss me off?