Building a company knowledge graph from identifiers and events

A company knowledge graph answers questions that cross tables: which brands roll up to a listed parent, which companies run the same tool, what changed around the same time. This guide builds one from Fokals data: Technology Stack, Technology Changes, Company News, Company Funding, Intent Scores, Hiring Activity and Web Traffic. It covers the node types and the keys that identify them, the edges the data supports with a date and a source, the SQL that produces them, and how to link your own records.

Nodes and the keys that identify them

Each company-level file has a stable company_id, and a listed company, or a brand or subsidiary of one, also carries the identifiers of its listing. Use company_id as the identity of a company, and keep identifiers as separate nodes or attributes, never as the identity. An ISIN or LEI that fails its check digit is not stored at all, so the identifiers in the files are safe to join on.

The figi column on every other dataset holds the share-class FIGI, which is share_class_figi in listed_securities. A share class can have several listings, so key the security node on the share class, and keep each listing's ticker, mic and status as properties of an edge.

Edges the data supports

Give every edge three properties beyond its type: a validity interval, valid_from and valid_to, the source dataset, and the label version where there is one. A graph without them is a picture of today that cannot say when or from what. Funding notices are tied to a company on an exact match of the issuer, so FILED edges are conservative.

A RUNS edge needs one more flag. A technology present at a company's first observation has a first_seen_at equal to the date of that observation, which is when it was first seen and not when it was adopted. If the company has an added event for the technology at about that time, the adoption was observed. If not, the tool was there from the start. Store valid_from_known accordingly, and draw adoption dates only from edges where it is true. A tool removed and added again gives more than one interval, and each interval is its own edge.

Parent edges from identifiers

The rows of a brand or subsidiary hold the identifiers of its listed parent, and listed_securities.company_id names the company that each listing belongs to. The parent edge is therefore a join: take any row whose figi belongs to a listing owned by a different company.

select distinct
  t.company_id as child_id,
  l.company_id as parent_id
from company_technologies t   -- any table that carries company_id and figi
join listed_securities l
  on l.share_class_figi = t.figi
where l.company_id is not null
  and l.company_id <> t.company_id;

The edge links a brand or subsidiary to its listed parent, current state, with the parent's identifiers on every row. Delisted securities stay in listed_securities with status set to delisted, so a parent edge does not vanish when a listing does. The glossary entry on corporate hierarchy explains the wider idea, of which this is the listed part.

Rolled up, the edge answers what the group is doing, not what each trading name is doing. Group by coalesce(parent_id, company_id) and sum open_postings from Hiring Activity (company_hiring_daily) to see the hiring of a listed group, brands included.

An illustration, with Acme Robotics as the listed group. Its brands run their own websites and job boards, so each is a company with its own company_id and domain, and each row it produces carries the Acme Robotics ISIN and share-class FIGI. The join returns one row per brand, all with the same parent_id. Without the edge, the hiring of the brands is filed under their trading names and is missing from the group's total. With it, the group's open postings are the sum over the parent and its brands.

Building the tables

Two tables are enough: nodes with a type, a key and a label, and edges with a source, a target, a type and the three properties. The statement below builds the RUNS edges, taking valid_to from a removal event when one exists.

insert into edges (src, dst, type, valid_from, valid_to, source)
select
  'company:' || t.company_id,
  'technology:' || t.technology,
  'RUNS',
  t.first_seen_at,
  r.observed_at,
  'company_technologies'
from (
  select company_id, technology, min(first_seen_at) as first_seen_at
  from company_technologies
  group by company_id, technology
) t
left join company_tech_events r
  on  r.company_id = t.company_id
  and r.category   = 'technology'
  and r.key        = t.technology
  and r.change     = 'removed'
  and r.observed_at >= t.first_seen_at;

Add missing_since and seen_via as further columns if you want the edge to show them. With ADDED edges built the same way from company_tech_events, a question about neighbours is a self-join. This one finds the companies that added the same technology within 30 days of Acme Robotics, an illustrative company.

select b.src as company, b.valid_from as added_at
from edges a
join edges b
  on  b.dst = a.dst
  and b.type = 'ADDED'
  and b.src <> a.src
  and b.valid_from between a.valid_from and a.valid_from + interval '30 days'
where a.src = 'company:' || :acme_robotics_id
  and a.type = 'ADDED';

Asking the graph about a date

Because every edge has a validity interval, the graph can answer as of a date. An edge holds on date t when valid_from is on or before t and valid_to is empty or after t. Read an empty valid_to as no removal observed by your last load. For RUNS edges, missing_since marks a tool that is absent from recent observations but not yet counted as removed, so show it as present with a question mark.

select dst as technology
from edges
where type = 'RUNS'
  and src = 'company:' || :company_id
  and valid_from <= :t
  and (valid_to is null or valid_to > :t);

Run the same query over a parent and its brands to see the technologies of a listed group on that date. Edges start at a company's first observation, which sets a baseline, so read an empty result for an earlier date as unobserved.

Keeping the graph in step

The sources refresh at different speeds, and the graph should follow them. Website changes arrive daily to weekly, announcements daily to every three days, intent scores weekly, and the listed-company index monthly. Load events by cursor, as the guide to incremental sync describes, and upsert each edge on its natural key. Never delete an edge. Close it by setting valid_to, so that the graph can still answer for the past. Recompute OWNED_BY after each refresh of the listed-company index and when new companies enter the index, since a brand can gain its link when its listing is matched.

Questions the graph answers

• Roll-up. Hiring, technology and announcements for a listed group, brands included.
• Neighbours. Which companies added a technology within 30 days of the company you follow.
• Sequence. What else happened to a company within 14 days of a platform change or a funding notice.
• Context for a model. The neighbourhood of one company, with dates and sources, as the context an agent or a retrieval step receives.
• Similarity. Companies with overlapping technology sets. Co-occurrence is a statistic and not a relationship: two companies that run the same tools have not necessarily dealt with each other.

Linking your own records to the graph

Your own accounts, suppliers or portfolio need links to company_id, and the work is entity resolution. Use a ladder, strongest evidence first: ISIN, LEI or FIGI, then ticker with MIC, then the website domain, then name and country with human review. Fokals ties each listing to a company on identifiers and records how each was matched. Record the rule on each link you make, and never merge on name alone. The identifiers guide and the guide to mapping alternative data to a security master cover the keys in detail.

What the graph holds

• Companies and securities. Listed companies worldwide, the brands they own and verified private companies, each with one company ID, and every listed company with its ticker, MIC, ISIN, LEI and share-class FIGI.
• Dated events. A leadership change is an edge to a role, with its direction. An announcement carries its event type, one of 13, and its link to the company's own text.
• Technologies. 6,283 recognised in 68 categories, each with its first-seen and last-seen dates and a dated event for every adoption and removal.
• Refresh. Daily to weekly by dataset, every observation dated and written once.

How Fokals delivers it

The nodes and edges above come from the marketing stack and announcements and scale datasets and from listed_securities, which the API serves and which is available as a file on request. Every table and column is defined in the data dictionary.

Frequently asked questions

The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.