A good way to start is to list gameplay that you wished were different in your game and look for mods for those specific things. Some mods are big and make sweeping changes, but most mods are very specific for very specific gripes that the mod creator is targeting. At this point there is a mod for pretty much anything. I'd suggest you check out LittleMsSam's mods, she has a very handy list of mods and will give you an idea of what is possible. Fair warning though; once you learn what IS possible to mod, it's easy to get addicted and chances are you won't be able to go back to playing vanilla. :p