Gyro motion controls are great for aiming in FPS games. There was a Team Fortress 2 youtuber SolarLight who used gyro aiming in a competitive setting and managed to do pretty well for himself in terms of kills. His video on it is worth a watch if you're interested.

Not too sure where else it could be useful though.

I’ve gotten so used to having the extra re-mappable buttons on the back of controllers that I won’t use one that doesn’t have them, and have even modded both of my DualSense controllers to add them. The 8bitdo controller I mentioned has ‘em, plus it has hall effect joysticks, so IMO it’s even better than the 1st party “elite” controllers and it still comes in at half the price. If I were going to make heavy use of the extra touchpads on the Steam Controller I’d definitely be buying one.

I have yet to figure out what good motion controls are in game with any controller.

Same. I think this puts me off using the deck more undocked. Not sure if I just need to eat the pain of getting used to them but the gap versus a mouse is just so huge.