It depends.
That's really the beauty of unit upgrades, you get to tailor your units to the situation. If you're looking for two upgrades to always take, you're asking the wrong question. (Still a fine question for Gaming.StackExchange. It gets the ball rolling...) The right question is what upgrades will be good for this situation?
- Are there LOTS of enemies in the city? Go for collateral damage. (Barrage I, II)
- Facing lots of melee units? Take anti-melee. (Barrage I, II)
- Do you have a massive force besides, and are just trying to take the cities as fast as possible? Go for more bombard damage so you can move on quickly. (Barrage I + Accuracy)
- Are you having trouble with your first few attacks? Go for city attack or first strike. (City Raider I, II) (Drill I, II)
These suggestions apply to all siege weapons, since they are allowed the same upgrades.
However, there will be times when you don't know what upgrades will be BEST. In those cases:
- Take upgrades that you know will at least be useful. Faster bombard, and city attack can't be wrong against cities. First strike should be ok against cities, and collateral should be ok against multiple units, but check to see if the enemy units are immune to those first.
- Take a variety of upgrades, and use them as they seem useful, or try each combo and see what works.
- Save the upgrades till the last moment you can. Then you'll have the most possible info to choose with.
Good luck!