Weld logo
salesforce logo
Salesforce

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.
1with
2    timeseries as (
3        select
4            day as date_day
5        from
6            unnest (
7                generate_date_array(date('2020-01-01'), '2099-12-31')
8            ) as day
9    )
10  , task as (
11        select
12            date_trunc(activity_date, day) as activity_date
13          , count(id) as tasks_completed
14        from
15            {{raw.salesforce.task}}
16        group by
17            1
18    )
19  , salesforce_event as (
20        select
21            coalesce(
22                date_trunc(activity_date, day)
23              , date_trunc(activity_date_time, day)
24            ) as activity_date
25          , count(id) as events_completed
26        from
27            {{raw.salesforce.event}}
28        group by
29            1
30    )
31  , salesforce_lead as (
32        select
33            date_trunc(created_date, day) as created_date
34          , count(id) as leads_created
35        from
36            {{raw.salesforce.lead}}
37        group by
38            1
39    )
40  , salesforce_converted_lead as (
41        select
42            date_trunc(converted_date, day) as converted_date
43          , count(id) as leads_converted
44        from
45            {{raw.salesforce.lead}}
46        where
47            is_converted
48        group by
49            1
50    )
51  , opportunity as (
52        select
53            opportunity_id_c
54          , date_trunc(created_date, day) as created_date
55          , account_id
56          , date_trunc(close_date, day) as close_date
57          , is_closed
58          , is_deleted
59          , is_won
60          , owner_id
61          , stage_name
62          , type
63          , amount
64          , case
65                when is_won then 'Won'
66                when not is_won
67                and is_closed then 'Lost'
68                when not is_closed
69                and lower(forecast_category) in ('pipeline', 'forecast', 'bestcase') then 'Pipeline'
70                else 'Other'
71            end as status
72        from
73            {{raw.salesforce.opportunity}}
74    )
75  , opportunities_created as (
76        select
77            created_date
78          , count(opportunity_id_c) as opportunities_created
79          , round(sum(amount)) as opportunities_created_amount
80        from
81            opportunity
82        group by
83            1
84    )
85  , opportunities_closed as (
86        select
87            close_date
88          , count(
89                case
90                    when status = 'Won' then opportunity_id_c
91                    else null
92                end
93            ) as opportunities_won
94          , round(
95                sum(
96                    case
97                        when status = 'Won' then amount
98                        else 0
99                    end
100                )
101            ) as opportunities_won_amount
102          , count(
103                case
104                    when status = 'Lost' then opportunity_id_c
105                    else null
106                end
107            ) as opportunities_lost
108          , round(
109                sum(
110                    case
111                        when status = 'Lost' then amount
112                        else null
113                    end
114                )
115            ) as opportunities_lost_amount
116          , round(
117                sum(
118                    case
119                        when status = 'Pipeline' then amount
120                        else null
121                    end
122                )
123            ) as pipeline_amount
124        from
125            opportunity
126        group by
127            1
128    )
129select
130    timeseries.date_day
131  , salesforce_lead.leads_created
132  , salesforce_converted_lead.leads_converted
133  , task.tasks_completed
134  , salesforce_event.events_completed
135  , opportunities_created.opportunities_created
136  , opportunities_created.opportunities_created_amount
137  , opportunities_closed.opportunities_won
138  , opportunities_closed.opportunities_won_amount
139  , opportunities_closed.opportunities_lost
140  , opportunities_closed.opportunities_lost_amount
141  , opportunities_closed.pipeline_amount
142from
143    timeseries
144    left join salesforce_lead on timeseries.date_day = cast(salesforce_lead.created_date as date)
145    left join salesforce_converted_lead on timeseries.date_day = cast(salesforce_converted_lead.converted_date as date)
146    left join task on timeseries.date_day = cast(task.activity_date as date)
147    left join salesforce_event on timeseries.date_day = cast(salesforce_event.activity_date as date)
148    left join opportunities_created on timeseries.date_day = cast(opportunities_created.created_date as date)
149    left join opportunities_closed on timeseries.date_day = cast(opportunities_closed.close_date as date)
Example of output from model:
+------------+--------------+----------------+----------------+------------------+---------------------+---------------------------+-------------------+------------------------+---------------------+------------------------+----------------+
|  date_day  | leads_created | leads_converted | tasks_completed | events_completed | opportunities_created | opportunities_created_amount | opportunities_won | opportunities_won_amount | opportunities_lost | opportunities_lost_amount | pipeline_amount |
+------------+--------------+----------------+----------------+------------------+---------------------+---------------------------+-------------------+------------------------+---------------------+------------------------+----------------+
| 2023-04-20 |     50       |       30       |       40       |        60        |          20          |             15000           |        10         |          8000          |          5          |          4000          |      7000     |
| 2023-04-19 |     45       |       29       |       38       |        58        |          19          |             14000           |        9          |          7500          |          4          |          3500          |      6500     |
| 2023-04-18 |     48       |       28       |       37       |        57        |          18          |             13500           |        8          |          7100          |          3          |          3300          |      6100     |
| 2023-04-17 |     47       |       27       |       36       |        56        |          17          |             13000           |        7          |          6800          |          2          |          3100          |      5700     |
| 2023-04-16 |     46       |       26       |       35       |        55        |          16          |             12500           |        6          |          6500          |          1          |          2900          |      5400     |
| 2023-04-15 |     44       |       25       |       34       |        54        |          15          |             12000           |        5          |          6200          |          0          |          2700          |      5000     |
| 2023-04-14 |     43       |       24       |       33       |        53        |          14          |             11500           |        4          |          5900          |          0          |          2500          |      4700     |

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.

Ready to start modeling your own salesforce data?

Get started building your data warehouse with salesforce and 100+ more apps and databases available.

salesforce logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.