For me, it's this:
For some people, books are a form of escapism. For others, movies are a form of escapism. A means to escape, to get lost in your imagination, and to get lost in your own world.
Well for me, video games are that form of escapism (as well as books, plays, movies, etc... but for the sake of this post, it's video games
)
When I play video games, I like to get lost in my imagination in another world. If it is an RPG, for example Skyrim or any other Elder Scrolls games (as those are my favorite games all time) I love to get lost in the character, get lost in his journey, get lost in the world and in his adventures.
In the case of Madden, I love to play a franchise mode and get lost in my imagination in another world where I am the general manager / coach of my favorite football team, existing in this alternate world where I make the moves that I think will give the team the best success. My first real experience with a franchise mode was with NFL 2K4 when I did a fantasy draft franchise, and then my experiences went to a whole other level with NFL 2K5 where I was in my longest running franchise mode to date. (My mention of NFL 2K is not to compare, simply to state my history.)
My problem with Madden comes from when I see certain things that are so blatantly and inexplicably unrealistic. It takes me out of that imagination world that I live in. It completely snaps me out of that fantasy when virtually every CPU QB is a robo-QB with no weaknesses, throwing perfectly accurate passes no matter the circumstances. It completely snaps me out of that fantasy when the game is basically built on busted plays that end up being routine 80+ yard TD's. It completely snaps me out of that fantasy when defense is completely non-existent outside of turnovers.
It really is frustrating.
The ONLY reason why I even play Madden anymore? Check out my signature. I've found a great 32 man league, where I no longer have to deal with the CPU anymore. It's a 32 man league of like minded players, who are all there to have fun and not "win at all costs" (but still remains a truly competitive league), and don't exploit the game or weak AI but just rather a group of guys that want to have fun with the game of football.
Madden isn't perfect, but it gets a lot better being able to play with people that are playing the game the way I think it should be played.
But if it weren't for the 32 man league I'm in, I wouldn't be playing Madden. And that's why. That's why, even though it's "just a video game", as you put it (and you're right, but I don't think anyone is asking for a perfectly adapted replication of NFL football that is 100% exactly like the real thing), Madden doesn't even succeed on that level. At least for me.