Aggregated Daily Salesforce Report

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)