The Snowflake v2 development work is in progress. For now, please use v1 for all Snowflake requests.

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_v1(
  date date,
  inventory_id string,
  device string,
  country string,
  region string,
  impressions int,
  creative_format string,
  creative_bytes int,
  creative_width int,
  creative_height int,
  creative_duration int,
  inventory_type string)
    returns OBJECT
    api_integration = scope3_api
    headers = (
        'AccessClientId'='****',
        'AccessClientSecret'='****'
    )
    as 'https://scope3-snowflake-api-gateway-5qbjzj9o.uc.gateway.dev/api/v1/snowflake';

Field	Description	Example
date	Date in UTC	2022-05-04
inventory_id	Fully qualified domain name or app ID (same as inventoryId in campaign API)	nytimes.com
country	ISO 3166-1 alpha-2 country code OR null When null default global emission value will be used.	US
region	ISO 3166-2 region code OR null When null default country value will be used.	NY
device	mobile	desktop
impressions	Sum of impressions for these dimensions	10000
creative_format	banner	video
creative_bytes	Creative size in Bytes OR null	3024
creative_width	Width of creative OR null	300
creative_height	Height of creative OR null	250
creative_duration	Length of creative in seconds OR null	30
inventory_type	app or site	site

Response

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

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

{
	"total_emissions": "<float|null>",
	"base_emissions": "<float|null>",
	"supply_path_emissions": "<float|null>",
	"creative_emissions": "<float|null>",
	"domain_coverage": "<modeled|missing|null>",
	"error": "<string|null>",
}

Field	Description	Example
totalEmissions	Total Impressions in gCO2e	300.99
baseEmissions	Base Emissions in gCO2e	100.33
supplyPathEmissions	Supply Path Emissions in gCO2e	100.33
creativeEmissions	Creative Emissions in gCO2e	100.33
domainCoverage	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
error	null / missing if no error. If error this will contain a string error message	creative_width without creative_height supplied

Term	Definition
Base Emissions	The Base Emissions figure is the total emissions of an impression, excluding the AdTech portion. This includes content creation and end-user device emissions. Content creation is the publisher's scope 1,2,3 emissions proportioned and attributed on an ad slot basis -- we are exploring a new model for how we attribute this in the next version. End-User device emissions cover the power consumption (CPU, Networking, etc.) for rendering the publisher's content considering the user's location and power grid mix.
Creative Emissions	The creative emissions figure is the total emissions associated with the creative delivery. This includes the networking and CPU costs of delivering and rendering the creative.
Supply Path Emissions	The supply path emissions figure is the total emissions associated with the supply path from the inventory ad slot and the complete graph of all advertising parties involved. This includes the Scope 1,2,3 emissions data of each party involved broken down into a per impression number.

Example Usage

Select a single Example Row

SELECT scope3_v1(DATE('2022-05-04'), 'bbc.com', 'desktop', 'US', 'NY', 1000, 'banner', null, 300, 250, null, 'site');

Using in a JOIN

SELECT
	d.app_url,
	d.country,
	d.imps,
	scope3_v1(d.date, d.app_url, 'desktop', d.country, null, d.imps, 'banner', null, 300, 250, null, 'site'):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_v1(DATE('2022-05-04'), 'bbc.com', 'desktop', 'US', 'NY', 1000, 'banner', null, 300, 250, null) 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, totalEmissions: 123}') as V,
  V:error,
	V:totalEmissions,
	IS_NULL_VALUE(v:error),
	IS_NULL_VALUE(v:totalEmissions),
	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:totalEmissions) is null, 'Key does not exist', 'Key exists')
;