But I agree with what you're saying. If this thing is actually good, it could have some real mainstream appeal.
Steam chords are brilliant. If you have a steam controller and don't know about steam chords, go look it up. Life changing.
The biggest single win for me is replacing the right stick for camera control, as the trackball emulation with haptics on the touchpad is so much faster and more responsive than a traditional thumbstick. There are some games that I'd still prefer the standard twin sticks or dpad (twin-stick shooters maybe, some platformers, or hyper-specific designed games like Brothers: A Tale of Two Sons), but overall I've much preferred the touchpad over thumbsticks for almost every first- or third-person game I've played.
There are plenty of people with great things to say about it, but they largely seem to be people who wanted to play FPS games without a mouse and keyboard, and were willing to put in some effort to get it configured right. I can imagine it being more successful if it had gotten first-class out-of-the-box support for more games, but it seems like it never reached the level of adoption where that was worth it for developers.
I've never tried it myself, though. If the Steam Deck is a success, maybe we'll see the Steam Controller model finally take off (since the Deck has similar touchpads built in). Or maybe it will be like the Kinect, a pet feature deleted in later hardware revisions.