The most important part of being a good (or bad) lover in Fallout is your Sex Rating (or SR):
Base Sex Rating: CH*0.5 + EN*0.25 + AG*0.13 + ST*0.12
+2 if you have the Kama Sutra perk
+2 if you have the Sexpert reputation
+1 if you have the Sex Appeal trait
All fractions of a point rounded down.
Obviously, having a high Sex Rating is good. It can be as low as 1 to as high as 15.
Sometimes, your Sex Rating is checked without your player actually having sex; your 'sex appeal', as it were. However, it is usually checked when the PC has sex. Having sex takes (1d5+3) * EN minutes, where EN is the PC's endurance.
Now, after you have sex, you are rated by the system.
Abysmal: SR 1.
Horrible: SR 2.
Bad: SR 3 or 4.
Normal: SR 5 or 6.
Good: SR 7 or 8.
Great: SR 9 or higher.
Obviously, better sex leads to better things happening. You might get more info out of Mrs. Bishop, for example, if you leave her begging for more.
In addition, after having sex, the rating of your sex is added to your Sex Level. For example, if your Sex Rating is 4, then every time you had sex, you would add 4 to your Sex Level.
Of course, this being Fallout, there are a number of sex-related traits you can get.
You may get the Sexpert trait in two ways:
1) Have a Sex Level of 100 or more; or
2) Get the Porn Star reputation.
Either will net you the precious +2 to your Sex Rating.
You can get the Gigilo trait by having your sex rated as Great at least once. See below.
Getting the Porn Star rep involves talking to the Corsican Brothers at the Golden Globes porn studio in New Reno. You must have an IN of 4 and a very high result on your 'screen test', or you must otherwise impress him (such as by being a champion boxer), in order to see your name in flickering holo-lights.
Note that gender preference, species, etc., has no influence on how your sex is rated. Note, also, that Luck has no role in getting lucky.
In the real world, Drugs are bad and wrong. They should never be done by anyone, and even 'tame' drugs make someone untrustable.
In the Fallout world, they're just another tool of the trade. Hey, trading in your life is nothing compared to the lives of your tribesmen, right?
Anyhow, here are the effects of various drugs and their affect on your ability to get the job at hand done well:
BUFFOUT: +1.25
MENTATS: +0.50
PSYCHO: +0.36
JET: +0.12
So to have Great sex, take Mentats twice and Buffouts twice. That should get you up to at least a Sex Rating of 9. Characters with a low CH may need more Mentats. (Feel free to check the math for yourself for your own character if you want to make sure, using the formula for your SR listed above.)