Draft Data Update Slack Post

Data update communications draft

Generate a draft for the #data-updates-comms Slack form. The skill inspects the current branch of the ETL repo to fill the mechanical fields and seeds the editorial fields with context the user then rewrites in their own voice.

Why this channel exists (read before drafting)

OWID's #data-updates-comms channel is not an internal "FYI I did X" log. It gives information to Charlie (OWID's Communications & Outreach Manager), who turns this input into public-facing posts on social media (Instagram, LinkedIn, X) and in newsletters.

So the form's editorial fields are written for Charlie and indirectly for the general public — not for the data team. A big mistake is writing them in an internal/engineer voice.

The reframing that matters most:

Don't tell Charlie what you'd say to your colleagues ("I updated all the WDI charts"). Tell him what you'd say to a friend who asks what you did this week ("I updated hundreds of our charts to the latest release of the World Bank's largest dataset, called the World Development Indicators. It's a core dataset with hundreds of indicators across global development. This update added new data up to 2025 for dozens of our most-viewed charts…").

What Charlie needs from each field is a reader-centric view: what work was done, what it changes or enables, what's interesting about the source, what it helps people understand about the world, and why anyone should care that it's been updated.

This skill must surface this framing to the user — both at the top of the output draft file and inside each editorial-field prompt block — so the user has it in front of them when they sit down to write. Do not delete or paraphrase the framing.

When to use

• The user says "draft the data-update comms post for X", "fill out the Slack form for X", or similar.
• After an update-dataset run finishes (step 9 of that skill should delegate here with workbench/<short_name>/update-context.yml rather than re-implement).
• After a manual update where the user wants help drafting the announcement.

Inputs

• Preferred after update-dataset: workbench/<short_name>/update-context.yml — reuse gathered facts first, and only gather missing fields directly.
• <namespace>/<new_version>/<short_name> — the updated dataset (garden path).
• Optional: <old_version> — for "what changed since last release".
• Optional: branch name — defaults to current branch (used for the staging-site hostname).

If the user only gives a branch or no input at all, infer the dataset(s) from git diff master...HEAD --name-only filtered to etl/steps/data/grapher/** or snapshots/**. If multiple new datasets are touched, ask the user which one(s) the announcement covers.

What's mechanical vs. editorial

The editorial fields are deliberately not auto-prosed. Slack posts in the editorial voice ("Why we have this dataset on OWID") read flat when LLM-written; the value is in the human framing. The skill's job is to surface the relevant snippets so the user doesn't have to grep for them.

Workflow

0. Reuse update context if available.

   • If workbench/<short_name>/update-context.yml is provided or exists, read it first and use its values for mechanical fields, chart count/views, and editorial snippets.
   • Continue with the steps below only for fields missing from that context. This keeps update-dataset responsible for gathering context during the update while preserving this skill as a standalone fallback.

1. Resolve the dataset.

   • Parse <namespace>/<new_version>/<short_name> or infer from git diff.
   • Locate snapshot DVC: snapshots/<namespace>/<new_version>/*.dvc.
   • Locate garden step: etl/steps/data/garden/<namespace>/<new_version>/<short_name>.py and .meta.yml.
   • Locate built garden dataset: data/garden/<namespace>/<new_version>/<short_name> (must have been run; if missing, tell the user to run the garden step first).

2. Extract mechanical fields.

   • Read DVC origin block via etl.files.ruamel_load (preserves comments if we ever write back) or just yaml.safe_load.
   • Load the garden dataset to get coverage:

     from owid.catalog import Dataset
     ds = Dataset("data/garden/<ns>/<ver>/<sn>")
     tb = ds.read("<table>", safe_types=False).reset_index()
     year_min, year_max = int(tb["year"].min()), int(tb["year"].max())
     n_countries = tb["country"].nunique()
     has_regions = tb["country"].isin(REGION_NAMES).any()  # use etl.helpers.regions

   • Year/country coverage: report the range, the distinct-country count, and whether OWID-defined regions are included. Flag if the most recent year has notably fewer countries than the overall median (sparse-recent caveat).

3. Query staging for affected published charts.

   make query SQL="
     SELECT COUNT(DISTINCT c.id) FROM charts c
     JOIN chart_configs cc ON cc.id = c.configId
     JOIN chart_dimensions cd ON cd.chartId = c.id
     JOIN variables v ON cd.variableId = v.id
     WHERE v.catalogPath LIKE '%<ns>/<ver>/<sn>%'
       AND c.publishedAt IS NOT NULL"

   • Map the count to a qualifier: 1–9 = "handful", 10–49 = "moderate", 50–199 = "large", 200+ = "massive".
   • Only count published charts. Drafts are excluded by design — the Slack audience cares about user-facing impact.

4. Seed the editorial fields with snippet bullets. For each of "why it matters", "caveats", "anything interesting": don't write prose, write 2–6 substantive bullets above an empty text fenced block. Bullets are clean reader-facing prose — no Snapshot description: / Garden description: prefixes in the output. Use those as internal source pointers only.

5. Pick chart views — rank by actual data change, not just our intuitions about which chart is "flagship".

   The picked views show up in the public announcement under "Here's what changed". If the underlying data didn't actually change between the old and new release, the announcement misrepresents the update. So the primary ranking signal is: for each published chart on the new dataset, how many of its y-variables have a different dataChecksum than the matching variable on the old dataset? This is the same signal Chart Diff uses.

   Run on staging (which has both old and new variables side-by-side):

   from etl.config import OWID_ENV
   from sqlalchemy import text
   import pandas as pd
   
   sql = """
   SELECT c.id AS chart_id, cc.slug,
          JSON_UNQUOTE(JSON_EXTRACT(cc.config, '$.title'))       AS title,
          JSON_UNQUOTE(JSON_EXTRACT(cc.config, '$.hasMapTab'))   AS has_map,
          JSON_UNQUOTE(JSON_EXTRACT(cc.config, '$.type'))        AS chart_type,
          SUM(v_new.dataChecksum <> v_old.dataChecksum)        AS n_changed,
          COUNT(*)                                             AS n_vars
   FROM charts c
   JOIN chart_configs cc        ON cc.id = c.configId
   JOIN chart_dimensions cd     ON cd.chartId = c.id
   JOIN variables v_new         ON cd.variableId = v_new.id
   LEFT JOIN variables v_old    ON v_old.shortName = v_new.shortName
                                AND v_old.datasetId = :old_dataset_id
   WHERE v_new.datasetId = :new_dataset_id
     AND c.publishedAt IS NOT NULL
     AND cd.property = 'y'
   GROUP BY c.id
   HAVING n_changed > 0
   ORDER BY n_changed DESC, has_map DESC
   LIMIT 30
   """
   with OWID_ENV.engine.connect() as conn:
       df = pd.read_sql(text(sql), conn, params={"old_dataset_id": OLD_ID, "new_dataset_id": NEW_ID})

   Resolve OLD_ID / NEW_ID from the datasets table by catalogPath.

   Then rank the data-changed subset by chart views. Among the charts whose data actually changed, prefer the ones readers actually look at — a chart with 246 views/day and 1 changed variable is a far better announcement candidate than a chart with 3 views/day and 18 changed variables:

   from etl.analytics.data import get_chart_views_last_n_days
   views = get_chart_views_last_n_days(chart_ids=df["chart_id"].astype(int).tolist(), n_days=30)
   df = df.merge(views[["chart_id", "views_daily"]], on="chart_id", how="left").fillna({"views_daily": 0})
   df = df.sort_values(["views_daily", "n_changed"], ascending=[False, False])

   Apply secondary tie-breakers within the top of that ranked list:

   • Prefer has_map = true — readers can find their own country.
   • Skip population-weighted variants and country-specific cuts.
   • If two charts tie on views but one has many y-vars changed (e.g. probability-of-dying-by-age with 18/18), that's a slightly stronger signal than "1 of 1 changed".

   A checksum change is not "gained the new year." A base-year rebase or in-place revision marks every chart data-changed, including charts whose series still end at the previous year (recipient/sector tables that only update in the producer's detailed release, series absent from a preliminary file). For releases that add a partial year, additionally verify each picked view's indicators actually reach the new year (metadata.json → dimensions.years), and prefer the chart carrying the release's headline measure — the one where the announcement's own numbers are visible on open — even when its traffic is low (the announcement is how it gets discovered).

   Reuse charts.selected_views from update-context.yml only if it's already been built using this views-then-checksum process (older runs picked views by intuition and produced misleading recommendations like "life expectancy now updated" when the data was effectively unchanged, or "malaria deaths" when the chart gets ~3 views a day).

   Output 1–3 as [<chart title>](<admin URL>) — <rationale that names the change>. Hyperlink each title to the admin editor URL, not the bare admin path:

   • Production: https://admin.owid.io/admin/charts/<id>/edit
   • Staging (new charts only): http://staging-site-<branch>/admin/charts/<id>/edit

   The bare /admin/charts/<id> path takes the reader to a non-existent route on production; /edit opens the chart editor where they can verify the change. If the chart already existed before this PR (published earlier than the branch was cut — c.publishedAt is older than the first branch commit), link to production. If the chart was first published on this branch, link to staging.

6. Build the search URL.

   The datasetProducts query parameter matches against the grapher datasets.name field (the dataset row's name column in MySQL — same value visible in the OWID admin's dataset list). When garden's gho.meta.yml sets a dataset.title override, that becomes datasets.name on upload. Otherwise it falls through to the snapshot origin's title.

   An update that spans several grapher datasets gets one link covering all of them: join each dataset's datasets.name with ~ (?datasetProducts=<name 1>~<name 2>, each part quote_plus-encoded). Resolve every name the same way as below — companion datasets often carry titles of their own that differ from the main one's.

   So the resolution order is:

   1. Garden .meta.yml → dataset.title (if set as override). This is the most common case — most large datasets carry a curated title like Global Health Observatory - World Health Organization or World Bank Poverty and Inequality Platform (PIP).
   2. Snapshot .dvc → meta.origin.title (fallback).

   Resolve the value programmatically rather than guessing — query the grapher DB or read the garden YAML directly:

   # Most reliable: grab the literal value from the prod-mirror grapher DB
   make query SQL="SELECT name FROM datasets WHERE catalogPath = 'grapher/<ns>/<v>/<short>'"
   
   # Or from the garden YAML
   from etl.files import ruamel_load
   meta = ruamel_load("etl/steps/data/garden/<ns>/<v>/<short>.meta.yml")
   title = (meta.get("dataset") or {}).get("title")  # falls through if missing

   Build the URL with urllib.parse.quote_plus(title) so spaces become + and parens become %28/%29. NOT the bare producer field — that mismatches the index. And don't put the producer's homepage URL here, even as a fallback — the Slack template specifically wants this search URL form.

   Important caveat: the search index bakes asynchronously after a PR merge. A correctly-formed URL can still return zero results for a few hours post-merge while the index re-bakes. If the user reports "no results", double-check the URL value against datasets.name first; if it matches, tell them to retry in a couple of hours.

7. Best-effort next-release date.

   • Fetch url_main (WebFetch) and extract any phrase like "next release", "annual update", "updated yearly" near the page header. Tag the answer [verify] so the user knows to confirm.
   • If nothing extractable, output [unknown — please confirm with the producer's release schedule].

8. Write the draft.

   • Output path: ai/data-update-comms.md by default, or workbench/<short_name>/slack-announcement.md when invoked from update-dataset step 9.
   • Use the canonical format in the Output format section below — no example lines, no [filled] / [prompt] tags, no inline instructions. If a field can't be filled mechanically, write [missing — <what's needed>] inside the fenced block and stop.

9. Show the user the file path and stop. In a git worktree, give the absolute path: a relative link opens the main checkout's copy, which may be a previous update's draft. Do not post to Slack — that's a human action. The user copy-pastes from the Markdown file into the Slack form.