Entity Segment Revenue

entity_segment_revenue is the reported view of what a company earns its revenue from: one row per entity, year and disclosed business segment (segment_id), with the segment mapped into the taxonomy. It is built from the segment-level revenue evidence in supporting_facts, which each row links back to.

Versioned — filter is_current = TRUE.

`entity_segment_revenue`

Column	Type	Description
`entity_id`	NUMBER	Who. Joins to `entities`
`year`	NUMBER	Fiscal year
`segment_id`	NUMBER	The disclosed business segment. Joins to `segments`
`revenue_amount`	FLOAT	Revenue of the segment, USD
`revenue_pct`	FLOAT	Segment revenue as a share of total revenue
`taxon_id`	NUMBER	The taxonomy activity the segment maps to; NULL where the segment could not be mapped. Joins to `taxonomy`
`confidence`	NUMBER	Confidence score for the segment-to-taxon mapping. A figure 1 (low) to 3 (high).
`supporting_fact_id`	NUMBER	The evidence row the revenue figure comes from. Joins to `supporting_facts`
`year_rank`	NUMBER	1 = most recent year available for the entity

Example: a company’s latest revenue assement

SELECT s.segment_category || ':' || s.segment_class || ':' ||
       s.segment_instance                       AS segment,
       esr.revenue_amount,
       esr.revenue_pct,
       t.name                                    AS mapped_taxon,
       t.sdg_alignment_type                      AS alignment,
       ts.goal                                   AS sdg,
       esr.confidence
FROM NET_PURPOSE_REVENUE.REVENUE.ENTITY_SEGMENT_REVENUE esr
JOIN NET_PURPOSE.FOUNDATIONS.SEGMENTS s
  ON s.segment_id = esr.segment_id
LEFT JOIN NET_PURPOSE.REFERENCE.TAXONOMY t
  ON t.taxon_id = esr.taxon_id
JOIN NET_PURPOSE.REFERENCE.TAXONOMY_SDGS ts
  ON t.taxon_id = ts.taxon_id
WHERE esr.is_current = TRUE
  AND esr.entity_id = 12345
  AND esr.year_rank = 1
  AND ts.is_primary
ORDER BY esr.revenue_amount DESC NULLS LAST;

Example: trace a segment revenue figure to its source

SELECT esr.entity_id,
       esr.year,
       esr.revenue_amount,
       sf.recorded_value,
       src.name    AS source_document,
       sf.page_num
FROM NET_PURPOSE_REVENUE.REVENUE.ENTITY_SEGMENT_REVENUE esr
JOIN NET_PURPOSE.FOUNDATIONS.SUPPORTING_FACTS sf
  ON sf.supporting_fact_id = esr.supporting_fact_id
 AND sf.is_current = TRUE
LEFT JOIN NET_PURPOSE.REFERENCE.SOURCES src
  ON src.source_id = sf.source_id
WHERE esr.is_current = TRUE
  AND esr.entity_id = 12345
  AND esr.year = 2024;