Identifying the Final Segmentation of a Company from Multi-Segment Accounts

[Mwatts23]

Hi Everyone,

I would like to ask your help about getting the final segmentation of a company which has multiple segments and with different scenarios.

Below is the ss of the raw data and the expected result. Attached is the excel file that contains the data and the explanation of each company scenarios.

For any clarifications, please let me know. Thank you.

*Segmentation Hierarchy
1	Shoes
2	Apparel
3	Slippers
4	Cap

How to identify the Final Segmentation per scenario
Nike	If a Company has 2 Shoes and 2 Apparel then the final Segmentation is Shoes since in the Segmentation Hierarchy Shoes is on top of the hierarchy
Adidas	If a Company has 1 Shoes and 1 Apparel then the final Segmentation is Shoes since in the Segmentation Hierarchy Shoes is a much higher hierarchy
Reebok	If a Company has 1 Cap, 1 Apparel and 1 Slippers then the final Segmentation is Apparel since in the Segmentation Hierarchy Apparel is a much higher hierarchy among the 3
Underarmor	If a Company has 1 Shoes and 2 Slippers then the final Segmentation is Slippers since MAJORITY of the Segmentation is Slippers
World Balance	If only 1 segmentation then stays as is
JD Sports	For no segmentation then stays as null

[aatalai]

@Mwatts23 I concatnated the segments grouping by company then using an If statement going down the hierarchy to update the final segmentation than joined it back up

hope it helps, let me know how you get on

[Pilsner]

Hello @Mwatts23 

I would begin by joining your input raw data table to your hierarchy table. This allows you to see which hierarchy (or rank) each of the segmentations gets

After this, you can sort the ranks ascending and sample the top record per company to identify the Final Segment per company.

Finally, you need to join this table back to your original data, based on the company field (use the union tool to make sure no unjoined records are excluded).

I've attached the workflow below. I hope this helps but if you have any questions please let me know.

Regards - Pilsner

 

[Mwatts23]

Hi @aatalai,

Thank you for your reply. The result looks good!

However, Underarmor's final segmentation should be Slippers instead of shoes. Even though shoes is the top of the hierarchy but majority of Underarmor's segmentation will be followed, thus Slippers becomes the final Segmentation.

Regards,

Mike

[Mwatts23]

Hi @Pilsner,

Cool! Thank you for taking your time on my inquiry.

However, Underarmor's final segmentation should be Slippers instead of shoes. Even though shoes is the top of the hierarchy but majority of Underarmor's segmentation will be followed, thus Slippers becomes the final Segmentation.

I can't get around on this particular issue.

Regards,

Mike

[Pilsner · Accepted solution]

Hello @Mwatts23 

Great spot, I missed this first time round. To get this to work as expected, all you should need to do is make two changes to the original workflow. 

Firstly, add a summarise tool in between the main input and the join tool. The summarise tool should be set to group by company and segmentation, and count on segmentation.

Finally, you need to add this new count column to the sort tool (and sort descending), making sure that the count column appears first in your sort list.

 

This should get you the following results.

  

I've updated the workflow and re-attached it below.

Please let me know if this helps/ you have any questions.

Regards - Pilsner

[Mwatts23]

Awesome @Pilsner! It works perfectly! Thank you.

[Pilsner]

Glad to hear, thanks @Mwatts23!