I don't see mods as competing with expansion packs in the RPG niche/genre.
Making one's own adventure, having the community provide small new adventures freely while the developper works on an expansion that provides new art and stories is certainly something worth having for the players, and the expansion will mostly require a lot of work and storytelling that mods can rarely provide.
The most moddable RPG on the planet IMO is Neverwinter Nights. (II to be exact) I actually played the first Neverwinter Nights.. on AOL. It started waaaay back in the heyday of AOL as one of the first psuedo MMO games before Ultima Online.
Anyway, flash foward a few decades and you have a heavily moddable RPG game with a huge fanbase developing modules and even player-run worlds where players login to a player-created server with its own rules, own monsters, stoylines, classes.. whatever you can imagine.
Bioware has even successfully introduced "modules" for the game as purchasable expansions. These aren't, necessarily, conventional "Expansions" for the game. They're developer produced modules with sometimes a bit of fluff or new mechanics added in. Players/modders can opt to use some of the components or not for their own modules and mods. AFAIK, these module expansions don't break anything not specifically coded to use them. Basically, Bioware has entered the player's Mod Marketplace, competing with its modbase by offering exceptionally crafted mods with, in some cases, added game mechanics to entice new players and modders.
As someone else has mentioned, Oblivion has done well in the mod market place. So well, in fact, that Bethesda also sells "plug ins" for the game. These are developer produced add-ons and flavor enhancements. Need a new stronghold? Want a storyline to go with it? Well, you can download "Fred's uber cool cave!!1!111ELVENTYONE!11!!" or "Fighter's Stronghold by Bethesda." Which do you think is a quality product that will work seamlessly in your own Oblivion campaign? Here, Bethesda is successfully competing with their mod community. And, what's more, they also crossover to the platform market.(Mostly XBox, from what I can see.) So, those platform geeks have a shot at new content as well.
Someone out there, and I forget who atm, has managed to allow players/modders to actually "sell" their game modifications in a sort of "Second Life" way. But, I can't recall who it is. But, if someone is big enough and has the chutzpah enough to figure out how to allow modders to benefit from selling their work without breaching their intellectual property rights, they could stand to make a pretty nice profit and secure a dedicated mod community if their timing and product is right. For a game like Civ, it wouldn't necessarily work. But, it could be something to think about down the road. After all, a game is a system and mod tools are basically an SDK... It's all already done but the licensing..