It's hardly a dirty little secret. More like blatantly obvious. If all you are ever going to do is "browsing, itunes, iPhoto, mail, Office, loading files from camera to computer or computer to ipod, etc." then even the 3Ghz C2D is overkill... my 2Ghz iMac could do that in its sleep and it's 3 years old. A bottom end Macbook or Mini would be more appropriate for that sort of thing - aside from having a nice big ass screen.
For the rest of us, it's about doing more at the same time. If I'm unzipping a large file on my C2D (which I do quite a lot of) then it tends to hog resources, slowing things down when I want to be surfing, or whatever. My wife and I remain logged in all the time, so my login could be unpacking some files whilst my wife is logged in trying to surf. It's just a bit slow.
With a Quad, I can get SABNZBD+ to use no more than 2 cores for unpacking or repairing with par files, and that still leaves a 2 full cores with hyperthreading free for surfing, listening to itunes, or whatever. It's like having that intensive task stuck away on another PC out of the way.
You really don't need to be a power user to make the most of it. If you routinely run more than one task at a time, then you'll feel the benefits. I noticed it going from a single core cpu to a dual core cpu. Jumping to a quad core just increases the available resources meaning less chance of overloading the resources.
You say that the Photoshop benchmarks were similar. They probably were. Would they have been if the guy had been unpacking some files, or encoding a movie, or something, at the same time? Even just pulling in a large RAW image into Aperture makes my current iMac weep. Being able to do that and still have one or two cores free to work on other things or surf is a great thing.