Please enable JavaScript.
Coggle requires JavaScript to display documents.
Lead Gen Referral Policy, Referral Performance Dashboard-2021:Rahul -…
Lead Gen Referral Policy
WorkBooks
Ashish Employee Referral Master
Sheets-3
1.Fresh Meeting Lead
Upsell Meeting Lead
Source: Comments
1.Referral Form Responses
Sheets-4
Referral Performance Dashboard-2021:Rahul
Sheets-14
5.Referral Lead Calling Sheet
sheet-Referral Lead- MC Fixing
Import-Referral Form Responses-WB-sheet-Referral Lead!A:K
Additional Columns Present L: W
Final Master Data-Cleanup
Copy of Hood HeadCount
Referral Performance Dashboard-2021:Rahul
Sheets-14
Final List to be Processing: comments: contains formulas
Columns-10
1.Email Address
=FILTER('Total Payout List'!A2:J,'Total Payout List'!J2:J>0)
empID
Status
4.Location
5.Role
6.Total Referral Lead
Lead eligible for Referral Amount
Referral Amount
9.Referral Amount Processed
10.
Balnace Referral Amount
Sheet12: copy of Final List to be Processing (completely filled)
Column missing:
Balnace Referral Amount
Policy
Total Payout List; comments: contains formulas
Columns-10
Email Address
=UNIQUE('Master Data'!B:B) ---> B - Email ID
empID
ARRAYFORMULA(IF(LEN(A2:A),IFNA(VLOOKUP(A2:A,{'HR Data'!L:L,'HR Data'!A:A},2,0)),))
3.Status - Active/ Inactive
ARRAYFORMULA(IF(LEN(B2:B),IFNA(VLOOKUP(B2:B,'HR Data'!A1:Z,3,0)),))
Location
ARRAYFORMULA(IF(LEN(B2:B),IFNA(VLOOKUP(B2:B,'HR Data'!A1:Z,6,0)),))
Role
ARRAYFORMULA(IF(LEN(B2:B),IFNA(VLOOKUP(B2:B,'HR Data'!A1:Z,26,0)),))
6.Total Referral Lead
ARRAYFORMULA(IF(LEN(A2:A),IFNA(VLOOKUP(A2:A,'Referral Payout List'!A:M,11,0)),))
Referral Pay-out List - Total- header - Total Lead
Lead eligible for Referral Amount
=ARRAYFORMULA(IF(LEN(A2:A),IFNA(VLOOKUP(A2:A,'Referral Payout List'!A:M,12,0)),))
Referral Pay-out List - Total- header - Lead Eligible for Payout
Referral Amount
ARRAYFORMULA(IF(LEN(A2:A),IFNA(VLOOKUP(A2:A,'Referral Payout List'!A:M,13,0)),))
Referral Pay-out List - Total- header - Referral Amount
9.
Referral Amount Processed
=SUMIFS('Referral Processed'!B:B,'Referral Processed'!A:A,A2)
Balnace Referral Amount:red_cross:
Balnace Referral Amount
Referral Amount (H1) - Referral Amount Processed(I2)
Referral Payout List:Comments: Contains formulas
Columns
Email Address
=UNIQUE('Master Data'!B2:B)
2.Referral Lead
3 columns
Total Lead
COUNTIFS('Referral Lead'!B:B,A3)
Lead Eligible for Payout
COUNTIFS('Referral Lead'!B:B,A3,'Referral Lead'!Q:Q,">"&0)
Referral Amount
SUMIFS('Referral Lead'!Q:Q,'Referral Lead'!B:B,A3,'Referral Lead'!Q:Q,">"&0)
Referral Lead'!Q -
Referral Amount
if(IF(and(P2="Yes",or(N2>=100,H2>=100)),IF(N2="",H2,N2),0)>250,250,IF(and(P2="Yes",or(N2>=100,H2>=100)),IF(N2="",H2,N2),0))
3.Fresh Meeting Lead
3 columns
Total Lead
COUNTIFS('Fresh Meeting Lead'!B:B,A3)
Lead Eligible for Payout
COUNTIFS('Fresh Meeting Lead'!B:B,A3,'Fresh Meeting Lead'!Y:Y,">"&0)
Referral Amount
SUMIFS('Fresh Meeting Lead'!Y:Y,'Fresh Meeting Lead'!B:B,A3,'Fresh Meeting Lead'!Y:Y,">"&0)
Upsell Meeting Lead
3 columns
1.Total Lead
=COUNTIFS('Upsell Meeting Lead'!B:B,A3)
Upsell Meeting Lead'!B=
Email Address
A-Email Address
2.Lead Eligible For Payout
=COUNTIFS('Upsell Meeting Lead'!B:B,A3,'Upsell Meeting Lead'!T:T,">"&0)
Upsell Meeting Lead'!T -
Amount Earned
=IF(or(Q2="ACTIVE",Q2="OnBoarded"),IF(R2="West",VLOOKUP(S2,Policy!$E$21:$G$26,3,1),VLOOKUP(S2,Policy!$H$21:$J$25,3,1)),0)
1 more item...
Upsell Meeting Lead'!B - Email ID
3.Referral Amount
=sumIFS('Upsell Meeting Lead'!T:T,'Upsell Meeting Lead'!B:B,A3,'Upsell Meeting Lead'!T:T,">"&0)
Total
Referral Processed: No formula which dump? :red_cross:
7.Master Data:Referral Form Responses(WB)-Sheet- Form Responses 1! Till - AE
8.Referral Lead:
Referral Form Responses(WB)-sheet-Referral Lead : Contains formulas- add-ons
Additional - Columns
P
Referral Eligible
=IF(OR(M2="Non Operational",trim(M2)="existing",M2="Not Relevant"),"No",IF(and(M2="Meeting Fixed",O2<>"No"),"Yes",IF(AND(L2="Yes",O2="No"),"Yes","No")))
M=Calling Status
o - Duplication
Q
Referral Amount
=if(IF(and(P2="Yes",or(N2>=100,H2>=100)),IF(N2="",H2,N2),0)>250,250,IF(and(P2="Yes",or(N2>=100,H2>=100)),IF(N2="",H2,N2),0))
P -
Referral Eligible
H -
Number of Flats in the society
N -
Flat Count
9.Fresh Meeting Lead:
Referral Form Responses(WB)-sheet-Fresh Meeting Lead: Contains formulas-need to check :warning:
Upsell Meeting Lead:
Referral Form Responses(WB)-sheet-Upsell Meeting Lead: Contains formulas-need to check :warning:
Additional columns - 8
Lead ID - N
=ARRAYFORMULA(IF(LEN(A2:A),IFNA(VLOOKUP(A2:A,IMPORTRANGE("Ashish Employee Referral Master","Upsell Meeting Lead!A:o"),15,0)),))
ClosureNumber - 0
ARRAYFORMULA(IF(LEN(N2:N),IFNA(VLOOKUP(N2:N,'Closure Data'!L1:T,9,0)),))
Flats(in the lead) - P
ARRAYFORMULA(IF(LEN(N2:N),IFNA(VLOOKUP(N2:N,'Closure Data'!L1:T,6,0)),))
Onboarding Status - Q
ARRAYFORMULA(IF(LEN(P2:P),IFNA(VLOOKUP(P2:P,Onboarding!C1:V,20,0)),))
City Bucket - R
IF(OR(L2="Pune",L2="Mumbai",L2="Navi Mumbai",L2="South Mumbai"),"West","Pan India")
Final Flats Count - S
IF(P2>0,P2,H2)
Amount Earned - T
IF(or(Q2="ACTIVE",Q2="On-Boarded"),IF(R2="West",VLOOKUP(S2,Policy!$E$21:$G$26,3,1),VLOOKUP(S2,Policy!$H$21:$J$25,3,1)),0)
Amount left for distribution - U
blank
Additional columns-12
Lead ID - O
O2
=ARRAYFORMULA(IF(LEN(A2:A),IFNA(VLOOKUP(A2:A,IMPORTRANGE(Ashish Employee Referral Master,"Fresh Meeting Lead!A:o"),15,0)),))
ClosureNumber - P
Flats(in the lead) - Q
4.Demo Status -R
=ARRAYFORMULA(IF(LEN(O2:O),IFNA(VLOOKUP(O2:O,Demo!A1:J,10,0)),)
5.
Demo Status -S
=IF(R2>=15,"Done","Not Done")
6.
Onboarding Status - T
=ARRAYFORMULA(IF(LEN(P2:P),IFNA(VLOOKUP(P2:P,Onboarding!C1:V,20,0)),))
7.
City Bucket -U
=IF(OR(L2="Pune",L2="Mumbai",L2="Navi Mumbai",L2="South Mumbai"),"West","Pan India")
8.Final Flats Count -V
=IF(Q2>0,Q2,H2)
9.
Demo Referral Amount-W
W2
=IF(S2="Done",IF(U2="West",VLOOKUP(V2,WestBucketDemoSLab,3,1),IF(U2="Pan India",VLOOKUP(V2,Policy!$L$9:$N$14,3,1),0)),0)
Onboarding Referral Amount - X
=IF(T2<>"",IF(U2="West",VLOOKUP(V2,WestBucketDemoSLab,4,1),IF(U2="Pan India",VLOOKUP(V2,Policy!$L$9:$O$14,4,1),0)),0)
11.
Amount Earned-Y
=W2+X2
12.Amount left for distribution -Z
Blank
Onboarding:
Import-Final Final Master Data Cleanup
Sheet- Final Data-1 :societyStatus column=Active
12.Demo
Import Sheet: WB :red_cross: Pending
13.Closure Data:No formula:-
MIS-Tab3-Closure
HR Data:Hidden
Copy of Hood Active and Inactive Headcount A:Z