I agree about your different types of open worlds, with most falling into 'Ubisoft' style.
One thing I'd push back on is that Rockstar really pushed the boundaries of exploration with RDR2's open world.
I think this is true of GTAIII up through to GTAV, but not so much RDR2. Red Dead 2 has amazing, natural exploration. The bulk of exploration in the game is unmotivated ... there's no "Go explore the Tall Trees" or "Investigate the Noise," it's this silent narrative woven throughout the entire world that tells a cohesive story about the world. In RDR2, if anything visually grabs your attention from afar you'll usually be rewarded by riding out and investigating it, and it's amazing how much joy you get by the small little details they sneak all throughout the world. My favorite gameplay moments in the game are these ... unprompted discoveries, like, deciding to follow the Dakota River to it's source on the furthest, farthest reach of the map. I just rode out there, there's nothing prompting you to do it but you can't help but explore it... and along the way you can stumble upon all of these untold stories about the world... former camps that were abandoned, people that took to cannibalism, and a half dozen other little unprompted stories, and then if you do ride all the way out there you find "The Hermit Woman"
It's a woman who has become almost feral, she lives among her wild dogs who are hostile to you, in her house she has a unique gun, and also a fragment of a treasure map. You can discover this whole way of how she lives, what she eats, her motivations. And... the fragment of a treasure map leads you nowhere but it provokes your curiosity: Where is the other piece?
Well, the other piece is on almost the exact opposite side of the map, in a shack "owned by an angry isolationist." this one is more easily discovered, it's near a town and a side-mission takes place near it so it's hard to miss. You confront a man in a heavily armed shack, and rummaging through it provides the other piece of the map ... are these two related maybe? The map, when pieced together, shows a point of location that you can't even get to until after the end game, and it's a unique gun -- "Otis Miller's Revolver" -- who is one of the dozens (or hundreds) of dead/lore, lore characters in the game. Want to learn more about Otis Miller? Well, his name is carved into 'Register Rock' (a real thing reproduced in RDR2, people who moved west would carve their name/caravan into this famous rock), there's a cigarette card of him, and you can uncover other details about him throughout the game.
RDR2 weaves this narrative element around its open world. You're not explicitly encouraged to do anything, but because you're always rewarded by intriguing story elements and world-building lore, you want to explore, and then further exploration usually rewards you in small ways: A discovered treasure map (which is one of the most fun ambient side events that the game gives you, I *love* RDR's treasure maps), a unique pistol, or some other lost fragment or minor collectible... a small in-game reward for pulling on the threads of the open world.
Reinforcing all of this is how Arthur takes notes of all of these unique locations with interesting notes inscribed in your notebook, so you can go back and read them and be reminded of the unique locations.
(the notebook page for the Hermit woman, who Arthur describes "... might be a witch...")
(Register Rock, which has Otis Miller inscribed in it, you can discover his pistol by discovering both hermit houses, finding the map fragments in them which might link both hermits -- one being Miller's wife the other being a member of the Otis Miller gang.)
Or, if you explore just north east of Menito Glade (where the second Hermit lives), there's an ancient inscription in a large rock.
What does it translate to? "We arrived by boat. Beautiful land, gracious people. So we left them to live in peace"
These little mysteries are strewn throughout the world, there's nothing prompting you to discover them, but there's a handful of missions (like the serial killer ambient mission) that kinda suggests there's more to find in the world than just what you're called to explore. The serial killer ambient side quest is the first one that I was like "Jesus the care they put into this world is amazing..." and so it kinda nudges you, the player, to say ... "Hay, maybe I should explore all of these areas if these little secrets and stories are told throughout it..."
The serial killer ambient side story is probably the most well known one in the game. En route to the first town, in the wilderness, you might discover this grisly murder scene of a disembowled person hung up on a rock overlooking 'the new world.' There's a map fragment you can discover. As you go to the next town, nearby there is a similar murder scene of another murdered person with a bizarre note. Throughout the world you can find messages scratched or painted on walls which all seem to link together, before there's a third murder scene in a more remote portion of the map near a later camp. Newspaper clippings throughout the story hint towards a killer on the loose. Finally, if you stitch all the maps together you can find a key to a locked basement that you've probably passed a thousand times already and never thought much about ... and it brings you to the climax of that story. There's nothing guiding you to find these pieces, or map markers, or anything, you just discover them, and it's early enough in the game that it encourages discovery throughout all of it.
I *love* how Rockstar did this in RDR2. And there's hundreds of these little ambient stories woven throughout the whole game, and you only discover them by naturally exploring on your own. There's easily a dozen of them that are great -- the confederate soldier who runs off is another great one -- and so memorable. The biggest one that weaves in RDR2 lore and RDR1 lore is "The Strange Man." Note, this video has spoilers:
This YouTube channel is good, about ~70 videos of various mysteries in RDR2 and other Rockstar games (primarily RDR2 + RDR), but some of them are lamer than others and theyr'e all dressed up as spooky mysteries. This one is good though, especially if you played RDR1 & 2.
