I can personally say, I love it. Not only do I use it several times a day, instead of going to Google and conducting a site command, it actually returns what I am looking for (at least most of the time). With the built in search features in Movable Type, it sorts search results by data and not by relevance. So I often find the built in search feature both a time waster and very slow to return results. The Google CSE works fast and accurately.
In addition to being a great way to find my content, it also has earned me some money. I have integrated it with my Google AdSense account and I have earned some nice pocket change on the side.
They have a great support staff in the Google Custom Search Google Groups discussion forum, to answer your questions also.
I am not the only one in love with the Google CSE. A DigitalPoint Forums thread has a few people praising Google over it.
I started a bit of a case study a few months ago and combined a Google co-op search engine with googlepages and am so far pleased with the results.
I've never set one up for money but I have used it for personal use and found it very easy to use.
Forum discussion at DigitalPoint Forums.