What I'm trying to understand is exactly why top tiers are overwhelmingly popular. I think people pick up Fox, Falco, and Sheik starting from a non-competitive level because they "feel" nice to play. Others might have been inspired by the pros who play the same characters. Others, still, could simply be influenced by the incredible amount of information, video footage, and conversation there is about the top tiers.
If Axe is so freaking good, why are there not more Pikachus?
You definitely answered most of your question just there.
Tiers are based on how well a tool accomplishes a common goal: Winning. Many people play competitive games for different reasons, but one thing all players love to do is win. Winning or beating your opponent becomes a motivation factor to choose a tool that helps you accomplish this the best.
When I play a fighting game I think about it like this: Say you enter an arena. You enter the floor and on your table there are a large array of weapons. Knives, pistols, rocket launchers, etc. The arena awards prizes to the winner. So in my head I scan the table for the best weapons and simply ignore the less viable weapons.
In Axe's case, he brought a knife to a gun fight. Before the metagame progressed people felt like this was suicide. However, Axe was able to master his knife to do things common knives can't do like... deflect bullets. His skill with the knife is unrivaled and he has learned how to combat the more stronger weapons. The thing is, it takes a lot of time and dedication to master a tool like a knife and be able to enter a gun fight and win. It is not an easy task at all, but it is more than possible.
This is why there are not that many Axe's. This is also why there are a lot of high tier users. Ease of use. Achieving a novelty status like Axe or Armada is very difficult.