for consoles, modding is practically non-existent.
for PCs, modding is a good thing to have, but not 'very important'. that's not to say that modding isn't important or hasn't had an impact.
as for the impact itself, it largely depends on how much control modders are given, and what type of game.
WC3 and SC/SC2 are games that are designed to be played in short bursts, or many rounds of short bursts (30min-1hr). thus creating whole new games isn't that much of a time investment for modders allowing for loads of creativity. look at shear number of the custom games in those titles.
games like FO3/NV and Elder Scrolls are much larger games with much larger playtimes. and most mods in those games are less about changing the main 'game' than changing specific pieces in the larger whole - and after a few years, pretty much any change you'd like to see, someone has taken the time to mod it. personally, i'm more a fan of aesthetic and quality of life mods than major changes to the game - i want the game to still feel like the game with most of the intended design quirks, just able to take advantage of the newer cards/processors. high rez texture packs for terrain, weapons, enemies, NPCs - awesome. being able to reduce or eliminate weapon decay - awesome. lighting mods - awesome.
but they also lend themselves to lots of crappy/broken mods (i can't stand most of the night vision mods for example), pervy fantasies - either making the females nude, or disproportionate, and/or sex scenarios, and just generally a lot of crap to sift through.
yes, there are examples of really good modding out there, some that have spawned new games or genres entirely or integrated some new ideas, but you have to remember, for every good mod, there are 100 bad ones. its great that others can try out your new mod, but can they even find it in the sea of mods? will they risk it crashing their game?
i will say though, when a group of modders decide to revamp a game, they can really do it justice - like the Wanderer's Edition for FO3. and of course KOTOR 2 and dark souls, but the difference is that the vanilla versions had major issues to begin with and weren't necesarily designed to be modded, while WC/SC and FO3/NV/Elder Scrolls are designed with modding in mind.