The reason that some great college QBs fail to have a similar impact in the NFL could be for several reasons:
College athletes are slower and defenses are worse, which adds up to being a much easier environment for QBs. This means that some QBs who aren't actually that good are made to look like superstars because of a weaker field of competition. It's the same reason why failed major league hitters often become megastars in Japan, because when the level of difficulty is toned down, a lot of their problems disappear and they look like world beaters.
Higher difficulty means new problems showing up, and lower difficulty means fewer problems. Some QBs have the traits required to withstand more added difficulty than others. This is why QBs with athleticism, accuracy, mental grasp of pro-style concepts, and arm-strength are prized so highly, because they will tend to be hurt by the jump far less than a QB who is deficient in those areas.
For Tebow, that deficiency was accuracy. For McCoy, that deficiency was basically everything save leadership. For Wilson (Russell Wilson?) that deficiency was height, but he's proven both in college and the pros that it was not a significant factor. For Kellen Moore, it was size, mobility, and arm strength, although I think Moore is actually a tad under-rated and it woudn't shock me if he's a backup in the NFL for a decade. He's a very smart QB with great accuracy, kind of like a smaller Chad Pennington.