One compelling use of macros in lisp can be found in the CLSQL library. What they did there was to embed a pseudo-SQL directly into the language, which compiles to calls to the underlying database (it supports several different SQL servers, including Postgresql). Some example code:
        (select [email] :from [users] :where [= ["lower(name)"] (string-downcase customer)])
This eliminates the problem of having to represent SQL as strings, which can lead to SQL-injection vulnerabilities. I think this solution is far more elegant than string-based parameterized queries in other languages, and it's way better than using an ORM framework.
One time, I had to write DB code for a customer that was using a database with an absolutely horrible proprietary query language, which was based on XML. The names of fields had to be looked up to get a corresponding field number, which was what you had to use in the query. Ditto for the table. You could write a whole page of code for one query.
They provided a framework in Ruby, but it was nothing more than a direct transliteration of the XML API. Using Lisp macros, it only took a day to boil this system down into a near-SQL macro language similar to CLSQL's macro language, which prevented the project from accruing technical debt.
Macros can save you from far more than just the word "lambda."
## Okay, CLOS is an interesting topic in the light of macros.
So, why does CLOS use macros and how does it use macros.
First, CLOS is *not* a set of macros and functions.
CLOS is defined in terms of CLOS. At the base, CLOS is written in CLOS itself. Especially if you have a MOP-based implementation. So, the lowest level of CLOS is generic functions, methods and classes. On top of that, a functional level is defined. And again on top of that, macros are defined.
An example: Class definition.
At the lowest level you create an object of type standard-class:
(make-instance 'standard-class :name 'foo)
The functional level is represented by:
(ensure-class 'foo)
The macro-level then is:
(defclass foo () ())
So, why does CLOS have this three level architecture and why does it use macros at the top level? Well, the implementation of CLOS in CLOS enables the software developer to change and extend CLOS via the usual CLOS mechanisms (subclassing, writing methods, ...).
The macros are used, because they allow to give the Lisp developer a familiar surface syntax. Plus, and that is a big PLUS, macros are making it possible to use load- and compile-time side-effects.
If a Lisp compiler sees (ensure-class 'foo) the compiler itself has no special knowledge about that function call. It is just an ordinary function call. So, all the compiler will and can do is to create the byte- or machine-code for that function call.
But if the compiler sees (defclass foo () ()), the macro will be expanded (!) at compile time and the resulting (!) code will be compiled to byte- or machine-code. Since the macro code for DEFCLASS is run at compile time, the writer of that macro can execute arbitrary code at that time and he can generate arbitrary code.
For what is that good?

• the Lisp compiler can be extended that way to do special processing.
• the Lisp development environment can record side effects. For example it will record the place where the class has been defined (the file)and what additional CLOS methods are being created by that DEFCLASS.
  
  

Many of the macros in Common Lisp are not really provided because they make the syntax more convenient to the Lisp programmer. The real reason is that it makes it possible for the Lisp implementor to achieve side-effects at load- or compile time.
Typical examples are: DEFUN, DEFVAR, DEFMETHOD, DEFCLASS, DEFMETHOD, DEFSTRUCT, DEFINE-CONDITION, DEFPACKAGE, DEFTYPE, ...
To see what your Common Lisp implementation does, just try:
(pprint (macroexpand '(defclass foo () ())))
Example:
        Welcome to OpenMCL Version (Beta: Darwin) 0.13.2!        ? (pprint (macroexpand '(defclass foo () ())))
        (PROGN (EVAL-WHEN (:COMPILE-TOPLEVEL) (CCL::%COMPILE-TIME-DEFCLASS 'FOO NIL))                (CCL::%DEFCLASS 'FOO '(STANDARD-OBJECT) NIL NIL NIL NIL 'NIL)                (CCL::RECORD-ACCESSOR-METHODS 'FOO 'NIL))So, macros are especially powerful, because at compile-time or load-time the whole power of Common Lisp is available. And this is not just only for writing clever functions transforming code (like the LOOP macro), but also for larger facilities like CLOS who need to be integrated tightly into the Common Lisp system and its development environment.
For the necessary understanding, you might want to read the book TheArtOfTheMetaObjectProtocol (Kiczales, des Rivieres, Bobrow). The code shown in that book is just beautiful and everything seems to just fall in its place.