Instructions: Setting Up Salesforce Push for Buyer Journeys

To enable CaliberMind to push Buyer Journey data back to your Salesforce instance, please follow the steps below. This process involves configuring a Connected App in Salesforce, authorizing the connector in CaliberMind, and creating the necessary destination fields.

Step 1: Configure Salesforce Connected App & Permissions

Please review the full documentation here: Setting Up the Salesforce Push Connector

  • Install the Connected App
    • Navigate to Setup in Salesforce.
    • Go to Connected Apps OAuth Usage.
    • Search for the CaliberMind OAuth2 app.
    • Click Install, then authenticate and install the app.
    • Configure which users are permitted to access the connected app.
  • Configure User Permissions We highly recommend authenticating with the same user used for the CaliberMind Pull Connector to ensure consistency.
    • Required Permissions: The connecting user must have “Write” and “Modify All” permissions for any objects and fields you want CaliberMind to update (specifically Leads, Contacts, and Accounts).

Step 2: Authorize the Push Connector in CaliberMind

Once the Salesforce configuration is complete, return to CaliberMind to authorize the connection.

  1. Within CaliberMind, navigate to Settings
  2. Select Connectors
  3. Click Create Push Connector
  4. Locate the Salesforce Connector Card and select the + Connect button.
  5. Authorize the connector using the Salesforce user credential that has the “Write” and “Modify All” permissions confirmed in Step 1.

Step 3: Create Custom Fields in Salesforce

Please work with your Salesforce Administrator to create the following fields to store the Buyer Journey data.

Account Object

  • cm_buyer_journey (Data Type: Text Area – Rich)
  • cm_buyer_journey_last_updated (Data Type: Date)
  • cm_account_engagement_score (Data Type: Number [16,0])
  • cm_account_engagement_score_last_updated (Data Type: Date)

Step 4: Finalize Setup

Once these steps are complete, please notify your Technical Account Manager (TAM) at CaliberMind. They will complete the final configuration to begin pushing Buyer Journey data to your new Salesforce fields.

Customer Summary Template (advanced)

  • Here is sample SQL to feed into the Ask Cal to Salesforce account flow as a list:

WITH

-- ENGAGEMENT MODEL SELECTION
model_name as (
                select 
                model_name model_filter 
                from cm.cm_engagement 
                -- where model_name = 'xxx' -- add model name here
                order by model_name 
                limit 1
                )

-- COMPANY FILTER
, company_filter as (
                -- add companies here to run summaries
                select 
                company_id 
                from cm.cm_companystats 
                where account_score1 >0 
                and object_type = 'Account'
                )

-- CUSTOMER SUMMARY
,
CUSTOMER_SUMMARY as (
select
c.company_name
, c.company_id
, c.customer_date
, coalesce(c.company_type,c.customer_status) company_type
, c.company_tier
, c.industry_short as company_industry
# , c.customer_status
--, case when c.pipeline is not null then cast(round(c.pipeline) as string format '$999,999,999') end as open_pipeline
--, case when c.customer_ltv is not null then cast(c.customer_ltv as string format '$999,999,999') end as lifetime_revenue
, round(c.pipeline) as open_pipeline
, round(c.customer_ltv) as lifetime_revenue
-- , opps_won number_won_opportunities
-- , opps_open number_open_opportunities
, coalesce(e.surge_rating,'engagement data not available') as momentum
, coalesce(cast(round(e.account_score30,1) as string),'engagement data not available') `Score Change Last 30 days`
, coalesce(cast(ee.company_score_index as string),'engagement data not available') as engagement_index

from `cm.cm_companystats` c
join company_filter using(company_id)
join (select * from `cm.cm_engagement_trend` where model_name in (select model_filter from model_name)) e on e.company_id = c.company_id
join (select * from `cm.cm_engagement` WHERE model_name in (select model_filter from model_name)) ee on ee.company_id = c.company_id
where c.company_id in (
  select company_id from cm.cm_event where is_inbound and 
  date(event_datetime) < current_date() and 
  date(event_datetime) > date_sub(current_date(), interval 2 day)
  )
-- where c.company_id = '{company_id}'
)


-- BUYER GROUP

, BUYER_GROUP as (
      WITH cm_scoring AS (
              SELECT
                  id
                , person_id
                , touch_score_raw
                , touch_score_weighted AS engagement_score
              FROM
              cm.cm_scoring
              join model_name on model_name.model_filter = cm_scoring.model_name
              -- WHERE
              -- company_id = '{company_id}'
              -- AND model_name = '{engagement_model}'
            )

        , person_scores AS (
              SELECT
                  cm_eventstats.company_id
                  ,person_name
                  ,
                  case
                  when cm_eventstats.job_level like '%CXO%' then 0
                  when cm_eventstats.job_level like '%VP%' then 1
                  when cm_eventstats.job_level like '%Dir%' then 2
                  else 3 end title_rank
                , person_title title
                , count(case when is_inbound then 1 end) interactions
                , sum(engagement_score) engagement_score
                , row_number() over(partition by cm_eventstats.company_id order by sum(engagement_score) desc nulls last) as eng_rank
              FROM `cm.cm_eventstats` cm_eventstats
              join cm.cm_person on cm_person.id = cm_eventstats.person_id
              LEFT JOIN cm_scoring ON cm_scoring.id = cm_eventstats.event_id
              WHERE
              -- cm_eventstats.company_id = '{company_id}'
              -- AND (is_inbound = TRUE OR event_class IN ('Opp Won', 'Opp Lost', 'Opp Create'))
              date(cm_eventstats.event_datetime) >= date_sub(date(current_date()), INTERVAL 2 YEAR)
              AND person like '%@%'
              GROUP BY ALL
            )

      , final_ranking AS (
        select
        company_id
        , person_name
        , title
        , case
          when eng_rank = 1 then '(Most Engaged)'
          when engagement_score is not null then '(Engaged)'
          else '(Inactive)' end
            engagement_level
        , interactions
        , row_number() over(partition by company_id order by eng_rank, interactions, title_rank desc nulls last) bj_rnk
        from person_scores
        -- order by eng_rank, interactions desc, title_rank, title desc
      )
      select *
      from final_ranking 
      where bj_rnk <8
      order by company_id, bj_rnk
      
)

-- BUYER JOURNEY DATA
, BUYER_JOURNEY_DATA as (
#@title Step 4. Pull inbound events and opps (page and sales events older 1+yr get agg)

  with events as (
    select *, row_number() over() touch_order
    from cm.cm_eventstats
    
    where
     (is_inbound = TRUE OR event_class IN ('Opp Won', 'Opp Lost', 'Opp Create'))
  )
  , pre_counter as (
  SELECT
        company_id
        , 
        case when date(event_datetime) < current_date() - interval 1 year AND
          (
            event_class like 'Inbound Sales%' OR
            event_system = 'AnalyticsJS'
            )

          then date(timestamp_trunc( event_datetime, month))
            else date(event_datetime)
            end
            `date`
        , CASE
            -- WHEN event_class = 'Inbound Sales' THEN 'Sales Touch'
            WHEN date(event_datetime) < current_date() - interval 1 year AND event_class = 'Inbound Sales%' then 'Sales - '||event_type
            WHEN date(event_datetime) < current_date() - interval 1 year AND event_system = 'AnalyticsJS' then event_type
            WHEN event_system = 'AnalyticsJS' AND
              (
                lower(event_name) like any ('%app%','%login%')
                or
                lower(event_detail) like any ('%app%','%login%','%my.%')
              ) THEN 'Product Usage'
            ELSE TRIM(REGEXP_REPLACE(event_name, r'\[[^\]]*\]', '')) END event_name
        ,
        CASE
          WHEN event_type like 'Opp %' then
            replace(replace(event_type, 'Opp ', 'Opportunity '),'Create','Created')
            ||'Amount: '
            || SAFE_CAST(REGEXP_EXTRACT(event_detail, r'Amount:\s*([\d.]+)') AS FLOAT64)
          WHEN campaign_type is not null then 'Campaign: '|| event_type
          WHEN event_system = 'AnalyticsJS' AND
              (
                lower(event_name) like any ('%app%','%login%')
                or
                lower(event_detail) like any ('%app%','%login%','%my.%')
              ) then null else
          event_type end event_type
        , person_name
        , case when channel = 'Direct' then null else channel end channel
        , case when count(*) >1 then count(*) else null end as multiple_engagements
      FROM events
      GROUP BY ALL
      ORDER BY `date` DESC
  )
  , counter as (
    select *
    , row_number() over (partition by company_id order by `date` desc) event_rnk
  from pre_counter
  )
  select * from counter 
  where event_rnk <5001
      )


    , customer_summary_json as (
        select
          company_id as context_id
        , TO_JSON_STRING(STRUCT(
            company_name
          , cast(customer_date as string)  as customer_date
          , company_type
          , company_tier
          , company_industry
          , open_pipeline
          , lifetime_revenue
          , momentum
          , `Score Change Last 30 days`   as score_change_30d
          , engagement_index
          )) as customer_summary_data
        from CUSTOMER_SUMMARY
      )

  , buyer_group_json as (
    select 
      company_id as context_id 
      , 
        ARRAY_AGG(STRUCT(
       	person_name	
        , title	
        , engagement_level	
        , interactions
        	-- bj_rnk
      )) buyer_group_data
          from buyer_group
          group by company_id
  )
  , buyer_journey_json as (
    select 
      company_id as context_id 
       , ARRAY_AGG(STRUCT(
       	`date`	
        , event_name
        ,	event_type	
        , person_name	
        , channel	
        , multiple_engagements
      
      )) buyer_journey_data
          from buyer_journey_data
          group by company_id
  )



---- TRYING TO MERGE INTO ONE RESULT 
select 
c.context_id
, TO_JSON_STRING(
        ARRAY_AGG(STRUCT(
        c.customer_summary_data
        , b.buyer_group_data
        , d.buyer_journey_data))) data_to_summarize
from customer_summary_json c
left join buyer_group_json b on b.context_id = c.context_id
left join buyer_journey_json d on d.context_id = c.context_id
group by 1
order by 1
  • Here is a sample prompt to use to generate the Rich Text to push into Salesforce at the Account Level:
You are a sophisticated marketing professional employed at a B2B company, skilled at explaining complex marketing concepts using easy to understand language
You are tasked to explain the buyer journey of a B2B company named using marketing event data

You will be provided data tables-- 1) Customer Summary, 2) Buyer Group, 3) Buyer Journey, 4) Open Opportunity Summary

OUTPUT FORMATTING:
- Use the following format for the summary, make sure it is in HTML  format using tags for Headers, bold and italics.  Do not start or end with ```html. 
- Bulleted information should by 12 point font and NOT be bolded.
- IMPORTANT: Ensure there is a space between numbers and words (e.g., "1 Renewal" instead of "1Renewal").
- Avoid using underscores (_) or tildes (~) as they can trigger unintended Markdown formatting.
- Ensure all numerical values and words are presented cleanly.
- If data for an attribute is blank, none, or 0, omit it from the output.

HERE ARE THE HEADINGS:
- Customer Overview: Using the Customer Summary data and other data available-- an executive summary about the account in a few sentences, include information such as customer name / tier / industry, customer's status and customer date. If NOT BLANK OR 0 dollars, include opportunity information if it's available (open pipeline / lifetime revenue formatted at $xxx,xxx in dollars).
- Open Opportunities: Briefly summarize in sub bullets if there are any open opportunities-- including name, type and pipeline amount formatted as $xxx,xxx in dollars. Ensure you use clear spacing between quantities and labels (e.g., "2 Won Opportunities" rather than "2Won"). If describing a "downsell" or "adjustment," wrap the value in plain text to avoid it being interpreted as Markdown syntax.
- Buyer Group: Using the Buyer Group Table, created a bulleted list of up to 7 important individuals with titles. Include with engagement level and a persona-- based on engagement levels and title seniority tag each with ("Decision Maker", "Champion" or "Influencer")
- Engagement: Using the Customer Summary Table, Engagement index as a percentage of the others, momentum and score change in last 30 days ** OMIT THIS SECTION IF ENGAGEMENT DATA IS NOT AVAILABLE
- Key Marketing Activities: Using the Buyer Journey table, a bulleted list summarizing event/campaign type, and mention of who did the action when data is available
- Buyer Journey: Using the Buyer Journey table, a descending bulleted list of activity, summarized by month. Group similar activities together to improve readability. If there are opportunities in the history display them prominently in their own sub bullets (created, won or lost and amount).

ADDITIONAL TIPS:
Do not include additional notes, recommendations or a conclusion. Avoid repetitive bullets.
Group similar activities together to improve readability. For example instead of: John attended a meeting, Frank attended a meeting-- say Frank and John attended a meeting.

DATA DEFINITIONS:
Here are some field definitions for the tables:
Engagement Index: A Prospect or Company's Percentile rank of engagement versus other accounts
Momentum: Status indicating if the company is having and engagement surge
Event Date: date of the event
Event Name: The name of the event
Event Type: Categorization of event type (or campaign if designated)
Person Name: The person's name in the buyer group engaging
Channel: The sourcing channel of the event

The output must be in Standard HTML format using tags for Headers, bold and italics.  Do not start or end with ```html.