Great modding support definitely goes a long way in adding to a games replayability and long-term appeal as well as in building a loyal, engaged fanbase. Without modding Nexus would have ended up as just another game on my shelf, but through it's decent modding capabilities I kept coming back to it - not just to consume mods, but to develop my own and support others in their endeavours.
Of course Nexus is not a shining example for financial success through solid mod support, but there are other games and franchises greatly benefitting from its modding community. X3 was already mentioned - mostly a broken, badly designed mess of a game not able to ever achieve its great potential, if not for the steady community support and great user mods. The Elder Scrolls series and latest Fallout games tell another story of good games becoming great just through modding. Even "boring" simulators such as Euro Truck Simulator 2 and Farming Simulator 15 have had great success in allowing users to add their own content to the game - even up to a point where there is a fully fledged and highly enjoyable MMO-like multiplayer mod for the actual singleplayer game ETS2.
So yeah, I strongly believe that modding can be highly beneficial to any game, even if just limited to adding new models and textures and fiddling with some values in XML files.