-
Notifications
You must be signed in to change notification settings - Fork 0
/
extract.py
316 lines (294 loc) · 14.3 KB
/
extract.py
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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
from functional_functions import get_snowflake_connection, query_snowflake
from datetime import datetime as dt
def get_acct_transaction_data(begin_date, end_date):
print('Running now ' + str(dt.now()))
full_query = query_snowflake("""select tb1.Market_Region,
tb1.Side_Represented,
tb1.Compass_Deals_Id,
tb1.Listing_Type,
Location_Name,
Department_Name,
tb1.Compass_Team_ID,
tb1.Close_Date,
tb1.Ending_Period,
tb1.Final_Close_Price,
tb1.Total_GCI,
(sum(tb1.total_referral) + sum(tb1.Total_GCI)) as GCI_Less_Referral,
tb1.commission_expense,
(sum(tb1.total_referral) + sum(tb1.Total_GCI) + sum(tb1.commission_expense)) as Company_Dollar,
tb1.AEP,
(sum(tb1.total_referral) + sum(tb1.Total_GCI) + sum(tb1.commission_expense)) + sum(tb1.AEP) as Company_Dollar_Plus_AEP,
tb1.Gross_Commission_Revenue__Condo_coop_Sales_400001,
tb1.Gross_Commission_Revenue__House_Single_Family_400002,
tb1.Gross_Commission_Revenue__Multi_Family_400003,
tb1.Gross_Commission_Revenue__Townhouse_Single_Residence_400004,
tb1.Gross_Commission_Revenue__Residential_Rentals_400005,
tb1.Gross_Commission_Revenue__Commercial_Sale_400006,
tb1.Gross_Commission_Revenue__Commercial_Lease_400007,
tb1.Gross_Commission_Revenue__Other_Property_GCI_400009,
tb1.Gross_Commission_Revenue__Credits_Discounts_from_Commission_401001,
tb1.Referral_Revenue__External_Referral_Revenue_410001,
tb1.Referral_Revenue__Internal_Referral_Revenue_411001,
tb1.Ancillary_Revenue__Admin__Resource_Fees_430003,
tb1.Commissions_and_other_transaction_related_costs_COGS__Commission_Expense_502001,
tb1.Commissions_and_other_transaction_related_costs_COGS__Commission_Expense__Agent_Equity_Program_502006,
tb1.Commissions_and_other_transaction_related_costs_COGS__ProductionBased_Bonus_503001,
tb1.Referral_Expense_COGS__Internal_Referral_Expense_510002,
tb1.Referral_Expense_COGS__Referral_Expense___Compass_Agent_Compass_Deal_510003,
tb1.Referral_Expense_COGS__Referral_Expense__External_Brokerage_510004,
tb1.Referral_Expense_COGS__Internal_Referral__Subsidiaries_510005,
tb1.Referral_Expense_COGS__Referral_Expense__Compass_Agent_External_Deal_510006,
tb1.Gross_Commission_Revenue__Gross_Commission_Income__All_Other_400090,
tb1.Gross_Commission_Revenue__Commission_RevenueNYC_400097,
tb1.Gross_Commission_Revenue__Commission_RevenueDC_400098,
tb1.Gross_Commission_Revenue__Commission_RevenueMIA_400099,
tb1.Gross_Commission_Revenue__RefundsAllowances_401002,
tb1.Gross_Commission_Revenue__Client_Pass_Through_401003,
tb1.Gross_Commission_Revenue__Renewals_401004,
Ancillary_Revenue__Ancillary_Revenue_430001,
Ancillary_Revenue__Contract_to_Close_430002,
Ancillary_Revenue__E_and_O_Billing_Income_430006,
Ancillary_Revenue__Discounted_Vendor_Service_Program_430009,
Ancillary_Revenue__Flat_Fees_430011,
Vendor_Salesforce_Agent_ID,
Company_Salesforce_Agent_ID
From
(select CASE WHEN a.Market_Region='Texas' THEN SPLIT_PART(a.Location_Name,',',0) ELSE a.Market_Region END as Market_Region,
a.Side_Represented,
CAST(CAST(COALESCE(a.Compass_Deals_Id,a.Compass_Deal_Id_Line_Id) as INTEGER) as STRING) as Compass_Deals_Id,
a.Listing_Type,
a.Location_Name,
a.Department_Name,
a.Compass_Team_ID,
a.Vendor_Salesforce_Agent_ID,
a.Company_Salesforce_Agent_ID,
a.Close_Date,
a.Ending_Period,
a.Final_Close_Price,
sum(case when a.account_number in('400001',
'400002',
'400003',
'400004',
'400005',
'400006',
'400007',
'400009',
'400090',
'400097',
'400098',
'400099',
'401001',
'401002',
'401003',
'401004',
'410001',
'411001')then a.amount else 0 End) as Total_GCI,
sum(case when a.account_number in ('510004','510002','510005') then a.amount else 0 end) as total_referral,
sum(case when a.account_number in ('502001','510003','510006') then a.amount else 0 end) as commission_expense,
sum(case when a.account_number = '502006' then a.amount else 0 end) as AEP,
Sum(Case When a.account_number = '400001' Then a.amount Else 0 End) as Gross_Commission_Revenue__Condo_coop_Sales_400001,
Sum(Case When a.account_number = '400002' Then a.amount Else 0 End) as Gross_Commission_Revenue__House_Single_Family_400002,
Sum(Case When a.account_number = '400003' Then a.amount Else 0 End) as Gross_Commission_Revenue__Multi_Family_400003,
Sum(Case When a.account_number = '400004' Then a.amount Else 0 End) as Gross_Commission_Revenue__Townhouse_Single_Residence_400004,
Sum(Case When a.account_number = '400005' Then a.amount Else 0 End) as Gross_Commission_Revenue__Residential_Rentals_400005,
Sum(Case When a.account_number = '400006' Then a.amount Else 0 End) as Gross_Commission_Revenue__Commercial_Sale_400006,
Sum(Case When a.account_number = '400007' Then a.amount Else 0 End) as Gross_Commission_Revenue__Commercial_Lease_400007,
Sum(Case When a.account_number = '400009' Then a.amount Else 0 End) as Gross_Commission_Revenue__Other_Property_GCI_400009,
Sum(Case When a.account_number = '401001' Then a.amount Else 0 End) as Gross_Commission_Revenue__Credits_Discounts_from_Commission_401001,
Sum(Case When a.account_number = '410001' Then a.amount Else 0 End) as Referral_Revenue__External_Referral_Revenue_410001,
Sum(Case When a.account_number = '411001' Then a.amount Else 0 End) as Referral_Revenue__Internal_Referral_Revenue_411001,
Sum(Case When a.account_number = '502001' Then a.amount Else 0 End) as Commissions_and_other_transaction_related_costs_COGS__Commission_Expense_502001,
Sum(Case When a.account_number = '502006' Then a.amount Else 0 End) as Commissions_and_other_transaction_related_costs_COGS__Commission_Expense__Agent_Equity_Program_502006,
Sum(Case When a.account_number = '503001' Then a.amount Else 0 End) as Commissions_and_other_transaction_related_costs_COGS__ProductionBased_Bonus_503001,
Sum(Case When a.account_number = '510002' Then a.amount Else 0 End) as Referral_Expense_COGS__Internal_Referral_Expense_510002,
Sum(Case When a.account_number = '510003' Then a.amount Else 0 End) as Referral_Expense_COGS__Referral_Expense___Compass_Agent_Compass_Deal_510003,
Sum(Case When a.account_number = '510004' Then a.amount Else 0 End) as Referral_Expense_COGS__Referral_Expense__External_Brokerage_510004,
Sum(Case When a.account_number = '510005' Then a.amount Else 0 End) as Referral_Expense_COGS__Internal_Referral__Subsidiaries_510005,
Sum(Case When a.account_number = '510006' Then a.amount Else 0 End) as Referral_Expense_COGS__Referral_Expense__Compass_Agent_External_Deal_510006,
Sum(Case When a.account_number = '400090' Then a.amount Else 0 End) as Gross_Commission_Revenue__Gross_Commission_Income__All_Other_400090,
Sum(Case When a.account_number = '400097' Then a.amount Else 0 End) as Gross_Commission_Revenue__Commission_RevenueNYC_400097,
Sum(Case When a.account_number = '400098' Then a.amount Else 0 End) as Gross_Commission_Revenue__Commission_RevenueDC_400098,
Sum(Case When a.account_number = '400099' Then a.amount Else 0 End) as Gross_Commission_Revenue__Commission_RevenueMIA_400099,
Sum(Case When a.account_number = '401002' Then a.amount Else 0 End) as Gross_Commission_Revenue__RefundsAllowances_401002,
Sum(Case When a.account_number = '401003' Then a.amount Else 0 End) as Gross_Commission_Revenue__Client_Pass_Through_401003,
Sum(Case When a.account_number = '401004' Then a.amount Else 0 End) as Gross_Commission_Revenue__Renewals_401004,
Sum(Case When a.account_number = '430001' Then a.amount Else 0 End) as Ancillary_Revenue__Ancillary_Revenue_430001,
Sum(Case When a.account_number = '430002' Then a.amount Else 0 End) as Ancillary_Revenue__Contract_to_Close_430002,
Sum(Case When a.account_number = '430003' Then a.amount Else 0 End) as Ancillary_Revenue__Admin__Resource_Fees_430003,
Sum(Case When a.account_number = '430006' Then a.amount Else 0 End) as Ancillary_Revenue__E_and_O_Billing_Income_430006,
Sum(Case When a.account_number = '430009' Then a.amount Else 0 End) as Ancillary_Revenue__Discounted_Vendor_Service_Program_430009,
Sum(Case When a.account_number = '430011' Then a.amount Else 0 End) as Ancillary_Revenue__Flat_Fees_430011
from "PC_STITCH_DB"."NETSUITE_ANALYTICS"."VIEW_ACCOUNTING_TRANSACTION_LINES" a
Where
a.Ending_Period between to_Date('{b}','mm-dd-yyyy') AND to_Date('{e}','mm-dd-yyyy')
group by 1,
a.Compass_Team_ID,
3,
a.Side_Represented,
a.Listing_Type,
a.Location_Name,
a.Department_Name,
a.Ending_Period,
a.Close_Date,
a.Final_Close_Price,
a.Vendor_Salesforce_Agent_ID,
a.Company_Salesforce_Agent_ID) tb1
Group by tb1.Market_Region,
tb1.Side_Represented,
tb1.Compass_Deals_Id,
tb1.Listing_Type,
Location_Name,
Department_Name,
tb1.Compass_Team_ID,
tb1.Close_Date,
tb1.Ending_Period,
tb1.Final_Close_Price,
tb1.Total_GCI,
tb1.commission_expense,
tb1.AEP,
tb1.Gross_Commission_Revenue__Condo_coop_Sales_400001,
tb1.Gross_Commission_Revenue__House_Single_Family_400002,
tb1.Gross_Commission_Revenue__Multi_Family_400003,
tb1.Gross_Commission_Revenue__Townhouse_Single_Residence_400004,
tb1.Gross_Commission_Revenue__Residential_Rentals_400005,
tb1.Gross_Commission_Revenue__Commercial_Sale_400006,
tb1.Gross_Commission_Revenue__Commercial_Lease_400007,
tb1.Gross_Commission_Revenue__Other_Property_GCI_400009,
tb1.Gross_Commission_Revenue__Credits_Discounts_from_Commission_401001,
tb1.Referral_Revenue__External_Referral_Revenue_410001,
tb1.Referral_Revenue__Internal_Referral_Revenue_411001,
tb1.Ancillary_Revenue__Admin__Resource_Fees_430003,
tb1.Commissions_and_other_transaction_related_costs_COGS__Commission_Expense_502001,
tb1.Commissions_and_other_transaction_related_costs_COGS__Commission_Expense__Agent_Equity_Program_502006,
tb1.Commissions_and_other_transaction_related_costs_COGS__ProductionBased_Bonus_503001,
tb1.Referral_Expense_COGS__Internal_Referral_Expense_510002,
tb1.Referral_Expense_COGS__Referral_Expense___Compass_Agent_Compass_Deal_510003,
tb1.Referral_Expense_COGS__Referral_Expense__External_Brokerage_510004,
tb1.Referral_Expense_COGS__Internal_Referral__Subsidiaries_510005,
tb1.Referral_Expense_COGS__Referral_Expense__Compass_Agent_External_Deal_510006,
tb1.Gross_Commission_Revenue__Gross_Commission_Income__All_Other_400090,
tb1.Gross_Commission_Revenue__Commission_RevenueNYC_400097,
tb1.Gross_Commission_Revenue__Commission_RevenueDC_400098,
tb1.Gross_Commission_Revenue__Commission_RevenueMIA_400099,
tb1.Gross_Commission_Revenue__RefundsAllowances_401002,
tb1.Gross_Commission_Revenue__Client_Pass_Through_401003,
tb1.Gross_Commission_Revenue__Renewals_401004,
Ancillary_Revenue__Ancillary_Revenue_430001,
Ancillary_Revenue__Contract_to_Close_430002,
Ancillary_Revenue__E_and_O_Billing_Income_430006,
Ancillary_Revenue__Discounted_Vendor_Service_Program_430009,
Ancillary_Revenue__Flat_Fees_430011,
Vendor_Salesforce_Agent_ID,
Company_Salesforce_Agent_ID;""".format(b=begin_date,e=end_date))
print('finish...' + str(dt.now()))
return full_query
def get_aep_data(begin_date):
'''
AEP used to be lumped in with overall commission expenses prior to aug 2020. This query
is intended to isolate and strip out the AEP as needed
'''
if begin_date > '2020-08-31':
begin_date = '2018-01-01'
print('Running now ' + str(dt.now()))
all_df = query_snowflake("""select
COALESCE(A.COMPASS_DEALS_ID, A.COMPASS_DEAL_ID_LINE_ID)::INT::VARCHAR as COMPASS_DEALS_ID,
A.COMPASS_TEAM_ID,
A.ENDING_PERIOD,
A.AMOUNT,
CASE WHEN A.MARKET_REGION='Texas' THEN SPLIT_PART(a.Location_Name,',',0) ELSE A.MARKET_REGION END as MARKET_REGION,
-- A.MARKET_REGION,
A.LOCATION_NAME,
A.SIDE_REPRESENTED,
A.LISTING_TYPE,
A.DEPARTMENT_NAME
from "PC_STITCH_DB"."NETSUITE_ANALYTICS"."VIEW_ACCOUNTING_TRANSACTION_LINES" A
WHERE
A.ENDING_PERIOD BETWEEN '{}' AND '2020-08-31'
AND A.COMPASS_ITEM_TYPE_NAME='AEP'
AND A.ACCOUNT_NUMBER=502001;""".format(begin_date))
print('finish...' + str(dt.now()))
return all_df
def get_admin_fees(begin_date):
'''
Flat Fees used to be lumped in overall admin resource fees and COGS prior to Jul 2020. This query
is intended to isolate and strip out the Flat fees as needed.
source: https://docs.google.com/spreadsheets/d/1h-uR1NuXJAAutquaLe75QRJ6dufPvO_pU90beSa0QCo/edit?ts=5f50e87b#gid=1588026256
'''
if begin_date > '2020-08-31':
begin_date = '2018-01-01'
print('Begin Admin Fee iso run...' + str(dt.now()))
af_df = query_snowflake("""SELECT
COALESCE(COMPASS_DEALS_ID, COMPASS_DEAL_ID_LINE_ID)::INT::VARCHAR as COMPASS_DEALS_ID,
COMPASS_TEAM_ID,
ENDING_PERIOD,
AMOUNT,
CASE WHEN MARKET_REGION='Texas' THEN SPLIT_PART(Location_Name,',',0) ELSE MARKET_REGION END as MARKET_REGION,
LOCATION_NAME,
SIDE_REPRESENTED,
LISTING_TYPE,
ACCOUNT_NUMBER,
ITEM_ID,
ITEM_NAME,
DEPARTMENT_NAME
FROM "PC_STITCH_DB"."NETSUITE_ANALYTICS"."VIEW_ACCOUNTING_TRANSACTION_LINES"
WHERE
ITEM_ID IN ('470','469','111','86','799')
AND ENDING_PERIOD BETWEEN '{}' AND '2020-07-31'
""".format(begin_date))
print('finito admin fee iso..' + str(dt.now()))
return af_df
def get_ica_data():
print('Running now ' + str(dt.now()))
ica_details = query_snowflake("""
SELECT
"Compass Agent ID",
"Compass Team ID",
"Account Team Name",
"Parent Account Name",
"Agent Name",
"Agent Role"
"Sales Manager Name",
"Market",
"SubMarket",
"Office",
"Contact Status",
"Original Start Date",
"Number of Agents",
"Current Contract Start Date",
"Current Contract End Date",
"ICA Expiration Date",
"Separation Date",
"Separated From Compass",
"Opportunity GCI",
"Total Split",
"Base Split",
"Resource Fee Type",
"Resource Fee Option" ,
"Resource Fee %",
"Resource Fee Cap",
"Actual Resource Fee % or #",
"Policy Resource Fee % or #",
"AMA ID",
"Salesforce Contact ID",
"Salesforce Account ID"
FROM
"PC_STITCH_DB"."ET_DEVELOPMENT"."VIEW_AGENT_ICA_HISTORY"
WHERE
"Agent Role" = 'Principal'
AND "Primary Principal Flag" = 'Y'
AND "Original Start Date" IS NOT NULL
AND "Parent Account Name" NOT IN ('Pacific Union','Pacific Union International','Paragon','Alain Pinel Realtors')
""")
print('finish...' + str(dt.now()))
return ica_details
def get_extracts(begin_date, end_date):
transaction_data = get_acct_transaction_data(begin_date, end_date)
aep_data = get_aep_data(begin_date)
ica_data = get_ica_data()
af_data = get_admin_fees(begin_date)
return {
'transaction_data':transaction_data,
'aep_data':aep_data,
'ica_data': ica_data,
'af_data': af_data
}