After conquering hundreds of games SP levels ranging from Medal of Honor to Portal 2 I have realized what creates a fun experience.
The design and layout of the levels, not the scripted events which are by today's standards what makes a "blockbuster" game.
The levels witnessed from great games such as Call of Duty 1 really stood out because they were varied from vertical elements to seamless transitions from huge battlefields to ambient back alley skirmishes something that is lacking by today's standards, just look at the later CoD series namely mw2 and blackops where many of the missions involved just a simple route from A to B being flanked and attacked by enemies every bit of the way and predictable scripted events that wouldn't be remembered occurring at the most meaningless of times.
Industry developers of today seem to have forgot this and increasingly try to create games that act as interactive movies rather than the games we used to play and even tho they may be 5,10 or 15 years old still play.
Thankfully the communities modders and mappers continue to release their work free of charge and to a quality where the level design of the past is actually present in 2011.