Some guys on Smogon put FRLG Butterfree in C tier, which means its in-game usefulness is slightly above average.
Availability: Early-game (Viridian Forest, level 3-5).
Typing: Bug / Flying is good for Erika, but leaves Butterfree weak to Lt. Surge, Blaine, and Lorelei.
Stats: Butterfree has mostly bad stats, outside of its decent Special Attack and Special Defense, though its stats decent for the early-game.
Movepool: Butterfree learns Sleep Powder and later on Silver Wind by level. By TMs, it learns Psychic, Giga Drain, and Aerial Ace via TMs, which provide good coverage. Psybeam at level 34 provides an alternative to Psychic if the TM is not available to Butterfree.
Major Battles: Butterfree beats Brock's Geodude, but is shaky against Misty, Surge, and Blaine. It wins against Erika, Koga, and Giovanni, while performing well against Sabrina. In general, Butterfree simply outspeeds and puts the foe to sleep with a nearly-accurate Sleep Powder and proceeds to take it down while it's asleep. This strategy is useful throughout the entire game, including at the Elite Four.
Additional Comments: Butterfree is a decent Pokemon to use in spite of its below-average stats thanks to its early availability, Sleep Powder + Compound Eyes (which results in an almost-accurate move), and great coverage, though it is held back by the rate at which it defeats opponents.
On the other hand, they decided to put Pidgeot in D tier, which means most Pokemon are more useful than Pidgeot regardless of whether they have sleep powder or compound eyes.
Availability: Early-game (Route 1, levels 2-5).
Typing: Normal / Flying is only useful for Erika and some of Bruno's Pokemon, being particularly bad for Brock, Lt. Surge, and Lorelei.
Stats: The line's stats before fully evolving are mediocre, which makes even type advantageous matchups difficult. Furthermore, by the time it evolves fully, its stats will have fallen off and thus won't be that useful either.
Movepool: Pidgey starts out with Gust, which upgrades to Wing Attack and Fly. Pidgeotto uses Tackle, then Secret Power, then Return. Other useful moves include Hyper Beam, Steel Wing, and Featherdance.
Major Battles: Pidgey won't be useful in any fight till it fully evolves, only being somewhat helpful against Erika. Pidgeot sweeps Sabrina and can KO some of the weaker Pokemon from the other opponents' teams, but will rarely achieve significant victories.
Additional Comments: Despite the very early arrival, Pidgey's average stats throughout the game and poor movepool make it a rather mediocre Pokemon as a whole.
source
In conclusion, Butterfree looks amazing partly because it learns sleep powder and more because you compared it to Pidgeot.
