Steam is a digital shopfront for the PC, first and foremost. Secondly, it is a DRM system. Thirdly and finally, it is a content management system. The real question here is which games benefit from the content management features, and are best on PC. Some content management features are great (cloud saves), some are ok (friend lists, but consoles have these too), some are atrocious (forced updates) and some are plain meaningless (achievements).
I'd go out of my way to purchase the following on Steam:
+ Any first-party (Valve Solftware) games.
+ Games that use Steam's cloud save service.
+ Multiplatform games that are best on PC (mods, mouse input, simply a better version) that don't fit in the negative categories below.
+ Multiplayer games that are PC specific or best on PC.
I'd intentionally avoid the following games on Steam:
- Modern AAA titles that are known to be update heavy. Steam will force updates on you, even if you are playing another game at the time. This creates huge performance dips to whatever you are playing at the time. The best prevention is to avoid purchasing/installing "needy" games.
- Games that require second-layer DRM. The Steam shop has a fine print referring to "third party digital rights management" on these titles. Again, horrible hit to performance. Avoid, avoid, avoid. If Games for Windows Live is mentioned, purchase on console only.
- Games that are best with a gamepad, if you have a console. Its just easier.
The main benefits of Steam are the cloud save, some multiplayer features (friends lists). The other benefits are the price and ability to redownload, but these are shared with rival digital shopfronts such as Good Old Games (GoG). I'd typically go Steam over a boxed copy of a PC title any day, but if the price is equal, I'd choose GoG for titles that don't benefit from Steam's extra features for performance reasons.