0

I am using MC connect and setting a Journey for a Campaign.

I want to update the Campaign Member Status to "open", "clicked" or "not responding" based on engagement with a 3-5 email send journey. If they open any of the emails they become "opened", etc.

My initial thought was to use the JB update Campaign Member Activity after an engagement split. But given that engagement splits only look at a defined email for defined set of time (ie 48 hours), this will miss a portion of my customer interactions.

See this post Looking for Journey Builder Best Practice for Engagement on Multiple Emails which is related

Also by nature of the wait activity, the customer status will not get updated until my wait is over, thus very late (potentially)

So i believe the best solution is write SSJS script to that runs hourly to look at dataviews and update Campaign Member Status.

Or Handle this on the Sales Cloud side (which i do not want to do)

Am i right? other ideas?

Thanks!

Improve this question

1 Answer 1

Reset to default
1

An SSJS activity's probably a bad idea from a scalability perspective. Instead, create an Automation to inject contacts into a second journey that updates the campaign member status for all contacts sent one of your emails. Your first step would be to identify who's opened your emails or clicked on any of their links. There are a couple of options for this, but I'll stick to SQL. You could potentially do something with Measures, Data Filters and Filter Activities, but it'd take a lot more explaining and Measures tend to give rise to timeouts in Automations.

You'll have to get the list of email ids you're wanting to monitor or if all emails in the Journey, you can add the _JourneyActivity data view to the SQL below and edit the WHERE clause:

SELECT
  s.SubscriberKey,
  CASE
    WHEN MAX(c.JobID) IS NOT NULL THEN 'clicked'
    WHEN MAX(o.JobID) IS NOT NULL THEN 'open'
    ELSE 'not responding'
  END AS 'CampaignMemberStatus'
FROM
  _Job j INNER JOIN
  _Sent s
    ON  j.JobID = s.JobID LEFT JOIN 
  _Open o
    ON  j.JobID = o.JobID AND 
        j.ListID = o.ListID AND 
        j.BatchID = o.BatchID AND 
        j.SubscriberID = o.SubscriberID LEFT JOIN 
  _Click c 
    ON  j.JobID = c.JobID AND 
        j.ListID = c.ListID AND 
        j.BatchID = c.BatchID AND 
        j.SubscriberID = c.SubscriberID
WHERE
  j.EmailID IN (123456,678910,1121314)
GROUP BY 
  s.SubscriberKey

Use the table populated by the query as the entry source for your second journey and update your campaign member records in Journey Builder based on CampaignMemberStatus and schedule your Automation to run each hour.

Improve this answer
4
  • interesting idea. Crossed my mind, wasn't aware of the scalability concerns with SSJS. Appreciate the SQL code, I am pretty comfortable with SQL, but actually never use the BatchID in my joins :-/ thought job+subkey was enough to do the data_view join. @macca - do you think I need to update my old data view queries?
    – Patrick Downing
    May 6, 2020 at 12:09
  • SSJS would obviously be running in a single thread iterating over all your response data (potentially quite large). To update Campaign Members, you'd need to embed AMPScript's UpdateSingleSalesforceObject(). The function's painfully slow and there's also the overhead of switching between AMP and SSJS. Your Script'll timeout after 15 mins and this type of setup wouldn't be able to process that many records. JB Triggered sends, like regular sends use a single JobID, but your subscriber can appear multiple times for each Job in the _Sent view.
    – Macca
    May 7, 2020 at 2:28
  • 1
    The primary key on _Sent is [JobID,ListID,BatchID,SubscriberID] and the indexes on the views tend to focus on these same fields. For performance and API traffic, you might want to take snapshots each time you run the automation to only inject contacts where the CampaignMemberStatus has actually changed.
    – Macca
    May 7, 2020 at 2:31
  • 1
    thanks gain - this is working great - interesting to note. JB requires a DE to be sendable - looks like the product wasn't (and still isnt) expecting this use case. No big deal to me, just felt like sharing :-)
    – Patrick Downing
    May 7, 2020 at 15:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .