So, so far I've been playing almost-vanilla. The only mod I use is a little patch the fixes a couple things. I want to do another play through, and I want to know what good mods there are. It doesn't matter what as long as it's fun and well done.
Also, if this matters, I have every DLC.
What kind of game experiance are you looking for? That would make a difference on what mods you'd want to get.
A couple of my favorites are: FO3 Wanderes Edition and Mart's Mutant Mod. FO3 Wanderes Edition can make the game tougher to play. No more soaking up damage, mowing down your enemies, and useing a couple dozen stimpacks when your in combat. Stimpacks heal over time and are harder to come by. So basically this mod makes you actually have to use a degree of tactics while fighting. Weapons also do more damage and you dont have as much health on top of that. I've actually had to get companions to help me out in certain areas where before I'd mow everthing down with ease. FO3 Wanderers Edition also comes with "bullet time", if you dont want to use VATS use this. It slows the action down for a short time depending on your current action points. There have been a few situations where VATS wouldnt have saved me, but "bullet time" made the combat survivable. Really too much to list here, check it out on nexus. Mart's Mutant Mod works great with it as well. MMM add some creatures that fit in the fallout universe nicely and gives you several other options as well.
If you use any of the larger well known mods you should look at getting Fallout Interoperability Program - FOIP as well. This will help the mods work together correctly.
A few other mods I like: RR Companion Vault (this can be overpowered, I just ignore one of the vendors and it's fine), RR Refurbished (upgrades the vault), Black Wolf Backpack (when you want to carry a bit extra), sunglasses collection, Pipboy Readius (hand held pipboy), Responsive Kill Reactions, just added this last one. Basically if there is a witness to someone dropping dead the npc's start looking around for the person who shot thier friend. Seems to work so far. And a slew of companion mods, they've become like Pokemon, gotta collect them all