In my opinion, The Last Of Us' "Factions" Mode is the gold-standard for a tacked-on multiplayer mode in a game mostly known for it's single-player campaign. While it wasn't the most popular thing in the world--and the press surrounding The Last of Us mostly focused on it's story & characters--Factions quickly gained a cult following and had a decent-sized player base which kept the game alive for many years (which isn't a bad feat for a console game). It's definitely one of the most tense & brutal pvp multiplayer modes I've ever played and captures the savagery & desperation of that universe perfectly where you're constantly weighing your options, sneaking into abandoned buildings to acquire resources, taking cover, crafting useful weapons/items on the fly & brutally dispatching your enemies. It's all so seamless and well-done. The tension that builds throughout a match, especially when you're the last one alive for your team (and everyone is watching since when you die you enter spectator mode), as you try to pull a win out of your ass against 2-3 opponents can create some genuine hilarious & epic "hero" moments. It's always been baffling to me when people refer to TLOU as a "movie game" when the gameplay absolutely holds up and is one of the best of it's genre imo. That's why i'm also gutted the standalone Factions game for PS5 was cancelled, because the gameplay of TLOU 2 was significantly upgraded and that combined with the new graphics & tech would've made for an absolutely insane multiplayer mode.
Some honourable mentions: GTA IV had a pretty awesome free-form multiplayer mode that went mostly ignored in the media, but nonetheless had a big community. You were just dropped into the open-world with about 30 other players with no real goal or purpose, but the chaos that ensued was fun regardless. It felt like the beta version of what GTA V's multiplayer would be and definitely had the feeling of being "tacked on", but you saw the potential of what a multiplayer Grand Theft Auto could be.
One multiplayer mode I haven't played but I wish I had was Ninja Gaiden 3. I remember people shitting on it back in the day asking "why does this exist?" I mean why not? it looked fun and chaotic for what it was. A stylish character action game including a multiplayer mode is always interesting to me and I wish more games did something like this, even if it's just a throwaway mode ancillary to the main plot.
What was your favourite "tacked on" multiplayer mode that probably didn't need to exist but you're glad it did?