I wrote an APEX script for the campaign object that updates when the campaign updates. Unfortunately I am running into the issue of hitting limits. Also, it would be a lot more useful if the data updated on a regular schedule rather than when the campaign is updated.
Also, trying to wrap my head around writing a test class for a scheduled Batch Update.
here is the code:
trigger UpdateInfluences on Campaign (before update) { Set campaignNames = New Set(); Map dateTimeMap = New Map(); Map infMQLCounts = New Map(); Map srcMQLCounts = New Map(); Map infSQLCounts = New Map(); Map srcSQLCounts = New Map(); Map infOpCounts = New Map(); Map infOpWonCounts = New Map(); Map infOpValue = New Map(); Map infOpWonValue = New Map(); Map srcOpCounts = New Map(); Map srcOpWonCounts = New Map(); Map srcOpValue = New Map(); Map srcOpWonValue = New Map(); for (Campaign currentCampaign : Trigger.new) { //Initiate all the variables campaignNames.add(currentCampaign.Name); dateTimeMap.put(currentCampaign.Name, currentCampaign.StartDate); infMQLCounts.put(currentCampaign.Name, 0); srcMQLCounts.put(currentCampaign.Name, 0); infSQLCounts.put(currentCampaign.Name, 0); srcSQLCounts.put(currentCampaign.Name, 0); infOpCounts.put(currentCampaign.Name, 0); infOpWonCounts.put(currentCampaign.Name, 0); infOpValue.put(currentCampaign.Name, 0); infOpWonValue.put(currentCampaign.Name, 0); srcOpCounts.put(currentCampaign.Name, 0); srcOpWonCounts.put(currentCampaign.Name, 0); srcOpValue.put(currentCampaign.Name, 0); srcOpWonValue.put(currentCampaign.Name, 0); } // For each member of the campaign, pull all relevant data that is later used to determine if they should be counted on the lists or not List leadResults = [SELECT CampaignMember.Id, Lead.MQL_Date__c, Contact.MQL_Date__c, Lead.SQL_Date__c, Contact.SQL_Date__c, Lead.isMQL__c, Contact.isMQL__c, Lead.isSQL__c, Contact.isSQL__c, Lead.mkto2__Acquisition_Program__c, Contact.mkto2__Acquisition_Program__c, Campaign.Name, Contact.Id, CampaignMember.HasResponded FROM CampaignMember WHERE CampaignMember.HasResponded = true AND (Lead.isMQL__c = true OR Contact.isMQL__c = true OR Lead.isSQL__c = true OR Contact.isSQL__c = true) AND Campaign.Name IN :campaignNames]; // Pull a list of all the primary contacts on opportunities List opportunityResults = [SELECT ContactId, Opportunity.StageName, Opportunity.Name, Opportunity.IsWon, Opportunity.CreatedDate, Opportunity.Quote_Accepted_Date__c, Opportunity.Adjusted_Opportunity_Amount__c, Opportunity.Acquisition_Program__c FROM OpportunityContactRole WHERE OpportunityContactRole.IsPrimary=true]; Map> opportunityMap = new Map>(); for(OpportunityContactRole o : opportunityResults){ if (opportunityMap.containsKey(o.ContactId)) { opportunityMap.get(o.ContactId).add(o); } else { opportunityMap.put(o.ContactId, new List{o}); } } for (CampaignMember c : leadResults) { //Influence Data - If the contact/lead is a member of the campaign and became a member of the campaign before the MQL date or the SQL date if (c.Lead.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name)) infMQLCounts.put(c.Campaign.Name,infMQLCounts.get(c.Campaign.Name)+1); if (c.Lead.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name)) infSQLCounts.put(c.Campaign.Name,infSQLCounts.get(c.Campaign.Name)+1); //Source Data - If the contact/lead is a member of the campaign and the acqusition program is the current campaign if ((c.Lead.isMQL__c == true || c.Contact.isMQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name)) srcMQLCounts.put(c.Campaign.Name,srcMQLCounts.get(c.Campaign.Name)+1); if ((c.Lead.isSQL__c == true || c.Contact.isSQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name)) srcSQLCounts.put(c.Campaign.Name,srcSQLCounts.get(c.Campaign.Name)+1); if (opportunityMap.containsKey(c.Contact.Id) == true) { //influence opportunities - If the opportunity was created/won after the contact/lead was added to the campaign for (OpportunityContactRole ocr : opportunityMap.get(c.Contact.Id)) { if (ocr.Opportunity.CreatedDate >= dateTimeMap.get(c.Campaign.Name)) { infOpCounts.put(c.Campaign.Name, infOpCounts.get(c.Campaign.Name)+1); infOpValue.put(c.Campaign.Name, infOpValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); if (ocr.Opportunity.Quote_Accepted_Date__c >= dateTimeMap.get(c.Campaign.Name) && ocr.Opportunity.IsWon == true) { infOpWonCounts.put(c.Campaign.Name, infOpWonCounts.get(c.Campaign.Name)+1); infOpWonValue.put(c.Campaign.Name, infOpWonValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); } } } } } for (OpportunityContactRole ocr : opportunityResults) { //source opportunities - If the primary contact of the opportunity acqusition program = this campaign if (campaignNames.contains(ocr.Opportunity.Acquisition_Program__c) == true) { srcOpCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpCounts.get(ocr.Opportunity.Acquisition_Program__c)+1); srcOpValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); if (ocr.Opportunity.IsWon) { srcOpWonCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonCounts.get(ocr.Opportunity.Acquisition_Program__c)+1); srcOpWonValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); } } } for (Campaign currentCampaign : Trigger.new) { // Update everythin on the campaign currentCampaign.Influenced_MQLs__c = (Decimal)infMQLCounts.get(currentCampaign.Name); currentCampaign.Influenced_SQLs__c = (Decimal)infSQLCounts.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Count__c = (Decimal)infOpCounts.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Amount__c = (Decimal)infOpValue.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Won_Count__c = (Decimal)infOpWonCounts.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Won_Amount__c = (Decimal)infOpWonValue.get(currentCampaign.Name); currentCampaign.Sourced_MQLs__c = (Decimal)srcMQLCounts.get(currentCampaign.Name); currentCampaign.Sourced_SQLs__c = (Decimal)srcSQLCounts.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Count__c = (Decimal)srcOpCounts.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Amount__c = (Decimal)srcOpValue.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Won_Count__c = (Decimal)srcOpWonCounts.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Won_Amount__c = (Decimal)srcOpWonValue.get(currentCampaign.Name); } }
I have looked through the Salesforce docs and many blog posts on how to schedule batch updates, but haven't been successful. This is what I was trying, but I can't seem to wrap my head around this: (long again, main changes are in the first few lines)
global class CampaignInfluence_BatchUpdate implements Database.Batchable { //Get all the campaign Ids public string query = 'SELECT Id FROM Campaign'; //, Schedulable //global void execute (SchedulableContext SC){ // database.executeBatch(this); // string sch = '0 30 2,6,10,14,18,22 * * ?'; // system.schedule('Batch', sch, new Scheduler_class()); //} global Database.QueryLocator start(Database.BatchableContext BC) { return Database.getQueryLocator(query); } global void execute(Database.BatchableContext BC, List scope) { // List of campaigns to update List campaigns_to_update = (List )scope; for (campaign current_campaign : scope){ campaigns_to_update.add(current_campaign); } Set campaignNames = New Set(); // List of campaign names // Object Maps for all the fields to be updated Map dateTimeMap = New Map(); Map infMQLCounts = New Map(); Map srcMQLCounts = New Map(); Map infSQLCounts = New Map(); Map srcSQLCounts = New Map(); Map infOpCounts = New Map(); Map infOpWonCounts = New Map(); Map infOpValue = New Map(); Map infOpWonValue = New Map(); Map srcOpCounts = New Map(); Map srcOpWonCounts = New Map(); Map srcOpValue = New Map(); Map srcOpWonValue = New Map(); //Initialize the campaigns name set and all the maps to 0 for (Campaign currentCampaign : campaigns_to_update) { campaignNames.add(currentCampaign.Name); dateTimeMap.put(currentCampaign.Name, currentCampaign.StartDate); infMQLCounts.put(currentCampaign.Name, 0); srcMQLCounts.put(currentCampaign.Name, 0); infSQLCounts.put(currentCampaign.Name, 0); srcSQLCounts.put(currentCampaign.Name, 0); infOpCounts.put(currentCampaign.Name, 0); infOpWonCounts.put(currentCampaign.Name, 0); infOpValue.put(currentCampaign.Name, 0); infOpWonValue.put(currentCampaign.Name, 0); srcOpCounts.put(currentCampaign.Name, 0); srcOpWonCounts.put(currentCampaign.Name, 0); srcOpValue.put(currentCampaign.Name, 0); srcOpWonValue.put(currentCampaign.Name, 0); } //Get the list of all campaign members List leadResults = [SELECT CampaignMember.Id, Lead.MQL_Date__c, Contact.MQL_Date__c, Lead.SQL_Date__c, Contact.SQL_Date__c, Lead.isMQL__c, Contact.isMQL__c, Lead.isSQL__c, Contact.isSQL__c, Lead.mkto2__Acquisition_Program__c, Contact.mkto2__Acquisition_Program__c, Campaign.Name, Contact.Id, CampaignMember.HasResponded FROM CampaignMember WHERE CampaignMember.HasResponded = true AND (Lead.isMQL__c = true OR Contact.isMQL__c = true OR Lead.isSQL__c = true OR Contact.isSQL__c = true) AND Campaign.Name IN :campaignNames]; // Get the list of all opportunities with primary contacts List opportunityResults = [SELECT ContactId, Opportunity.StageName, Opportunity.Name, Opportunity.IsWon, Opportunity.CreatedDate, Opportunity.Quote_Accepted_Date__c, Opportunity.Adjusted_Opportunity_Amount__c, Opportunity.Acquisition_Program__c FROM OpportunityContactRole WHERE OpportunityContactRole.IsPrimary=true]; // Map contact ID to opportunity contact role record Map> opportunityMap = new Map>(); for(OpportunityContactRole o : opportunityResults){ if (opportunityMap.containsKey(o.ContactId)) { opportunityMap.get(o.ContactId).add(o); } else { opportunityMap.put(o.ContactId, new List{o}); } } System.debug(opportunityResults); System.debug(opportunityMap); // Cycle through campaign members and add up the record counts on the campaigns. for (CampaignMember c : leadResults) { //Influence Data if (c.Lead.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.MQL_Date__c>= dateTimeMap.get(c.Campaign.Name)) infMQLCounts.put(c.Campaign.Name,infMQLCounts.get(c.Campaign.Name)+1); if (c.Lead.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name) || c.Contact.SQL_Date__c>= dateTimeMap.get(c.Campaign.Name)) infSQLCounts.put(c.Campaign.Name,infSQLCounts.get(c.Campaign.Name)+1); //Source Data if ((c.Lead.isMQL__c == true || c.Contact.isMQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name)) srcMQLCounts.put(c.Campaign.Name,srcMQLCounts.get(c.Campaign.Name)+1); if ((c.Lead.isSQL__c == true || c.Contact.isSQL__c == true) && (c.Lead.mkto2__Acquisition_Program__c == c.Campaign.Name || c.Contact.mkto2__Acquisition_Program__c == c.Campaign.Name)) srcSQLCounts.put(c.Campaign.Name,srcSQLCounts.get(c.Campaign.Name)+1); if (opportunityMap.containsKey(c.Contact.Id) == true) { //influence opportunities System.debug(opportunityMap.get(c.Contact.Id)); for (OpportunityContactRole ocr : opportunityMap.get(c.Contact.Id)) { if (ocr.Opportunity.CreatedDate >= dateTimeMap.get(c.Campaign.Name)) { infOpCounts.put(c.Campaign.Name, infOpCounts.get(c.Campaign.Name)+1); infOpValue.put(c.Campaign.Name, infOpValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); if (ocr.Opportunity.Quote_Accepted_Date__c >= dateTimeMap.get(c.Campaign.Name) && ocr.Opportunity.IsWon == true) { infOpWonCounts.put(c.Campaign.Name, infOpWonCounts.get(c.Campaign.Name)+1); infOpWonValue.put(c.Campaign.Name, infOpWonValue.get(c.Campaign.Name)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); } } } } } for (OpportunityContactRole ocr : opportunityResults) { if (campaignNames.contains(ocr.Opportunity.Acquisition_Program__c) == true) { srcOpCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpCounts.get(ocr.Opportunity.Acquisition_Program__c)+1); srcOpValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); if (ocr.Opportunity.IsWon) { srcOpWonCounts.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonCounts.get(ocr.Opportunity.Acquisition_Program__c)+1); srcOpWonValue.put(ocr.Opportunity.Acquisition_Program__c, srcOpWonValue.get(ocr.Opportunity.Acquisition_Program__c)+ocr.Opportunity.Adjusted_Opportunity_Amount__c); } } } // Update the appropriate fields on the campaign for (Campaign currentCampaign : Trigger.new) { currentCampaign.Influenced_MQLs__c = (Decimal)infMQLCounts.get(currentCampaign.Name); currentCampaign.Influenced_SQLs__c = (Decimal)infSQLCounts.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Count__c = (Decimal)infOpCounts.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Amount__c = (Decimal)infOpValue.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Won_Count__c = (Decimal)infOpWonCounts.get(currentCampaign.Name); currentCampaign.Influenced_Opportunity_Won_Amount__c = (Decimal)infOpWonValue.get(currentCampaign.Name); currentCampaign.Sourced_MQLs__c = (Decimal)srcMQLCounts.get(currentCampaign.Name); currentCampaign.Sourced_SQLs__c = (Decimal)srcSQLCounts.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Count__c = (Decimal)srcOpCounts.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Amount__c = (Decimal)srcOpValue.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Won_Count__c = (Decimal)srcOpWonCounts.get(currentCampaign.Name); currentCampaign.Sourced_Opportunity_Won_Amount__c = (Decimal)srcOpWonValue.get(currentCampaign.Name); } } global void finish(Database.BatchableContext BC){} }