Catconnect/Marketing.sql at main · ssocite/Catconnect · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
--- Employment - Primary

with employ as (select distinct
c.UCINN_ASCENDV2__RELATED_CONTACT_DONOR_ID_FORMULA__C,
max (c.ap_is_primary_employment__c) keep (dense_rank First Order by c.ucinn_ascendv2__start_date__c desc) as primary_employ_ind,
max (c.ucinn_ascendv2__job_title__c) keep (dense_rank first order by c.ucinn_ascendv2__start_date__c desc) as primary_job_title,
max (c.UCINN_ASCENDV2__RELATED_ACCOUNT_NAME_FORMULA__C) keep (dense_rank first order by c.ucinn_ascendv2__start_date__c desc) as primary_employer
from stg_alumni.ucinn_ascendv2__Affiliation__c c
where c.ap_is_primary_employment__c = 'true'
group by c.UCINN_ASCENDV2__RELATED_CONTACT_DONOR_ID_FORMULA__C),


--- Special Handling

SH as (select  s.donor_id,
       s.no_contact,
       s.no_email_ind
from mv_special_handling s),

--- TP

TP as (select C.CONSTITUENT_DONOR_ID,
c.constituent_university_overall_rating,
c.constituent_research_evaluation
from DM_ALUMNI.DIM_CONSTITUENT C ),


--- Assignment

assign as (Select a.household_id,
       a.donor_id,
       a.sort_name,
       a.prospect_manager_name,
       a.lagm_user_id,
       a.lagm_name
From mv_assignments a),

--- Giving Summary

give as (select g.household_id,
g.household_primary_donor_id,
       g.ngc_lifetime,
       g.ngc_cfy,
       g.ngc_pfy1,
       g.ngc_pfy2,
       g.ngc_pfy3,
       g.ngc_pfy4,
       g.ngc_pfy5,
       g.last_ngc_tx_id,
       g.last_ngc_date,
       g.last_ngc_opportunity_type,
       g.last_ngc_designation_id,
       g.last_ngc_designation,
       g.last_ngc_recognition_credit
from mv_ksm_giving_summary g),

--- email

email as (select  c.ucinn_ascendv2__donor_id__c,
c.email
from stg_alumni.contact c),

address as (select a.donor_id,
       a.sort_name,
       a.linkedin_url,
       a.primary_geocodes_concat,
       a.address_preferred_type,
       a.preferred_address_line_1,
       a.preferred_address_line_2,
       a.preferred_address_line_3,
       a.preferred_address_line_4,
       a.preferred_address_city,
       a.preferred_address_state,
       a.preferred_address_postal_code,
       a.preferred_address_country,
       a.preferred_geocode_primary,
       a.business_address_city,
       a.business_address_state,
       a.business_address_postal_code,
       a.business_address_country
from mv_entity_contact_info a)

--- Final Query

select distinct
       e.donor_id,
       e.sort_name,
       e.first_name,
       e.last_name,
       e.primary_record_type,
       e.institutional_suffix,
       address.primary_geocodes_concat,
       address.address_preferred_type,
       address.preferred_address_city,
       address.preferred_address_state,
       address.preferred_address_postal_code,
       address.preferred_geocode_primary,
       address.business_address_city,
       address.business_address_state,
       address.business_address_postal_code,
       address.business_address_country,
       d.first_ksm_year,
       d.program,
       d.program_group,
       employ.primary_employer,
       employ.primary_job_title,
       c.INDUSTRIES,
       c.industry_subsectors,
       address.linkedin_url,
       a.prospect_manager_name,
       a.lagm_name,
      case when sh.no_email_ind is null then email.email else 'No Email' end as email,
       sh.no_contact,
       sh.no_email_ind,
        give.ngc_lifetime
from mv_entity e
left join DM_ALUMNI.Dim_Constituent c on c.constituent_donor_id = e.donor_id
--- ksm degree information
left join mv_entity_ksm_degrees d on d.donor_id = e.donor_id
--- address
inner join address on address.donor_id = e.donor_id
--- employment
left join employ on employ.UCINN_ASCENDV2__RELATED_CONTACT_DONOR_ID_FORMULA__C = e.donor_id
--- special handling
left join sh on sh.donor_id = e.donor_id
--- Top Prospect
left join TP on TP.CONSTITUENT_DONOR_ID = e.donor_id
--- assignment
left join assign a on a.donor_id = e.donor_id
--- Give
left join give on give.household_primary_donor_id = e.donor_id
--- email
left join email on email.ucinn_ascendv2__donor_id__c = e.donor_id
where
--- KSM Alumni in Chicago, 2015-2020 exclude Exec Ed
(d.program not like '%STUDENT%'
and sh.no_contact is null
and sh.no_email_ind is null
and e.primary_record_type = 'Alum'
and d.program != 'EXECED')


--- Wants folks with Product in Title and/or in Tech or HR

and (c.industry_subsectors like '%Advertising%'
or c.industry_subsectors like '%Marketing%'

)


--- Remove No Contacts
order by e.sort_name asc