I would use sed for transformations (simple or complex) where I only care about one transformation (even though you can do multiple): Does the current line match this pattern? Change it to this other thing and move on the next line.
I would use awk when there are a handful or more of potential transformations: Does the current line match any of these multiple patterns? Do the action that's defined for each of the patterns, then move on to the next line.
If I need to do multiple transformations, and still want to use sed, I find it easiest to create a chain of single sed transformations, piped together. Somewhere in that area a shift to awk (or python) becomes justified.
I found sed great for messing around with SVN repository dumps. Delete a few lines here to remove the commit that added a directory... change a few paths to pretend the files in that directory had always been somewhere else... add a few lines somewhere else.
Sed scripts are a quick way to automate simple edits to large files.
I would use awk when there are a handful or more of potential transformations: Does the current line match any of these multiple patterns? Do the action that's defined for each of the patterns, then move on to the next line.
If I need to do multiple transformations, and still want to use sed, I find it easiest to create a chain of single sed transformations, piped together. Somewhere in that area a shift to awk (or python) becomes justified.