It works GREAT! It keeps my kids occupied the next morning while I get the fire and breakfast going.
My son is 7 and my daughter is 5. I try to mark some things with chalk in a perimeter of where I buried the coins to help reduce the search area that they are going over with the metal detector. For example, I would put skulls and cross bones on the trees and any large rocks that border the area for my pirate story, or shamrocks for my leprechaun story.
You can get really imaginative with it too. If you don't want to bid on coins on ebay, go to Chuck E. Cheese, gets some tokens and smash them up with a hammer till they don't look like tokens... voila... "gold doubloons". hehehe
You can make up any kind of story that involves treasure, but try to be specific with a number of coins or they might keep searching ALL day for the "next piece". I will usually say something like, "When the Leprechaun got his pot of gold home, he counted it and realized that 5 pieces of gold were missing". It makes it more definate!
How old are the kids you want to do it for?