I'd say it depends on what your expectations are. If your artistic ability is on the low end of the scale, you're simply not going to produce the breathtakingly awesome maps that you see in the ads. If you buy it expecting it to make you a greater artist than you are, you'll probably be disappointed. Personally I'm mediocre artistically, and I tend to produce mediocre maps with Campaign Cartographer, though they're loads better than what I can do with colored pencils and paper.
The main thing I liked about CC was the fact that I can make changes without having to redraw the map from scratch. This saved me quite a bit of time versus pencil and paper. It's also nice having printable maps that can be marked on or whatnot without worrying about destroying the one good map.
I haven't tried the city or dungeon add on packs. I did get the character portrait one, mostly to make counters. It was serviceable for that, although none of the included templates were sized properly for my battle mat and I wound up having to cut off the edges of the counters I made with it. If I were buying it now I'd give the character module a miss probably.
