"I have used Steam for some games and personally don't like it."
You may not personally like it, however that doesn't mean that it is objectively a bad thing.
"It seems way to intrusive on my computer and I don't like the idea that it keeps track of how many hours I have played each game, friends list pop-ups,Internet connection requirements, etc."
A lot of people are fine with it. You can set your profile to private if you don't want to share your hours with others. You don't see the friends list unless you tell Steam to show it.
"I also don't like having to be connected to the internet to install and play single player games. For instance when I bought Supreme Commander 2 I HAD to create a steam account and register the game just to play the single player game."
Umm, how else would you install the game?
You can, if you want, install many games via CD and register the key with Steam. You can even install a game via CD and just add a shortcut in Steam.
You don't need to be online to actually play a game. Just switch Steam to offline mode.
"I was just curious if I'm missing something about Steam that others are seeing. Why does everyone like Steam so much?"
Right now Impulse is actually being a pain in the ass. It won't accept new Object Desktop registrations, and I have a Tiles update that will haunt me until I get the matter resolved. Unfortunately, I don't know how to get the matter resolved, as they don't want to work with me. I'm not too thrilled about the situation.
Steam is a great place because it's a centralized place for every game I know of. Impulse came a bit later, and while it's a fine platform, 99% of my games I had already bought in Steam. It's just more convenient.