An honest summary.
USE IT IF
You receive attack or bot traffic consuming resources Your visitors are geographically spread You want fast, free, well-designed DNS management You need protection against traffic spikes
USE DNS ONLY IF
Your visitors are local and your server is reachable The proxy caused problems you would rather not manage You want the DNS benefits without another system in the path
DO NOT BOTHER IF
Your site is small, local, already cached and untroubled
WHAT TO DO FIRST REGARDLESS
Enable server-side caching Optimise your images Keep your software updated
Those three matter more than Cloudflare for most sites, and Cloudflare does not substitute for any of them.
THE CONFIGURATION THAT MATTERS
Full (strict) SSL, mail records DNS only. Get those two right and most problems never occur.
THE HONEST POSITION
Cloudflare is genuinely useful and frequently misconfigured.
Configured correctly it is an asset. Configured carelessly it is the cause of the next three support tickets.