My usual copy/pastes for the common newbie mod request topics can be found below. The most important thing to remember is to take it easy, and take it slow. Strat off with just some small well known mods that are known to be clean, reliable, and easy to install. Get a taste for the little mods and tweaks you might want. As you need to slowly look into utilities and stuff you might need. You see this little mod you really want that requires OBSE, then look into OBSE for a bit. A week later you see another mod that mentions Wrye Bash a lot look into wrye bash. Once you have like 10-20 mods look into BOSS for sorting that load order. etc.
Basically start out slow, install small safe things, slowly expand your knowledge as you need to, and don't add a bunch of mods at once.
If you just decide to install FCOM, 8 quest mods, 3 texture overhauls, 5 gameplay mods, 4 race mods, all the utilities, extenders, and potential requirements you might need and do all that right off the bat then of course it's going to be intimidating. Just take it slow, do some searching and research, install the mods YOU really want, and do it all at your own pace. Personally I didn't install a major gameplay mod until like 1 year and half after I started modding, and I didn't install OOO+MMM until 2 years after.
Spoiler
Lists and resources for finding many popular mods, and some good mods:
(note the distinction between the two, don't just follow mindless peer pressure, try and make your own opinions and find what you'll like)
List of lists:
http://www.gamesas.com/index.php?showtopic=1010109
Top 100
http://www.tesnexus.com/downloads/top/alltime.php
Files of the months:
http://www.tesnexus.com/downloads/filesofthemonth.php
Hall of fame:
http://planetelderscrolls.gamespy.com/View.php?view=oblivionmods.HOF
http://www.tesnexus.com/downloads/categories.php, http://www.tesnexus.com/downloads/tags/search.php, http://www.tesnexus.com/downloads/advancedsearch.php
General help in terms of installing, utilities, workshops, etc.:
http://tesivpositive.animolious.com/
http://www.oblivionmodwiki.com/index.php/Main_Page
http://tesalliance.org/forums/ (Check The Enclave forum, the Oblivion forum, or the Voice actor forum depending on your pursuits)
http://www.gamesas.com/index.php?/forum/87-iv-oblivion/ (Hardware/software for non mod related issues, Mods for mod users, Construction set for making mods.)
List of topics exactly like yours generic pleadings for "Any mods?" that have already been answered:
http://www.gamesas.com/index.php?/topic/1072579-the-any-mods-list-of-topics/
Read the AZERessential topics to get the basic "get wryebash, OBMM, OBSE, start slow" basic advice for starters recommendations
If you want any further help I suggest you tell us about yourself; what you like and what you're looking for... Here's some questions to get you started:
Have you played Oblivion already?
What sort of things interest you?
What type of Character do you usually play? (good/evil, theif/mage, roleplayer/random-no-story-hack-and-slasher?)
Are there specific types of mods you think would interest you? (Quests, graphic mods, gameplay mods, overhauls, etc.)
When did you leave?
What did you not like about Oblivion? What did Oblivion not have or need more of?
Male or female character?
For the occasional "What are your must haves?" Mostly quest mods: http://kno.li/i
My favorite quests are:
http://theelderscrolls.info/?go=dlfile&fileid=349 (my most favorite)
http://www.tesnexus.com/modules/members/index.php?id=125368
http://www.tesnexus.com/downloads/file.php?id=21989
http://www.tesnexus.com/downloads/file.php?id=17448
http://www.tesnexus.com/downloads/file.php?id=6977
http://www.tesnexus.com/downloads/file.php?id=6855
Ihttp://www.tesnexus.com/downloads/file.php?id=19591
[b]Gameplay mods:
http://www.tesnexus.com/downloads/file.php?id=25462
http://www.tesnexus.com/downloads/file.php?id=25835
http://www.tesnexus.com/downloads/file.php?id=22894
http://www.tesnexus.com/downloads/file.php?id=25844
http://www.tesnexus.com/downloads/file.php?id=25844
http://www.tesnexus.com/downloads/file.php?id=26196
http://www.tesnexus.com/downloads/file.php?id=18442
http://www.tesnexus.com/downloads/file.php?id=3409
http://www.tesnexus.com/downloads/file.php?id=21862(good for troubleshooting)
http://www.tesnexus.com/downloads/file.php?id=20736
http://www.tesnexus.com/downloads/file.php?id=20807
http://www.tesnexus.com/downloads/file.php?id=20371 (Needed for Integration)
http://www.tesnexus.com/downloads/file.php?id=22124 (Needed for Integration)
I'm mostly a quest mod player and I recently posted a big rundown for most of them that I've playedhttp://thenexusforums.com/index.php?s=&showtopic=157600&view=findpost&p=1349075 and http://www.gamefaqs.com/boards/genmessage.php?board=924363&topic=51137901.
(Some of these are probably outdated by now)
http://www.gamesas.com/index.php?act=findpost&pid=15598757
http://www.gamesas.com/bgsforums/index.php?s=&showtopic=1073167&view=findpost&p=15603156
http://www.gamesas.com/bgsforums/index.php?s=&showtopic=1073167&view=findpost&p=15602927 (Don't really want a lot top 10's or simple lists but I'm desperate for cop/pastes at the moment)
http://www.gamesas.com/bgsforums/index.php?s=&showtopic=1075680&view=findpost&p=15650213