T2X for Thief 2. It is practically an entire new game.. When Thief 2 becomes available, I recommend everyone who buys it to try it out.

Close behind in joint 2nd place for me are Europa Barbarorum for Rome Total War, and Stainless Steel for Medieval 2 Total War. Both are huge mods that massively expand the original gameplay and are huge fun to play.

Project Reality for BF2 is also extremely good. I never got to play it too much though.

Hm....that's a tough one.

Thief 2X - Shadows of the Metal Age certainly comes to mind. A well designed single-player campaign for Thief 2 that can almost stand side by side with the original games. Very well done and definitely worth your time.

They Hunger (Half-Life) was a trilogy of Zombie-themed single-player episodes that did a good job of recreating the atmosphere of a classic Zombie flick in the Half-Life engine.

Silent Threat Reborn - A modification for Freespace 2 that took the original Silent Threat expansion for Freespace ( the first one), heavily modified and expanded on it. The result is nothing short of impressive, because it improves on the original. A lot!

The Maimed God Saga
A custom campaign for NWN 2. Very focused and well done experience. I wasn't enthusiastic about being forced to play a Cleric of Tyr, but once I started the campaign, I saw that the creator had done a great job of making the concept work. I had a great 15 hours with that campaign.

Kung Fu mod for Max Payne:
http://www.youtube.com/watch?v=RIhjtSY-nxg

Desert Combat and Forgotten Hope for BF1942

Movie Duels 2 for Jedi Academy:
http://www.youtube.com/watch?v=6EcRebkPdVY3

That was the last new game I bought; I should reinstall it and give that mod a try. Thanks for the tip.

Really, there's this awesome mod for Crysis 1 which I just started playing called "The Worry of Newport". Unbelievable Lovecraftian atmosphere and leaves no traces or crap in your PC, once you're done with it you can delete it and go back to Crysis without problems. More information here:

http://www.moddb.com/mods/the-worry-of-newport

oh and DOTA for WC3
(i guess you can call it a mod)

How about that Half Life mod for Quake?

*Badda dush*

I usually only play "game enhancing mods" not "new content mods", so I can't over a proper anwer here.

BG1 NPC Project for Baldur's Gate - adds lots of new content in the form of banters, interjections and quests.

Sword Coast Stratagems for Baldur's Gate - makes enemies smarter and the combat extremely much better.

Havish for Morrowind - best quest mod I've played.

Oscuro's Oblivion Overhaul for Oblivion - fixes most of the faults of Oblivion, most notably the level scaling. Also adds lots of content in the form of more items, enemies and quests, and lots of little touches that add to the immersion

Goldeneye Source - This uses Source engine, so you have to have a source game (Half-Life 2, or some other source game).

It's basically the N64 version of Goldeneye, complete with its weapons and levels that's upgraded to a high-res version. It's multiplayer only, but is a lot of fun!

I'm big fan of Company of Heroes so my best mods are Eastern Front and Blitzkrieg

GWX3 for Silent Hunter 3 : Totally revamped a rather mediocre game and turned it into the definitive U-Boat experience. Essential if you are geeky enough to be into that sort of thing...

The 'Nameless Mod' for Deus Ex: Adds an extra single player campaign which is surprisingly well designed. It's quite light hearted and full of Deus ex related in-jokes, probably not everyone's cup of tea but I was quite impressed.

Blue Planet: Age of Aquarius/War in Heaven, for Freespace 2: Fan made campaign still in development, has an epic story arc, new ship models, cut scenes and reasonable voice acting. But what stood out for me was the mission design, I thought they were better than the original Freespace 2 game.

I'm stuck at escort mission. What the heck is it with freespace and escort missions, it's the worst possible genre for those!

The escort mission in Blue Planet? Yeah that mission is really hard! If I remember rightly it's easier if you take a maxim gun and trebuchets, as you need to quickly destroy the beam cannons on the enemy cap ships as soon as they start jumping in. Good fun though...

Personally I like escort missions in space shooters, especially the ones that make you feel like you have to juggle priorities and be in several places at once, it gives the feeling of a puzzle that you need to solve rather than just straight up shooting. I remember Xwing and TieFighter was very good for that.