Oh boy, one that I can actually answer! I will try to be thorough enough for this sub's standards. For the record, I have a Master's Degree in Clinical Psychology, my thesis was written on in-group/out-group formation through auditory processing, and I specialize in adolescent Bipolar Disorder and ADHD. I also have a BSN and am certified as a Psychiatric Nurse.

So, to briefly answer your question before I explain: Yes, Freud's ideas on hyper-sexualization (and honestly most of his ideas on sexual development and sexuality in general) are considered incredibly outdated and not terribly scientific^1. In fact, Karl Popper (who is regarded by many as the progenitor of modern scientific thought/philosophy) famously used Freud as an example of pseudoscience in his work Conjectures and Refutations: The Growth of Scientific Knowledge^2 (Popper essentially considered most of Freud's theories to be the pinnacle of pseudoscience, since many of them could not be falsified).

However, Freud remains influential to this day for several reasons. First I will explain his impact on modern psychotherapy and psychological/psychiatric science. Then I will discuss why he is such an important historical figure for the fields of psychology and psychiatry. As /u/stev0supreemo mentioned, Freud has also been studied heavily in a literary context, but I'm not as familiar with that area so I won't touch on it too much.

So, Freud's ideas are, as previously discussed, absolutely insane by modern standards. A lot of it seems like he just made it up himself based on what he saw and thought, and there is definitely some truth to that^3. Im not even going to go into the Oedipus Complex or his stages of psychosexual development because... well because I don't want to. Much of Freud's early work focuses on far more than sexuality and the unconscious mind, and he was actually one of the earliest people to correctly dismiss lack of oxygen during birth as a primary cause of cerebral palsy. However, Freud's biggest contributions that still remain today are most relevant to the practice of psychotherapy and our understanding of unconscious drives. Freud identified, described, and/or synthesized quite a few phenomena of which many are scientifically testable and still widely taught today. Among these are words you're no doubt familiar with: Id, Ego, and Superego (these are...less testable), Libido, Repression, Transference, Countertransference, Projection, and many more. His work on the unconscious and dreams is really hit or miss in terms of scientific validity and empirical support, but there are some aspects that are testable and have been supported by evidence (e.g. he proposed that particular traumatic events or life stressors could manifest themselves in dreams, and there is considerable empirical support for this idea though not necessarily for the deeper interpretation of those dreams)^4. I'll touch a little more on this in the section where I talk about his historical significance.

His biggest contribution to psychotherapy was probably his advocacy for psychodynamic therapy. He specifically championed a type of therapy called Psychoanalysis that is still practiced today, but psychodynamic therapy is the more general practice of talking to patients, forming a dialogue, listening to them, and helping them work through their issues. I know this seems like a no-brainer now, but back then it was pretty revolutionary (I mean, why would you talk to a crazy person?). There are arguably others who "invented" this therapy, and I will leave that debate for more qualified historians (read: actual historians) than I, but Freud undoubtedly popularized the method. He also provided the basis for what many consider the prototypical "therapist-patient" relationship, with practices such as trying to remain objective and not judge the patient, maintaining confidentiality (though he was less than stellar on this front), and providing analysis and recommendations where appropriate. The details of modern psychoanalytic practice and how the current practice differs from his original work is way too complicated for a single post (and I was trained in different methods), but a lot of his original techniques were the original basis for modern psychotherapy of all disciplines.

So to summarize, again, Freud's theories, ideas, and his techniques vary wildly in terms of how well they stand up to scientific and philosophical scrutiny, but they undoubtedly had a massive impact on how psychology and psychiatry are practiced today.

However, in my personal opinion (which is shared by others, like Ernest Jones^5 and David Eagleman^6) is that Freud's true impact is a historical one. That is to say: Freud was a freaking rock star in his day. Psychiatry was a well established field by the time Freud came around, and Freud didn't invent the concept of psychotherapy, but Freud brought psychology into the mainstream. He gave lectures at universities around the world, he promoted his ideas and his works in sold-out talks. He was invited to speak and participate in events and research in almost every relevant scientific area. His work was so popular that it radically changed the fields of psychiatry and psychology forever. People devoted their lives to either proving his theories or to disproving them (Neo-Freudians like Karen Horney and Alfred Adler essentially founded their entire fields of work on disagreeing with Freud). The idea that the unconscious mind is the primary driver of human behavior (rather than the conscious mind, which Freud believed was important but only a small part of the human psyche) was not a new one, but it was not a mainstream idea until Freud made it so with his works, such as The Interpretation of Dreams (1899) and in Jokes and Their Relation to the Unconscious (1905). This idea touched a huge variety of fields, including criminal justice (can criminals really be responsible for impulsive acts if our unconscious is so influential?), philosophy (how much agency do humans really have?), medicine, and more. His works were (and are) dense and honestly written with an air of authority and certainty that is totally in contrast to how we would expect modern scientists to present their findings. But Freud’s ideas were accessible to the common man, and they seem to make sense on a basic level. Freud was certain of his theories, and was famously bad at taking criticism (When somebody pointed out that Freud’s beloved cigars were a bit phallic, he replied, “sometimes a cigar is just a cigar”). But his charisma, confidence, and authority sold a lot of people, and his popularity overshadowed almost all of his contemporaries. Like I said, he was a rock star, and without him, I don’t think psychology would look anything like what it is today, for better or for worse.

Sources:

1. Grünbaum, A. The Foundations of Psychoanalysis: A Philosophical Critique. University of California Press, 1984, pp. 97–126.

2. Popper, Karl. Conjectures and Refutations: The Growth of Scientific Knowledge. London: Routledge and Keagan Paul, 1963, pp. 33–39

3. MacKinnon, Donald W.; Dukes, William F. (1962). Postman, Leo, ed. Psychology in the Making: Histories of Selected Research Problems. New York: Alfred A. Knopf. pp. 663, 703

4. Stevens, R. Freud and Psychoanalysis Milton Keynes: Open University Press 1985 p. 96: "the number of relevant studies runs into thousands".

5. Jones, Ernest. Sigmund Freud: Life and Work, vol. 1. London: Hogarth Press, 1953, pp. 94–96.

6. Eagleman, David Incognito: The Secret Lives of the Brain Edinburgh: Canongate, 2011, pp. 17