I played the game once when I was in college about seven or eight years ago. I enjoyed it and ended up reading some of the short stories, which handle atmosphere very well.
The funny thing for me is that my D&D games have always had an undercurrent similar to that of the CoC games. There is a darkness and a sense of desperation. While I don't use Insanity points, I do explain how terrible the appearance of a Pit Fiend is, or how mad-shatteringly incredible the presence of a Dragon or a high level NPC is. Terrible, dark things happen regularly to PCs, their families, their loved ones, and to the people to whom they feel responsible. PCs are faced with knowing that there are many things hidden in the world that pose a risk to mortals and that many of these things hate Humanity and want to destroy it. They know that even their allies cannot be fully trusted. I just don't use Insanity scores in my games.... but I think the Players get the idea.
I love what d20 did for CoC. One of the reason I never got into the last game (aside from the fact that the overwhelming horror turned away other players) was because I already knew 2ed D&D, Marvel Superheroes, DC Superheroes, and Spycraft and was thinking of getting into Star Wars or Star Trek. I did not want to have to buy all these other resources, especially when other people weren't playing. Now, since I don't have to learn an entirely new system, I would love to play CoC regularly. I've even toyed around with implementing the Insanity elements into my D&D campaign, thereby capturing the Dark Fantasy overtones that have always existed but have never been quantified. Ravenloft has elements of this but was always restricted due to the fact that most people played it like "grey" D&D rather than Dark D&D.
CoC is a great game that really offers some great things. It can be like Raiders of the Lost Ark, or it can be like Angel Heart. Loads of fun!