ROW Territories - Reorganization

ruipperez commented 3 years ago

Shipping Region - pull out the code list (Omega)
All accounts (with an open opp?) should have the Region informed (at conversion for Leads, at creation for manual opps)
Each child account has its own territory

Next step: pull a list of all accounts with existing Shipping Region Country Code.xlsx

Shipping Country Code--> copies country code (made visible for Admin only)

Shipping Territory if( or(ShippingCountry = "Canada", ShippingCountry = "United States"),"North America",if(ShippingCountry="","","Rest of World"))

Current Shipping Regions and Territories

image (7)

Current formula

Shipping Region if(text( ShippingCountryCode )="","Unknown", if( CONTAINS("GB:IE:IM:JE", text( ShippingCountryCode )),"United Kingdom", if( CONTAINS("US:CA", text(ShippingCountryCode)),"North America", if( CONTAINS("ES", text(ShippingCountryCode)),"Spain", if( CONTAINS("AI:AG:AR:AW:BS:BB:BZ:BM:BO:BQ:BR:VG:KY:CL:CO:CR:CU:CW:DM:DO:EC:SV:GF:GD:GP:GT:GY:HT:HN:JM:MQ:MX:NI:PA:PY:PE:KN:LC:VC:SX:SR:TT:TC:UY:VE", text(ShippingCountryCode)),"Latin America", if( CONTAINS("AU:BD:BT:IO:KH:CN:FJ:IN:ID:JP:KP:KR:LA:MY:MV:MN:MM:NP:NZ:PK:PG:PH:SG:LK:TW:TH:TL:VN", text(ShippingCountryCode)),"Asia-Pacific", if( contains("IT:MT:ES:CY:FR:GR:LV:MC:NO:PL:RU:RS:SE:TR:AL:AD:AM:AT:BY:BE:HR:DK:EE:FI:GE:DE:HU:IS:IE:RO:UA:BG:PT:SK:SI:LT:AZ:KZ:LU:ME:SM:NL:CH:LI:CZ:MK:BA:VA",text(ShippingCountryCode)),"Europe","Africa & Middle East") ) ) ) ) )

@Jorgedelara for visibility

ruipperez commented 3 years ago

Working export: https://docs.google.com/spreadsheets/d/1oAs4VQzRZo6r6cA9GOrzj-vFqV5HNXMyYN2jplb8VIU/edit#gid=1887503541

ruipperez commented 3 years ago

Update:

Entonces, te resumo los siguientes pasos: Confirmar los nuevos territorios (Shipping Region), esto es es la tabla de arriba Confirmar los países que van en cada territorio (la segmentación en la fórmula es por país): aquí los puedes ver Confirmar el reparto entre los AEs Modificar la fórmula en SF Sacar listado de cuentas con contrato current y/o open opp Hacer/revisar reparto por owner y hacemos mass update (if required) @Victor Folqué Ha incluido en el excel también una pivot con el current ARR según la nueva clasificación por si te ayuda :)

ruipperez commented 2 years ago

Update - territories ready.

Lead assignation: https://docs.google.com/spreadsheets/d/11eAlSLZPmcQU2MsAx9q5qqkPxzAYARNEcteZHEY3FnI/edit#gid=857505551

ruipperez commented 2 years ago

Look into creating a new formulated field based on the PODs .

victorfolque commented 2 years ago

New formula has been applied and here (in the unique countries tab) we can the final distribution. I've checked it but maybe I there are misclassified countries so we should double check it.

Also, we should revisit this at some point as we've been working with all the countries that we've done/try to do business at some point. If a new weird country comes in maybe it will be misclassified as it's not implicit in the formula.

NEW FORMULA:

if(text( ShippingCountryCode )="","Unknown",
if(
CONTAINS("US:CA", text(ShippingCountryCode)),"North America",
if(
CONTAINS("ES:PT:IT:GR:AI:AG:AR:AW:BS:BB:BZ:BM:BO:BQ:VG:KY:CL:CO:CR:CU:CW:DM:DO:EC:SV:GF:GD:GP:GT:GY:HT:HN:JM:MQ:MX:NI:PA:PY:PE:KN:LC:VC:SX:SR:TT:TC:UY:VE", text(ShippingCountryCode)),"SE & LATAM",
if(
contains("GB:IE:IM:JE:SE:CH:NO:NL:LU:DE:FR:FI:DK:BE:AE:SA:IN",text(ShippingCountryCode)),"NE & CE & Asia","APAC & Africa & BR & EE")
)
)
)

ruipperez commented 2 years ago

@victorfolque Hey :) Can you pull a full export so we can check please

victorfolque commented 2 years ago

@ruipperez what do you mean? 🤔 , I put the spreadsheet in the comment above.

victorfolque commented 2 years ago

Here an export of all-time opportunities regardless of the status. There are two checking:

Check if the Sales Team matches the Shipping Territory.
Check if the POD and the Shipping Region are aligned.

Regarding the second checking, we should know beforehand what’s the theoretical assignation.

ruipperez commented 2 years ago

Next steps:

[x] Update formula in sandbox
[x] New tab with pivot/s ---> Current Q, by Status, by POD
[x] Save All exports in drive folder: https://drive.google.com/drive/folders/100zaAfrYgomW6FdBLRAXztf5CYBUCAsZ?usp=sharing

victorfolque commented 2 years ago

All Updated!

ruipperez commented 1 year ago

New Territories (Feb 2023): https://docs.google.com/spreadsheets/d/1glwgLjNb6TS-92RL9fypp5nzggs1mAwKPLPb5hJUoFA/edit#gid=0

ruipperez commented 7 months ago

Shipping Region changed back to former formula (changed pending in uat)

Shipping Region if(text( ShippingCountryCode )="","Unknown", if( CONTAINS("GB:IE:IM:JE", text( ShippingCountryCode )),"United Kingdom", if( CONTAINS("US:CA", text(ShippingCountryCode)),"North America", if( CONTAINS("ES", text(ShippingCountryCode)),"Spain", if( CONTAINS("AI:AG:AR:AW:BS:BB:BZ:BM:BO:BQ:BR:VG:KY:CL:CO:CR:CU:CW:DM:DO:EC:SV:GF:GD:GP:GT:GY:HT:HN:JM:MQ:MX:NI:PA:PY:PE:KN:LC:VC:SX:SR:TT:TC:UY:VE", text(ShippingCountryCode)),"Latin America", if( CONTAINS("AU:BD:BT:IO:KH:CN:FJ:IN:ID:JP:KP:KR:LA:MY:MV:MN:MM:NP:NZ:PK:PG:PH:SG:LK:TW:TH:TL:VN", text(ShippingCountryCode)),"Asia-Pacific", if( contains("IT:MT:ES:CY:FR:GR:LV:MC:NO:PL:RU:RS:SE:TR:AL:AD:AM:AT:BY:BE:HR:DK:EE:FI:GE:DE:HU:IS:IE:RO:UA:BG:PT:SK:SI:LT:AZ:KZ:LU:ME:SM:NL:CH:LI:CZ:MK:BA:VA",text(ShippingCountryCode)),"Europe","Africa & Middle East") ) ) ) ) ) )

CartoDB / RevOps

ROW Territories - Reorganization #307