I think a lot of it is the dopamine of getting to upgrade your character.
Also, I have observed that people LOVE getting everything together to get kitted out for a mission. If there’s some special equipment they’re going to need to go into the temple, and they’re trying to think what they would need once they get there and running around town putting it all together, they just get super excited and it gets them amped up for the adventure. It is fun in my experience, although yes YMMV.
My players stumble upon an abandoned version of one of my Dwarf Fortress cities.
I already know the whole layout backwards and forwards without needing to work with a map, and it has traps, epic architecture, purpose to every room, little random stuff scattered through, and I can usually come up with some kind of theme or ending to the dungeon for if they get that far into it. Easy peasy.