Counter Strike and Team Fortress. Both were Half Life mods when they started.

Black mesa. Half life and half life 2 modder community is amazing.

Multiverse Mod for FTL

Its literally just a free sequel

No love for Fallout: London?

It's not just a Fallout 4 mod—it's full-blown AAA experience—done so well that I'd believe it was made by the New Vegas devs if I didn't already know. If you're tired of waiting for Bethesda to make the next FO game, just play this instead.

Multiplayer for GTA San Andreas was quite revolutionary at its time.

Withers big naturals

Surprised no one has said MyHouse.wad yet

There are quite a few honestly. Team Fortress and DayZ are obvious examples, I also quite liked Natural Selection.

Dragon Genitals for Monster Hunter World. Gave each monster either a realistic cloaca or an incredibly well-crafted dong, tailored to the individual style of each monster. The details were amazing, like a monster called Bagel Goose has a rock dick, but when he transforms into B-52 bomber mode he glows red hot with lava… and his cock does, too.

Allowing you to collect all three triforce pieces in Ocarina of Time using arbitrary code execution.

A Link to the Past/Super Metroid combo randomizer. Both games smooshed into a single ROM, with certain doors taking you from one to the other. Item pools are then shuffled together, sending you on a big scavenger hunt to find Metroid items in Hyrule so that you can explore Zebes to find Zelda items so that you can explore Hyrule.

Unfortunately it's somewhat lopsided due to how much bigger ALttP is than SM, but the technical novelty of it is amazing and worth a playthrough.

Elden ring seamless coop. I can't believe that such a small file could create such a better experience in the game.

Inigo in Skyrim. Fully voiced follower who is funnier, more interesting and more authentic than any companion I’ve had in the last decade of games.

In terms of actual impact, a Fallout: New Vegas mod named Tale of Two Wastelands that takes a Fallout 3 copy and brings all of it into the Fallout: New Vegas world.

The mod makers didn't create the great bulk of the content, made a converter, but it amounts to a new experience with an AAA-game level of content addition.

Does make me wonder about other mods that can leverage pre-existing work. Cataclysm: Dark Days Ahead had a mod where someone imported a real life map of Massachusetts, for example. Some flight sims can do similar stuff.

"The Last Days" for Mount & Blade. Intro

Mount & Blade was a medieval combat simulator where you're a wandering mercenary in a war torn region. The gameplay was somewhat innovative when it came out (originally 2008), but as far as story and setting, it was kinda meh. But the great thing about it were the mods, and by far the best mod was "The Last Days," a total conversion mod that lifted all the mechanics but put it in The Lord of the Rings.

These nerds put so much detail into that mod, it was incredible. I didn't know half the factions existed. The map goes beyond Gondor and Rohan and even Rivendell, it goes all the way out to the goblins of Mt. Gundabad fighting against the men of Dale. Each location has tons of detail, some of them are truly massive, and they pushed the limits of the base game with trolls and giant spiders and things like that. Each individual faction has their own faction strength, so you have to think strategically about where to reinforce. The differences between how each factions' units fight on the battlefield is more emphasized, and tbh the combat is a bit better balanced.

It really hit the nail on the head of what to do with the game engine, and then put real passion, creativity, and effort into making it work (seriously, the last update was in 2017, new stuff was being added after nearly a decade. And there's comments on the page I linked as recently as 10 days ago, so people are still playing it). Next to it, the base game feels a bit more like a proof of concept. The open world nature of the game really allows you to take in the setting and experience the story from a different perspective, rather than following the heroes from the books. It really made the setting come alive in a way I haven't found in really any other LotR game (although I'm not super into the setting and haven't played a ton of them).

The mod was for the original game at first but it update when they put out the sequel, Mount & Blade: Warband, so if anyone decides to check it out, get that one rather than the original.

Space Age for Factorio before their basically made it a full DLC for the game

Expanded Stardew Valley! So much added stuff!

Young me (and current me too) was always blown away by the Game Genie on the NES. Simple piece of tech that could do so much with so many games.

For me personally it has to be certain rom hacks from before the era of decomps and creation kit style tools.

I was fairly active in the gen 3 Pokemon hacking scene on the pokecommunity forums back in the day. And hex editing and trial and error was one thing but real wizards were the ASM hackers that could completely out of the box stuff like adding in new move effects or character customisation functionality or day and night systems, to games that had none of these.

Those were actually the trailblazers that led to all the decomps we have today that have made it so easy.

Does the flight sim in Excel count? If yes, then that.

Otherwise, Day of Defeat (Steam mod). Still somewhat peeved that it never got the same recognition as CS.

Vampire: The Masquerade - Bloodlines Unofficial Patch by Wesp5.

That game was already broken at launch, and has become more broken as operating systems advance. Furthermore, Activision shuttered Troika almost immediately after VTMB was released, so there has really never been any official support. But, Wesp5 has been working on the Unofficial Patch since shortly after the game came out in 2004.

It fixes the game to a completely playable state, which is a miracle for VTMB. He basically just finished the game for them.

Furthermore, there was some controversy over some of the changes that the patch started implementing, so he split the patch into two versions. You can download the True Patch, which fixes the game to a playable version of its release state, but Wesp5 also offers the Plus Patch, which restores all content cut from the game before release. He reimplemented character backgrounds, real-world weapon names, multiplayer, quests, locations, music, and the list goes on.

It's arguable if the Plus Patch actually represents the original vision of the creators, since we don't know why all of the content was cut. A good bit was cut for time, but there were some things that were dropped earlier in development. Either way, the Plus Patch went feature-complete last year, so what we have now is a fully working version of the game containing absolutely everything that was ever planned during its development.

I don't know if anything like that has happened before, and the only other thing like this that I can think of is the Saints Row 2 patch that IdolNinja was making before he died.

Mods that add stuff are great, but it's hard to beat a mod that lets you play one of the greats.

Fall from Heaven 2 (overhaul mod for Civ4) would've been top pick until this year, now it's Enderal (even calling it an overhaul is selling it short, it's a whole new game built on the Skyrim engine).

Space Exploration for Factorio. My dude built a huge mod for free, then was hired by the devs to desogn the paid sequel, Factorio 2, and although large and great its still smaller and less complex then the mod.

Pokemon unbound. Its a completely new game.

Doom: Infinite is pretty cool. It turns it into a roguelike. It’s amazing as is, and is still in beta.

Cursed Halo.

I believe it was a passion project by one guy that took 5 or 10 years but holy shit it's a masterpiece.

Star Trek New Horizons for Stellaris. Best Star Trek game I've played.

Strawberry jam for celeste. It's a huge collaborative project that introduces 100+ levels (base game has 25) with a really wide range of difficulties, graphical styles, new mechanics, original music and ideas on par with the quality from the base game. Definitely a must play if you like 2d platformers

Cataclysm: Darker Days Ahead.

Daily pull requests for over two decades on an open source project

Super Mario 64 that added lugi as co-op

Team Fortress and Team Fortress Classic.

Witchery for Minecraft. Start out, building your altar room, find as many unique living things as possible and put them within 100 blocks of your altar. Mid game, make sleeping tonics to visit the spirit realm and try not to die from nightmares. Create voodoo dolls, and other nice high level gear, craft some powerful pvp oriented spells. Late game create a ritual room to summon demons to trade with. Don't forget your protection circles because they move really fast and kill you almost instantly. Late game, become a vampire or werewolf or hybrid. Become nearly invincible outside of fire and sunlight.

X-Com and X-Com 2 Long War mods. Essentially the games at their full potential.

That reminds me, It's almost time for another playthrough i think.

DSfix

Single-handedly made an awful PC port playable.

Princes of Darkness for Crusader Kings 3

Midian XL for Diablo 2

The entire OAR for Fallout 4:

Outcasts and Remnants

Fusion City Rising

Depravity

Project Valkyrie

Doom 3 OpenCoop. There's a few mods that convert the single player campaign into online co-op. At the time the competition was Last Man Standing, but I liked how vanilla OpenCoop was. There's better mods now, and OpenCoop never left Alpha versions. Although the only game breaking bug is with the first teleporter sequence which kicked all clients from the server. Easy enough to skip that segment.

It's a bit of a scripting nightmare since the game uses so many floor triggers for events never intended for multiple players. That's what impresses me about the mods.

That or The Dark Mod which is now a Standalone freeware title using idTech 4 engine to make a fan sequel to the classic Looking Glass Thief games. There's a TON of fan made high effort maps to play on.

Since someone already mentioned Fallout London, then I'll recommend Shutoko Revival Project for Asseto Corsa, a full blown open map in the speedways of japan with online support and even functioning traffic so the illegal racing isn't a breeze.

It's lesser-known, but the Battletech Advanced 3062 mod for Battletech from Harebrained Schemes is incredible. It adds so much it's almost an entirely new game (but still Battletech).

I came across the Star Wars Helldivers mod. It’s basically Battlefront 3 but man it looks cool