One of the cleanest examples in the book is the CL-PPCRE library for regular expressions.

Without macros, regular expressions are strings at compile time and then at runtime objects are created based on the strings to evaluate them.

With macros, regular expressions are expanded at compile time into Common Lisp code that implements the evaluation.

There's a similar idea with an ORM library.

Without macros, the object-relational mapping is defined at compile time and any dynamic SQL is generated at run time.

With macros, the object-relational mapping is used at compile time to expand the query macros into Common Lisp code that includes the actual SQL.

Then also remember that the compiler is available at runtime in a lisp, so the distinction only matters when comparing it to non-lisps.