The model of game where there's a big shared goal among the playerbase that they can work together in is fun and can include stuff like easter-egg hunting, solving unsolved puzzles, figuring out speedrun strategies, etc. It's exciting playing a game knowing that if you figure something out or do things right then it could be what's talked about in the game forums among the playerbase and move things forward for everyone.
Another interesting model of game is one where the act of playing it leaves you with an interesting and personal artifact, like the bases you end up making in Minecraft, the factories and lines of automation you make in Factorio, the codebase you build in Screeps, etc. These games are great for pushing you to pick up skills in building kinds of things that you find interesting but wouldn't otherwise have a good excuse to work on.
Games that don't fall into the above two models seem to mostly be about exploring the same space that everyone else does, and that kind of thing has a tendency to run out after a while and can be limited in how often they give you unique experiences. (It could be a physical carefully-constructed space in the game you're exploring, or the space of possible ways a Counterstrike match can play out, etc.) I think it's important to recognize in a game when you've finished exploring the space of the game and now only re-treading familiar ground, and to decide if you're still getting something out of that. When thinking about designing games, I wonder a lot about how to make the experience space wide and avoiding having long-time players only be re-tread familiar experiences.