Update CampaignMemberStatus for a Contact based on Opportunity Closure

Question

Currently I am using a campaign to track the status an offer sent to customers. These were sent to Contacts which are attached to an Account. Here's the tricky part: The Opportunity that is being used to track the offers are not related to the Account, but DO share naming conventions.

For example: Account: ABCD-1234 / [email protected] is not related in SF to Opportunity: ABCD-1234-Offer / [email protected]

When the opportunity status changes from Contract Sent or Not Started TO Closed Won or Closed Lost, update that campaign member's status from Sent to Responded.

Is there a way I can update the campaign from the contact for a change in opportunity?

Answer 1

Aside from a programmatic solution, I am coming up with a declarative solution and I am hoping someone may look through it and point out any glaring errors I may have made:

In process builder-

Fire off opportunity; Meets criteria (naming convention AND (closed won OR closed lost)); Immediate action - launch flow, pass variables stage & account id

Flow - receive stage & account id variables look up contact related to account (this does not currently account for multiple contacts per account) update campaignmember status = Responded

Answer 2

Here is what you can do:

1) Write trigger on OPP
2) When its close-won select Account by naming convention
3) From account select contact (there could be multiple contacts you will need to Select one for ex primary contact or most recently updated contact)
4)From contact select campaign member and update it

Few points to note:
1) You may get many records at point # 2,3,4 so you need to write selection logic
2) Bulkify your code and merge queries when ever possible for ex 2 & 3 or 3 or 4

Edit 1

AS SOQL does not support the inline string function we have following options:

1) Create formula field that gives you account name only

2) use like keyword. for ex.

3) Use apex

String s = opp.name;
s = s.left(s.length - 6);
List<Account> acc_list = [Select id, name from account where name = :s];