Actually, what you said unlocked a memory. Though I don't know if it falls in line with the Gameranx video (I'll have to go watch that) or your sentiment. But the 'Players need that catharsis and pay off for all their efforts or else it inevitably starts to feel pointless rather than fun.' immediately made me think of the first Shadow of Mordor game. It was a great game, undone by a QTE final boss.

But yeah, so many of these games just don't go anywhere. To your point, the live service games. It's not 100% with what I intended, but I feel it ends up in the same area.... I'm spending all these hours... what am I accomplishing? What's the point of all of this? It's just endless padding with endless travel time, side quests, and anything that requires you to wait real time for the quest to progress. Dailies in WoW, were my WoW killer. Some people saw it as "easy gold"; I saw it as non-content meant to drive daily engagement but not actually accomplish anything in the game. It's all just padding for extra "engagement" or to make a game seem bigger than it is (or should be).

I'll break down some of the issues I had with the games I listed for better context. And I'll front this with, I know you don't have to do side missions. It's more like, you realise instead of giving you a tight, compact story that's well crafted, they spent too much time padding it out so it appears to be a bigger game. CP2077, the main story is absolutely dwarfed by all the side content. The main quest line is like... ~35 missions? There are like 70+ "gigs" and the same for "side missions". The main story is the thing you do the least. With missing mechanics, I can't help but think it would have been more interesting if it were done in a more linear fashion like Deus Ex Human Revolution. Instead of a giant city that's mostly empty boxes (the buildings aren't buildings) and padded out with side quests. Skyrim, the thing that killed it for me, was just how pathetically easy it was to become the leader of the various groups/factions. It felt so unearned. I can only take being handed "wins" left and right because I'm the fucking chosen one... before it's just dull. It was Medieval Idiocracy. I could have just started learning spells and they're ready to give me the college because I'm the smartest person they've ever seen. Brawndo, it's what Dragonborns crave. And Hogwarts, walking around the castle, was the best part. It felt magical and alive. Some of the puzzles were fun. But the classes were boring tutorial sections, and the main thing you do in the game is LEAVE Hogwarts to go do unspeakable things in non-descript burrows and dungeons scattered all over the place. That game has 15 main quests, 21 side quests. 95 Merlin Trials....

The tl;dr: An easy way to look at it, CP2077, Hogwarts, and Expedition 33 have similar playtime for just the main quest (per howlongtobeat.com, ~26-28 hours). But how it feels to play the game is drastically different. One had a story to tell and a point to get to, and it does that. The others made a world with a whole bunch of other stuff to do.