I'm a single player first, co-op second, multiplayer third kind of guy. And in multiplayer I much prefer local multiplayer.
For me, nothing beats a deep, engaging single player experience.
As to your point about one-and-done games that gather dust after your first play through, I think that's more the mark of a badly designed game than it is an indictment of single-player only software.