"Most people should never use [macros]" is not a very good take-away. This is silly and overly paternalistic, like most programmers are children who shouldn't enjoy an extraordinarily expressive programming tool because it's "dangerous to readability". I feel as though people imagine more macro horror stories than they've actually ever experienced. I've experienced just one horror story in an optional-use open-source numerical library. And so I just didn't use it because I couldn't figure out how to extend it for my needs.

Many macros—just like programs as a whole—are bad and unhelpful. But this has little to do with macros, and more to do with programmer technique. It's not intrinsically difficult to write a useful macro, but it does presuppose that the programmer has a useful syntactic idea.

The Lisp family of languages so so broad and loose. It's hard to say anything generally about them anymore. But if you narrow down to Common Lisp, there's still a treasure trove of unique features. Common Lisp and Clojure seem to be the only two lisps where you've achieved broad community agreement on tooling, ecosystem, and general library design. They're also two languages where you can get paid to write them. Can't say the same about Scheme.

Lastly, as a personal anecdote, I don't think I've stubbed my toe on anything in Common Lisp. It's a very safe language with a lot of language features. I have stubbed my toe with CALL/CC though and memory leaks.