Snowflake (version 2)

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. = true;

Cre

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 n scope3_api api_provider = google

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')
;