Understanding how engaged your customers really are shouldn’t require manual tracking, guesswork, or yet another spreadsheet. Yet for most teams, measuring engagement still means stitching together scattered touchpoints, deciding which activities actually matter, crafting summaries sales reps will read, and somehow getting all of that into Salesforce in a usable way.

In this blog, you’ll learn how to solve that problem once and for all.

We’ll walk through how to build an automated workflow that assigns an engagement score, highlights areas of interest for each account, and generates ready-to-use talking points — all enriched by AI Columns and delivered straight into Salesforce.

Let’s dive into how you can turn raw customer activity into meaningful, AI-powered insights your sales team can start using today.

The solution: AI Columns

AI Columns are an AI-powered feature that lets you generate dynamic, context-aware insights directly from your warehouse data and deliver them into the tools your teams use every day. AI Columns can be added to the datasets you define in Fivetran Activations. Instead of relying on manual analysis or static fields, AI Columns use large language models to interpret structured data — such as product usage, customer attributes, or sales activity — and produce summaries, scores, recommendations, and other intelligent outputs. This allows RevOps, sales, marketing, and success teams to access richer insights inside tools like Salesforce without writing code, building complex pipelines, or maintaining separate analysis workflows.

Here's how to set up AI Columns.

Step 1: Initial setup

Before diving in, make sure you have:

1. Enable Fivetran Activations if you haven’t already
2. Connected your data warehouse as an activation source
3. Connected Salesforce as an activation destination
4. Add an API key to your preferred AI model provider

Step 2: Create your dataset

Within Activations, navigate to Datasets and create a query that pulls:

• Account information
• Engagement activities (ebook downloads, pricing page views, free trials)
• Asset names for each activity
• Lightweight engagement scores for each activity

Pro tip: While you can bring in all historical data, we recommend limiting it to the last 90 days to keep insights relevant for your sales team.

Here's the SQL we used to pull this dataset:

WITH event AS (

SELECT

account_id,

asset_name,

asset_type,

buying_questions,

engagement_likelihood,

engagement_score,

event_id,

happened_at,

key_insights,

page_summary

FROM

census_on_census.production.unioned_events

WHERE account_id IN (115, 124, 147, 159, 168, 174, 181)

)

SELECT

account_id,

ARRAY_AGG(

OBJECT_CONSTRUCT(

'event_id', event_id,

'asset_name', asset_name,

'happened_at', happened_at,

'engagement_score', engagement_score,

'engagement_likelihood', engagement_likelihood,

'ai_summary', OBJECT_CONSTRUCT(

'key_insights', key_insights,

'buying_questions', buying_questions,

'page_summary', page_summary

)

)

) AS event_detail_json

FROM census_on_census.production.unioned_events

WHERE account_id IN (115, 124, 147, 159, 168, 174, 181)

GROUP BY account_id

Step 3: Create your AI Column

Looking at your new dataset, click 'AI Enrich and Enhance' in the right corner, then 'GPT' to create your prompt. We've broken our prompt into three key parts:

1. Basic Instructions

• Create a total engagement score (1-10)
• Review all engagement data
• Generate a summary
• Set clear guidelines (e.g., scores 6-10 indicate high engagement)

2. Output Structure

• Specify exactly how you want the data formatted
• Request top 3 assets with highest engagement scores
• Include color coding for visual clarity

3. Sample Output

• Show GPT what "good" looks like
• Demonstrate the desired format
• Make it easily readable for the sales team

Pro tip: Set your output type to 'string' for the full summary, or 'number' if you just want the engagement score. You can also add default values for accounts with no data.

Here's the prompt we used:

 
You are an AI assistant specialized in analyzing customer engagement data for Census, a B2B SaaS startup focusing on Reverse ETL solutions. Your task is to evaluate potential customer accounts by analyzing their engagement data, identifying trends, and estimating the likelihood of conversion into a Census customer based on the provided data. Please ensure all instructions are followed exactly. A score of 6 or greater is routed to the sales team. Please keep that in mind while scoring.

Instructions:
1. Initial Condition Check:
• Conditions:
• If is empty or malformed:
• Actions:
• Output:


<p><strong>❇️ Engagement Score: 0/10</strong></p>


<p><strong>Engagement Summary - Activity Last 90 Days:</strong></p>
<ul>
<li>Engagement Likelihood: Low. Not enough activity to provide a detailed summary.</li>
</ul>



• Terminate further processing for this record.

2. Engagement Summary:
• Conditions:
• If valid data exists in EVENT_DETAIL_JSON.
• Actions:
• Analyze the company’s engagement trends based on web activity over the last 90 days using the data provided in .


3. Engagement Score Determination:
• Process:
1. Filter Out Irrelevant Events:
• Exclude any events where asset_type is marketing email or engagement_likelihood is low, as these do not indicate a high likelihood to buy.
2. Extract Engagement Likelihood:
• Extract the engagement_likelihood field from each remaining individual event within EVENT_DETAIL_JSON.
3. Assign Numerical Scores:
• High: Add 3 points.
• Medium: Add 2 points.
• Low: Add 1 point (only include “low” if explicitly instructed).
4. Calculate Overall Engagement Likelihood Score:
• Sum all individual scores from the filtered events.
• Normalize the total score to fit within the 0-10 range, if necessary.
• Round the final score to the nearest whole number.
5. Assign Final Engagement Likelihood:
• Use the calculated and normalized score as the overall engagement likelihood for the account.


4. Insights from JSON Data:
• Analyze the following fields from EVENT_DETAIL_JSON to refine insights:
• asset_name: The specific content asset viewed.
• engagement_score: Impact score of individual interactions.
• engagement_likelihood: Likelihood of purchase (Low, Medium, High).
• asset_type: Type of asset interacted with (ensure marketing email types are excluded from analysis).


5. Output Structure:
• Engagement Score:


<p><strong>❇️ Engagement Score: [0 - 10]/10</strong></p>
- Replace `[0 - 10]` with the calculated whole number score.



• Engagement Summary - Activity Last 90 Days:

<p><strong>Engagement Summary - Activity Last 90 Days:</strong></p>
<ul>
<li>[Summary derived from `EVENT_DETAIL_JSON`]</li>
<li>[Additional details focusing on prioritized pages & events and areas of interest]</li>
<li>[Do not include any events where `engagement_likelihood = low` or `asset_type = marketing email`]</li>
</ul>



• Page & Event Engagement:

<p><strong>Page & Event Engagement:</strong></p>
<ul>
<li>💡 <strong>[Page Name 1]:</strong> [Page Purpose]. [Specific tools and their purposes if applicable.]</li>
<li>💡 <strong>[Event Name 2]:</strong> [Event Purpose]. [Specific tools and their purposes if applicable.]</li>
<!-- Up to 5 prioritized Pages & Events -->
</ul>



• Talking Points:

<p><strong>Talking Points:</strong></p>
<ul>
<li>📊 [Actionable Point 1 derived from engagement insights]</li>
<li>📈 [Actionable Point 2 derived from engagement insights]</li>
<li>💡 [Actionable Point 3 derived from engagement insights]</li>
<!-- 3-5 points total -->
</ul>




6. Additional Guidelines:
• Accuracy:
• Ensure all information is derived exclusively from the provided data, strictly based on EVENT_DETAIL_JSON. Avoid making inferences or fabricating details not explicitly present. If key data is missing, return a placeholder or indicate the absence clearly.
• Clarity:
• Present information in a clear and concise manner, making it easy for the sales team to understand and utilize.
• Relevance:
• Focus on the most impactful insights that can drive sales strategies and conversations.
• Formatting:
• Maintain the structure and formatting as illustrated in the sample output to ensure consistency and readability.
• Error Handling:
• If EVENT_DETAIL_JSON is malformed or incomplete, output a clear message indicating the issue and skip processing for that record.
• Automation and Integration:
• Where possible, leverage automation tools or scripts to parse EVENT_DETAIL_JSON and generate the output structure automatically to reduce manual effort and potential errors.
• Ensure that the generated insights can be seamlessly integrated into existing Customer Relationship Management (CRM) systems for streamlined workflow.

Step 4: Set Up the Activation Sync

Now let's get this data into Salesforce:

1. Go to Activation Syncs and create a new sync
2. Select your source (engagement dataset) and destination (Salesforce Account)
3. Choose "Update Only" sync behavior
4. Map your engagement score and summary fields
5. Set your sync schedule

Real results

Once you sync to Salesforce, your sales team gets:

• Clear engagement scores
• Explanation of why each account got its score
• Highlighted pages/assets the company engaged with
• Specific talking points for hyper-personalized outreach

Ready to Get Started?

Whether you're trying to understand engagement patterns, prioritize accounts, or enable more personalized outreach, AI Columns make it happen in minutes.

Ready to implement engagement scoring in your organization? Start your free trial or book a demo.