(personal opinion)
most of the time NSFW mods just don't fit in the game, in term of mood, atmosphere, style, narrative... they look out of place and kinda ruin immersion in the process
but there are always exceptions, CK2/CK3 kinda fit wild medieval era, Rimworld fit dark chaotic world, Sims obviously just expand on "adult" interactions... just to name a few
it all falls into personal view on the game, how you want it to look like and be presented as virtual world