for me it was Harley Quinn. the fallout series has a culture aesthetics that stretches from the 50s-late 70s/early 80s so some of the cultural references I could dig/ the Fallon Slaves traitors have a companion that's a jason vorhees character that mocks slasher movies and even does Jason's iconic "ki ki ki ma ma ma " moan. great love it. They have a Brotherhood of Steel character named Ash that's an homage to Army of Darkness Ash Williams. Little goofy but hey it's subtle enough, it still fits and I dig it. And then you are introduced to Harley Quinn. Not only is the character straight up called Harley Quinn, not only does she look and sound like the character, she mentions the Harley Quinn character BY NAME AS A COMIC. Despite the fact that the character wasn't made until the mid to late 90s, plus the in game character is based on the 2016 suicide squad film version of the character.
also what place does a...dildo have in a fallout game?
I think the issue with stuff in those mods is that's it's too blatant and too common. You can have some stuff in a fallout mod that's a reference to a real thing, the Someguy series does it now and then and it's great. But Someguy only includes that stuff occasionally, it's not the first thing you encounter in the mod, it's a reference, not just the thing itself, and it is a background element. A core questgiver in Outcasts and Remnants is a guy that's just a blatant Bruce Campbell reference and little else, and it overshadows the nominal story of the mod because taking orders from Bruce Campbell really sticks out as a notable story element.
Though for me the horniness is the bigger issue. Fallout stuff can have varying tones, Fallout NV had DLC that was a wacky comedy, a deep character study, a "horror" setting, and a Pretentious Monologue by Chris Avellone. But despite the varying theme between those expansions, the tone is similar and the writing is similar and they feel like they fit the same game. 4 is similar, though I'll admit not quite as good and a great deal shallower. But it's still mostly a mature, reserved tone. The Thuggysmurf mods, on the other hand, are very adolescent and do not fit with the rest of the game at all.
And then the real core issue with those mods is that it does not bill itself as the horney pop culture reference marathon mod, it bills itself as a serious story mod and then crams it full of pop culture stuff as well (again, for a certain value of "pop culture", given the aforementioned Olsen Twins reference). I have no problem with totally incongruous elements being modded into the game, my current fallout 4 character is an anime waifu. But I made the knowing decision to include those elements.
Also, I missed this the first time:
mods really the only one of its kind that gives much needed expansion to the game.
If that's what you're looking for, I would suggest Tales from Commonwealth. It's a different sort of mod, a bunch of smaller, largely unrelated quests and content that you stumble onto every now and then rather than one big story, but it's an amazing mod because it fits in so seemlessly that I actually can't tell you which part of it is my favorite because it blends so well I don't know whats from the mod and what's from the base game.