I find that the most serene and unassuming title screens have been the most impressive for me. Like TLOU part I and II. Just window curtains moving gently in the wind, or a boat bopping on the waves and nothing else. You just know it's going to be some deep stuff. You want to know what the story is going to be.
If I were a Nintendo gamer, that Xenoblade Chronicles thing with just the sword in the grass would do it for me.

Many. But the three that comes to might right away are FFX, OOT and SOTC.

Here are some title screens that really impacted me over the years to the point that I would love to just sit back and watch for a good while.  (For some of these ones in the 80's you need to realize that they blew almost everything else away by a mile.)

This might have been the first console game I saw with a real intro.  It's definitely memorable.

The music on Double Dragon is what really sticks with me.  The other arcade games were often quiet when no one was playing them, so this might be the only sound you'd hear in the arcade when it wasn't busy.

I've never heard music like in any non-Metroid video game.  However it somewhat reminds me of the famous music from 2001: A Space Odyssey.

I really love the PS1 intro to Final Fantasy Tactics, however I think I like this intro FFT Advance even better.

Not sure how this'll work, but hopefully these show up.
Favorite is earthbound. That opening music and picture of ufos attacking earth made 8 year old me fall in love. https://m.youtube.com/watch?v=viP2-Mz4aaQ

As far as Title Screens go, this is one is as iconic as it gets (and I'm surprised no one has posted it yet):

P.S. this is my post number 1.001! Yey! Help me celebrate my 1.000th post here: https://gamrconnect.vgchartz.com/post.php?id=9368779

Xenoblade Chronicles and Sonic CD (JP).

Ahh yeah!!! Sonic CD was great, we had the same opening in PAL too.

https://www.youtube.com/watch?v=rUTbCQsaujE

First game I thought of when I saw the thread title. I got this and Final Fantasy with my NES for Christmas as a kid. Let's just say it was a great Christmas.

My first Final Fantasy game. Doesn't help that it segues directly into one of the best openers I'd ever seen in a game at that point.

One of my very first RPGs, period.

FF Tactics intro screen. With this one, I wasn't sure I would like it. I didn't have a lot of experience with SRPGs. Now they're one of my favorite genres.

Square didn't even wait for the official opening animation to begin trying to tell you that this was going to be an exciting game. Animated logo.