The members formerly known as HPCWAL ;)

Alien Swarm is also Free: http://store.steampowered.com/app/630/

List of FREE games on Steam (Click on the link for direct links to download/install) http://forums.steampowered.com/forums/showthread.php?t=851573

For ATI/AMD (Radeon) graphic card users: install - storefront
- Half-Life 2: Deathmatch [6]
- Half-Life 2: Lost Coast

For Nvidia (GeForce) graphic card users: install - storefront
- Half-Life 2: Deathmatch [6]
- Half-Life 2: Lost Coast
- Portal: First Slice (demo install - storefront)

For all Steam users:
America's Army 3: install - storefront
BattleForge [1][4][7][8]: install - storefront
Between [1][4]: install - storefront
Codename Gordon: install
COIL [1]: install - storefront
Mightier [1]: install - storefront
Peggle Extreme: install - storefront
Sam & Max: Episode 4: install - storefront
Spacewar: install
TrackMania Nations Forever [2]: install - storefront

Free addons and resources for already purchased games:
Half-Life (high definition models): install - uninstall - comparison - screenshot
RACE 07 ... and more....

Games with Steam enabled Achievements: http://v1.steam.hlxgame.cc/stats/

It looks like you can indeed add third party retail games to Steam, however, only certain games are supported. Go to this list to see the complete list:

https://support.steampowered.com/kb_article.php?ref=7480-WUSF-3601

Im not sure ares, but if you find out, let us know.

in Steam there is an option to "Activate Product on Steam"

when I click on it I get a pop up that says "To register your product with Steam, enter the product code distributed with a retail DC/DVD or other Steam product key here. Click next to continue"

does this mean that I can register any retail game (as long as it's also offered on Steam) and it will officially be added to my Steam account? e.g. I bought Rome: Total War retail from a store, will adding the key to Steam register it as a Steam game from now on?

or is this only for official Steam retail games or something?