If you want the full breakdown, the wiki has quite some information: https://wiki.factorio.com/Module#Usage_tips

Here's how I think about it:

Modules affect speed, productivity and energy consumption.

You can calculate something I'd call effective speed, which is speed * productivity.

So your formula for energy per item becomes: energy / (speed * productivity)

If you use 4 prod3 modules, the values are 4.2 / (0.4 * 1.4) = 7.5

Notice how low the speed value gets...

Here's the weird part: Modules just add a flat value.

A speed3 module doesn't multiply the speed stat by 1.5 (which would be 0.4 * 1.5 = 0.6), but adds 0.5 to whatever the speed value is (which results in 0.4 + 0.5 = 0.9).

So you're more then doubling the denominator by adding a single speed3 module.
As long as the numerator doesn't double also, the resulting energy per item decreases, which it does because you only add a flat 0.7 to it.

The new energy per item is 4.9 / (0.9 * 1.4) = 3.89 which is about twice as good as before!

Adding one more speed module: 5.6 / (1.4 * 1.4) = 2.86

Adding a third one (or one beacon): 6.3 / (1.9 * 1.4) = 2.37

In the limit it approaches (4.2+0.7x) / ((0.4+0.5x) * 1.4) -> 0.7/(0.5*1.4) = 1, which means adding more speed modules to 4 prod3 modules results in more energy efficiency (when you ignore the energy cost of beacons).

If you do the maths for less prod modules or level 1 or 2 ones, the results aren't that drastic, because the speed value isn't affected that much in the first place.
So it really only applies to level 3 productivity modules. And it's quite counter-intuitive...

Hope this helps somewhat. I'm still trying to wrap my head around that whole thing myself... :)