Taxonomy

The taxonomy is the classification tree at the heart of the Revenue & SDI dataset and Net Purpose estimations. Every revenue-generating activity a company can perform sits somewhere in this tree. Likewise all researched solutions and their baselines also sit within this activity tree.

The hierarchy

The taxonomy tree consists of four levels, linked by parent_id:

`type`	What it represents
`theme`	A top-level impact area. Themes have no parent
`sub_theme`	A problem area within a theme
`domain`	A solution space addressing the problem
`technology`	A concrete activity/technology a company can earn revenue from

`taxonomy`

The Net Purpose activity taxonomy. Each row is one node in the classification hierarchy. All company revenue segments and Net Purpose estimations map to a node somewhere in this tree, and that mapping drives the final SDG allocation.

The view already contains only the current version of each taxon, so no is_current filter is needed.

Column	Type	Description
`taxon_id`	NUMBER	Primary key. Referenced by all revenue tables
`name`	VARCHAR	Name of the taxon
`type`	VARCHAR	Level in the hierarchy (see above)
`parent_id`	NUMBER	`taxon_id` of the parent taxon; NULL for themes
`path`	VARCHAR	The full path from the root to this taxon, with levels separated by `>` (`theme > sub_theme > domain > technology`). Useful for selecting a whole branch with `LIKE`
`depth`	NUMBER	Depth of the taxon in the tree: theme = 1, each level below adds 1
`sdg_alignment_type`	VARCHAR	The direction of the activity’s contribution to sustainable development. One of: `positive`, `potentially_positive`, `potential`, `negative`

`taxonomy_sdgs`

The SDG goals and targets assigned to each taxon. A taxon has one primary target (its dominant contribution) and can have any number of secondary targets, so expect multiple rows per taxon.

Column	Type	Description
`taxon_id`	NUMBER	The taxon this assignment relates to
`reporting_standard_id`	NUMBER	The matching row in `reporting_standards`
`goal`	VARCHAR	The SDG goal of the assignment, e.g. `13`
`target`	VARCHAR	The specific SDG target, e.g. `13.1`
`is_primary`	BOOLEAN	`TRUE` for the taxon’s primary (dominant) contribution, `FALSE` for secondary contributions

-- The name and primary SDG of every positively contributing taxon
SELECT t.taxon_id, t.name, t.type, ts.goal, ts.target
FROM NET_PURPOSE.REFERENCE.TAXONOMY t
JOIN NET_PURPOSE.REFERENCE.TAXONOMY_SDGS ts
    ON ts.taxon_id = t.taxon_id
   AND ts.is_primary = TRUE
WHERE t.sdg_alignment_type = 'positive';

-- All taxa in one branch of the tree, using the materialised path
SELECT taxon_id, name, type, depth, path
FROM NET_PURPOSE.REFERENCE.TAXONOMY
WHERE path LIKE (SELECT path FROM NET_PURPOSE.REFERENCE.TAXONOMY WHERE taxon_id = 42) || '%'
ORDER BY path;