I think it will find its own niche, overlapping some of the games which were previously keyboard/mouse exclusive as well as some of the games previously XBox/DualShock controller exclusive.
For example, Dishonored plays fantastically well with the steam controller. You have all the advantages of a traditional controller, except the camera control is much more precise and mouse-like.
And although the steam controller may never compete with a mouse and keyboard for games like CS:GO, r/SteamController is reporting encouraging findings, with people claiming to be surprisingly competitive using a steam controller by utilising dual aiming (trackpad for large, coarse movements and gyro for tiny correctional movements).
The bottom line is that for me a steam controller is a massive improvement over a traditional controller, it is like it takes the best aspects of mouse+kb and game controllers and combines them in one package.
Console makers should take note, but unfortunately at this point outside nintendo the traditional console makers are very unwilling to innovate with the base controller.
When you'll join a PC server with your steam controller, you will have 0 kills and 20 deaths, because everyone is playing with a mouse.
That is not fun.
Steam controller helps with, I don't know, 10% of the gaming - the single player. And if you're casual and single player is 100% of your games - hell, buy a Wii U, because PC has zero split screen games.
https://www.youtube.com/watch?v=GKuO9M0WuMg&feature=youtu.be
(disclaimer - I work at Valve but not on the steam controller so this technique was a surprise to me)
If any of the popular game consoles were to implement something similar for their own controllers, it would be considered a "killer feature".
That is very fun.
As for local co-op on PC, it could use some improvement, but there are already more local co-op PC games on Steam than Wii U.
http://store.steampowered.com/tag/en/Local%20Co-Op/#p=0&tab=...
Lately people in my social circle have been getting together on a weekend afternoon and playing a cheap game. Usually they don't have much depth, but if I get four or five hours of play out of a game that cost $4 I'm happy.