In a pithy one liner: it's curl pipe bash for local binaries.

I've run into it when I used to install Virtualbox "Guest Additions" or whenever I'd make the questionable decision to use the latest video drivers during my annual dalliance with desktop Linux a decade ago.

The value it provides is fewer steps in an installation guide for non-power users (or as jakobdabo mentions below, particularly for users coming from the Windows "double click to install" world). It would extract and run a post extract hook. I haven't come across it much in the last few years though.