Optimization for aggregation and grouping with MySQL
Using temporary and filesort are not as evil as everyone makes them out to be.

Do not split a DATE into year, month, and day columns; it will cause more pain than it is worth.

I prefer (in your specific case) to do GROUP BY LEFT(c.date, 7), although it may not speed up anything.

"Summary Tables" is the way to go for that type of query, assuming you have large tables.  More discussion in my blog.  You would add one (maybe more) row to the summary table each night, then a query against that table could summarize any day range, including your month.

Do not store AVG(...) in a Summary table; it is probably more 'correct' to store SUM(foo) AS sum_foo and COUNT(*) AS foo_ct, then have the report

SUM(sum_foo) / SUM(foo_ct) AS 'Average foo'


Probably the optimal summary table would have

PRIMARY KEY(goal_id, date)


and have sum_conversions calculated from SUM(c.conversions), plus anything else relevant.  Often, one summary table can handle a small set of standard 'reports'.