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
-
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.
-
Fetch Opportunities last 120 days (fetch task) – Here I’m fetching latest opportunity data for each CSM in the last 120 days.
-
Merge Data (Merge task) – I’m doing a left join because I wanted to retain all the active customers and CSMs
-
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
*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
-
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.
*Pivot conditions are given as below,
-
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.
*Calculation as follows,
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.