In Lisp, code is data. What benefit does that provide?
In Lisp, any program's code is actually a valid data structure. For example, this adds one and two together, but it's also a list of three items.
(+ 1 2)
What benefit does that provide? What does that enable you to do that's impossible and/or less elegant in other languages?
To make things a little clearer with respect to code representation, consider that in every language code is data: all you need is strings. (And perhaps a few file operations.) Contemplating how that helps you to mimic the Lisp benefits of having a macro system is a good way for enlightenment. Even better if you try to implement such a macro system. You'll run into the advantages of having a structured representation vs the flatness of strings, the need to run the transformations first and define "syntactic hooks" to tell you where to apply them, etc etc.
But the main thing that you'll see in all of this is that macros are essentially a convenient facility for compiler hooks -- ones that are hooked on newly created keywords. As such, the only thing that is really needed is some way to have user code interact with compiler code. Flat strings are one way to do it, but they provide so little information that the macro writer is left with the task of implementing a parser from scratch. On the other hand, you could expose some internal compiler structure like the pre-parsed AST trees, but those tend to expose too much information to be convenient and it means that the compiler needs to somehow be able to parse the new syntactic extension that you intend to implement. S-expressions are a nice solution to the latter: the compiler can parse anything since the syntax is uniform. They're also a solution to the former, since they're simple structures with rich support by the language for taking them apart and re-combining them in new ways.
But of course that's not the end of the story. For example, it's interesting to compare plain symbolic macros as in CL and hygienic macros as in Scheme implementations: those are usually implemented by adding more information to the represented data, which means that you can do more with those macro system. (You can also do more with CL macros since the extra information is also available, but instead of making it part of the syntax representation, it's passed as an extra environment argument to macros.)
My favorite example... In college some friends of mine were writing a compiler in Lisp. So all their data structures including the parse tree was lisp s-expressions. When it was time to implement their code generation phase, they simply executed their parse tree.
Lisp has been developed for the manipulation of symbolic data of all kinds. It turns out that one can also see any Lisp program as symbolic data. So you can apply the manipulation capabilities of Lisp to itself.
If you want to compute with programs (to create new programs, to compile programs to machine code, to translate programs from Lisp to other languages) one has basically three choices:
use strings. This gets tedious parsing and unparsing strings all the time.
use parse trees. Useful but gets complex.
use symbolic expressions like in Lisp. The programs are preparsed into familiar datastructures (lists, symbols, strings, numbers, ...) and the manipulation routines are written in the usual language provided functionality.
So what you get with Lisp? A relatively simple way to write programs that manipulate other programs. From Macros to Compilers there are many examples of this. You also get that you can embed languages into Lisp easily.
It allows you to write macros that simply transform one tree of lists to another. Simple (Scheme) example:
(define-syntax and (syntax-rules () ((and) #t) ((and thing) thing) ((and thing rest ...) (if thing (and rest ...) #f))))
Notice how the macro invocations are simply matched to the (and), (and thing), and (and thing rest ...) clauses (depending on the arity of the invocation), and handled appropriately.
With other languages, macros would have to deal with some kind of internal AST of the code being transformed---there wouldn't otherwise be an easy way to "see" your code in a programmatic format---and that would increase the friction of writing macros.
In Lisp programs, macros are generally used pretty frequently, precisely because of the low friction of writing them.