Lots of games on Steam simply don't have great (or really any) controller support. Steam controller can sort of play some of them though since it can emulate mouse + keyboard etc.

My experience with Steam Input is ... OK in some cases. It's annoying that it seems to break games that actually do support the DualSense properly (though full haptics only work in wired mode) like FFXIV.