With L4D, it's due to the game design. Zombies rush at you in throngs, precision isn't as crucial since you can just spray into the crowd and take the whole bunch out in a few shots. There are no iron sights in the game because they aren't necessary. Zombies in the background stagger around slowly or stand in place, and precision is less of an issue. Hunters leap and climb, and that's where the precision comes into play. It's intentional because the game was designed for frantic moments, and no one is going to be pinpointing single zombies when a crowd of a dozen is bearing down on them. Auto aim, really, isn't even necessary in the game.
Call of Duty, on the other hand, absolutely requires a certain amount of precision, and if it weren't for the slightly magnetic "stickiness" of auto aim, I doubt the game would be much fun on a controller. I immediately noticed differences in the beta between the PC and 360 versions.
I prefer a stick for movement since keyboard finger presses have always felt slightly awkward to me, but I certainly prefer a mouse for look/aim.
Ideally, for me, would be an attachment somewhat like the nintendo nunchuk that I could use for moving/crouching/jumping, and a mouse and pad for aiming. That's just for fps games, though. RTS and other games which require many keypresses are generally borderline unplayable on consoles due to complicated controller schemes.