I wrote earlier a "fist impressions" blog post, about a week or two after I bought the Steam Controller. Now, several months later, here are my impressions with more actual experience using the controller.
It turns out that the controller is a bit of a mixed bag. With some games it works and feels great, much better than a traditional (ie. Xbox 360 style) gamepad, in other games not so much. The original intent of the controller was to be a complete replacement of a traditional gamepad, and even the keyboard+mouse mode of control (although to be fair it was never claimed that it would be as good as keyboard+mouse, only that it would be good enough as a replacement, so that you could play while sitting on a couch, rather than having to sit at a desk). With some games it fulfills that role, with others not really.
When it works, it works really well, and I much prefer it over a traditional gamepad. Most usually this is the case with games that are primarily designed for gamepads, but support gamepad and mouse simultaneously (mouse for turning the camera, gamepad for everything else). In this kind of game, especially ones that require even a modicum of accurate aiming, the right trackpad feels so much better than a traditional thumbstick, especially when coupled with gyro aiming. (Obviously at first it takes a bit of getting used to, but once you do, it becomes really fluent and natural.)
As an example, I'm currently playing Rise of the Tomb Raider. For the sake of experimentation, I tried to play the game both with an Xbox 360 gamepad and the Steam Controller, and I really prefer the latter. Even with many years of experience with the former, aiming with a thumbstick is always so awkward and difficult, and the trackpad + gyro make it so much easier and fluent. Also turning around really fast is difficult with a thumbstick (because turning speed necessarily has an upper limit), while a trackpad has in essence no such limitation. You can turn pretty much as fast as you physically can (although the edge of the trackpad is the only limiting factor on how much you can turn in one sweep; however turning speed is pretty much unlimited.)
Third-person perspective games designed primarily to be played with a gamepad are one thing, but how about games played from first-person perspective? It really depends on the game. In my experience the Steam Controller can never reach the same level of fluency, ease and accuracy as the mouse, but with some games it can reach a high-enough degree that playing the game is very comfortable and natural. Portal 2 is a perfect example.
If I had to rate the controller on a scale from 0 to 10, where 10 represents keyboard+mouse, 0 represents something almost unplayable (eg. keyboard only), and 5 represents an Xbox 360 controller, I would put the Steam Controller around 8. Although as said, it depends on the game.
There are some games, even those primarily designed to be played with a gamepad, where the Steam Controller does not actually feel better than a traditional gamepad, but may even feel worse.
This is most often the case with games that do not support gamepad + mouse at the same time, and will only accept gamepad input only. In this case the right thumbstick needs to be emulated with the trackpad. And this seldom works fluently.
The pure "thumbstick emulation" mode just can't compete with an actual thumbstick, because it lacks that force feedback that the springlike mechanism of an actual thumbstick has. When you use a thumbstick, you get tactile feedback on which direction you are pressing, and you get physical feedback on how far you are pressing. The trackpad just lacks this feedback, which makes it less functional.
The Steam Controller also has a "mouse joystick" mode, in which you can emulate the thumbstick, but control it like it were a trackpad/mouse instead. In other words, in principle it works like it were an actual trackpad, using the same kind of movements. This works to an extent, but it's necessarily limited. One of the major reasons is what I mentioned earlier: With a real trackpad control there is no upper limit to your turning speed. However, since a thumbstick has by necessity an upper limit, this emulation mode has that as well. Therefore when you instinctively try to turn faster than a certain threshold, it just won't, so it feels unnatural and awkward, like it had an uncomfortable negative acceleration. Even if you crank the thumbstick sensitivity to maximum within the game, it never fully works. There's always that upper limit, destroying the illusion of the mouse emulation.
With some games it just feels more comfortable and fluent to use the traditional gamepad. Two examples of this are Dreamfall Chapters and Just Cause 2.
As for the slightly awkwardly positioned ABXY buttons, I always suspected that one gets used to them with practice, and I wasn't wrong. The more you use the controller, the less difficult it becomes to use those four buttons. I still wish they were more conveniently placed, but it's not that bad.
So what's my final verdict? Well, I like the controller, and I do not regret buying it. Yes, there are some games where the Xbox360-style controller feels and works better, but likewise there are many games where it's the other way around, and with those the Steam controller feels a lot more versatile and comfortable (especially in terms of aiming, which is usually significantly easier).