Documentation

Green Compliance Intelligence — Data Endeavour Ltd

Overview

Green Compliance Intelligence delivers AI-enriched environmental compliance data covering 3.1M regulated facilities across the US, UK, and EU. Raw regulatory data is sourced from public registries, normalised to a consistent schema, and enriched with deterministic risk scores, AI-assisted entity resolution, and industry classification.

The dataset is updated weekly and delivered as five curated read-only views via Google Analytics Hub. Subscribers access the data as a linked dataset directly in their own GCP project — no ETL or data transfer required.

Access & setup

The dataset is delivered via Google Analytics Hub, which is part of Google Cloud. If you already use GCP, skip to step 3. If not, setup takes about five minutes and costs nothing — you only pay for BigQuery queries you run, which for typical usage is pennies per month.

Step 1 — Create a Google Cloud account

Go to console.cloud.google.com and sign in with any Google account (Gmail or Google Workspace). If this is your first time, Google will prompt you to activate Cloud — you'll need to enter a credit card, but there is a free tier and you will not be charged for simply subscribing to this dataset.

Step 2 — Create a project

In the top bar, click the project selector (it may say "Select a project") and choose New Project. Give it a name (e.g. compliance-data) and click Create. This is the container your linked dataset will live in.

Step 3 — Subscribe via Analytics Hub

Contact us at info@dataendeavour.co.uk to request access. Once confirmed, you will receive a direct link to the Analytics Hub listing. Open it, click Add dataset to project, select the project you created, and confirm. The five views will appear immediately in your BigQuery console under a linked dataset in your project.

Step 4 — Open BigQuery and run your first query

Go to console.cloud.google.com/bigquery. In the left-hand panel, expand your project and find the linked dataset. Click any view to see its schema. Click Query to open an editor — a sample SELECT is pre-populated. Click Run to see results.

Exporting data

Two options are available depending on how you want to work with the data. Neither requires writing any code or leaving your browser.

Option A — Google Sheets (recommended for most users)

Connected Sheets lets you pull BigQuery data directly into a Google Sheet and refresh it on demand — no SQL required beyond the initial query. This is the easiest path if you work in spreadsheets and want to filter, pivot, or chart the data alongside your own files.

1. Open Google Sheets and create a new spreadsheet.
2. In the menu bar, click Data → Data Connectors → Connect to BigQuery.
3. Select your GCP project, then find the linked dataset and choose a view (e.g. v_global_compliance_risk).
4. Click Connect. Sheets will load a preview — click Apply to import the data.
5. To filter before importing (recommended for large views), click Edit query and add a WHERE clause — for example WHERE country_code = 'GBR' to limit to UK facilities.
6. Once imported, click Refresh at any time to pull the latest weekly data.

Option B — CSV download from the BigQuery console

If you want a flat file to open in Excel or load into another tool, you can download query results directly from the BigQuery console as a CSV.

1. Go to console.cloud.google.com/bigquery and open a new query tab.
2. Write or paste a query (use the sample queries below as a starting point). Include a WHERE clause to scope the results — for example by country, risk category, or company name.
3. Click Run. Results appear in the panel below the editor.
4. Click Save results → CSV (local file). The file downloads immediately.

For result sets larger than around 16,000 rows, use Save results → Google Sheets instead, or export to Google Drive via Save results → Google Drive (CSV).

Sample queries

The examples below can be pasted directly into the BigQuery console. Replace your-gcp-project and your_linked_dataset with your project ID and the name you gave the linked dataset at subscription.

Screen Critical facilities in a country

View: v_global_compliance_risk

SELECT
  regulatory_id,
  facility_name,
  parent_company_name,
  city,
  predicted_industry,
  predicted_pollutant,
  severity_score,
  risk_category
FROM `your-gcp-project.your_linked_dataset.v_global_compliance_risk`
WHERE country_code = 'GBR'          -- ISO 3166-1 alpha-3: USA, GBR, DEU, FRA, ESP, ITA …
  AND operational_priority = 'Critical'
ORDER BY severity_score DESC
LIMIT 100;

Look up a company and retrieve its full compliance footprint

Views: v_dim_global_companies joined to v_global_compliance_risk

SELECT
  co.entity_name,
  co.business_status,
  co.is_lei_verified,
  cr.facility_name,
  cr.city,
  cr.country_code          AS facility_country,
  cr.risk_category,
  cr.severity_score,
  cr.operational_priority
FROM `your-gcp-project.your_linked_dataset.v_dim_global_companies` co
LEFT JOIN `your-gcp-project.your_linked_dataset.v_global_compliance_risk` cr
  ON UPPER(co.entity_name) = UPPER(cr.parent_company_name)
WHERE UPPER(co.entity_name) LIKE '%WASTE MANAGEMENT%'
ORDER BY cr.severity_score DESC NULLS LAST;

Rank the top EU pollutants for a given year

View: v_agg_emissions_summary

SELECT
  pollutant_name,
  ROUND(SUM(total_emissions) / 1e9, 2)   AS total_million_tonnes,
  COUNT(DISTINCT country_name)            AS countries_reporting,
  SUM(total_records)                      AS contributing_facilities
FROM `your-gcp-project.your_linked_dataset.v_agg_emissions_summary`
WHERE reporting_year = 2023
GROUP BY pollutant_name
ORDER BY total_million_tonnes DESC
LIMIT 10;

Find high-risk facilities within a geographic bounding box

View: v_geospatial_hotspots — example bounding box: Greater London

SELECT
  facility_name,
  parent_company_name,
  clean_city,
  latitude,
  longitude,
  risk_category,
  severity_score
FROM `your-gcp-project.your_linked_dataset.v_geospatial_hotspots`
WHERE latitude  BETWEEN 51.3 AND 51.7
  AND longitude BETWEEN -0.5 AND 0.3
  AND severity_score >= 5
ORDER BY severity_score DESC;

Due diligence screen — compare corporate parents by risk score

View: v_corporate_risk_summary

SELECT
  parent_company_name,
  total_facilities_managed,
  ROUND(avg_corporate_risk_score, 2)  AS avg_risk_score,
  highest_individual_facility_risk,
  critical_alert_count,
  watchlist_count,
  portfolio_pollutant_mix
FROM `your-gcp-project.your_linked_dataset.v_corporate_risk_summary`
WHERE parent_company_name IN (
  'Waste Management',
  'Arch Resources, Inc.',
  'Republic Services',
  'Clean Harbors'
)
ORDER BY avg_corporate_risk_score DESC;

v_global_compliance_risk

Source: US EPA ECHO, UK Environment Agency (permits + enforcement), EEA E-PRTR
Grain: One row per regulated facility
Coverage: USA (99%), UK, EU (DE, ES, FR, IT, PL, CZ, HU, DK, and others)

Risk category distribution

Known caveats

parent_company_name and matching_confidence are NULL for ~7% of rows — facilities where no corporate parent could be resolved (EEA E-PRTR facilities are excluded from entity resolution by design). USA accounts for the majority of records due to EPA ECHO scale. city quality is lower for EU records from EEA E-PRTR.

v_dim_global_companies

Source: GLEIF (Global LEI Foundation), UK Companies House
Grain: One row per legal entity
Coverage: Global — 3.3M entities across all jurisdictions

Known caveats

99.99% of records are from GLEIF. UK Companies House records are supplementary. country_code is null for a small number of GLEIF records where the source jurisdiction field is not populated.

v_agg_emissions_summary

Source: EEA E-PRTR (European Pollutant Release and Transfer Register)
Grain: One row per country + pollutant + reporting year
Coverage: EU member states, years 2007–2024

Known caveats

Full EU E-PRTR dataset (~372K facility records) aggregated to 13.6K rows. total_emissions units are kilograms as reported — not normalised across substances. 2024 data is partial (facilities report with a ~12-month lag).

v_geospatial_hotspots

Source: US EPA ECHO, UK Environment Agency, EEA E-PRTR (coordinates via GeoNames postcode lookup)
Grain: One row per regulated facility with resolved coordinates
Coverage: 3.1M facilities globally — 100% coordinate coverage

Known caveats

Coordinates are postcode centroid-level, not facility-level. Precision is ±500m to ±5km depending on postcode area density. GeoNames coverage spans 21 countries. parent_company_name is NULL for ~7% of rows — see entity resolution note.

v_corporate_risk_summary

Grain: One row per resolved corporate parent entity
Coverage: 944K entities — one row per resolved corporate parent

This view provides a rolled-up risk profile per corporate parent, aggregating facility-level compliance data across all subsidiaries and operating sites where a parent entity has been resolved.

Risk score methodology — overview

The dataset produces four types of enrichment. The majority is deterministic and fully auditable. Gemini LLM is used only as a fallback in entity resolution.

Risk category and severity score

Columns: risk_category, severity_score, operational_priority

Produced by deterministic SQL rules applied to compliance status data from source registries (primarily US EPA ECHO). Fully transparent and auditable. No AI involved.

Risk category mapping

Severity score (1–10)

Operational priority

Limitations

Compliance status reflects what has been reported to the regulator, not independently verified. UK EA and EEA facilities have less granular violation count data than EPA ECHO — scores for these facilities may be less precise. A score of 4 (Status Unknown) does not indicate a compliant facility; it indicates insufficient data to assess compliance.

Industry and pollutant classification

Columns: predicted_industry, predicted_pollutant

Produced by deterministic rules applied in priority order:

1. EEA E-PRTR sector code (1=Energy, 2=Metals, 3=Minerals, 4=Chemical, 5=Waste, 6=Paper, 7=Agriculture, 8=Food)
2. UK EA activity type keyword matching
3. Facility name keyword matching (fallback for all sources without sector codes)

Industry categories: Energy & Power, Metal Production, Mineral Industry, Chemical Industry, Waste Management, Paper & Pulp, Agriculture, Food & Beverage, Oil & Gas, Other Industrial.

Limitations

Classification is best-effort inference with no manual review. Facility name keyword matching (the fallback) is less reliable than structured sector codes. ~0.002% of facilities have no facility name and cannot be classified.

Corporate parent linkage — entity resolution

Column: parent_company_name (in v_global_compliance_risk and v_geospatial_hotspots)

This is the only enrichment step that uses a large language model. Matching is attempted in three tiers:

Tier 1 — Exact SQL match

Facility name compared directly against the GLEIF Golden Copy LEI registry after trimming and uppercasing, matched on name and country code. Highest-confidence match.

Tier 2 — Cleaned SQL match

Legal suffixes (LLC, INC, LTD, PLC, CO, CORP) and punctuation stripped from both sides before comparison, matched on country code.

Tier 3 — Gemini 2.5 Flash (LLM fallback)

For facilities that fail Tiers 1 and 2, Gemini 2.5 Flash is prompted to identify the corporate parent and return the GLEIF LEI code where known. Responses are returned as structured JSON and parsed. EEA E-PRTR facilities are excluded from entity resolution entirely as they do not carry company-level identifiers.

Limitations

Gemini inferences are not guaranteed to be correct and should be verified for high-stakes use cases. Name matching across jurisdictions is imperfect for facilities operating under trading names or subsidiary names. Private companies without LEIs may not be resolvable.

Known data issue — current dataset

The existing 3.1M entity resolution records were all produced by Tier 3 (Gemini LLM). A country code format mismatch between the internal pipeline and the GLEIF reference data caused Tiers 1 and 2 to silently produce no matches during the initial enrichment run. This was identified and fixed in June 2026; new facilities added in future weekly pipeline runs will be processed through all three tiers in sequence. The entity_resolution_method column in v_global_compliance_risk reflects the actual method used for each record.

Geocoordinates

Columns: latitude, longitude (in v_geospatial_hotspots)

Derived from a GeoNames postcode lookup table (1.8M entries covering UK, US, and EU postcodes). Facilities are matched on postal code and country code. Where multiple entries exist for the same postcode, the highest-accuracy entry is used. Approximately 2% of facilities cannot be geocoded and are absent from v_geospatial_hotspots.

Data sources

Update cadence

Data licences & attribution

All underlying data sources permit commercial redistribution. Required attributions when publishing derived work: