The canonical example of macro that is useful "in the wild" is embedding SQL in your code. See for example http://www.gigamonkeys.com/book/practical-an-mp3-database.ht...

(Disclosure: I am just an occasional LISP dabbler)