IDK. Sometimes they're useful as a hint to content I have missed and would enjoy. Sometimes they give me the rough shape of a game so I understand the pacing better (like the table of contents of a book, or the book's size), and that can be nice. They can annoy me when I feel like I've as good as completed a game and the percentage is too low to match that feeling, but I'm mostly (only mostly) able to move on and ignore them when that happens, rather than play the game too much past its welcome.
I basically just ignore them on Steam, and I didn't play PS3 with trophies iirc and I don't remember my early X360 experience, except the art for the Oblivion achievements. So I'm mostly thinking about my recent Trophy experience, having gotten a PS4 last December.
Basically, it is fine when I'm able to just admire or think about it as a result of my gaming but it can be a bit of a thing on my shoulder from time to time and I don't enjoy it as much when I find myself trying to get trophies.
Thinking about Switch...the existence of Zelda trophies would've probably annoyed me. There'd be Korok trophies. I felt I had a really could, complete experience doing all the shrines, getting all the gear, doing the main quest, etc. And I also feel there are way too many Koroks (I think I may have 200+? Maybe under 200?), so the tug of "Zelda is a game you platinum!" would've caused some friction for me. I'd get over it, but I'm glad Zelda at least had no achievements
.