I _highly_ recommend On Lisp by Paul Graham and Let Over Lambda by Doug Hoyte. However: As you rightly point out, usually macros are just shorthand for lambdas (or more interestingly, closures). This is in some ways due to the inherent expressive power of the lambda calculus. As a first response, macros can help clarity; I routinely write ease-of-definition macros for special types of functions, etc. However, there are places where macros really save time: creating context and compile-time calculation.
Functions suffer from the fact that they require lexically-scoped arguments to be supplied explicitly, and as we know today dynamic/global variables have many drawbacks. Context creation combines the two: Variables are not global and you don't have to supply them as arguments everywhere in your code.
Compile-time calculation can be used for everything from optimization to automatic code generation.
Examples:

• Anecdotes:
  
  

I am working on cl-ana (on github) and currently have it so that functions defined for numbers are available as element-wise versions for (nested) sequences; this is due to storing lambda-lists and function names at compile time and then automatically executing proper function definitions for sequences. The macro is transparent, just replace defun with defmath.
In some of my physics analysis code, I use functions which keep track of their dependencies and only run if necessary, i.e. if function-b calls function-a somewhere, then whenever function-a is set to re-run function-b automatically knows to re-run as well. This is useful when you have long chains of computation and update the data source at some point; you just specify the most basic function(s) which need re-running and call the master function, only the parts which need to be re-run are executed. This is a blend of compile-time calculation and optimization.

• Anaphoric macros
  
  

How often do you find yourself testing the value of some expression, but then want that expression's value later on? Sure, you could go edit previous code to allow this, but that would take time and you potentially lose focus. Enter anaphoric macros.
Like aif:
  (defmacro aif (test then &optional else)    `(let ((it ,test))       (if it           ,then           ,else)))
It violates referential transparency, but we gain in essence a new special term "it" which is bound to whatever the containing aif set it to. The fact that language has pronouns is a big hint that often times it's better to express ideas within a context than to insist that everything be understandable via substitution.
Example:
  (aif (member x some-list)       it       (print "x is not in some-list"))
And you can do this for any conditional construct you can think of, e.g. awhen, acond, etc.
Another use for anaphoric macros: Ever wanted to write a recursive anonymous function but instead were forced to add a labels to your code? Try alambda:
  (defmacro alambda (lambda-list &body body)    `(labels ((self ,lambda-list                 ,@body))       #'self))
If you make heavy use of functions then you probably are prone to using recursion, so alambda may find quite a few good uses.
Let Over Lambda has even more examples (these are from On Lisp), but I won't bog down the page with them.
Gary Hollis