Here are the guidelines that I adhere to, personally.
~ Unofficial Oblivon Patch should always be first on the list. The fixes are great, but most aren't essential, so if a mod overwrites them its not a big deal, and the fixes have the potential for screwing up other mods if loaded later.
~ Offical Content (DLC's) should be loaded last, until you complete all quests associated with them (that includes buying all furniture for the houses and whatnot). Once they're complete, they can be safely moved up in the list. This is especially important for Knights of the Nine, which will have some fairly major problems unless loaded last (unless you get the UOP for KotN)
~ Major overhaul mods (OOO, Frans, MMM) should be loaded near the end. That gives you the most complete experience with any of those particular mods. It also lets you carefully choose which other mods to load afterwards... only move mods that you know will conflict and that you want the changes from. For example, I have Improved Soul Gems below OOO because I know that OOO changes the icons of some of the SG's, and ISG needs to be below to show through.
So basically, it stacks up like this...
Unofficial Oblivion Patch
Minor Mods / DLC's (Post-Completion)
Major Overhaul Mod/Mods
Mods that specifically conflict with overhauls and need to take precedence