Let me list pros and cons that I have experienced or otherwise noticed with Steam.
Pros:
Steam allows me to download my games onto any internet-enabled computer in the world.
Steam allows me to browse game servers without having to fire up the game in question.
Steam allows me to chat with my friends, and see what games they are playing, as well as join them with only a few clicks.
Steam allows me to stop worrying about game updates, since they are applied automatically and I can be sure when I launch a game that I am up to date.
Steam allows me to easily purchase games online from the comfort of my home and begin playing them quicker than if I went out and bought retail, in some cases.
Steam allows me to play games without having to look for my CD or DVD, or a no-CD crack.
Steam installs no drivers or constantly-running services, apart from a helper service when run in limited user mode.
Steam "just works" when I reinstall Windows without needing to reinstall Steam or any of the games in Steam.
Steam occasionally allows me to preview the latest client features or bug fixes by opting into the Steam client beta.
Steam has achievements, a game overlay, friends list and chat and voice chat, and unlike other similar online services Steam offers all of its services for free.
Steam has frequent game sales and free game weekends, and guest passes and gift passes, as well as packaged games for good deals. Retail can't compare.
Steam allows for free mod developers to take advantage of Steamworks features such as achievements, auto-updating and Steam integration, and (eventually) Steam Cloud.
Steam is constantly being improved. Coming soon is the ability to sync Source engine games' saved games and settings with any internet-enabled computer running Steam (right now it's limited to L4D). It's called Steam Cloud.
It is possible through trickery to run as many copies of a game from your account on as many computers as you want for LAN party fun. The fun can't be taken online through as servers will reject duplicate accounts joining the same server.
Cons (though some of these are only cons to cheaters and pirates):
Steam must be running to run any Steam games.
Your account can only be signed on from one location at once.
If you cheat once in cheat-protected Valve games you are banned from joining any cheat-protected Valve games for life. Despite the seemingly high-risk of false positives such a system would have, Valve must have done a good job since I don't recall seeing any large-scale outrage over false bannings.
Sometimes offline mode doesn't work, preventing you from playing your games without the internet, but to be fair they said they fixed it and I haven't tried it since then.
If you're stupid, someone can steal your account including all your games.
Many of the game integration pros are limited to Valve games which have a tighter integration with Steam than third-party titles, even with Steamworks.