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: | 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: | pc | |
channel |
| 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 |
Banner uses payload size and conventional model. Video uses duration and power model. | 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 |
| fixed | |
seller | Name of the seller entity that sold the media | xandr | |
buying_method |
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>"
}
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
- https://docs.snowflake.com/en/sql-reference/data-types-semistructured.html#label-data-type-object
- https://docs.snowflake.com/en/user-guide/querying-semistructured.html#label-traversing-semistructured-data
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')
;
Updated 13 days ago