In my view, Aikido can be a very street effective martial art. There is a couple of reasons for this, I have outlined them below. Keep in mind that all of the reasoning below applies to a lot of other martial arts as well. Nothing in this post is about Aikido being better than something else, it is simply about Aikido. I have practiced other martial arts as well and do not think that one art is better than another, they just represent different ways.
To people reading this that might be sensitive or previous victims of violence, I apologize for this light and somewhat theoretical reasoning about situations that can have devastating and life-long consequences.
Addressing your second question first, how can Aikido be at all effective when what you see in training videos and on the mat seem to be so far from what you imagine is going on in a street fight? To understand this, think about the value of weight lifting in regards to self defense. Anyone can agree that if you want to be able to defend yourself against aggressive force, it is not a bad thing to add a couple of pounds of muscle. And if you get really beefy, no attacker in their right mind would chose you as their target.
To make this an analogy to your question: How can weight-lifting be at all effective, when what you see in training videos and in the gym is so far from what you imagine is going on in a street fight? Well, the answer is the same regarding both Aikido and weight-lifting: the simplest and most effective forms of training does not need to look like as if you are in a street fight. By creating a structured representation of everything that goes on in a physical conflict, you can study it with intent and diligence over many years without loosing interest. All martial arts does this, but in different ways.