/**
* Get all subnodes of a campaign
*
* @param integer $id campaign id
* @param integer $depth maximum depth
*
* @return array
*/
function civicrm_api3_campaign_tree_getids($params)
{
return CRM_Campaign_Tree::getCampaignIds($params['id'], $params['depth']);
}
/**
* Get Key Performance Indicators (KPIs) for a specific campaign (-subtree):
*
* Total Costs (sum of expenses)
* Total revenue (sum of contributions connected with campaign)
* Total revenue Goal (field in campaign)
* Number/amount of Contributions (all but cancelled & failed)
* Number/amount of Contributions (only completed)
* Number of first contributions
* ROI (Total revenue divided by total costs)
* average contribution amount
* average cost per first contribution
* Revenue goal reached in percent (total revenue divided by total revenue goal *100)
*/
public static function getCampaignKPI($id)
{
// get all sub-campaigns
$campaigns = CRM_Campaign_Tree::getCampaignIds($id, 99);
$ids = array();
$ids[] = $id;
if (count($campaigns['children']) > 0) {
$campaigns = $campaigns['children'];
}
$ids_list = implode(',', $ids);
// needed status ids
$status = array();
$status['completed'] = CRM_Core_OptionGroup::getValue('contribution_status', 'Completed', 'name');
$status['cancelled'] = CRM_Core_OptionGroup::getValue('contribution_status', 'Cancelled', 'name');
$status['failed'] = CRM_Core_OptionGroup::getValue('contribution_status', 'Failed', 'name');
// get total revenue
if (count($campaigns['children']) > 0) {
$ids_list_tr = implode(',', array_merge(array($id), array_keys($campaigns)));
} else {
$ids_list_tr = $ids_list;
}
$query = "\n SELECT SUM(contrib.total_amount) as revenue\n FROM civicrm_contribution contrib\n WHERE contrib.campaign_id IN ( {$ids_list_tr} )\n AND contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']})\n ";
$contribution = CRM_Core_DAO::executeQuery($query);
$kpi = array();
$total_revenue = 0.0;
while ($contribution->fetch()) {
$total_revenue = is_null($contribution->revenue) ? 0.0 : $contribution->revenue;
}
$kpi["total_revenue"] = array("id" => "total_revenue", "title" => "Total Revenue", "kpi_type" => "money", "vis_type" => "none", "description" => "Total revenue", "value" => isset($total_revenue) ? $total_revenue : 0.0, "link" => "");
// get total revenue goal
$query = "\n SELECT camp.goal_revenue\n FROM civicrm_campaign camp\n WHERE camp.id = {$id};\n ";
$campaign = CRM_Core_DAO::executeQuery($query);
while ($campaign->fetch()) {
$total_revenue_goal = is_null($campaign->goal_revenue) ? 0.0 : $campaign->goal_revenue;
}
$kpi["total_revenue_goal"] = array("id" => "total_revenue_goal", "title" => "Total Revenue Goal", "kpi_type" => "money", "vis_type" => "none", "description" => "Total revenue goal", "value" => isset($total_revenue_goal) ? $total_revenue_goal : 0.0, "link" => "");
// get all completed and average contribution amount
$query = "\n SELECT COUNT(contrib.id) as amount_completed,\n AVG(contrib.total_amount) as amount_average\n FROM civicrm_contribution contrib\n WHERE contrib.campaign_id IN ({$ids_list})\n AND contrib.contribution_status_id = {$status['completed']};\n ";
$contribution = CRM_Core_DAO::executeQuery($query);
while ($contribution->fetch()) {
$amount_completed = $contribution->amount_completed;
$amount_average = is_null($contribution->amount_average) ? 0.0 : $contribution->amount_average;
}
$kpi["amount_completed"] = array("id" => "amount_completed", "title" => "Number of Contributions (completed)", "kpi_type" => "number", "vis_type" => "none", "description" => "Number of completed contributions", "value" => isset($amount_completed) ? $amount_completed : 0.0, "link" => "");
$kpi["amount_average"] = array("id" => "amount_average", "title" => "Average Amount of Contributions", "kpi_type" => "money", "vis_type" => "none", "description" => "Average amount of completed contributions", "value" => isset($amount_average) ? $amount_average : 0.0, "link" => "");
// get all but cancelled and failed
$query = "\n SELECT COUNT(contrib.id) as amount_all\n FROM civicrm_contribution contrib\n WHERE contrib.campaign_id IN ({$ids_list})\n AND contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']});\n ";
$contribution = CRM_Core_DAO::executeQuery($query);
while ($contribution->fetch()) {
$amount_all = $contribution->amount_all;
}
$kpi["amount_all"] = array("id" => "amount_all", "title" => "Number of Contributions (all but cancelled/failed)", "kpi_type" => "number", "vis_type" => "none", "description" => "Number of Contributions (all but cancelled/failed)", "value" => isset($amount_all) ? $amount_all : 0.0, "link" => "");
// get all expenses
$result = civicrm_api3('CampaignExpense', 'getsum', array('campaign_id' => $id));
if ($result['is_error'] == 0) {
$total_costs = $result['values'][$result['id']];
}
$kpi["total_cost"] = array("id" => "ttlcost", "title" => "Total Costs", "kpi_type" => "money", "vis_type" => "none", "description" => "Sum of (known) expenses to this campaign", "value" => isset($total_costs) ? $total_costs : 0.0, "link" => "");
$query = "\n SELECT COUNT(id)\n FROM civicrm_contribution first_contribution\n WHERE first_contribution.campaign_id IN ({$ids_list})\n AND NOT EXISTS (SELECT id\n FROM civicrm_contribution other_contribution\n WHERE other_contribution.contact_id = first_contribution.contact_id\n AND other_contribution.receive_date < first_contribution.receive_date);";
$first_contributions = CRM_Core_DAO::singleValueQuery($query);
// get all first
$kpi["amount_first"] = array("id" => "amount_first", "title" => "Number of First Contributions", "kpi_type" => "number", "vis_type" => "none", "description" => "Number of first contributions associated with this campaign", "value" => $first_contributions, "link" => "");
// get average cost per first contribution
$kpi['amount_average_first'] = array("id" => "amount_average_first", "title" => "Average Cost per First Contribution", "kpi_type" => "money", "vis_type" => "none", "description" => "Average Cost per first contribution associated with this campaign", "value" => $total_costs / $first_contributions, "link" => "");
$second_or_later = $amount_all - $first_contributions;
// get average cost per second or later contribution
$kpi['amount_average_second_or_later'] = array("id" => "amount_average_second", "title" => "Average Cost per Second or Later Contribution", "kpi_type" => "money", "vis_type" => "none", "description" => "Average Cost per second or later contribution associated with this campaign", "value" => $second_or_later, "link" => "");
// get ROI
$kpi["roi"] = array("id" => "roi", "title" => "ROI", "kpi_type" => "number", "vis_type" => "none", "description" => "Return on investment", "value" => $total_revenue / ($total_costs == 0.0 ? 1.0 : $total_costs), "link" => "https://en.wikipedia.org/wiki/Return_on_investment");
// get revenue goal reached percent
if ($total_revenue_goal) {
$total_revenue_goal_pc = $total_revenue / $total_revenue_goal;
} else {
$total_revenue_goal_pc = -1;
}
$kpi["total_revenue_goal_pc"] = array("id" => "total_revenue_goal_pc", "title" => "Total Revenue Reached", "kpi_type" => "percentage", "vis_type" => "none", "description" => "Total Revenue reached", "value" => $total_revenue_goal_pc, "link" => "");
// get revenue breakdown
$query = "\n SELECT SUM(contrib.total_amount) as revenue,\n camp.title as label\n FROM civicrm_contribution contrib,\n civicrm_campaign camp\n WHERE contrib.campaign_id IN ( %s )\n AND contrib.campaign_id = camp.id\n AND contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']})\n ";
$e_query = sprintf($query, $id);
$contribution = CRM_Core_DAO::executeQuery($e_query);
while ($contribution->fetch()) {
$revenue_current = array("label" => $contribution->label, "value" => (is_null($contribution->revenue) ? 0.0 : $contribution->revenue) / $total_revenue);
}
$revenue_subcampaigns = array();
$tmp_idslist = array();
$children = CRM_Campaign_Tree::getCampaignIds($id, 0);
if (count($children['children']) > 0) {
$children = $children['children'];
foreach ($children as $c_id => $label) {
$subcampaigns = CRM_Campaign_Tree::getCampaignIds($c_id, 99);
$tmp_idslist[] = $c_id;
if (count($subcampaigns['children']) > 0) {
$subcampaigns = $subcampaigns['children'];
foreach ($subcampaigns as $key => $value) {
$tmp_idslist[] = $key;
}
}
$id_string = implode(',', $tmp_idslist);
$e_query = sprintf($query, $id_string);
$curr_contrib = CRM_Core_DAO::executeQuery($e_query);
while ($curr_contrib->fetch()) {
if (is_null($curr_contrib->revenue)) {
continue;
}
$revenue_subcampaigns[] = array("label" => $label, "value" => $curr_contrib->revenue / $total_revenue);
}
$tmp_idslist = array();
}
}
$revenue_combined = array();
$revenue_combined[] = $revenue_current;
$revenue_combined = array_merge($revenue_combined, $revenue_subcampaigns);
$kpi["revenue_breakdown"] = array("id" => "revenue_breakdown", "title" => "Revenue Breakdown", "kpi_type" => "hidden", "vis_type" => "pie_chart", "description" => "Revenue Breakdown", "value" => $revenue_combined, "link" => "");
// get donation heartbeat
if (count($campaigns['children']) > 0) {
$ids_list_hb = implode(',', array_merge(array($id), array_keys($campaigns)));
} else {
$ids_list_hb = $ids_list;
}
$query_contribs = "\n SELECT `receive_date` as date,\n COUNT(*) as value\n FROM civicrm_contribution contrib\n WHERE contrib.campaign_id IN ( {$ids_list_hb} )\n AND contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']})\n GROUP BY DATE(`receive_date`)\n ;";
$all_contribs = array();
$contribution = CRM_Core_DAO::executeQuery($query_contribs);
while ($contribution->fetch()) {
$date = new DateTime($contribution->date);
$date = $date->format('Y-m-d 00:00:00');
$all_contribs[] = array("date" => $date, "value" => $contribution->value);
}
$kpi["donation_heartbeat"] = array("id" => "donation_heartbeat", "title" => "Donation Heartbeat", "kpi_type" => "hidden", "vis_type" => "line_graph", "description" => "Donation Heartbeat", "value" => $all_contribs, "link" => "");
CRM_Utils_CampaignCustomisationHooks::campaign_kpis($id, $kpi, 99);
return json_encode($kpi);
}