I have mods for almost every game I own. I have quite high standards for games, and for most of the games that I get, I need to mod them before I consider them enjoyable. Fallout 3 was a prime example of this. The game bored me till I got the Enclave Commander mod, some sniper rifle mods, a radio mod that added 100 more 1950's songs, and some perk mods.
I've spent many hours in Third Age:Total War, a mod for Medieval 2, and Stainless Steel is essentially Medieval 2, but 3 times bigger, more detailed, with better factions, units, and graphics.
The seven deadly sins mod for Sins of a Solar Empire adds quite a few new races into the game, and the volumetric explosion mod makes things go boom shinier.
For Company of Heroes, standard play now consists of me booting the game through the Eastern Front mod launcher and being able to play withe everyone as well as play as the Russians with other people who have the mod. The Battle of the Bulge mod is an awesome historical mod that gives tanks realistic ranges(about 2 screen lengths or so), makes weapon ranges and damage accurate(when an mg opens fire, expect people to die) and improves gameplay mechanics.