I like to hire a few champions early on in the game to use as scouts. They are really the ultimate scouts (especially the Adventurer champions for the extra movement) for several reasons:

1) They can collect notables which means they can pay for themselves easily. Units can't

2) They can run away this is a big deal for any scout, regular units can't and will eventually just die because something attacks them.

3) Generally they will be hired at the borders of your explored area meaning turns aren't wasted running them out to where they need to be.

4) Turns/materials are not wasted producing them so you can focus on settlers or actual military units.

5) Their cost is generally really low (don't hire the expensive ones just to scout) especially when you compare it to a unit that won't suffer from 2).

6) Their use never expires, there's always new notables for them to fetch and carry (unless you never research notables later in the game), even top of the line units eventually suffer from 2).

I do think it's kind of sad that the best argument I can make for most champions is they're over-glorified gophers but I work with what I'm given.

Edit: I've also seen lots of arguments for high intelligence heroes being imbued for massive caster armies, but that brings back the equipping/leveling dilemma and gimps your sovereign's casting until you start getting a temple of essence in a few cities, or you mass level which can be difficult with the monster spawn reduction compared to earlier game builds.

Also

A "bare" hero can be a reasonably cheap garrison unit.  And when an attack seems imminent, buying him a bunch of stuff is the fastest way to bulk-up a small garrison to fight something off.  Dreadfully expensive, but still the fastest way.

I employ many heros and make most of them mages then attach them to other unit stacks. Also you have to use a hero for the Quest and notables.

Hire them all, embue them all, go kill a load of spiders & wolves, level up essence to 10(ish), research spell level 5 (I think), summon an Ice Lord each, win game...

Primarily, I use my heroes/champions as imbued spellcasters (which takes some patients to become useful, but is worth it), scouts / notable location 'gophers' as mentioned above, and garrisoned to keep creatures etc. from preying upon my cities as well as to make use of their admin/farming abilities.  Lots of this has been said above.  Merchant master, etc. abilities aren't much on their own but over time as you get more heroes I think it does add up (although this is another point -- has anyone noticed that late game they might have TONS of heroes, but the AI opponents have like 1 or 2 pretty often??).

What I am very unhappy about regarding heroes is their complete lack of use in direct combat (even the ones with the specialty powers like crushing blow -- which are VERY limited in their scope by the way, I hope many different types and more interesting powers are on their way).  Even late game, maybe especially late game, heroes simply don't have the hitpoints to stand up to anything in direct combat.  If I pump all their level-up points into constitution, then they lose whatever strength or dexterity edge they might have gained.  I completely understand that just any hero shouldn't be able to take down a powerful unit alone, but shouldn't SOME (with proper development and leveling) be able to stand up to some pretty powerful creatures without being killed so easily?  I've found that even high level heroes with powerful weapons and armor are 100% useless for melee fighting in almost all cases.  Being able to pop off a specialty attack or two might be nice, but shouldn't a hero I've developed to be a fighter also be able to stand up and kill a powerful creature with its sword, etc?

I would support this in a heartbeat. In any of the total war series (Best RTS of all time) allows non-combat characters to level up by doing what they are meant to do. For instance, a spy will level by spying successfully. A merchant levels up slightly each turn they are trading etc. The abilities besides adventurer really seem less meaningful in the later portions of the game. This also goes with the same perks we can pick as sovereign.

I am pretty sure they are currently working on making champions more useful.

I buy everyone I can. The bonuses all stack so it starts to add up. And, they seem to be worth more militarily in the AI eyes. And, if you band them together as a party and with other units, they do pretty well even into the mid game.