Campaign Budget vs. Actual Costs (By Fiscal)

Dynamics CRM

 

Dynamics CRM is a widely used cloud CRM from Microsoft, focused on Sales, Marketing, and Service (help desk) sectors.

The chart takes all campaigns for the current fiscal year and compares the budgeted amount versus the actual costs recorded to run the campaign

Query Text

SELECT 
 (case when (strftime('%m', c."actualend") in ('01','02','03')) then 'Q1' WHEN (strftime('%m', c."actualend") in ('04','05','06')) then 'Q2' WHEN (strftime('%m', c."actualend") in ('07','08','09')) then 'Q3' WHEN (strftime('%m', c."actualend") in ('10','11','12')) then 'Q4' else 'blank' end ) "Quarter" ,
  SUM(c."budgetedcost") "Budget Allocated",
  SUM(c."totalactualcost") "Total Cost of Campaign"
  FROM campaign c
  WHERE ( strftime('%Y', c."actualstart") = strftime('%Y','now') OR strftime('%Y', c."actualend") = strftime('%Y','now')) AND
          c."statuscode" not in ('Canceled', 'Inactive', 'Suspended')
  GROUP BY 1
  ORDER BY 1
  

Open Query