I think the secret to success is pretty simple, really. And has much less to do with superstar QB's than the following: does the team have a good coach? If a team has a coach and a QB, they'll be competitive more often than not.
Putting two all-timers - Brady and Belichick - on the same team created a force that dominated the league for over a decade. Then think of the other less - but still solid - QB/coach duos that have had prolonged success: Roethlisberger and Tomlin, Wilson and Carroll, and what Haurbaugh has gotten out of Flacco (who was, at least, break-even) and Jackson (who is very good). Payton and Brees. All these teams are competitive, year after year. Sure, they have some down years, sometimes several in a row, but that's just the nature of football. The only times the Lions were ever close to good for more than a few years in a row, Stafford had Caldwell.
Looking forward, KC have a coach and a QB. Unless Mahomes gets severely injured, they'll be competitive for years to come, even if Mahomes doesn't reach the same heights of his first few years (which, I agree with Floyd, are enough to consider him a great player that we'll think about for the basis of those years alone decades from now). The Texans have a QB, but they don't have a coach. With Peterson and Wentz, it's unclear if they have a good coach and a good QB - perhaps both guys are barely good enough to succeed at their best - but unbelievably, Peterson's Eagles have been competitive every year. The Eagles would be foolish to abandon ship on either mid-season.
If you don't believe this theory, that a superstar QB will not have success without a good coach, then I invite you to participate in a simple thought exercise: imagine any starting current starting QB in the league having success playing under Adam Gase for the New York Jets. It is literally impossible to conceptualize.