b18_market_watch.qmd

b18_market_watch.qmd

---
title: "Market Watch data"
---

```{r}
#| label: setup
#| message: false
library(tidyverse)
library(bigrquery)
library(DBI)

# Connect to GBQ with billing project from SUB Göttingen,
# You have to use your own :-)
bq_con <- dbConnect(
  bigrquery::bigquery(),
  project = "subugoe-collaborative",
  dataset = "openalex",
  billing = "subugoe-collaborative"
)
```

Data gathering

- Crossref as a basis to get more normalised publisher names and avoid multiple versions from OpenAlex

```sql
CREATE OR REPLACE TABLE `subugoe-collaborative.oa2020.b18_raw` AS
WITH
    basis_cr AS (
      SELECT DISTINCT
        doi,
        publisher,
        DATE(CAST(issued AS TIMESTAMP)) AS publication_date,
        EXTRACT(YEAR FROM CAST(issued AS TIMESTAMP)) AS cr_year,
        MAX(CASE
          WHEN lic.content_version != 'am'
            AND REGEXP_CONTAINS(lic.url, r'(?i)creativecommons')
          THEN 'cc'
          ELSE NULL
        END) OVER (PARTITION BY doi) AS cc
      FROM
        `subugoe-collaborative.cr_instant.snapshot` AS cr,
        UNNEST(license) AS lic
      WHERE
        type = 'journal-article'
        AND DATE(CAST(issued AS TIMESTAMP)) BETWEEN '2017-01-01' AND '2025-12-31'
        AND (
          NOT REGEXP_CONTAINS(issue, '^[a-zA-Z]')
          OR issue IS NULL)
        AND NOT REGEXP_CONTAINS(title, '[0-9]{3} pp.')
    ),
    cr AS (
      SELECT DISTINCT doi, publisher, publication_date, cr_year, cc
      FROM basis_cr
    ),
    oalex_filtered AS (
      SELECT DISTINCT
        oalex.doi,
        primary_location.source.issn_l AS issn_l,
        primary_location.source.is_in_doaj AS is_in_doaj,
        open_access.oa_status AS oa_status,
        country AS country_code,
        au.is_corresponding,
        au.author_position,
        inst.ror
      FROM `subugoe-collaborative.openalex_walden.works` AS oalex
      LEFT JOIN UNNEST(authorships) AS au WITH OFFSET AS pos
      LEFT JOIN UNNEST(au.countries) AS country
      LEFT JOIN UNNEST(au.institutions) AS inst
      WHERE
        is_xpac = FALSE
        AND is_paratext = FALSE
        AND publication_year BETWEEN 2016 AND 2026
        AND primary_location.source.type = "journal"
    )
  SELECT
    DISTINCT
    cr.doi,
    cr.publisher,
    cr.cr_year,
    cr.publication_date,
    cr.cc,
    oa.issn_l,
    oa.is_in_doaj,
    oa.oa_status,
    oa.is_corresponding,
    oa.author_position,
    oa.country_code,
    oa.ror
  FROM cr
  INNER JOIN oalex_filtered AS oa
    ON cr.doi = oa.doi
```

## Estimate transformative agreement coverage

Match articles from `b18_raw` to Journal Checker Tool (JCT) transformative
agreements. An article-author row is TA-matched when (i) the journal
(`issn_l`) is part of an agreement, (ii) the author's institution (`ror`,
including ROR variants derived from OpenAlex associated institutions)
participates in the same agreement (`esac_id`), and (iii) the publication
date falls within the agreement period. Only first or corresponding
authors and only gold/hybrid articles are retained.

```{sql connection="bq_con"}
CREATE OR REPLACE TABLE `subugoe-collaborative.oa2020.b18_jct_articles_b18`
AS (
  WITH
    obtain_associated_ror_ids AS (
      SELECT esac_id, jct_inst.ror_id AS ror_jct, inst.ror AS ror_associated
      FROM `subugoe-collaborative.openbib.jct_institutions` AS jct_inst
      LEFT JOIN `subugoe-collaborative.openalex.institutions` AS oalex_inst
        ON jct_inst.ror_id = oalex_inst.ror
      LEFT JOIN UNNEST(oalex_inst.associated_institutions) AS inst
    ),
    create_matching_table AS (
      SELECT esac_id, 'ror_jct' AS ror_type, ror_jct AS ror
      FROM obtain_associated_ror_ids
      UNION ALL
      SELECT esac_id, 'ror_associated' AS ror_type, ror_associated AS ror
      FROM obtain_associated_ror_ids
    ),
    enriched_ror_variants AS (
      SELECT DISTINCT
        create_matching_table.esac_id,
        create_matching_table.ror_type,
        create_matching_table.ror,
        DATE(jct_inst.start_date) AS start_date,
        DATE(jct_inst.end_date) AS end_date
      FROM create_matching_table
      INNER JOIN `subugoe-collaborative.openbib.jct_esac` AS jct_inst
        ON create_matching_table.esac_id = jct_inst.id
    ),
    journal_agreements AS (
      SELECT j.issn_l, e.id AS esac_id
      FROM `subugoe-collaborative.openbib.jct_journals` AS j
      INNER JOIN `subugoe-collaborative.openbib.jct_esac` AS e
        ON j.esac_id = e.id
    )
  SELECT DISTINCT
    md.doi,
    md.publisher,
    md.issn_l AS matching_issn_l,
    md.ror AS matching_ror,
    md.author_position,
    md.is_corresponding,
    md.country_code AS author_country,
    md.is_in_doaj,
    md.oa_status,
    erv.ror_type,
    erv.esac_id,
    erv.start_date,
    erv.end_date,
    md.publication_date
  FROM `subugoe-collaborative.oa2020.b18_raw` AS md
  INNER JOIN journal_agreements AS ja
    ON md.issn_l = ja.issn_l
  INNER JOIN enriched_ror_variants AS erv
    ON md.ror = erv.ror AND ja.esac_id = erv.esac_id
  WHERE
    md.ror IS NOT NULL
    AND md.issn_l IS NOT NULL
    AND (md.author_position = 'first' OR md.is_corresponding = TRUE)
    AND (md.publication_date >= erv.start_date OR erv.start_date IS NULL)
    AND (md.publication_date <= erv.end_date OR erv.end_date IS NULL)
    AND md.oa_status IN ('gold', 'hybrid')
)
```

## Analytical queries

Conventions used throughout:

- Article counts are always `COUNT(DISTINCT doi)` because `b18_raw` is at
  author–institution level, so one article can span multiple rows.
- OA categories: `gold (in DOAJ)`, `gold (not in DOAJ)`, `hybrid`.
- `ta_covered` flags whether the DOI appears in
  `b18_jct_articles_b18` with a match for the *same author role*
  (corresponding-author analyses use corresponding-author TA matches,
  first-author analyses use first-author matches).
- `total_articles` is the number of distinct articles per grouping unit
  *without* any OA status filter (i.e. including closed, bronze, green),
  providing the denominator for shares of total output.
- Caveats: an article with corresponding authors in several countries is
  counted once per country, so country sums exceed unique articles; rows
  with `author_country IS NULL` do not join to the totals subquery
  (NULL never equals NULL); the latest year of the snapshot may be
  incomplete.

### Country by year, OA status and TA coverage (corresponding authors)

```{sql connection="bq_con", output.var="country_year_corresponding"}
SELECT
  c.author_country,
  c.cr_year,
  c.oa_category,
  c.ta_covered,
  c.n_articles,
  t.total_articles
FROM (
  SELECT
    md.country_code AS author_country,
    md.cr_year,
    CASE
      WHEN md.oa_status = 'gold' AND md.is_in_doaj THEN 'gold (in DOAJ)'
      WHEN md.oa_status = 'gold' THEN 'gold (not in DOAJ)'
      WHEN md.oa_status = 'hybrid' THEN 'hybrid'
    END AS oa_category,
    ta.doi IS NOT NULL AS ta_covered,
    COUNT(DISTINCT md.doi) AS n_articles
  FROM `subugoe-collaborative.oa2020.b18_raw` AS md
  LEFT JOIN (
    SELECT DISTINCT doi
    FROM `subugoe-collaborative.oa2020.b18_jct_articles_b18`
    WHERE is_corresponding = TRUE
  ) AS ta
    ON md.doi = ta.doi
  WHERE
    md.is_corresponding = TRUE
    AND md.oa_status IN ('gold', 'hybrid')
  GROUP BY author_country, cr_year, oa_category, ta_covered
) AS c
LEFT JOIN (
  SELECT
    country_code AS author_country,
    cr_year,
    COUNT(DISTINCT doi) AS total_articles
  FROM `subugoe-collaborative.oa2020.b18_raw`
  WHERE is_corresponding = TRUE
  GROUP BY author_country, cr_year
) AS t
  ON c.author_country = t.author_country AND c.cr_year = t.cr_year
ORDER BY c.author_country, c.cr_year, c.oa_category, c.ta_covered
```

Persisted in BigQuery as
`subugoe-collaborative.oa2020.b18_jct_country_year_oa_counts`.

### Percentage view

Two shares per row: the cell's share of the country's total gold+hybrid
output that year, and the TA vs non-TA split within each
country–year–OA-category.

```{sql connection="bq_con", output.var="country_year_pct"}
SELECT
  author_country,
  cr_year,
  oa_category,
  ta_covered,
  n_articles,
  ROUND(100 * n_articles / SUM(n_articles)
    OVER (PARTITION BY author_country, cr_year), 2) AS pct_of_country_year,
  ROUND(100 * n_articles / SUM(n_articles)
    OVER (PARTITION BY author_country, cr_year, oa_category), 2) AS pct_within_category
FROM `subugoe-collaborative.oa2020.b18_jct_country_year_oa_counts`
ORDER BY author_country, cr_year, oa_category, ta_covered
```

### Country by year, OA status and TA coverage (first authors)

Mirror of the corresponding-author query for first authors, used to check
the robustness of TA attribution. Both methods agree closely for
agreement countries; they diverge mainly for articles whose first and
corresponding authors sit in different countries.

```{sql connection="bq_con", output.var="country_year_first"}
WITH ta_first AS (
  SELECT DISTINCT doi
  FROM `subugoe-collaborative.oa2020.b18_jct_articles_b18`
  WHERE author_position = 'first'
),
counts AS (
  SELECT
    md.country_code AS author_country,
    md.cr_year,
    CASE
      WHEN md.oa_status = 'gold' AND md.is_in_doaj THEN 'gold (in DOAJ)'
      WHEN md.oa_status = 'gold' THEN 'gold (not in DOAJ)'
      WHEN md.oa_status = 'hybrid' THEN 'hybrid'
    END AS oa_category,
    t.doi IS NOT NULL AS ta_covered,
    COUNT(DISTINCT md.doi) AS n_articles
  FROM `subugoe-collaborative.oa2020.b18_raw` AS md
  LEFT JOIN ta_first AS t ON md.doi = t.doi
  WHERE md.author_position = 'first' AND md.oa_status IN ('gold', 'hybrid')
  GROUP BY author_country, cr_year, oa_category, ta_covered
),
totals AS (
  SELECT country_code AS author_country, cr_year, COUNT(DISTINCT doi) AS total_articles
  FROM `subugoe-collaborative.oa2020.b18_raw`
  WHERE author_position = 'first'
  GROUP BY author_country, cr_year
)
SELECT
  c.author_country,
  c.cr_year,
  c.oa_category,
  c.ta_covered,
  c.n_articles,
  t.total_articles
FROM counts AS c
LEFT JOIN totals AS t
  ON c.author_country = t.author_country AND c.cr_year = t.cr_year
ORDER BY c.author_country, c.cr_year, c.oa_category, c.ta_covered
```

### Publisher by year, OA status and TA coverage (corresponding authors)

Top publishers kept as-is (Crossref member names from `b18_raw`),
everything else grouped as `Other`. Note that publisher name assignment
in Crossref changes over time (imprint consolidation), which explains
jumps in some publishers' early-year totals.

```{sql connection="bq_con", output.var="publisher_year"}
WITH ta_corr AS (
  SELECT DISTINCT doi
  FROM `subugoe-collaborative.oa2020.b18_jct_articles_b18`
  WHERE is_corresponding = TRUE
),
base AS (
  SELECT
    doi,
    cr_year,
    oa_status,
    is_in_doaj,
    CASE WHEN publisher IN (
      'Elsevier BV',
      'Springer Science and Business Media LLC',
      'Wiley',
      'MDPI AG',
      'American Chemical Society (ACS)',
      'Frontiers Media SA',
      'Oxford University Press (OUP)',
      'Informa UK Limited',
      'SAGE Publications',
      'American Physical Society (APS)',
      'Walter de Gruyter GmbH',
      'Institute of Electrical and Electronics Engineers (IEEE)',
      'IOP Publishing',
      'Royal Society of Chemistry (RSC)',
      'Cambridge University Press (CUP)',
      'Copernicus GmbH',
      'Public Library of Science (PLoS)',
      'EDP Sciences',
      'S. Karger AG',
      'AIP Publishing',
      'BMJ',
      'Ovid Technologies (Wolters Kluwer Health)',
      'Hogrefe Publishing Group',
      'The Royal Society',
      'The Electrochemical Society',
      'Optica Publishing Group',
      'Association for Computing Machinery (ACM)',
      'Emerald'
    ) THEN publisher ELSE 'Other' END AS publisher_group
  FROM `subugoe-collaborative.oa2020.b18_raw`
  WHERE is_corresponding = TRUE
),
counts AS (
  SELECT
    b.publisher_group,
    b.cr_year,
    CASE
      WHEN b.oa_status = 'gold' AND b.is_in_doaj THEN 'gold (in DOAJ)'
      WHEN b.oa_status = 'gold' THEN 'gold (not in DOAJ)'
      WHEN b.oa_status = 'hybrid' THEN 'hybrid'
    END AS oa_category,
    t.doi IS NOT NULL AS ta_covered,
    COUNT(DISTINCT b.doi) AS n_articles
  FROM base AS b
  LEFT JOIN ta_corr AS t ON b.doi = t.doi
  WHERE b.oa_status IN ('gold', 'hybrid')
  GROUP BY publisher_group, cr_year, oa_category, ta_covered
),
totals AS (
  SELECT publisher_group, cr_year, COUNT(DISTINCT doi) AS total_articles
  FROM base
  GROUP BY publisher_group, cr_year
)
SELECT
  c.publisher_group,
  c.cr_year,
  c.oa_category,
  c.ta_covered,
  c.n_articles,
  t.total_articles
FROM counts AS c
LEFT JOIN totals AS t
  ON c.publisher_group = t.publisher_group AND c.cr_year = t.cr_year
ORDER BY c.publisher_group, c.cr_year, c.oa_category, c.ta_covered
```

### Publisher by country, year, OA status and TA coverage (corresponding authors)

Same as above, additionally split by corresponding-author country.

```{sql connection="bq_con", output.var="publisher_country_year"}
WITH ta_corr AS (
  SELECT DISTINCT doi
  FROM `subugoe-collaborative.oa2020.b18_jct_articles_b18`
  WHERE is_corresponding = TRUE
),
base AS (
  SELECT
    doi,
    country_code,
    cr_year,
    oa_status,
    is_in_doaj,
    CASE WHEN publisher IN (
      'Elsevier BV',
      'Springer Science and Business Media LLC',
      'Wiley',
      'MDPI AG',
      'American Chemical Society (ACS)',
      'Frontiers Media SA',
      'Oxford University Press (OUP)',
      'Informa UK Limited',
      'SAGE Publications',
      'American Physical Society (APS)',
      'Walter de Gruyter GmbH',
      'Institute of Electrical and Electronics Engineers (IEEE)',
      'IOP Publishing',
      'Royal Society of Chemistry (RSC)',
      'Cambridge University Press (CUP)',
      'Copernicus GmbH',
      'Public Library of Science (PLoS)',
      'EDP Sciences',
      'S. Karger AG',
      'AIP Publishing',
      'BMJ',
      'Ovid Technologies (Wolters Kluwer Health)',
      'Hogrefe Publishing Group',
      'The Royal Society',
      'The Electrochemical Society',
      'Optica Publishing Group',
      'Association for Computing Machinery (ACM)',
      'Emerald'
    ) THEN publisher ELSE 'Other' END AS publisher_group
  FROM `subugoe-collaborative.oa2020.b18_raw`
  WHERE is_corresponding = TRUE
),
counts AS (
  SELECT
    b.publisher_group,
    b.country_code AS author_country,
    b.cr_year,
    CASE
      WHEN b.oa_status = 'gold' AND b.is_in_doaj THEN 'gold (in DOAJ)'
      WHEN b.oa_status = 'gold' THEN 'gold (not in DOAJ)'
      WHEN b.oa_status = 'hybrid' THEN 'hybrid'
    END AS oa_category,
    t.doi IS NOT NULL AS ta_covered,
    COUNT(DISTINCT b.doi) AS n_articles
  FROM base AS b
  LEFT JOIN ta_corr AS t ON b.doi = t.doi
  WHERE b.oa_status IN ('gold', 'hybrid')
  GROUP BY publisher_group, author_country, cr_year, oa_category, ta_covered
),
totals AS (
  SELECT
    publisher_group,
    country_code AS author_country,
    cr_year,
    COUNT(DISTINCT doi) AS total_articles
  FROM base
  GROUP BY publisher_group, author_country, cr_year
)
SELECT
  c.publisher_group,
  c.author_country,
  c.cr_year,
  c.oa_category,
  c.ta_covered,
  c.n_articles,
  t.total_articles
FROM counts AS c
LEFT JOIN totals AS t
  ON c.publisher_group = t.publisher_group
  AND c.author_country = t.author_country
  AND c.cr_year = t.cr_year
ORDER BY c.publisher_group, c.author_country, c.cr_year, c.oa_category, c.ta_covered
```

### Export

```{r}
#| label: export
#| eval: false
write_csv(country_year_corresponding, "data/ta_articles_by_country_year_oa_status.csv")
write_csv(country_year_pct, "data/ta_articles_by_country_year_oa_status_pct.csv")
write_csv(country_year_first, "data/ta_articles_by_country_year_oa_status_first_authors.csv")
write_csv(publisher_year, "data/ta_articles_by_publisher_year_oa_status.csv")
write_csv(publisher_country_year, "data/ta_articles_by_publisher_country_year_oa_status.csv")
```