For me, it has to be Total War: Third Age (specifically the Divide & Conquer version).

I mean two total conversions come to mind: Caveman2Cosmos for Civ IV, and FrackinUniverse for Starbound.

For me, maybe Nehrim for Oblivion?

Followed by Pirate DOOM and The Adventures of Square for DOOM (plus Hedon, but that one's commercial so I guess that disqualifies it)

(Not counting all the great stuff made for the two Neverwinter Nights and Forgotten Realms Unlimited Adventures because if I did that, I'd have a really hard time deciding.)

Perhaps the Restored Content mod for KOTOR 2: The Sith Lords could be mentioned here.

Yeah, probably should of specified total conversions as that's what I meant.

This. Haven't played Enderal, yet, though.

Chow Yun-Fat mod for Max Payne 2. Better than Stranglehold, I tell ya!

And "Mass Murderer - a FPS mod" for Hatred.

Enderal is pretty good, too (amazing soundtrack specifically made for the game on top of it), but I've never managed to completely play through it so far. The respawning enemies were a little off-putting to me.

moddb gave Far Cry 2 - Redux the 2019 editors choice award for best overhaul game mod. I played the game once stock and second time using redux. After playing stock I felt that most of the mods were great but some annoyed me, like all the pen on the map. However, I quickly grew to appreciate the design choice and overall I prefer the game in redux mode.

Falcon BMS (Falcon 4.0) and Power & Glory 3 (GTR2); both are great projects.

Ultima 5 - Lazarus

A faithful recreation of Ultima 5 using a total conversion of Dungeon Siege, with added content, which fits seamlessly into the great writing of the original game. The 'dark path' has been extended substantially. Awesome music too.

My two favorites are for Civilization IV:

BUG Mod: it's an interface mod. It doesn't change gameplay, just rearranges the information you can find in different screens in a more accessible way. It's so good that some people believe it's cheating, but it just shows information that the game already provides. This is the only mod I will always use in any game.

Fall From Heaven II: a dark fantasy total conversion mod so deep and fleshed out that it's basically its own game, that you get with Civ4 for free. Kind of a spiritual successor to Master of Magic, with a whole new spell system.

Never could get into it, unfortunately, though I started several times. Maybe it was a little too old-school for me, I never knew what I was supposed to do. There doesn't even seem to be a full playthrough of it on youtube.

Edit: No, I found a German one. Well, maybe I'll try it again some time or I'll just watch that one.

I haven't played Lazarus, since I liked the original one so much.

But the similar The Ultima 6 Project is one of my favourite mods.

Other great ones:
Oscuro's Oblivion Overhaul
Sword Coast Stratagems for the Baldur's Gate game.

Small, but extremely effective: the UOP for Bard's Tale 3, which fixed the bugs that made the original unplayable (to me at least).

Plus probably many I can't think of at the moment.

T2X for Thief 2, the first mod I ever saw that looked like it has the production values of a professionally developed game, high quality cut scenes, good voice acting, excellent level design and consistency with the original games designs and lore.