It completely depends on the type of game.
For shooters I'd say an ideal length for the campaign would be from 6-10 hours; depending on how action packed it is. Also I'm only counting shooters with good stories so that narrows out all CODS. They don't count. Games like Bioshock excel in this department with a 20+ hour story on most fronts.
RPG's I'd go with 20+ hours for a main storyline; with upwards of 100-200 hours for 100% the game including all side quests and what not .
Sports games I can't really say. I always put 100+ hours into the myPlayer mode alone in NBA 2k.
Action/adventure/3rd person IE the Uncharted/Tomb Raider/Gears/Darksiders I'd say a good 20-30 hour campaign with 50+ for 100%. Not as action packed as most shooters and most action games have a bit of RPG elements in them.













