If the effect isn't worthwhile is another question. I think your initial idea
@Stalker0 of free splash is the right direction, whether it's through those promotions specifically or not. Missiles can remain the premier option for targeted area damage to knock out a Citadel's defenses, pillaging it and allowing you to move up. Damaging a group of fortified melee units so that they are can either be picked off, or they have to cycle away and the replacements are no longer pre-fortified.
This is the key division in the philosophy. We have one of two possible problems with missiles.
1) A balance problem. In this version, there is a version of the missile with certain strength and cost that is considered viable enough to a human to use as a regular part of their arsenal BUT is not so good that it takes over as the dominant unit.
2) A design problem. In this version, the concept of consumable units is so against the current war design that to make it viable requires making it so powerful that it then becomes the only weapon worth considering, and there is no true middle ground. That the missile is just not balanceable as a direct damage unit compared to other non-consumable direct damage units.
We could look at the skirmisher doctrine for guidance. While at the end of the day its a simple promotion, it was a radicaly re-think of what a skirmisher does. We changed it into a fast support unit that greatly magnified other units, BUT the unit itself was relatively weak and expensive. This provided a niche of a support unit that was good to augment a force, but a mass of them might not be optimal play.
Could we do the same for missiles? Possibly. Now the garrison attack is a unique niche for the missle. Do we simply need to make that stronger? We had a lot of suggestions of ranged pillaging this congress, what if that was given but only to the missile? Maybe the missile leaves a lingering promotion (similar to the incan slinger), etc.
There might be something there, but I do think we need to go beyond a simple "well make the missile do more damage, or be cheaper, or add a bit of splash, etc". It needs unique concepts that make it relevant without making it dominant.