Now, the concept is limited, and the article author admits it - it's something that really applies mostly to the science fiction and action genres - but the idea is that we're not watching a show to see the characters live a normal life: we want to see the characters step up to an extraordinary situation and deal with it. The characters become interesting when we see why we're watching them in the first place. And I'd say this applies to less action-y genres, though you might not actually say "Fuck yeah!" during such a moment. Even in a romance, the main character needs to come to realization of what he/she wants and make his/her decision, or action, or sacrifice that brings the story to completion.
The article is making me rethink parts of the long fic that I've been working on for a while now. The main character doesn't need to make the reader go "Fuck yeah!" - that's not the type of story I'm going for - but she does need more moments of revelation, more moments of take-charge.
The article lists the five types of "Fuck yeah!" moments and gives examples of each of them. As I was reading them, I started to try to think of great "Fuck yeah!" moments in Doctor Who, and the first one that came to mind, one of the best ever, was the moment the Doctor forcibly suspended his regeneration and commandeered the spaceship in order to return to Androzani Minor to save Peri. And look, the author cites that moment in type 4, "the moment of amazing altruism or saving others." It's great to see other people remembering that wonderful episode.