This is definitely a niche card, but it can be clutch in quests that require one character to provide loads of single-handedly. It was absolutely essential to blazing through stage 3 on my first successful playthrough of Mount Doom, but would also help with Hide tests, Racing tests, Escape tests, etc. Éowyn, Galadriel, and Glorfindel are a few of the great targets for this card, but really any hero with access to a lot of resources will work. Don't forget that it's a song, and can thus be fetched by Rivendell Minstrel or used to trigger things like Love of Tales!
Overall, Lay of Nimrodel is far from a staple, but it can be a very powerful card under a very specific set of circumstances.