We have multiple email programs that we can send to the same person multiple times. We calculate our campaign stats in SFDC using formulas from campaign statuses sent from Marketo (i.e. Sent, Delivered, Opened, Clicked, Responded, Unsubscribed). We have smart campaigns set up to change the campaign status based on the contact's activity.
When a contact is sent an email more than once, the stats for that contact are being overridden instead of averaging out each time they are sent a specific campaign in SFDC. (i.e. Lead A is sent Campaign 1 and clicks the campaign, the next time Lead A is sent campaign 1 they only open the campaign. Stats are being changed to 0% clicked and 100% opened, instead of 100% opened 50% clicked).
A workaround we thought of was to create a score field for each status (i.e. Sent, Delivered, Opened, Clicked, Responded, Unsubscribed) so when the lead runs through the specific status of that specific campaign more than one time, we could roll this up and average out the stats in SFDC. Our SFDC Admin advised we would need to update this new score field on the Campaign Member, not the contact. If we are to create these 6 new score fields on the campaign member in SFDC, is there a way to update this from Marketo? I am not sure what Flow step would like - there is only one field for each status, but we are using this for multiple Marketo programs. So ideally it would work as follows:
Lead 1 was sent campaign A: Campaign A Sent Score = 1
Lead 1 was sent campaign B: Campaign B Sent Score = 1
Lead 1 was sent campaign A again: Campaign A Sent Score = 2
And so on...
Please advise if this is possible or if there may be another solution.