I think it works in the context of Gen 1.
The first Magikarp you can get is the one from the Route 4 salesman at level 5, training it is actually a pretty major investment, and there's a very decent chance a kid playing the games for the first time during the 90s wouldn't know about Gyarados. However, anyone stubborn enough to continue training Magikarp on the off chance it'll get better will be rewarded with a beast that can easily muscle past Misty (an actually scary boss) and continue to be useful for the rest of the game. Like I said before, later games just made it way easier to get it Gyarados.
Plus iirc older gens locked fishing catches to specific levels based on the rod and Magikarp wasn't part of the Super Rod table, so you couldn't cheat a Gyarados later on. You had to invest in it regardless of what part of the game you were in.
Also the dichotomy of Magikarp being completely and utterly useless compared to Gyarados being a total powerhouse was really effective for Gen 1, especially in the context of the Magikarp salesman being the first means to obtain it.
It's supposed to be a ripoff in-universe, a waste of 500 yen for a Pokemon who is seemingly completely useless, not only having poor stats but only knowing Splash, a move that does nothing. If someone gives up, it will be a ripoff and a waste, but if you're persistent enough to try to raise the Magikarp from Level 5 up to Level 20 and beyond you're rewarded with a Pokemon who is incredibly strong, with high stats, a great movepool, and only two weaknesses, turning what should've been a wasted 500 yen into possibly the best monetary investment ever.
The game even subtly encourages people to try even if they don't know about Gyarados from what I dug into, because there are NPCs at the Pewter City PokeMart who allude to the Magikarp salesman. One of them brings up a shady salesman who sold a "totally weak" Magikarp for $500, but another NPC there says "Good things can happen if you raise a Pokemon diligently, even the weak ones!". The duality there notes Magikarp being useless and weak but also subtly encourages you to not only buy it but actively try to raise it, and the "good thing" that can happen from doing that is Gyarados.
Not to mention Magikarp as a whole was more effective in its gimmick in older gens because there was no EXP All mechanic and Magikarp is also in the Slow EXP Group, which made raising one a tedious chore in tandem with how weak it is. Later generations have made the process of raising Magikarp less of a chore due to scaled EXP gain and EXP All, especially in the Switch era where EXP being shared across the party is a permanently baked-in mechanic that cannot be turned off.




