I'll have to chime in that while I guess something being 'lame' is a matter of opinion, some people have some interesting opinions of what qualifies as lame.
To me an artifact that is 'good' will fall into one of two categories:
1) Its useful (that is, you can place it somewhere to make a room valuable, or use it as a weapon/armor, or whatever).
2) It has an awesome description (I don't care if its a pointless siltstone trinket, if it has an awesome description of my dwarves slaughtering a minotaur its alright by me).
Sometimes these categories overlap, such as a cool but useless weapon/armor (due to material, type, etc), I assign them to some non-military dwarf I like for looks.
As for value.. Well even the crappiest most valueless artifacts will buy out most if not all of an average caravan, and it will (I assume) add to the value of your fort, but these things aren't good enough on their own to make them 'good' when measured against other artifacts.
I've gotten countless instruments or crafts studded with or ringed with the same material they are made of, those are lame IMO.
The coolest one I ever got was a silver sword (I know not the ideal material but a coolness factor) that had various decorations on it as well as an inscription of the death of one of my previous named dwarves. I put it in her tomb.
-MB