Interestingly I responded to a similar question when someone was comparing it to Donald Trump's "wall" a couple of weeks ago.

To be clear, the Great Wall of China was not a single line of heavily defended wall to keep the invaders out. Most of the time the wall was very lightly defended, with troops stationed just in a few choke points. In addition, adjacent area north of the wall were mostly Chinese territory as well.

Militarily, it primarily functioned as multiple lines of early warning system to detect northern nomads raids. The idea was to give up the indefensible northern settlements and even the wall itself, slow enemies long enough for message to be sent back to the capital via smoke signals, allowing the imperial court to plan strategies and mobilize troops. Facing organized resistance and with logistics line constantly threatened by mountainous terrain and Chinese defenders on other parts of the wall or northern settlements, the raiders usually didn't dare heading too far south before retreating. The wall again served to slow enemy retreat, allowing defending army to catch up, attempting to rout the raiders and recapture loots (treasure, livestock, civilian captives etc were slow to move given the geography). For every successful breach of the wall (including the two times China was entirely conquered under special circumstances - Chinese civil wars), there were hundreds of raids successfully repelled thanks to the wall, so it's not really THAT ineffective.

Of course, the cost was also extremely high. The wall that is standing today was a collective effort of multiple dynasties spanning over a thousand years. Each time it was renovated and expanded it took multiple decades and tens of thousands of lives.

In terms of pure cost benefit analysis, it gets tricky. It was a huge drain on resources but also reduced risks of northern raids. For the dynasty that put too much effort into building / expanding it, it sometimes led to peasant rebellions causing the fall of the dynasty; however, the succeeding dynasty that united China would then benefit from the wall built by the predecessor.

The modern stone Great Wall was built AFTER the Mongol invasions in the Ming dynasty around 500 years ago.

The earlier walls built by the Qin, Han, and Jin were earth ramparts which decayed. The wall that the Mongols faced was built by the Jin and it did not even block out China's northwestern border. The entire northwestern border was exposed. Only the area around Beijing was blocked. Any army could walk through the exposed part.

The Manchus never managed to penetrate the Ming Great Wall with a full scale invasion force. They could only get raiding parties through which had to return to their base or else they would get stuck. In 1644, the Ming General Wu Sangui defected to them and let them in through the wall at Shanhai Pass. He opened the gates for them. The wall was successful in blocking them before.