Revolution DCM for Civ4

Deus Ex has a few great ones, Nihilum, 2027 & The Nameless Mod are all pretty good if you're looking for more imsim content

Half-Life of course has a pretty massive modding community, and they've made a lot of good stuff over the years - Echoes and Field Intensity for the 1st game and Entropy Zero 2 for the 2nd are basically on the level of proper expansion packs. Minerva, Mission Improbable, Uncertainty Principle and Downfall (all for HL2) are somewhat shorter, but still really good. There's also new campaigns for the Portal games, but I haven't gotten around to playing those yet.

Mount & Blade has a ton of total conversions, for various historical periods and fantasy settings

Medieval 2 was already mentioned, so some other cool mods for it are The Italian Wars and 1648, for the pike-and-shot fans among you

The Blizzard RTS games have a ton of custom campaigns, although I haven't played many yet. StarCraft 2 in particular has recently been having a ton of new campaigns made (although mostly in the format of overhauls of the existing campaigns, rather than being entirely new missions, but they can still be pretty extensive with the new units and mechanics)

MGS V has had a decent amount of modding done to it too - nothing too massive, since the modding tools are very limited, but Infinite Heaven allows you to tweak a whole bunch of stuff (and, in combination with some other mods, significantly lighten the grinding, which I probably wouldn't have been able to stomach the game without), and allows people to add new custom side-ops

I used to play a shit ton of Forgotten Hope/Forgotten Hope 2 in the Battlefield 1942/Battlefield 2 heyday

Legacy of the Dragonborn for Skyrim is foundational to most of my builds, it gives you a museum and plays on one of the best parts of any RPG: finding unique items. It displays your shit and makes money off it, and adds some more unique stuff to find like coins, statues and of course equipment.

I miss the Masterwork mod pack for Dwarf Fortress. It added a bunch of stuff, playable races, and enemy factions. I still think about the playable orcs from time to time. The guy who organized the pack seems to not be active in the community anymore after he used free but unattributed sprites in his tileset, and this maybe costed him his collaboration on the official tileset for the Steam release.