<p>Many examples of macros seem to be about hiding lambdas, e.g. with-open-file in CL. I'm looking for some more exotic uses of macros, particularly in PLT Scheme. I'd like to get a feel for when to consider using a macro vs. using functions.</p>

I'll start answering the last question. When to use a macro instead of a function. The macros do things the functions can't, and the functions do thing the macros can't, so it'll be hard to mix them, but let's go deeper.
You use functions when you want the arguments evaluated and macros when you want the arguments un-evaluated. That's not very useful, is it? You use macros when you want to write something in a different way, when you see a pattern and you want to abstract. For example: I define three functions called foo-create, foo-process and foo-destroy for different values of foo and with similar bodies where the only change is foo. There's a pattern but a too-high level for a function, so you create a macro.
In my humble experience, macros in Scheme are to used as much as in other Lisps, like Common Lisp or
Clojure
. I suppose it's proof that maybe hygienic macros are not such a good idea, and here I would disagree with Paul Graham about why. It's not because sometimes you want to be dirty (un-hygienic) but because hygienic macros end up being complex or convoluted.