Many companies wish to understand their customers from an industry perspective, but struggle to get a reportable structure in Microsoft Dynamics CRM that makes sense both at the summary level as well as the detailed level. Having a data strategy can help. Here are the steps we use to create that strategy.
1. Find a code that works for you. Different structures exist in different areas of the world, and you may already be tied into one. For this blog, we will focus on North America. The two primary industrial classification codes are:
- SIC (Standard Industrial Classification) – replaced by NAICS in many government statistics
- NAICS (North American Industry Classification System) – replaced SIC in 1997 – Jointly adopted by US, Canada, and Mexico. I prefer the US Census Bureau for current files.
2. Understand the structure – Here is what you need to know:
- NAICS codes change about every 5 years. The current version is 2012. The prior version was 2007.
- You can use a published map from the old 2007 codes to the new 2012 codes to help you update to the current version
- You can use a published map from SIC codes to the new 2012 NAICS codes to help you convert your existing data from SIC to NAICS
- There are 5 levels starting with 2 digit broad categories and moving to detailed 6 digit level. See figure #1 for the number of codes within each level.
Figure #1 – 2012 NAICS Code counts by level
- Each level has its own description. Use the appropriate description to keep your data aligned. See figure #2.
- There is no way to systematically take a summary level code (e.g. 11) and make it into a valid detail level code by adding zeros (e.g. 11 does not become 110, or 1100, or 11000, or 110000). Although it may be tempting to create this summary level for your 6 digit data, please resist. It will hamper your reporting and make it difficult to update to the next set of codes (eta 2017?). Note that in figure #2 just adding a zero to the 5 digit code will get you to one specific 6 digit code, not a general catch-all category. Not a good idea.
Figure #2 – A drill upward from the lowest level for the first 6 digit code in the list, “Soybean Farming”
- Key Point: Always start from the lowest level of detail and you will automatically know all the summary levels that relate to it. Work upward from the detail. Don’t try to work down from the summary level (here there be monsters).
3. Reflect that structure in a custom entity in Dynamics CRM (for help please see our blogs about creating a custom entity, uploading data, and creating custom views or refer to our online resource guide The CRM Book)
- Create 10 fields – 5 to hold the codes and 5 to hold the descriptions
- Choose the field that will be the code returned by lookups (in this case we used the 4 digit level which is the primary level we want to use on a daily basis – only 312 industries – just enough detail for daily use.)
- Load all the 1,065 records into the entity
Figure #3 – a record in the custom, “NAICS Code” entity
Figure #4 – a view of the records in the, “NAICS Code” entity
Now you can formulate a data strategy to help you optimize the use of industry codes in Dynamics CRM. For more helpful Dynamics CRM tips and tricks keep checking our blog!