However, all the examples I've seen so far seem pretty convoluted and just made me think "Why would I want to do that?". I think I just didn't "get" it yet.
Is there a simple real-life example that shows the usefulness of the Lisp macro system to a beginner?
No; all the simple macro examples are in fact in the "why would I do that" category. Their value is the how, rather than the why.
A very simple macro can often be eliminated by writing whatever it writes by hand without a lot of additional effort, and without the baggage of carrying the macro definition. It doesn't seem useful, especially if you fail to imagine it being used thousands of times, rather than just the two or three times that occur in the example.
It's the convoluted macros that answer the "why": because if you don't have the convoluted macro to generate code in a complicated way, and you need to do that dozens of times, then your alternative is to do it all by hand, which is tedious and error-prone. THe macro does the job consistently, and if there is a bug, you just fix it in the macro and re-expand all the uses of it to spread the bugfix.
Otherwise, the way to approach Lisp programming is the same as with any other language: how can the existing syntax and library features (such as macros!) be orchestrated to solve this problem?
Which kinds of macros would be useful will become apparent during development. You often don't know what language features may be nice to have until you have worked in that problem domain.
Lisp programming sometimes happens without knowing what the ideal language of the problem domain would look like; you don't know that upfront to just it down and make macros for it.
Secondly, there may be third-party libraries for the problem domain. For instance, if you're sure that the problem domain requires solving goals stated as queries over logical predicates, that doesn't mean you have to implement your own Prolog in macros.
So the priority is roughly: built-in macros > stable third-party macros > experimental/own macros.
It's one of those books I learned from online, but I like it so much and kept on returning to it. So I bought a copy, and I still read it and write notes in the margins. It's that good. I don't call many things "bibles" but PCL is pretty much the Common Lisp bible. And it is as practical as the title suggests (as per your request!)
Chapters 7 and 8 are both entirely about Lisp macros. But even then you aren't done with learning about macros yet. The natural progression of the book means you keep building on the prior concepts until the last chapter.
Of course, the CD database you create while working through the book is a tad naive, but the idea isn't to make a commercially viable application - it is to demonstrate Lisp thoroughly by creating apps right from the get-go. And you would do exactly the same things in a commercial setting.
PS: It's Common Lisp and not Clojure. But you were asking about macros specifically, and the Lisp flavour doesn't matter much at all when it comes to how macros work. This book will lead you in the right direction.