Recent Opportunity Win Percent per CSM

  • 27 May 2020
  • 1 reply
  • 114 views

Badge +1

In our business every Quarter a number of opportunities are closed (won/lost). The following design depicts the opportunities won by each CSM in the last 120 days. 

I’m considering only active customers for which opportunities were won.

 

Below is the overall data flow diagram

 

MopDBtp52NDWWL6sn4qm6afBulovrR3jdflnUBe0m-mYocI3Ha2qwNeFTI-BdZopprFxQRCKmjK-G5ElOumoWL3EoyaqSEmjjoHaDBlz3WwqFSAkHh20kR6FOil-2VIpLZp-ZCevs7sx6LFAGA


 

  1. Fetch Customers and CSMs (fetch task) – Fetch all the active customers and their respective CSMs data. In my case, owner is the acting acting CSM hence I’m fetching Owner Id Name.

 

jZoAI3CBp9mlVVqENqj6VAH1j06p7iR_OrXshs7lhihQKKZPknYLRy0q-HRTQJvRqZ9t3jIaMyK0CKCaAf-NPQ5qHPsgKOxpl0scwED5yKq9Bxaj6MCvZxieuY0HrzjTVx1Olo3-XyxfmeLmnw

 

  1. Fetch Opportunities last 120 days (fetch task) – Here I’m fetching latest opportunity data for each CSM in the last 120 days.

 

ZETm-cBtATPcRPJAPN1_ztBbI1d_9vuVAOFK5b1Uj7kD1nKegauTLpOgojSn8PCGrSmAVfv_KmkaT2Ea1S04nFgzDeyJYTqIqkWFOjUX_1l2PdD9hQgb99u1euOQCzbpzOCAgyocsMf8pISTbA

 

  1. Merge Data (Merge task) – I’m doing a left join because I wanted to retain all the active customers and CSMs 

gPrsFwDwINyhYyOCyLUS6wEwp4X_VJLnvgCIySpc3xaX3l3jJKPwwhf3ZujdzL4yfN4kDqJJPuli4ODAZw-kmmi6C8VWmd7kD-2RsQ8vCnHp10e2mbWsZRtghDPg9x8JuNeEmmygv6qLk9EKBg

 

  1. Determine Accounts with Opportunities (Transformation task) – we are determining the accounts which had opportunities in the last 120 days based on the flag field shown after the transformation task

 

g6OGcH4r-QLcbZMnlnGZbEKd4O_EgI22aRCTqtWBPw0gQUU1882BKBaSaveKvZLOpjZiA4Bls-5TWhirhFQrRsobg3Zk6QyJ0WEUuVqR-rZW5GHWRe31-go800IzcpJcINDXYKfQsVWObM-7vQ

 

*From the aforementioned Merge, if we find accounts which had opportunities recently then we are marking as YES and NO vice-versa using the case statement

 

Jt8HSjQAu-jrJkua-uSrs_e5bpcMK6CXYNFMD2L7joySO_FSfGYlYayvKhdjFYwEuzuphuz49IQS5-eGqauh1KOgJAEMQqbBPvTU832R9KhQfveMjWBTU6CEG_vGf-AkUjnFHRaJQUoz4NhBcQ

 

  1. Pivot Data (pivot task) – Pivot the data based on the above generated Acct Flag field and group by CSM Name.

*Here onwards, data is presented at CSM level only.

 

CsXRLGOwrnkZdqZow82GbVlDmcUTqV6H-0iOH4IBZfB0TAqHEdKiCscM8DWBwrjaIkHVxVr5dElIaLujpmuZmH2YsTGlb4XlFif5ndDfRABZiGdGw_6IRp51LoIJCjo63Dyiihkr6syWexjxBA

 

*Pivot conditions are given as below,

PdFfn8bl0xJhQ4mkL8fZtkLi5hcOYIR0F89R5IXiRhO2ChVnHq_k3MhqupWsr4GROwtfHVPb-fItZapWP9y4hesZDXrvhYbKH9JcijlaR57gX35omOv_jYIgVAfdXrtK8OV0KSBboRq91uUOmA

 

  1. Final Dataset (transformation task) – Below is the final data set where we calculate the percentage of opportunities won by each CSM in the last 120 days.

 

aT5PkKWCJqQlhN_cqKqedqO3ULStez3EWPd39p8Cp043rW4O3dw8WdkttrwxSU80lSAE2Pvf_YtkW5pzT62RapeNzdMyq_dIjXFvzhi1tQnQPBjQPl2o_HfBWGuH4mySDluRtQJVgmpnaN-GwQ

 

*Calculation as follows,

-PmO1LmzBGSrWGIv2usye9jY8NkRfKDmBe9LA8_CtpMOmACzHGUKVx-TW7CD-_TomucDJLbBs6PPJXyTxJK6XH4Rzoh54nSux0vqCJZbPsi5gyCdWxMQEAm0KILyWLEnh0wnVIKGjTfSU2_KtQ

 

I’m loading this data into MDA object – ‘CSM Oppty Percents’ which has the following structure,

Column Name

Datatype

CSM Name

String

Non Oppty Accts

Number

Oppty Acct Percent

Percentage

Oppty Accts

Number

Total Accounts

Number

isActive

Boolean



At any point of time, there’ll be a single record for each of the CSMs.

 

*For Data sustainability, compare this data with Company object everyday and mark any CSM inactive whenever they leave the organisation.

 


1 reply

Userlevel 7
Badge +1

@phaneendhar_lingam Excellent, thanks for sharing! 

Reply