Let’s say, you’re writing a web application. In most programming language you’d be using libraries or rely on a framework. With Lisp macros, you can program the archetype of a web application. And then use a simpler language to describe you application. Think of it as programmable snippets. Something like Ultisnips [0], but inherent to the language.
Think how much common code can exist in a software but cannot be refactored to a functions because it will have too many variables. Or the multiple problems with classes tree and overloading. Macros let you solve that.
One example is the ~use-package~ macro (Emacs plugin) [0]. Using packages in emacs is mostly the same code over and over. They've already been abstracted in functions, but you still find yourself juggling with so many utilities. You could write a bigger functions, but it will then have a lot of conditional branches. This macro selectively select the code it needs and transforming it if needs be and then the result will be evaluated.
It's a bit hard to explain for me (English is not my native language). But it's the difference between coding a solution with all the edge cases baked in and coding an archetype that let you add your own cases. With functions, you abstract common algorithms, with macros you abstract common architecture.
Think how much common code can exist in a software but cannot be refactored to a functions because it will have too many variables. Or the multiple problems with classes tree and overloading. Macros let you solve that.
[0]: https://github.com/SirVer/ultisnips