It's important to note that the magic-user/elf version of this spell is slightly different than the clerical one. It's a third level cleric spell and 2nd level for MU/Elves. Clerics have a fixed range (120'), which is better than the arcane types' at first, but their version gets boosted by caster level, eventually outpacing the cleric at 7th. The cleric gets a much longer duration, though. The general effects of the spell is identical for both. Most of the time I've seen the spell it's been clerical. After all, the cleric can just pray for the spell for the day rather than permanently tying up a slot in their spellbook with it. So when used, it's often the 120'/6 turns version.
There's nothing in the BX description about being blocked by materials like lead, as some versions of the game have used. So dungeon walls, etc. aren't going to impede it. The spell will tell only direction, not distance, and only if the object is in range. Outdoors, even converted to yards, that's not terribly far unless you already know you're close.
To locate something specific, you need to know exactly what it looks like. You can't just say "The Crown of Oogabooga!" unless you've seen it or at least a good likeness/detailed description. Common, nonspecific objects (the text uses "stairs" as an example) are searchable based on what the closest example is (again, if in range at all).
I think the "common item" facet is where the spell is most likely to be useful. Once a party has been through a stretch of dungeon and mapped it out, movement back through the area can be made faster than "exploration" speed. If, foir example, a cleric casts the spell and moves steadily through the cleared area sweeping for "secret doors," he stands a good chance of finding any the party might have missed. If he "pings" one farther away in the dungeon, they know that there may areas they haven't seen yet.
The spell can't locate creatures, but -as the illustration above shows- it can get a fix on loot. "Gold." or "Coins" or "Gems" could save valuable searching time.
One caveat: The spell only locates one thing (or type) per casting. Meaning you can't start the spell with the crown example, then switch to secret doors, and then to gold coins. The duration's function is not to take inventory of the dungeon. It's to triangulate!
Let's say the caster was looking for secret doors. If they are right in front of one, small movements to either side will cause drastic swings in the "angle" of the ping. If the nearest secret door is nearer to maximum range, the shift in angle is subtler. While I wouldn't allow precision to seconds of arc, I wouldn't punish clever spell use coupled with careful map-making to help the party find something. If multiple castings were used over time, it might be possible to find hidden treasure vaults or the like.
As far as specific objects are concerned, I can imagine scenarios where the spell could be useful, but unless the DM deliberately sets things up for it (e.g. a portrait with a detailed image of the item) or the players plan well ahead on this tactic, I see that use being somewhat uncommon. Still, it could be fun if it happened!