Cuben is a sandwich with plastic (mylar) outer layers and the tough "spectra" strands in between. These spectra strands are what really makes it strong. The 0.74oz cuben uses the same plastic layers as the 0.51oz, but it gets nearly double the spectra strands inside. So for a fairly small increase in weight you get a much stronger material.
The 0.51oz stuff is generally good enough, but more attention needs to be paid to tie-outs, seams etc and ultimately it's not as strong which can be a concern if you're expanding into 4 season use. Ultimately they're both good materials, but the 0.51oz stuff is more of a niche item. Manufacturers likely prefer the 0.74oz because it can handle some misuse by the customer - and because it is quite a bit stronger which means a longer lasting more versatile shelter for the same price and only 1-2oz in weight.
I'd use 0.51oz for a 2-3 season tarp, but for a mid that I want to use with snowloads etc I think the extra weight is well spent.
You also see people using 1-1.5oz cuben in some applications, but the appeal there is different. The main attraction with this heavier variants is the thicker outer layers, which are more than twice as thick and thus give it better abrasion resistance. It's still not great at abrasion though, so it's suited for gear that sees light abrasion (stuff sacks) but not serious abrasion (packs).