Although not very intuitive, "melee units" in the description includes all units capable of melee attack. This means that the Siege Tower affects light and heavy cavalry, anti-cavalry, and both land and naval melee units.
The Siege Tower is the opposite of the Battering Ram in the sense that units will do major damage to the city and and minor damage to the wall (while the ram allows major damage to the wall instead). Note that these two support units do not increase damage at all - they merely redirect it. Having one of the two units will FOCUS damage on either city or wall. Having both these support units will SPLIT the damage into both the wall and the city, which would be worse than having only one.
This unit is helpful if you plan to attack a city with melee units only. If you want to use other units in a siege attack as well you might want to build a Battering Ram instead. Normally, the wall must be torn down before the city can be attacked. It is generally done with the combination of melee, cavalry, naval, etc. With a Siege Tower, however, your non-melee units can focus on the enemy units, leaving the melee units to attack the city. Most importantly, this support unit is best suited for fast sieges (5 turns or less) because the wall is left standing and it will slowly whittle down your army (especially when the city is garrisoned). Once again, the Battering Ram is the slower but safer choice. Just beware of war weariness!
There are two scenarios where this strategy can work well. The first one is when a strong new melee unit is unlocked. Most of the time, their combat strength will be much higher than that of a city of the same era. If the enemy is behind in military technology, the only thing that can stand in your way is the wall, which is neutralized by the Siege Tower. The second scenario is when the enemy is of similar military might, but there are undefended cities. All other units can block the reinforcements while melee units make short work of the poor city. The city combat strength may be high, but attacking melee units have the advantage of numbers and the freedom to pillage.
Siege Towers can also prove useful during joint wars. If you and another player are attacking the same city, whoever reduces the city's HP to 0 will capture it. This normally means that both players laying siege to the city will will stall until they can do enough damage to take the city in one turn, but this changes if you have a Siege Tower. Since the wall is left standing and only the city is in low health, the other player cannot steal it from you without a Siege Tower of his or her own.
Historical Context Edit
Climbing walls up ladders - while the defenders above hurled boiling oil, rocks and pointy objects down - wasn’t conducive to high morale among the troops, and so the Neo-Assyrians invented siege towers around the 9th Century BC. At the time, most of the towns in Mesopotamia were surrounded by mud-brick walls, generally sufficient protection against attackers, but the siege towers of the Assyrians allowed them to get over the walls relatively unscathed; thus their empire spread virtually unchecked for three centuries. It was an idea whose time had come, and soon enough all the ancient civilizations had siege towers in their arsenals. In simplest form, a siege tower was a covered wooden tower on wheels; once pushed near the offending wall, a gangplank was dropped to bridge the gap and happy warriors rushed into the town or castle. Made ever more sturdy and efficient by those master engineers the Romans, the siege tower was used throughout the Medieval Era, until gunpowder finally made them obsolete.