I don't find YKMN lyrically bad at all. It's easily one of the best all time Bond songs IMHO.
I like the idea of YKMN, but the execution is poor. The musical track that they chose for the version that is actually in the film is rather poor, as the mixing of the song leaves quite a bit to be desired (Cornell's vocals are far too muted in the film version). The version that Cornell released as a single, which is more rock-oriented in terms of its sound, is far superior.
But in terms of the lyrics, it's the overuse of the word "you" that just kills it for me. By my count, 36 of the song's 50 lines contain "you", and that's not even including other lines that include variations of the word. It becomes distracting after a bit, at least for me.
I do very much like the instrumental though, as it's presented in the non-film version of the song.