Can you consider a game to be "good" if mods are nearly necessary?

Honestly, it depends on if the game is supposed to have mods through developer encouragement.

The Sims, for instance, has a huge modding community because EA legit encourages people to create their own things. Finding mods is part of the game, really.

Skyrim, however, is but a decent game when left vanilla - and if you're playing on a console, you're SOL. I know I'm far from the only person who will start up a new Skyrim file every 3-4 years, get bored after 20-30 hours, give up and wonder what the hell I was doing whenever I go back, repeating the cycle anew.