Building company change alerts into your product

A company change alert tells a user that something about a company they follow has changed: a tool added to its website, a new market, an announcement. This guide shows how to build that feature on dated events. It covers the two datasets that carry the events, Technology Changes and Company News, a schema for the alerts you store, how to read the feeds so that a restart neither loses nor repeats an alert, and how to word each message so that it says what was seen and no more.

Which events can become alerts

Two datasets hold dated events about a company. Technology Changes (company_tech_events) in the marketing stack dataset records a dated event for every adoption, removal and platform migration, and for every new market, language, currency or app. Company News (company_news) in the announcements and scale dataset records company announcements and regulatory disclosures, classified into 13 event types. Every row names its source and says when it was observed.

A company's first observation sets a baseline and produces no event, so a company that a user starts to follow has alerts from its next change, and its present state comes from the Technology Stack and the Website Profile (company_technologies and company_site_facts). Show that state on the company page, and keep alerts for movement.

A schema for alerts

Keep the alert store apart from the rows you load. Three tables are enough: the alerts, the follows that map your users or accounts to companies, and a log of what was sent. The alerts table holds these columns.

The natural key is what makes the pipeline safe to rerun. For a website change use company_id, observed_at, category, key and change. For an announcement use company_id and url, since each announcement is stored once. Hash either into source_key and put a unique constraint on source_table with source_key. The follows table carries, for each user, the companies they follow, the alert kinds they want and whether they receive items one by one or in a digest.

Reading the feeds without losing or repeating an alert

A feed starts from a time of your choosing, runs from the oldest event forward and pages by cursor, so the cursor of the last page you read marks your place. The guide to incremental sync covers the loop in full. For alerts the order of operations matters more than the loop.

1. Choose the start time for the first run and read the pages in order.
2. Write each page to a raw table with an upsert on the natural key, and store the new cursor in the same transaction as the rows.
3. After the commit, create alerts only for rows that were new to the raw table.
4. Start the next run from the stored cursor.

Select on your own load time, not on the event time, when you create alerts from the raw table. A row can reach you after later rows have, and a watermark on observed_at would skip it.

-- loaded_at is a column your loader adds to the raw table
insert into alerts (company_id, kind, source_table, source_key, occurred_at, loaded_at, headline)
select
  e.company_id,
  'tech_' || e.change,
  'company_tech_events',
  md5(concat_ws('|', e.company_id, e.observed_at, e.category, e.key, e.change)),
  e.observed_at,
  now(),
  e.technology_name ||
    case e.change when 'added' then ' first seen on the website'
                  else ' no longer seen on the website' end
from company_tech_events e
where e.category = 'technology'
  and e.change in ('added', 'removed')
  and e.loaded_at > :last_run
on conflict (source_table, source_key) do nothing;

Check the order of each feed in the API reference before you rely on it, and sort by occurred_at yourself when you build a digest. Fokals is delivered by REST API and bulk files, refreshed daily to weekly, so an alert is as current as your last pull, and one pull a day suits the daily datasets.

Dedupe, hold-down and grouping

The key removes exact repeats. Four other cases need rules of their own.

Flapping. Consent banners and experiments add and drop tags, so a tool can be removed and added back within days. Hold removal alerts for seven days, a starting value to tune, and cancel the held alert when an added event for the same company_id and key arrives.

select r.company_id, r.key, r.observed_at as removed_at, a.observed_at as added_at
from company_tech_events r
join company_tech_events a
  on a.company_id = r.company_id
 and a.category = r.category
 and a.key = r.key
 and a.change = 'added'
 and a.observed_at > r.observed_at
 and a.observed_at <= r.observed_at + interval '7 days'
where r.category = 'technology' and r.change = 'removed';

Bursts. One observation can write many events: a site that switches on a dozen markets, or a replatforming that drops several tools and adds others. Group events with the same company_id and observed_at into one alert, and take the headline from the platform event when there is one. One message that says a site gained twelve markets gets read, where twelve messages get muted.

Promotions. A promo event records a new announcement-bar message, so alert on a promotion appearing.

Digests. For users who want a summary, group by company and day and order by occurred_at.

Wording that says what was seen

A website change is a dated observation, not a decision. The observed_at of an event is the observation that saw the change, so the change happened between that observation and the one before it, which can be a day or a week earlier. Words such as bought, cancelled or launched claim more than an observation shows. Write what was seen, and when. The examples below use an illustrative company, Acme Robotics.

For an announcement, show the company's own title and the link in url. Show the event type as a label, classified under a named, frozen version. An announcement outside the 13 types is shown as an announcement.

A leadership change carries the role concerned and its direction, appointed, departed or succession, so the alert names the role.

How to read alerts

• Alerts are as fresh as the observations, which refresh daily to weekly depending on the company. Promise first seen on a date, not the moment of the change.
• A detection shows presence on the website, so word an alert as what was seen and when.
• Announcements come from what a company publishes about itself and from regulatory disclosures. The event type is assigned under a named, frozen version.
• Alerts from Company Funding (company_funding) rest on private capital raises reported in regulatory filings, in the intent dataset.

How Fokals delivers it

Both datasets reach you through the REST API or as bulk exports in JSON, JSON Lines or CSV. Each client has its own bearer key, and limits apply per key by the minute and by the day. Every column named here is defined in the data dictionary, and the delivery page describes the API and the exports. For the commercial use of the same events, see running competitor displacement plays from technology changes.

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.