Mart's Mutant Mod is a must. Allows you to change creature popuation, spawn rates and adds new creatures.
Wasteland Wanderers Edition changes the game A LOT. New weapons, basic needs (water,food, sleeping) which can be turned on and off. Changes weapon damage, XP rates adds new Perks and all customizable from the Pip-Boy.
NMC's Texture pack upgrades a huges amount of textures.
Dynamic Weather adds rain and snow (even radioactive if you choose), sandstorms, fog and storm effects. Removes the horrid green filter making the game look more natural.
Weapon Mod Kits allows you to mod weapons with scopes, silencers, extended clips etc.
Energy visuals enhanced does what it says on the tin. Makes Plasma and Laser weapons look much nicer.
Fallout Mod Manager - Required. (1st Download)
FO3Edit - Vital! ( 2nd Download)
FOSE - Required for some mods, disables GFWL ( 3rd download)
DarnUI - Removes the 360 low res user interface and replaces it with a higher res one.
I posted some of these on my blog with a little guide to modding
here After that and once you have a hang of managing mods it's all down to personal taste. There are thousands of mods on the Nexus now ranging from small tweaks to full overhauls.