Originally from the ESF thread: Rules of Thumb for mod load orders.

General Load-Order Guidelines

by ElecManEXE

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.

Some people will recommend putting larger mods first, but personally I disagree. There are a number of mods out there that make minor tweaks, and loading after a large mod will end up completely overwriting a chunk from one of the bigger mods because of the way conflicts work in Oblivion (even one minor change will take precedence over the entire record... for example, simply tweaking the speed of a weapon can cause every stat of that weapon to be retained to vanilla levels if loaded later).

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>
<DLC's (Pre-Completion)>

Expanded Load-Order Guidelines

by dev_akm

I would extend this to include:

Unofficial Oblivion Patch
<Weather/Environment/Sound Mods>
<Minor Mods/New Items/Houses/DLC's (Post-Completion)>
<Major Overhaul Mods>
<Mods that specifically conflict with overhauls and need to take precedence>
<DLC's (Pre-Completion)>
<Compatibility Patches/UOMP/Merged Leveled Lists>

And a special-case warning for Knights.esp (Knights of the Nine) -- you may not be able to move it earlier than some other mods (some people have had problems after moving it before OOO, for example).

That's basically the structure I use and I have 140+ mods working well together.

Another way of describing this (posted by DMan77):
unoffical patch
Deeper realism mods that add sights and sounds
added content like weapons/items
gameplay changes, like 'must eat and sleep'
The OOO type
the 'new begining' type mod..


Color-Coded Example Load-Order

Agiel has created an excellent load order example spreadsheet that can be very helpful. See Agiel's Color-Coded Load Order.