Well... since the OP already mention one of my favorite Visual novels of all time -- and some other members on here already mention other games that blew my mind like Xenoblade, FarCry 3, Spec Ops: the line, Catherine and a few others. Let me bring these two up.
When I first heard of Ace Attorney, I immediately chose to ignore it because the concept sound really boring to me. "A lawyer? Really?", I said. "What so good about a game you read most of the time?", I said! Being a gamer that grew up on games that went with a more action-oriented pace, the visual novel titles only seem like you're playing for an expensive electronic book, instead of a game... Or so I thought.
I found people constantly telling me how fun and deep this game was, and the premise of it. And I'll admit, the more I heard, the more it enticed me. It was then that I decided to bite the bullet and actually play the first Ace Attorney. Not only did this game prove me wrong about visual novels, it completely shut my bias up against them and actually help make the genre one of my favorites in video games today.
Ace Attorney was nothing like I ever played before at the time. It had simplistic gameplay mechanics that prove challenging simply by how much you are actually paying attention to the investigations and cases. And to top it off, the story and characters of this series are possibly some of the BEST I've EVER experience in a game. I literally make it high-priority to buy each one that's localized. knowing that I'll have a good time.
Now, even though the Ace Attorney games brought me into the visual novel genre. I was still a little hesitant about playing the Professor Layton games because of the puzzles being actual academic-style puzzles. Again, my friends told me how intriguing the series was for the art-style, music, characters and story. The puzzles will frustrate here and there, but there was always a solution available with hints and the option to back out of them.
So I gave it a shot, and it amazed me quite a bit. I'll admit that the first title had the largest focus on puzzles, and at times they felt like they got in the way of the story, but I still came through it satisfied. And with each entry afterward, it felt like they found a much better balance between the stories and the puzzles.