How to profile a particularly slow or inefficient query
You didn't provide CREATE TABLE scripts; thus, it's hard to give you a specific advice; I'll try to describe general approach....

If you want to speed up this query, first of all, make sure you have indexes on the columns that define join condition, used as a filter (WHERE), or column[s] you have in GROUP BY or ORDER BY. In general, a good sign that some indexes are missed is when EXPLAIN shows "NULL" in key,key_len and "ALL" in type column (not always though; for instance if you select all rows from the table, or table is small enough, so full scan is faster than index seek + lookup).  Then you may want to tweak some indexes to make them covering for this query. 

Side note. The query seems to me like a BI query which normally is not executed against OLTP database. When dealing with a big data, it makes sense to build a few cubes based on data from operational db and query them instead of original data. The nature of OLTP implies high level of normalization and optimization for INSERT/UPDATE/DELETE, not for SELECTs (still possible, but queries can be very long, not clear, and quite slow).