Create Scope3 API Integration

create or replace api integration scope3_api
    api_provider = google_api_gateway
    google_audience = ''
    api_allowed_prefixes = ('https://scope3-snowflake-api-gateway-5qbjzj9o.uc.gateway.dev')
    enabled = true;

Create Scope3 External Function

Function Call

create or replace external function scope3_v2(
  impressions int,
  date date,
  inventory_id string,
  device_type string,
  channel string,
  country string,
  region string,
  creative_format string,
  payload_size int,
  duration_seconds int,
  network string,
  seller string,
  buying_method string,
  deal_id string)
    returns OBJECT
    api_integration = scope3_api
    headers = (
        'AccessClientId'='****',
        'AccessClientSecret'='****'
    )
    as 'https://scope3-snowflake-api-gateway-5qbjzj9o.uc.gateway.dev/api/v1/snowflake';

Field	required	Description	Example
impressions	✅	Sum of impressions for these dimensions	10000
date	✅	Dates must be in the past (i.e. prior to today). Dates should be in the UTC timezone in one of the following formats: `YYYY-MM-DD`, `YYYYMMDD`, or `YYYY/MM/DD`	2023-04-01
inventory_id		The inventory ID field is an alternative to site.domain and app.storeId. This field can accept either a domain or app ID. When channel is not specified, we will use inventoryId to make an informed guess.	nytimes.com
device_type		The consumer’s device: `phone`, `tablet`, `pc`, or `tv`. If not provided, we’ll infer the device type from the the channel or the device field.	pc
channel		Options: display-app, display-web, streaming-video, social `display-web`: Display ads, including outstream video, on a website `display-app`: Display ads, including outstream video, on a mobile app. Excludes streaming and social apps `streaming-video`: Streaming video including CTV and YouTube. Includes in app or browser based streaming. Currently in a closed beta. `social`: Includes in app or browser based social properties. Currently in closed beta	display-web
country		This is the country ISO 3166-2 alpha-2 code. e.g. US	US
region		The 2 or 3 letter region code - only supported for Canada, US, and Australia	NY
creative_format		Options: `banner`, `video`, `text` Banner uses payload size and conventional model. Video uses duration and power model. Text returns 0	video
payload_size		The number of bytes transferred to fully display the creative. Only used for banner and video format.	3024
duration_seconds		Time the creative plays. Only used for video format.	15
network		options: `fixed`, `mobile` The networking connection used by the end user's device.	fixed
seller		Name of the seller entity that sold the media	xandr
buying_method		options: `programmatic-open`, `programmatic-pmp`, `programmatic-guaranteed`, `direct`, `direct-takeover` The method used to buy the impressions	programmatic-open
deal_id		Seller deal identifier	deal-1234

Response

The function returns the following object. Each field may be missing/null depending if the data is available.

If total_emissions and error is null then scope3 does noes not have information for this impression.

{
  "ad_selection_emissions": "<float|null>",
  "domain_coverage": "<modeled|missing|unknown>",
  "gmp_eligible": "<true|false>",
  "matched_channel": "<display-web|display-app|streaming-video|social>",
  "matched_property": "<string>",
  "media_distribution_emissions": "<float>",
  "supply_directness": "<int>",
  "total_emissions": "<float>",
  "error": "<string|null>"
}

Field	Description	Example
ad_selection_emissions	Ad tech including servers and cloud computing; analytics; network traffic; storage; data providers; and vendor overhead. In grams of CO₂e.	300.99
domain_coverage	modeled - domains/apps are in the dataset with reasonable confidence of accuracy. missing - domains/apps have not been modeled yet and will automatically be queued for analysis. unknown - domains/apps appear to have errors. e.g. A site that has an invalid domain name.	modeled
gmp_eligible	Impressions are GMP eligible if the gCO₂e ad selection emissions per impression is lower than benchmark emissions data for the associated country and channel. If benchmarks are unavailable for a specific country then worldwide benchmarks will be used. [GMP sellers only]	true
matched_channel	Creative Emissions in gCO2e	100.33
matched_property	Modeled domains are in the dataset with reasonable confidence of accuracy. Missing domains have not been modeled yet and will automatically be queued for analysis.	modeled
media_distribution_emissions	Media delivery including CMS; CDNs and hosting services; publishing overhead including employee and office expenses; data transfer. In grams of CO₂e.	50.2
supply_directness	Maximum depth of ad selection path to the seller based on buying method [Programmatic Insights customers only]	3
total_emissions	Total footprint of the campaign (sum of above) in grams of CO₂e.	400.2
error	null / missing if no error. If error this will contain a string error message	null

Example Usage

Select a single Example Row

SELECT scope3_v2(
  1000, -- impressions
  DATE('2023-04-20'), -- date
  'nytimes.com', -- inventory_id
  'pc', -- device_type
  null, -- channel
  'US', -- country
  'NY', -- region
  'banner', -- creative_format
  null, -- payload_size
  null, --duration_seconds
  'fixed', -- network
  null, -- seller
  null, -- buying_method
  'deal-1234', -- deal_id
);

Real life usage

SELECT
	d.app_url,
	d.country,
	d.impressions,
  SELECT scope3_v2(
  	d.impressions, -- impressions
  	d.date, -- date
  	d.app_url, -- inventory_id
  	d.device_type, -- device_type
  	null, -- channel
  	d.country, -- country
  	null, -- region
  	null, -- creative_format
  	null, -- payload_size
  	null, --duration_seconds
  	null, -- network
    null, -- seller
  	null, -- buying_method
  	null, -- deal_id
	):total_emissions
FROM
	delivery_data d;

How to extract single fields

SELECT
	res:total_emissions,
	res:base_emissions,
	res:supply_path_emissions,
	res:creative_emissions
FROM
	(
    SELECT scope3_v2(
      1000, -- impressions
      DATE('2023-04-20'), -- date
      'nytimes.com', -- inventory_id
      'pc', -- device_type
      null, -- channel
      'US', -- country
      'NY', -- region
      'banner', -- creative_format
      null, -- payload_size
      null, --duration_seconds
      'fixed', -- network
      null, -- seller
      null, -- buying_method
      'deal-1234', -- deal_id
		) res
  )
;

Dealing with errors

If your use case requires special handling for errors, you can use IFF and IS_NULL_VALUE.

SELECT
	parse_json('{error: NULL, total_emissions: 123}') as V,
  V:error,
	V:total_emissions,
	IS_NULL_VALUE(v:error),
	IS_NULL_VALUE(v:total_emissions),
	IFF(IS_NULL_VALUE(v:non_existing_key) is null, 'Key does not exist', 'Key exists'),
	IFF(IS_NULL_VALUE(v:error) is null, 'Key does not exist', 'Key exists'),
	IFF(IS_NULL_VALUE(v:total_emissions) is null, 'Key does not exist', 'Key exists')
;