The concept of "alpha" is highly variable among species and therefore doesn't generalize easily. In humans, it describes the type of man who would have a large number of wives in a pre-monogamous society-- narcissistic, brutal, detached, and without conscience.

Healthy women don't want to be anywhere around those sorts of men, who tend to be abusive husbands and terrible fathers. However, a certain class of psychologically damaged woman does tend to find that type of man sexually attractive.