I like the Steam controller. I've tried both he Xbox and PS4 controllers. Their problem (design flaw I'd say) is that the left joystick to control your view only has 256 discrete positions in each axis. That makes it impossible to map an entire screen's worth of resolution, much less 360 degrees of rotation, to the joystick. As a result, all games using these controllers use a kludgy system where the stick controls camera slew rate. You push the stick, wait for your view to move to where you want it, wait, wait, wait, then let go of the stick. If you want it to slew faster, you push the stick further. It's like controlling view direction with the steering wheel of your car.
This is extremely non-intuitive, and makes aiming a PITA. The Steam controller replaces the joystick with a touchpad. This (1) has a lot more resolution, and (2) lets you lift your thumb and place it back down just like a mouse. Consequently, on the Steam controller, instead of controlling slew rate of view, you control view direction directly like with a mouse. It's like controlling view direction by turning your head. You just push your thumb where you want it on the pad, and you're now aimed at the target.
The drawback is that most games don't have native support for the Steam controller. Steam does a pretty good job of adding support for games in your Steam library (as well as giving you access to controller configs other users have made for each game). But a lot of times you'll need to do some tweaking to get it to your liking.
If you go with a PS4 controller, there's a user-developed project out there to map the buttons directly to the Xbox controller so it'll work with any game which supports the Xbox controller.
http://ds4windows.com/
That's the type of software you'll need to use third party controllers with most games. I suspect it'll change in the coming years. Now that Windows directly supports the Xbox controller, that should mean more PC games come with controller support, and third party controllers can make drivers to make their controllers compatible.