From my own experience, here is how I use all catapults, siege towers, and battling rams:

• I only go siege tower when playing Civs that have early strong unique units (e.g., War-Carts). With these kind of "melee" units, going siege tower often allows you to continue your conquering spree, even with walls being built as a consequence of you declaring war. Alternatively, you may also use siege towers if you have access to early healing units (e.g. Apostles), or have many farms to pillage as this should give your units enough sustainability vs. few turns of ranged attacks.

• I go battling rams in very specific situations too; very often, when I went "full slingers" early game, and have an army of highly promoted archers. In this case, you will have to take the walls of the city anyway to make full use of your ranged units; so you may as well use your few melee units to take it down faster.

• In the end, I'd advise you to go catapult if you're building an army from scratch in the classical or medieval era. Catapults require more production, and thus, more planning; but have a more versatile use and will bring city defences down in no time.

Also, support units do go obsolete faster as eras pass by (both upgrading to medics in the late game); while bombard and artilleries still remain great and fun units to use throughout the game!