The LOOP macro has actually seen a lot of work in designing and implementating it, including a syntax spec. Some people don't hate it because it is a DSL, but because it is a bit different from the regular Lisp macro, in that it requires more parsing and its clauses are not grouped by parentheses/s-expressions. Plus: understanding the relationship of the clauses is not really that simple, since there is some implicit grouping and dependencies.

This is what a LOOP macro looks like in code:

(loop for i from 0 below n
for v across vec
when (evenp n)
collect (* i v))

This is what a typical Lisp programmer would prefer:

(loop ((for i :from 0 :below n)
(for v :across vec))
(when (evenp n)
(collect (* i v))))

The clauses would be grouped in a list and each clause would be a list. The body would then use the usual Lisp syntax and the WHEN and COLLECT features would look similar to normal Lisp macros.

The LOOP macro is historically coming from Interlisp (70s), where it was a part of a certain language design trend called 'conversational programming'. The idea was to have some more natural language like programming constructs, combined with tools like spell checkers and automatic syntax repair (Do What I Mean, DWIM). From there this idea and the FOR macro was influencing the LOOP macro for Maclisp. The LOOP macro grew over time in capabilities and was then transferred to later Lisp dialects, like Common Lisp.

There are actually Lisp macros which are even more complicated to implement and even more powerful, but which create less resistance, since they are a bit better integrated in the usual Lisp language syntax. An example is the ITERATE macro: https://common-lisp.net/project/iterate/

Thus it is not the complexity or the functionality of the macro itself, but a particular style of macro and its implementation. I personally also prefer something like ITERATE, but LOOP works fine for me, too.

The advantage of something like ITERATE or even LOOP is that they are mostly on the developer level, and not fully the implementor level. A developer or group of developers can develop such a complex macro and can integrate it seamlessly into the language, making the language more powerful and allows us to reuse much of the knowledge/infrastrucure about/of the language.

Implementing and designing something like ITERATE and LOOP requires above the usual dev capabilities. Generally macros require some of that, since it makes it necessary that the dev can use or program on the syntactic meta-level. It's there where language constructs are reused, implemented and integrated.

Lisp docs will tell you to use macros only when necessary? They tell you to WRITE macros only when necessary. Since many Lisp dialects have a lot macros, you have to use them anyway. Most of the top-level definition constructs are already macros. If we use DEFUN to define a function, we already use macros.

In my experience actual Common Lisp uses a lot of macros. I also tend to write a fair number of macros.

But generally good macro programming style is slightly underdocumented, especially when we think of various qualities: robustness, good syntax, usefulness, readability, avoiding the obvious macro bugs, ...

On Lisp from Paul Graham is useful, as a few other sources, ... Read On Lisp here: http://www.paulgraham.com/onlisp.html

Macros are very useful and I use them a lot, but at the same time one needs to put a bit more care/discipline into them and some help of the development environment is useful...