This is a tough one. I think that, if we're going to add mods into our opinion of a certain game, we should consider, as MD said, whether or not the game was build with modding in mind, though in cases where games are not designed for this, it may be a good idea to judge the mod on its own, especially in cases of total conversion mods. By the same token, any mod that claims to "fix" things in the original game that players don't like, or perhaps wanted to see, can only really be judged well in comparison to the original game, the community's hopes & expectations, and the modder's ability to bring it all together. Some Unreal Tournament games, if I'm not mistaken, are designed to be modder-friendly, and tend to be good games on their own (at least, the ones I've played have been enjoyable). Strategy games, or at least the ones I play, don't tend to be built for modding, and can feel like totally different games after installing a mod.
One mod I've greatly enjoyed lately is for
Command & Conquer 3, and claims to be
the game that C&C3 should have been. I was completely sold on this claim after about half an hour. Nearly every memorable unit from the prequel games was brought back, modernized, and equipped with new upgrades and abilities that you'd expect after as much as 50yrs since the last time they entered the battlefield. Unit voices from old and new games across the C&C universe have been integrated, and the models of the new units, as well as enhancements of new ones, are so well done that someone who'd never seen the original game would believe they'd always been there. To make things better, the mod's designer is constantly working on new features, upgrades, and balances, and does such a good job that the mod never needs to be patched to keep one faction from having some dominating superweapon that that others can't do anything about.
In fairness to the glowing praise this mod gets within its respective community, I should note that C&C3 was kind of a let down for fans of the series such as myself. As I said above, many original units (mostly walkers that look like AT-ATs/STs on steroids) were removed in lieu of more "realistic" post-modern military weapons, and an entire faction went from "World superpower that rivaled the United Nations" to "quasi-religious fanatics with big toys". While the original is a decent game in its own right, EA's effort to make the game more realistic went against the somewhat comically absurd and self aware arsenal the series is known for, at least when they didn't make some designs that were impractical by even C&C's standards (lookin' at you, Stealth and Scorpion Tanks).