I used this feature for my last motion graphics project and I found that it was more of a hindrance than a help. I've used AE since CS3 and was used to making sure the keyframes went in where I wanted them to go. With Auto-keyframe mode I sometimes forget to turn it off and don't realize that I've set extra keyframes until thirty minutes after, making the elimination of extra keyframes harder to find. In theory it's good, but for users that have experience with setting their own keyframes, I don't think it is too helpful.
I haven't upgraded to CS5 but looking at John Dickinson's video, it doesn't look particularly useful, even for beginners. I prefer the concept that I set keyframes when I choose, for the values I choose. I'd find it very frustrating to be playing around with an animation only to find keyframes have been set without my being aware of them if I'd turned on auto-keyframes. I don't see the practical difference of switching on auto-keyframes versus setting keyframes in the timeline (apart from the fact that auto-keyframes I guess would apply to any transformation regardless of whether or not you'd intended to keyframe them). So I doubt I'd be using the feature at all.
Seems like an unnecessary layer of complexity for new users, and for experienced AE users I see no need for it. Of course it's possible I just don't understand all its uses yet. I see it as a sort of global switch that turns on keyframing, that's all. But the one thing I expect it to do - set a keyframe when I enable it - it doesn't do. (as John pointed out in his excellent video)
Thankfully it defaults to disabled so you never have to bother with it if you don't want to.