Weld
Start free
Salesforce model

Aggregated Daily Salesforce Report

Extracts a day-by-day breakdown of CRM activities on Salesforce, covering tasks, events, lead generation, and opportunities. It integrates various data points to deliver insights such as leads created, tasks completed, events held, and opportunity metrics.

See the SQLAll Salesforce models
Grain
day
Built from
  1. 4
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Salesforce
eventleadopportunitytask
Ships as
Weld and dbt SQL, open source on GitHub
core.salesforce.aggregated_daily_sales_report
1-- core.salesforce.aggregated_daily_sales_report
2-- One row per calendar day with the day's CRM activity: leads in, leads
3-- converted, closed tasks, events, and opportunities opened, won and lost.
4--
5-- Grain: day. The calendar runs from the first day with any activity to today,
6-- or further if open pipeline is expected to close later, so quiet days show
7-- up as zeros instead of going missing.
8--
9-- Each metric is dated on its own event: leads on created date, conversions on
10-- converted date, tasks on their activity (due) date, events on their start,
11-- new opportunities on created date, won/lost/open pipeline on close date.
12-- Days are UTC; Weld lands Salesforce date and datetime fields as timestamps.
13-- Deleted records (is_deleted) are left out. Amounts are in the org's
14-- currency as Salesforce stores them; multi-currency orgs are not converted.
15--
16-- Depends on: raw.salesforce.lead, task, event, opportunity
17--
18-- From the weld.app template library, rewritten and corrected. Not reconciled
19-- against Salesforce; check the output against the source before relying on it.
20
21WITH lead_days AS (
22    SELECT DATE(created_date) AS day, 'lead_created' AS metric, CAST(NULL AS FLOAT64) AS value
23    FROM {{raw.salesforce.lead}}
24    WHERE NOT COALESCE(is_deleted, FALSE)
25
26    UNION ALL
27
28    SELECT DATE(converted_date), 'lead_converted', NULL
29    FROM {{raw.salesforce.lead}}
30    WHERE NOT COALESCE(is_deleted, FALSE)
31      AND is_converted
32),
33
34activity_days AS (
35    -- A task counts once it is closed, on its activity date. Tasks with no
36    -- activity date have no day to land on and are left out.
37    SELECT DATE(activity_date) AS day, 'task_completed' AS metric, CAST(NULL AS FLOAT64) AS value
38    FROM {{raw.salesforce.task}}
39    WHERE NOT COALESCE(is_deleted, FALSE)
40      AND is_closed

This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

This SQL model offers a granular daily report of CRM activity on Salesforce. It integrates insights from tasks, events, leads, and opportunities to provide a holistic view of the business pipeline. The model captures metrics like tasks completed, events organized, leads generated and converted, opportunities created and closed, and the respective amounts associated with those opportunities.

Raw tables it reads

  • raw.salesforce.event
  • raw.salesforce.lead
  • raw.salesforce.opportunity
  • raw.salesforce.task

Put Salesforce in front
of your agents.

Connect Salesforce, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us