Salesforce Unified Activity Timeline

Salesforce stores activities across two objects: Tasks (calls, emails, to-dos) and Events (meetings, appointments). A unified activity timeline combines both into a single chronological view using a UNION pattern and polymorphic resolution to identify the entities each activity relates to.

Why Tasks and Events Are Separate

In Salesforce’s data model, Tasks and Events represent different activity types with different schemas:

• Tasks have ActivityDate (a date, not a timestamp), Status (Open, Completed, etc.), and Priority. They represent discrete actions: log a call, send an email, create a to-do.
• Events have StartDateTime and EndDateTime (timestamps), Location, and IsAllDayEvent. They represent time-bounded activities: meetings, demos, on-site visits.

Both objects share the polymorphic fields WhoId (person reference) and WhatId (object reference), plus common fields like Subject, OwnerId, and Description.

The UNION Pattern

Combining them requires mapping to a consistent column set. The key decision is how to normalize the date fields — Tasks have a date, Events have a datetime.

-- int__salesforce_unified_activity.sql
SELECT
    id              AS activity__id,
    'task'          AS activity__source,
    subject         AS activity__subject,
    activity_date   AS activity__occurred_at,
    who_id          AS activity__who_id,
    what_id         AS activity__what_id,
    owner_id        AS activity__owner_id
FROM {{ ref('base__salesforce__task') }}

UNION ALL

SELECT
    id              AS activity__id,
    'event'         AS activity__source,
    subject         AS activity__subject,
    start_date_time AS activity__occurred_at,
    who_id          AS activity__who_id,
    what_id         AS activity__what_id,
    owner_id        AS activity__owner_id
FROM {{ ref('base__salesforce__event') }}

The activity__source column preserves the original object type so downstream consumers can filter by it when needed (“show me only meetings” = WHERE activity__source = 'event').

A few details worth noting:

• activity_date vs. start_date_time — Tasks use a DATE field, Events use a TIMESTAMP. The UNION coerces the Task date to a timestamp (midnight). If precision matters, add CAST(activity_date AS TIMESTAMP) explicitly to be clear about the conversion.
• UNION ALL, not UNION — there’s no reason to deduplicate here. Tasks and Events have globally unique IDs within each object, and no Task ID will match an Event ID (they have different prefix patterns).
• Column alignment — only include columns that exist on both objects. Task-specific fields like Status or Event-specific fields like EndDateTime and Location can be added as NULLable columns if needed, but keep the core UNION clean.

Layering Polymorphic Resolution

The UNION model above gives you a unified activity list, but activity__who_id and activity__what_id are still opaque Salesforce IDs. Layer the polymorphic resolution pattern on top to get human-readable entity references:

-- int__salesforce_activity_enriched.sql
WITH

activities AS (
    SELECT * FROM {{ ref('int__salesforce_unified_activity') }}
),

contacts AS (
    SELECT contact__id, contact__full_name, contact__email
    FROM {{ ref('base__salesforce__contact') }}
),

leads AS (
    SELECT lead__id, lead__full_name, lead__email
    FROM {{ ref('base__salesforce__lead') }}
),

accounts AS (
    SELECT account__id, account__name
    FROM {{ ref('base__salesforce__account') }}
),

opportunities AS (
    SELECT opportunity__id, opportunity__name, opportunity__stage
    FROM {{ ref('base__salesforce__opportunity') }}
)

SELECT
    activities.activity__id,
    activities.activity__source,
    activities.activity__subject,
    activities.activity__occurred_at,
    activities.activity__owner_id,

    -- Who resolution
    CASE LEFT(activities.activity__who_id, 3)
        WHEN '003' THEN 'Contact'
        WHEN '00Q' THEN 'Lead'
    END AS activity__who_type,
    COALESCE(
        contacts.contact__full_name,
        leads.lead__full_name
    ) AS activity__who_name,
    COALESCE(
        contacts.contact__email,
        leads.lead__email
    ) AS activity__who_email,

    -- What resolution
    CASE LEFT(activities.activity__what_id, 3)
        WHEN '001' THEN 'Account'
        WHEN '006' THEN 'Opportunity'
    END AS activity__what_type,
    COALESCE(
        accounts.account__name,
        opportunities.opportunity__name
    ) AS activity__what_name
FROM activities
LEFT JOIN contacts
    ON LEFT(activities.activity__who_id, 3) = '003'
    AND activities.activity__who_id = contacts.contact__id
LEFT JOIN leads
    ON LEFT(activities.activity__who_id, 3) = '00Q'
    AND activities.activity__who_id = leads.lead__id
LEFT JOIN accounts
    ON LEFT(activities.activity__what_id, 3) = '001'
    AND activities.activity__what_id = accounts.account__id
LEFT JOIN opportunities
    ON LEFT(activities.activity__what_id, 3) = '006'
    AND activities.activity__what_id = opportunities.opportunity__id

The output is a complete activity timeline with resolved entity references: you know who the activity was with (contact name and email or lead name and email) and what it was about (account name or opportunity name and stage).

Downstream Use Cases

Rep Activity Dashboard

Group by activity__owner_id and activity__source to see activity volume by rep and type:

SELECT
    activity__owner_id,
    activity__source,
    COUNT(*) AS activity_count,
    COUNT(DISTINCT DATE(activity__occurred_at)) AS active_days
FROM {{ ref('int__salesforce_activity_enriched') }}
WHERE activity__occurred_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2

This enables per-rep activity volume breakdowns by type and correlation with pipeline progression.

Account Engagement Score

Count activities per account over a rolling window for an engagement signal:

SELECT
    activity__what_name AS account__name,
    COUNT(*) AS activities_30d,
    COUNT(DISTINCT activity__owner_id) AS reps_engaged,
    MAX(activity__occurred_at) AS last_activity_at
FROM {{ ref('int__salesforce_activity_enriched') }}
WHERE activity__what_type = 'Account'
    AND activity__occurred_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1

The output surfaces accounts with no recent activity and accounts with multiple reps engaged.

Opportunity Activity Timeline

Filter to a specific opportunity to see the full activity history:

SELECT
    activity__occurred_at,
    activity__source,
    activity__subject,
    activity__who_name,
    activity__who_type
FROM {{ ref('int__salesforce_activity_enriched') }}
WHERE activity__what_type = 'Opportunity'
    AND activities.activity__what_id = '006...'
ORDER BY activity__occurred_at

This replicates the activity timeline view that CRM UIs provide natively, for use in BI tools.

Materialization

Materialize the UNION model as a view or ephemeral model — it’s a simple UNION with no computation. Materialize the enriched model (with polymorphic joins) as a table, since it involves multiple LEFT JOINs across large tables.

For very active Salesforce orgs (50K+ activities per month), consider incremental materialization on activity__occurred_at. The UNION remains a view, and the enriched model becomes incremental, only processing new activities since the last run.

The HubSpot Contrast

HubSpot also tracks engagements (calls, emails, meetings, notes, tasks), but stores them in a single engagement object with a type field rather than splitting them into separate objects. This means HubSpot doesn’t need the UNION step — the data is already unified in the source. However, HubSpot engagements use the association model to link to contacts and deals, which introduces its own complexity at the join layer.

Both sources produce a unified timeline with resolved entity references, through different join patterns.