It really depends on the car and the kind of automatic transmission.

For example, a Dual Clutch Automatic Transmission (such as Audis S-Tronic System) can often handle far more than the stock output of the engine. Since the shifts are incredibly fast on this kind of system, faster than a manual transmission, it makes sense to build up on them if you want power.

Other more conventional systems like the Tiptronic systems you will find in BMWs and Audis might not benefit from significant modding as your return on modifications is much less significant.

Some systems, such as CVT automatics are absolutely not designed for significant power and should be left with a relatively stock setup unless the owner is okay with premature wear and potential early transmission replacement.

All in all more often than not you will see people only building up on Dual Clutch automatic transmissions and manuals. I personally have an Audi A4 B8 with a Tiptronic system which I built up to ~300 crank horsepower from 211 hp. I would definitely have benefited far more with an S-Tronic or Manual transmission, but the increase in power is noticeable and greatly improves driving in almost all conditions so I found it was worth it. I personally went with an automatic because I plan on using this car as a daily driver and as much fun as a manual can be, its SOOO nice to not have to focus on shifting in stop and go traffic.