Spaces:
Sleeping
Sleeping
| ## Overview | |
| This file provides instructions for extracting data from the Gamlog table which should use ANALYTICS.GAM360.GAMLOG. | |
| For 'like' operator be sure to use 'like' as opposed to 'Ilike'. | |
| When implementing the 'GROUP BY', please just use 'GROUP BY ALL' without listing any dimensions. | |
| When implementing the 'ORDER BY', please just use the column reference number instead of the column name if it's a metric that is being asked to be ordered by. | |
| Please add a date filter within the where clause to be set as default for the last 7 days, ending yesterday, unless the user specifies a date period. | |
| Always include impressions and revenue metrics in all your Gamlog SQL queries. | |
| If the user requests 'site', or 'site name' data, please reference the 'adunitname' column as this is the the dimension name found within ANALYTICS.GAM360.GAMLOG. | |
| If the user requests 'ad unit', or 'AU' data, please reference the 'topadunitname' column as this is the the dimension name found within ANALYTICS.GAM360.GAMLOG. | |
| If the user requests for only 'HBT' data then you will need to filter to where product = 'Header Bidding' | |
| ## Important Instructions for Gamlog Data Extraction with Syntax Enforcement | |
| When a user requests data from the `ANALYTICS.GAM360.GAMLOG` table and includes dimensions or filters related to the following columns (or any columns present in `ANALYTICS.SIGMA_SCRATCH.VIEW_AVI_SITE_DATASET_FROM_SQL_4D44494CEF294AF093BA92B8C008977A`): | |
| * `primary vertical` | |
| * `service condensed` | |
| * `new service condensed` | |
| * `tier` | |
| * `status` | |
| You **must** join the `ANALYTICS.GAM360.GAMLOG` table with `ANALYTICS.SIGMA_SCRATCH.VIEW_AVI_SITE_DATASET_FROM_SQL_4D44494CEF294AF093BA92B8C008977A` on the `siteid` column. | |
| **Crucial Syntax Enforcement:** | |
| * **Case Sensitivity:** Pay very close attention to case sensitivity. Ensure that all column names and string values in `WHERE` clauses match the exact case found in the `SITE_EXTENDED_snowflake.json` sample data. For example, if a column is named `Status` and the value is `'Active'`, the SQL query **must** use `Status = 'Active'`, not `status = 'active'` or `status = 'Active'`. | |
| * **Quoting:** String values in `WHERE` clauses **must** be enclosed in single quotes (`'...'`). Numeric values should not be enclosed in quotes. | |
| * **Column Names:** Column names must be used exactly as they appear in the data, including any spaces or special characters. If a column is named `"SITE ID"`, use it as `"SITE ID"` in the query. | |
| * **Data Types:** Ensure that the data types of values used in `WHERE` clauses match the data types of the corresponding columns. | |
| **Example SQL Query:** | |
| ```sql | |
| SELECT | |
| a.date, | |
| b."TIER", | |
| SUM(a.impressions) AS total_impressions | |
| FROM | |
| ANALYTICS.GAM360.GAMLOG a | |
| JOIN | |
| ANALYTICS.SIGMA_SCRATCH.VIEW_AVI_SITE_DATASET_FROM_SQL_4D44494CEF294AF093BA92B8C008977A b ON a.siteid = b."SITE ID" | |
| WHERE | |
| a.date = DATE '2025-03-30' AND b.Status = 'Active' | |
| and b."STATUS" = 'Active' | |
| GROUP BY | |
| a.date, | |
| b."TIER"; | |
| ## Prebid Data Extraction | |
| Target Column & Table: Use the column `ordername` from the `Gamlog` table. | |
| Filtering for Prebid Data: Filter for order names that include "prebid". | |
| Example SQL snippet: | |
| WHERE ordername LIKE '%prebid%' | |
| ## Open Bidding Data Extraction | |
| Identification: The terms "open bidding", "exchange bidding", or their abbreviations "OB" or "EB" should be interpreted as "open bidding". | |
| Filtering for Open Bidding: Filter where the column `product` equals "open bidding". | |
| Example SQL snippet: | |
| WHERE product = 'open bidding' | |
| ## Default Metrics in the Gamlog Table | |
| By default, always use the following metrics in your SQL queries: | |
| Impressions: | |
| sum(impressions) as Impressions | |
| Revenue: | |
| sum(impressions * revenue) as Revenue | |
| ## Optional Additional Metrics | |
| The following metrics are available if needed for extended analysis: | |
| Unfilled Impressions: | |
| sum(case when not isfilledrequest then 1 else 0 end) as Unfilled_Impressions | |
| Pageviews: | |
| count(distinct pvk) as Pageviews | |
| Sessions: | |
| count(distinct sess) as Sessions | |
| Viewable Impressions: | |
| sum(viewable) as Viewable_Impressions | |
| Measurable Impressions: | |
| sum(measurable) as Measurable_Impressions | |
| - CPM = (sum(impressions * revenue) / sum(impressions)) × 1,000 | |
| - RPM = (sum(impressions * revenue) / count(distinct pvk)) × 1,000 | |
| - IPPV (impressions per pageview) = (sum(impressions) / count(distinct pvk)) | |
| - If a user asks for CPM, RPM, or IPPV, please ensure that your sql query only contains sites with a status of 'Active' The reason is if a site has zero impressions, pageviews or revenue then the resulting calculation will be null or zero And thus we don't want this to be represented in the table. | |
| - If a user asks for the top 100 sites with the highest CPM, RPM, or IPPV, then default by using impressions or revenue or pageviews to identify the top 100 sites and then calculate. | |
| ## Abgroup Metric Filter | |
| To filter on the abgroup metric, use the following expression: | |
| filter(abgroup, x -> x LIKE '%dsllv%') AS filtered_abgroup, | |
| ## ctmap Field Extraction | |
| To extract any field from the ctmap based on the user's request, use the following syntax. | |
| For example, to extract the 'hb_adomain' field: | |
| ctmap['hb_adomain'][0]::varchar | |
| ## Mapping for Integration. This is not a mapping for Bidder or SSP. | |
| Mapping for existing columns found within the Gamlog table for integration: | |
| CASE | |
| WHEN ordername LIKE '%Prebid%' THEN 'Prebid' | |
| WHEN ordername LIKE '%TAM%' OR ordername LIKE '%Amazon%' THEN 'TAM' | |
| WHEN product LIKE '%Ad Exchange%' THEN 'AdX' | |
| WHEN product IN ('Header Bidding') THEN 'Prebid' | |
| WHEN product IN ('Open Bidding') THEN 'OB' | |
| WHEN orderid = 0 THEN 'OB' | |
| ELSE 'other' | |
| END AS Integration, | |
| ## Mapping for Format | |
| Mapping for Format: | |
| CASE | |
| WHEN topadunitname LIKE '%Outstream%' THEN 'Display' | |
| WHEN topadunitname LIKE '%Video%' THEN 'Video' | |
| ELSE 'Display' | |
| END AS Format, | |
| ## Mapping for Device | |
| Mapping for Device: | |
| CASE | |
| WHEN devicecategory LIKE '%Desktop%' THEN 'Desktop' | |
| WHEN devicecategory LIKE '%Tablet%' THEN 'Mobile' | |
| WHEN devicecategory LIKE '%Smartphone%' THEN 'Mobile' | |
| ELSE 'other' | |
| END AS Device, | |
| ## Mapping for Refresh | |
| Mapping for Refresh: | |
| CASE | |
| WHEN nref[0] = '0' THEN '0' | |
| WHEN nref[0] = '1' THEN '1' | |
| WHEN nref[0] = '2' THEN '2' | |
| WHEN nref[0] = '3' THEN '3' | |
| WHEN nref[0] = '3' THEN '3' | |
| WHEN nref[0] = '4' THEN '4' | |
| WHEN nref[0] = '5' THEN '5' | |
| WHEN nref[0] = '6' THEN '6' | |
| WHEN nref[0] = '7' THEN '7' | |
| WHEN nref[0] = '8' THEN '8' | |
| WHEN nref[0] = '9' THEN '9' | |
| WHEN nref[0] = '10' THEN '10' | |
| ELSE 'Other' | |
| END AS refresh_group, | |
| ## Mapping for Browser | |
| Mapping for Browser: | |
| CASE | |
| WHEN browser LIKE '%Safari%' THEN 'Safari' | |
| WHEN browser LIKE '%Chrome%' THEN 'Chrome' | |
| WHEN browser LIKE '%Firefox%' THEN 'Firefox' | |
| WHEN browser LIKE '%Edge%' THEN 'Edge/IE' | |
| WHEN browser LIKE '%Internet Explore%' THEN 'Edge/IE' | |
| ELSE 'other' | |
| END AS Browser, | |
| ## Mapping for Country | |
| Mapping for Country: | |
| CASE | |
| WHEN country LIKE '%United States%' THEN 'US' | |
| WHEN country LIKE '%Canada%' THEN 'Canada' | |
| WHEN country LIKE 'United Kingdom' THEN 'UK' | |
| ELSE 'ROW' | |
| END AS Country, | |
| ## Mapping for Ad Unit | |
| Mapping for Ad Unit: | |
| CASE | |
| WHEN topadunitname LIKE '%Content%' THEN 'Content' | |
| WHEN topadunitname LIKE '%Footer%' THEN 'Footer' | |
| WHEN topadunitname LIKE '%Recipe%' THEN 'Recipe' | |
| WHEN topadunitname LIKE '%Sidebar%' THEN 'Sidebar' | |
| WHEN topadunitname LIKE '%Header%' THEN 'Header' | |
| WHEN topadunitname LIKE '%Below_Post%' THEN 'Below_Post' | |
| WHEN topadunitname LIKE '%Outstream%' THEN 'Sticky Outstream' | |
| WHEN topadunitname LIKE '%Video%' THEN 'Video' | |
| ELSE 'Other' | |
| END AS ad_unit_group, | |
| ## Mapping for Bidder/SSP (hb_bidder) – Use this for bidder reporting. If a user asks for a specific Bidder/SSP, please only extract that mapping portion into a case when statement. | |
| ## if a user asks for bidder data, the query must extract the bidder using the provided bidder mapping (the CASE statement checking ORDERNAME, PRODUCT, ORDERID, and yieldgroupcompanyid) and not the integration mapping. | |
| ## If the users references any of these Bidder/SSP names, please ensure to use this mapping and be sure you also filter on product, ordername, and yieldgroupcompanyid where neccessary. | |
| Mapping for Bidder/SSP (hb_bidder): | |
| CASE | |
| WHEN lower(product) = 'ad exchange' THEN 'AdX' | |
| WHEN ordername LIKE '39_%_%' THEN 'Direct' | |
| WHEN ordername LIKE '38_%_%' THEN 'Direct' | |
| WHEN ordername LIKE '3_%_%' AND SUBSTRING(ordername, 5, 1) = '_' THEN 'Direct' | |
| WHEN ordername LIKE '8_%_%_%' AND ordername LIKE '%IX%' THEN 'Index Exchange' | |
| WHEN ordername LIKE '8_%_%_%' THEN 'Ignore' | |
| WHEN ordername LIKE '7_%_%_%' THEN 'PG' | |
| WHEN ordername LIKE '5_%_%_%' THEN 'PG' | |
| WHEN LEFT(ordername, 1) = '4' AND RIGHT(LEFT(ordername, 2), 1) BETWEEN '0' AND '9' AND RIGHT(LEFT(ordername, 3), 1) BETWEEN '0' AND '9' AND RIGHT(LEFT(ordername, 4), 1) BETWEEN '0' AND '9' AND RIGHT(LEFT(ordername, 5), 1) = '_' THEN 'Affiliate' | |
| WHEN ordername LIKE '0_%_%_%' THEN 'Ignore' | |
| WHEN lower(ordername) LIKE '%amazon_tam-pubmatic%' THEN 'PubMatic' | |
| WHEN lower(ordername) LIKE '%amazon_tam-yieldmo%' THEN 'Yieldmo' | |
| WHEN lower(ordername) LIKE '%amazon_tam-amazon proprietary%' THEN 'Amazon Proprietary' | |
| WHEN lower(ordername) LIKE '%amazon_amz%' THEN 'Amazon Proprietary' | |
| WHEN lower(ordername) LIKE '%33across%' THEN '33Across' | |
| WHEN lower(ordername) LIKE '%appnexus%' THEN 'Microsoft' | |
| WHEN lower(ordername) LIKE '%blockthrough%' THEN 'Blockthrough' | |
| WHEN lower(ordername) LIKE '%brightroll%' THEN 'Brightroll' | |
| WHEN lower(ordername) LIKE '%colossus%' THEN 'Colossus' | |
| WHEN lower(ordername) LIKE '%concert%' THEN 'Concert' | |
| WHEN lower(ordername) LIKE '%conversant%' THEN 'Conversant' | |
| WHEN lower(ordername) LIKE '%criteo%' THEN 'C-Grid' | |
| WHEN lower(ordername) LIKE '%deepintent%' THEN 'DeepIntent' | |
| WHEN lower(ordername) LIKE '%districtm-dmx%' THEN 'DistrictM-DMX' | |
| WHEN lower(ordername) LIKE '%districtm%' THEN 'DistrictM' | |
| WHEN lower(ordername) LIKE '%-dmx%' THEN 'DMX' | |
| WHEN lower(ordername) LIKE '%gumgum%' THEN 'GumGum' | |
| WHEN lower(ordername) LIKE '%index exchange%' THEN 'Index Exchange' | |
| WHEN lower(ordername) LIKE '%interchange%' THEN 'TTD' | |
| WHEN lower(ordername) LIKE '%ix-rounde%' THEN 'Roundel' | |
| WHEN lower(ordername) LIKE '%ix%' THEN 'Index Exchange' | |
| WHEN lower(ordername) LIKE '%kargo%' THEN 'Kargo' | |
| WHEN lower(ordername) LIKE '%mediagrid%' THEN 'C-Grid' | |
| WHEN lower(ordername) LIKE '%nativo%' THEN 'Nativo' | |
| WHEN lower(ordername) LIKE '%onevideo%' THEN 'Yahoo' | |
| WHEN lower(ordername) LIKE '%openx%' THEN 'OpenX' | |
| WHEN lower(ordername) LIKE '%pubmatic%' THEN 'PubMatic' | |
| WHEN lower(ordername) LIKE '%reset digital%' THEN 'Reset Digital' | |
| WHEN lower(ordername) LIKE '%roundel%' THEN 'Roundel' | |
| WHEN lower(ordername) LIKE '%rubicon-padsquad%' THEN 'Rubicon-Padsquad' | |
| WHEN lower(ordername) LIKE '%rubicon%' THEN 'Magnite' | |
| WHEN lower(ordername) LIKE '%sharethrough%' THEN 'Sharethrough' | |
| WHEN lower(ordername) LIKE '%sovrn%' THEN 'Sovrn' | |
| WHEN lower(ordername) LIKE '%spotx%' THEN 'SpotX' | |
| WHEN lower(ordername) LIKE '%teads%' THEN 'Teads' | |
| WHEN lower(ordername) LIKE '%telaria%' THEN 'Telaria' | |
| WHEN lower(ordername) LIKE '%the trade desk%' THEN 'TTD' | |
| WHEN lower(ordername) LIKE '%ttd%' THEN 'TTD' | |
| WHEN lower(ordername) LIKE '%triplelift%' THEN 'TripleLift' | |
| WHEN lower(ordername) LIKE '%trustx%' THEN 'TrustX' | |
| WHEN lower(ordername) LIKE '%undertone%' THEN 'Undertone' | |
| WHEN lower(ordername) LIKE '%unruly%' THEN 'Unruly' | |
| WHEN lower(ordername) LIKE '%verizon%' THEN 'Yahoo' | |
| WHEN lower(ordername) LIKE '%yahoossp%' THEN 'Yahoo' | |
| WHEN lower(ordername) LIKE '%yahoo s2s%' THEN 'Yahoo' | |
| WHEN lower(ordername) LIKE '%yieldmo%' THEN 'Yieldmo' | |
| WHEN lower(ordername) LIKE '%rtb house%' THEN 'RTB House' | |
| WHEN lower(ordername) LIKE '%improve digital%' THEN 'Improve Digital' | |
| WHEN lower(ordername) LIKE '%ogury%' THEN 'Ogury' | |
| WHEN lower(ordername) LIKE '%ozone%' THEN 'Ozone' | |
| WHEN lower(ordername) LIKE '%adform%' THEN 'Adform' | |
| WHEN lower(ordername) LIKE '%aidem%' THEN 'Aidem' | |
| WHEN yieldgroupcompanyid IN (4460609902) THEN 'OpenX' | |
| WHEN yieldgroupcompanyid IN (4642901988) THEN 'Unruly' | |
| WHEN yieldgroupcompanyid IN (4455444655) THEN 'Sovrn' | |
| WHEN yieldgroupcompanyid IN (4697115760) THEN 'Yieldmo' | |
| WHEN yieldgroupcompanyid IN (5091519355) THEN 'EMX' | |
| WHEN yieldgroupcompanyid IN (4867316569) THEN 'Sharethrough' | |
| WHEN yieldgroupcompanyid IN (4623743882) THEN 'PubMatic' | |
| WHEN yieldgroupcompanyid IN (5004881021) THEN 'Smart' | |
| WHEN yieldgroupcompanyid IN (5198265065) THEN 'GumGum' | |
| WHEN yieldgroupcompanyid IN (4634020290) THEN 'Media.net' | |
| WHEN yieldgroupcompanyid IN (4463579729) THEN 'Index Exchange' | |
| WHEN yieldgroupcompanyid IN (4926199169) THEN 'Yahoo' | |
| WHEN yieldgroupcompanyid IN (4805985973) THEN 'Sonobi' | |
| WHEN yieldgroupcompanyid IN (4550726766) THEN 'Magnite' | |
| WHEN yieldgroupcompanyid IN (4466143841) THEN 'Smaato' | |
| WHEN yieldgroupcompanyid IN (4623743690) THEN 'TripleLift' | |
| WHEN yieldgroupcompanyid IN (5072598885) THEN 'Microsoft' | |
| WHEN yieldgroupcompanyid IN (5032793665) THEN 'C-Grid' | |
| WHEN yieldgroupcompanyid IN (4642901988) THEN 'Unruly' | |
| WHEN yieldgroupcompanyid IN (5073030913) THEN 'Kargo' | |
| WHEN yieldgroupcompanyid IN (4807636548) THEN 'SpotX' | |
| WHEN yieldgroupcompanyid IN (5220141531) THEN 'C-Grid' | |
| WHEN yieldgroupcompanyid IN (4802163255) THEN 'Telaria' | |
| WHEN yieldgroupcompanyid IN (4725727878) THEN 'Chocolate' | |
| WHEN yieldgroupcompanyid IN (5282730859) THEN 'OneTag' | |
| WHEN yieldgroupcompanyid IN (5119659271) THEN 'TTD' | |
| ELSE 'unknown' | |
| END AS Bidder_Mapped, | |
| # Standalone columns within the gamlog table to be used when generating a SQL query. | |
| TIME | |
| TIMEUSEC2 | |
| KEYPART | |
| CTMAP | |
| ISFILLEDREQUEST | |
| REQUESTEDADUNITSIZES | |
| MOBILEDEVICE | |
| OSVERSION | |
| MOBILECAPABILITY | |
| MOBILECARRIER | |
| SERVINGRESTRICTION | |
| PUBLISHERPROVIDEDID | |
| ISCOMPANION | |
| VIDEOPOSITION | |
| PODPOSITION | |
| DEVICECATEGORY | |
| ISINTERSTITIAL | |
| USERID | |
| REFERERURL | |
| REQUESTLANGUAGE | |
| ADUNITID | |
| TOPADUNITID | |
| TOPADUNITNAME | |
| ADUNITNAME | |
| COUNTRY | |
| REGION | |
| BROWSER | |
| OS | |
| METRO | |
| POSTALCODE | |
| BANDWIDTH | |
| GFPCONTENTID | |
| ADVERTISERID | |
| CREATIVESIZE | |
| CREATIVEID | |
| LINEITEMID | |
| LINEITEMNAME | |
| ORDERID | |
| ORDERNAME | |
| CREATIVESIZEDELIVERED | |
| CREATIVEVERSION | |
| PRODUCT | |
| TARGETEDCUSTOMCRITERIA | |
| DEALID | |
| DEALTYPE | |
| ESTIMATEDBACKFILLREVENUE | |
| IMPRESSIONID | |
| YIELDGROUPCOMPANYID | |
| YIELDGROUPNAMES | |
| ADVERTISER | |
| BUYER | |
| ADXACCOUNTID | |
| VIDEOFALLBACKPOSITION | |
| MEASURABLE | |
| VIEWABLE | |
| CODESERVES | |
| REVENUE | |
| IMPRESSIONS | |
| PVK | |
| SESS | |
| SITEID | |
| BUCKET | |
| DATE | |
| HOUR | |
| AMP | |
| ABGROUP | |
| LAZY | |
| DEPLOYMENT | |
| REFRESH | |
| PLUGIN | |
| FLAG | |
| FLAG_SLOT | |
| HBHO | |
| VPWXVPH | |
| VBHO | |
| FPV | |
| VPRENUM | |
| NREF | |
| DOC_REF | |
| SITE_CODE | |
| BRANCH | |
| UTM_CAMPAIGN | |
| UTM_MEDIUM | |
| UTM_SOURCE | |
| AT_CUSTOM_1 | |
| HB_PB | |
| HB_BIDDER | |
| UTC_TIMESTAMP | |
| UTC_DATE_HOUR | |
| SOURCE_DATE_HOUR | |
| CHILDNETWORKCODE | |
| SELLERRESERVEPRICE | |
| AUDIENCESEGMENTIDS | |
| USERIDENTIFIERSTATUS | |
| PPIDPRESENCE | |
| VASTERROR | |
| VASTERRORNAMES | |
| CLICK | |
| BACKFILLKEYPART | |
| PROTECTEDAUDIENCEAPIDELIVERY | |
| HB_DSP | |
| HB_CRID | |
| EVENTTIMEUSEC2 | |
| VERTICALS | |
| YIELDCOMPANYNAMES | |
| NATIVEFORMAT | |
| NATIVESTYLE | |
| PROTECTEDAUDIENCESELLER | |
| HB_DSP_NAME | |
| # The below is a JSON object to help the model understand the sample output of the gamlog table for specific dimensions. For example if a user asks for content 1 data, then you would know to adjust your query to look for "AdThrive_Content_1". | |
| { | |
| "TOPADUNITNAME": [ | |
| "AdThrive_Sidebar_1", | |
| "AdThrive_Video_StickyOutstream_1", | |
| "AdThrive_Recipe_1", | |
| "AdThrive_Header_2", | |
| "AdThrive_Video_Coll_SOff_Smartphone", | |
| "AdThrive_Sidebar_4", | |
| "AdThrive_Content", | |
| "AdThrive_Video_In-Post_ClicktoPlay_SoundOn", | |
| "AdThrive_Sidebar_3", | |
| "AdThrive_Recipe_3", | |
| "AdThrive_Recipe_2", | |
| "AdThrive_Native_Recipe_1", | |
| "CAM_Direct_2", | |
| "AdThrive_Below_Post_1", | |
| "AdThrive_Recipe_5", | |
| "AdThrive_Content_5", | |
| "AdThrive_Content_2", | |
| "AdThrive_Recipe_4", | |
| "AdThrive_Content_3", | |
| "AdThrive_Sidebar_2", | |
| "ca-pub-5669406840537886", | |
| "AdThrive_Below_Post", | |
| "AdThrive_Content_4", | |
| "AdThrive_Video_Individual_Autoplay_SOff", | |
| "AdThrive_Video_Collapse_Autoplay_SoundOff", | |
| "AdThrive_Content_6", | |
| "AdThrive_WebStories_1", | |
| "AdThrive_Sidebar_5", | |
| "AdThrive_Header_1", | |
| "AdThrive_Footer_1", | |
| "AdThrive_Content_8", | |
| "AdThrive_Content_7", | |
| "AdThrive_Content_1", | |
| "AdThrive_Sponsor_Tile", | |
| "AdThrive_Content_9", | |
| "AdThrive_Interstitial_1", | |
| "ca-pub-5669406840537886: AdThrive", | |
| "CAM_Direct_1", | |
| "AdX_Rewarded_Video_Test_ADOPSG-1897", | |
| "AdThrive_Sidebar_9" | |
| ], | |
| "DEVICECATEGORY": [ | |
| "Desktop", | |
| "Tablet", | |
| "Connected TV", | |
| "Feature Phone", | |
| "Smartphone", | |
| "Set Top Box" | |
| ] | |
| } |