I have seen lots of examples of macros used to do things like square numbers. These are just minimal demonstrations of the technique, so you expect the code to be longer and make less sense. But the same thing appears to happen when the problem is more involved. The code makes less sense, and it's longer than the code I would write for a more traditional solution.
I know little about Lisp, but I wonder if Lisp programmers aren't conflating the advantages of macros with the advantages you get simply by building up functions in an intelligent way. Macros are neat, granted, but I've yet to see an instance where they actually increase productivity.
If this hypothesis is wrong, I would love to have someone point out why. I have been wary about approaching Lisp precisely because the preoccupation with macros seemed to me to be unjustified (and thus symptomatic of a culture more preoccupied with doing things the computationally interesting way rather than the more powerful way, e.g., using recursion when a for-loop would be shorter and faster).