Organization Hierarchy

Purpose

The Organization Hierarchy CDP contains current and historical information on all managerial chains within the organization, starting from the CEO and extending down through all levels of management. This CDP is especially useful for answering detailed questions about how employees are structured within divisions and managers, and it should be used in conjunction with the device and software application CDPs to provide a holistic view of group-level analytics.

Understanding the Table

In this CDP, Level 1 will always represent the CEO for every row, with all managers listed above the row’s user_id. This hierarchical structure clearly illustrates the number of managerial levels between the CEO and any given employee.

Whenever there is a change in the managerial chain, a new row is created for all affected users. This means that if a high-level manager's position changes, all employees below that manager in the hierarchy will have a new row added to the table. This approach ensures that the historical context of managerial changes is preserved, allowing for accurate tracking of the organization's evolving structure over time.

Joining to CDPs and OCSF

The row’s user_id should be used for all CDP and OCSF joins.

To identify and retrieve all current manager chains, one should use the active column in conjunction with checking if the end_time column is not null. By doing so, you can effectively filter out inactive records and focus on the relevant, up-to-date management structures.

Schema

Example Queries

Find the latest record for a given id

SELECT * FROM organization_hierarchy WHERE id={id} and active=TRUE

Find all active entities and their latest row

SELECT * FROM organization_hierarchy WHERE active=TRUE 

Find all direct reports for a manager

SELECT * FROM user where manager_id ={id}