Brokerage CIL's
(Note: information has been redacted and is only an example of what it looked like)
CIL's
Receiving a CIL (Customer Introductory Letter) went through a process to ensure that the brokerage firm has a legitimate reason to claim a commission from the company. This was done by email and a system custom designed for this task.
Brokers sending a CIL
Brokers would fill in the form as shown, this form automatically fills in the excel sheet that the Operations & Administration department are able to access and verify the CIL.
Received CILS's
Al the CIL's that have been recieved are added into this sheet, wehre it is automatically timestamped and manually checked according to the elligibility for the broker to receive a commission incase of a sale.
Fresh CIL's & Leads
The CIL's along with the leads are then sent out for distribution automatically to the sales team. They were ordered to check a sheet on their workbook for new leads (this is before distribution)
Reveiving of leads
The leads were here recieved by the sales team member and have been automatically added.
Contacting CIL's & leads
These CIL's & leads were contacted and the feedback was written on their sheet. this is done for recording. In the case that the person has shown some interest, the sales team member is able to add a time and date to contact again and the sheet would remind them on the day of the event.
Contacting flow
The sales team had to follow a process for contacting the leads and CIL's. These contacts had the following process:
Collecting Feedback
The actions done by the sales team member are all combined in a seperate sheet and then were analysed by the Operations Department and reported to the managers
Examples of formulas used
=query({YY!A2:Z;HN!A2:Z;SH!A2:Z;AS!A2:Z;PY!A2:Z;KA!A2:Z;VS!A2:Z}," select * where Col3 is not null ", 0)
​
=QUERY(IMPORTRANGE("1KOI-CBHijVHHE3xFczEXAMPLEx4TtvvW3yihZCY", "Fresh!B3:T"),"WHERE (Col18 = 'Sales Team Member') ")
​
=IF(ROWS(Leads!$A$1:$A9)>Fresh!$G$2,"",INDEX(Leads!H:H,MATCH(ROWS(Leads!$B$1:$B9),Leads!$B:$B)))