Skip to main content

Standard Fields

currency_code​

Returns the account/campaign currency in uppercase (e.g., USD) for further match with currency rates for the record's particular date

select upper(coalesce(dimensions:property.currency,account_info:currency_code,account_info:currency,campaign_info:currency_code)::varchar)

campaign_state​

Campaign status in Title Case (e.g., “Enabled”, “Paused”). Falls back to “Unknown” if missing from platform pull

select initcap(coalesce(campaign_info:state,campaign_info:status,'Unknown')::varchar)

adgroup_state​

AdGroup status in Title Case (e.g., “Enabled”, “Paused”). Falls back to “Unknown” if missing from platform pull

select initcap(coalesce(adgroup_info:state,adgroup_info:status,'Unknown')::varchar)

ad_state​

Ad status in Title Case (e.g., “Enabled”, “Paused”). Falls back to “Unknown” if missing from platform pull

select initcap(coalesce(ad_info:state,ad_info:status,'Unknown')::varchar)

ad_type​

Placeholder for ad type. Currently always returns “TO BE DEFINED.”

select 'TO BE DEFINED'

device​

Normalized device/platform (e.g., “Mobile”, “Desktop”, “Tablet”)

select initcap(coalesce(dimensions:device,dimensions:device_platform,'Unknown')::varchar)

network​

Advertising network where the impression/click occurred (e.g., “Search”, “Display”)

select initcap(coalesce(dimensions:ad_network_type,dimensions:network,'Unknown')::varchar)

dma​

DMA / Nielsen metro area code when available; otherwise null. (Mostly US-only.)

select coalesce(dimensions:geo_target:dma,null)::varchar

geo_country​

Country of the clicks/conversions (best available from geo fields).

select coalesce(dimensions:geo_target:country,dimensions_extended_info:geo_target:country, dimensions:geo:country)::varchar

geo_city​

City of the clicks/conversions (includes “municipality” when that’s what’s provided).

select coalesce(dimensions:geo_target:city,dimensions_extended_info:geo_target:city,dimensions:geo:city,dimensions_extended_info:geo_target:municipality)::varchar

geo_region​

Region/province/state of the clicks/conversions

select coalesce(dimensions:geo_target:province,dimensions:geo_target:state,dimensions_extended_info:geo_target:province, dimensions_extended_info:geo_target:state, dimensions:geo:region, dimensions:geo_target:region)::varchar

geo_metro​

Metro Area of the clicks/conversions

select coalesce(dimensions:geo_target:metro,dimensions_extended_info:geo_target:metro, dimensions:geo:metro)::varchar

geo_continent​

Continent of the clicks/conversions

select dimensions_extended_info:geo_target:continent::varchar

geo_continent_region​

Continent Region ("Asia","Americas" etc.) of the clicks/conversions

select dimensions_extended_info:geo_target:continent_region::varchar

geo_business_region​

Business Region ("LATAM","EMEA" etc.) of the clicks/conversions

select dimensions_extended_info:geo_target:business_region::varchar

campaign_type​

Human-readable campaign type/channel (underscores removed, Title Case) from platform fields (e.g., “Search”, “Display”, “Performance Max”, or objective)

select initcap(replace(coalesce(campaign_info:type,campaign_info:advertising_channel_type,campaign_info:campaign_type,campaign_info:objective)::varchar,'_',' '))

creative_type​

Creative format in Title Case. On Facebook: infers things like Image, Video, Carousel, Dynamic, Collection, IX (Instant Experience), or Image/Video. On LinkedIn: uses the provided creative type. Marks “Performance Max” for Google PMax. Otherwise uses the ad’s type

select regexp_replace(initcap(CASE WHEN platform = 'Facebook' THEN 
CASE WHEN 
ad_info:creative:object_story_spec.template_data IS NOT NULL AND 
ad_info:creative:object_story_spec.template_data.link ILIKE '%canvas%'
THEN 'Dynamic IX'
WHEN ad_info:creative:object_story_spec.template_data ILIKE '%collection%' THEN 'Collection'
WHEN ad_info:creative:object_story_spec.template_data IS NOT NULL THEN 'Dynamic'
WHEN ad_info:creative:object_story_spec ILIKE '%child_attachments%' THEN 'Carousel'
WHEN ad_info:creative:object_story_spec ILIKE '%canvas%' THEN 'IX'
WHEN ad_info ILIKE '%"images"%' AND ad_info ILIKE '%"videos"%' THEN 'Image/Video'
WHEN ad_info ILIKE '%"videos"%' or ad_info ilike '%video_data%' or ad_info ilike '%video_id%' THEN 'Video'
WHEN ad_info ILIKE '%image%' THEN 'Image'
ELSE 'Image' END
when platform = 'LinkedIn' then ad_info:creative_type::varchar
WHEN campaign_info:advertising_channel_type::varchar LIKE '%PERFORMANCE_MAX%' THEN 'Performance Max'
ELSE ad_info:type::varchar END),'_', ' ')

ad_name_or_type​

The ad’s name; if missing, shows the creative or campaign type in parentheses (e.g., “(Video)”)

select coalesce(ad_name,'('||coalesce(creative_type,campaign_type)||')')

creative_sub_type​

More granular creative format in Title Case (e.g., IX Image, Dynamic Carousel, Carousel Video, Image/Video). Uses LinkedIn creative type when on LinkedIn; marks “Performance Max” for Google PMax; otherwise uses the ad type

select regexp_replace(initcap(CASE WHEN platform = 'Facebook' THEN 
CASE WHEN 
ad_info:creative:object_story_spec.template_data IS NOT NULL AND 
ad_info:creative:object_story_spec.template_data.link ILIKE '%canvas%' THEN 'Dynamic IX'
WHEN ad_info:creative:object_story_spec.template_data ILIKE '%collection%' THEN 'Collection'
WHEN ad_info:creative:object_story_spec.template_data IS NOT NULL THEN 'Dynamic'
WHEN ad_info:creative:object_story_spec.template_data IS NOT NULL AND ad_info:creative:object_story_spec ILIKE '%child_attachments%' THEN 'Dynamic Carousel'
WHEN ad_info:creative:object_story_spec ILIKE '%canvas%' and ad_info:creative:object_story_spec ILIKE '%image%' then 'IX Image'
WHEN ad_info:creative:object_story_spec ILIKE '%canvas%' and ad_info:creative:object_story_spec ILIKE '%video%' then 'IX Video'
WHEN ad_info:creative:object_story_spec ILIKE '%child_attachments%' AND ad_info:creative:object_story_spec ILIKE '%image%' AND ad_info:creative:object_story_spec ilike '%video%'  THEN 'Carousel Image/Video'
WHEN ad_info:creative:object_story_spec ILIKE '%child_attachments%' AND ad_info:creative:object_story_spec ILIKE '%video%'  THEN 'Carousel Video'
WHEN ad_info:creative:object_story_spec ILIKE '%child_attachments%' AND ad_info:creative:object_story_spec ILIKE '%image%'  THEN 'Carousel Image'
WHEN ad_info ILIKE '%"images"%' AND ad_info ILIKE '%"videos"%' THEN 'Image/Video'
WHEN ad_info ILIKE '%"videos"%' or ad_info ilike '%video_data%' or ad_info ilike '%video_id%' THEN 'Video'
WHEN ad_info ILIKE '%image%' THEN 'Image'
ELSE  'Image' END 
when platform = 'LinkedIn' then ad_info:creative_type::varchar
WHEN campaign_info:advertising_channel_type::varchar LIKE '%PERFORMANCE_MAX%' THEN 'Performance Max'
ELSE ad_info:type::varchar END),'_', ' ')

creative_dpa​

Flags Dynamic Product if Facebook template data (DPA/Dynamic Creative) is present; otherwise Standard.

select CASE when
ad_info:creative:object_story_spec.template_data IS NOT NULL THEN 'Dynamic Product'
else 'Standard' end

utm_source​

UTM source (best available from GA/GA4/CRM traffic source fields).

select coalesce(dimensions:source,dimensions:last_non_direct_scoped_collected_traffic_source,dimensions:traffic_source.source, dimensions:session_source)::varchar

utm_medium​

UTM medium (best available from GA/GA4/CRM traffic source fields).

select coalesce(dimensions:medium,dimensions:last_non_direct_scoped_collected_traffic_source,dimensions:traffic_source.medium, dimensions:session_medium)::varchar

utm_campaign​

UTM campaign name (best available from GA/GA4/CRM traffic source fields).

select coalesce(dimensions:campaign,dimensions:last_non_direct_scoped_collected_traffic_source,dimensions:traffic_source.name,dimensions:session_campaign_name)::varchar

utm_term​

UTM term/keyword (best available; includes GA4/CRM session term when present).

select coalesce(dimensions:keyword,dimensions:session_term)::varchar

utm_content​

UTM content (best available; includes GA4/CRM session content when present).

select coalesce(dimensions:ad_content,dimensions:session_content)::varchar

primary_kpi_source​

Which data source is designated as primary for KPI reporting (from parameters, CRM/GA/Platform etc).

select parameter_info:reporting.primary_kpi_source::varchar

source_medium​

utm_source + “_” + utm_medium (e.g., google_cpc)

select concat(utm_source, '_', utm_medium)

channel​

Normalized channel in Title Case. On Google, uses the Google advertising channel type; otherwise uses GA channel if present; else falls back to the platform name.

select initcap(case when platform = 'Google' then campaign_info:advertising_channel_type::varchar when dimensions:channel is not null then dimensions:channel else platform END)

source_medium_clean​

Lower-case, human-friendly blend of platform and tactic (e.g., “google search”), except for Analytics rows with tactic “Other,” where it becomes utm_source utm_medium.

select lower(case when report_tactic = 'Other' and data_source_type = 'Analytics' then concat(utm_source,' ',utm_medium) else concat(platform,' ', report_tactic) end)

assumed_incremental_value​

For ad-platform rows only: pulls an incrementality factor from parameters (tries tactic then channel then platform then program total) as a numeric value.

select case when data_source_type = 'Ad Platform' then coalesce(parameter_info:incrementality[lower(report_tactic)],
parameter_info:incrementality[lower(channel)], parameter_info:incrementality[lower(platform)], parameter_info:incrementality.total_ads_program)::numeric(19,3)
end

creative_url_1​

First best image/video asset URL. On Facebook: attempts thumbnail/asset/child-attachment URLs; on LinkedIn: the creative thumbnail; on Google Display: preview URL; otherwise null.

select case when platform = 'Facebook' then 
        coalesce(ad_info:creative:object_story_spec:link_data:image_url::varchar
        , ad_info:creative:object_story_spec:video_data:image_url::varchar
        , ad_info:creative:asset_feed_spec:images[0].url::varchar
        , ad_info:creative:asset_feed_spec:videos[0].picture::varchar
        , ad_info:creative:object_story_spec:link_data:child_attachments[0].image_url::varchar
        , ad_info:creative:thumbnail_url::varchar)::varchar when platform = 'LinkedIn' then ad_info:creative_thumbnail when channel = 'Display' then ad_info:ad_preview_url else null end::varchar

creative_url_2​

Second creative asset URL (Facebook asset/feed or child attachment), if available.

select coalesce(ad_info:creative:asset_feed_spec:images[1].url, ad_info:creative:asset_feed_spec:videos[1].picture, ad_info:creative:object_story_spec:link_data:child_attachments[1].image_url)::varchar

creative_url_3​

Third creative asset URL, if available.

select coalesce(ad_info:creative:asset_feed_spec:images[2].url, ad_info:creative:asset_feed_spec:videos[2].picture, ad_info:creative:object_story_spec:link_data:child_attachments[2].image_url)::varchar

creative_url_4​

Fourth creative asset URL, if available.

select coalesce(ad_info:creative:asset_feed_spec:images[3].url, ad_info:creative:asset_feed_spec:videos[3].picture, ad_info:creative:object_story_spec:link_data:child_attachments[3].image_url)::varchar

creative_url_5​

Fifth creative asset URL, if available.

select coalesce(ad_info:creative:asset_feed_spec:images[4].url, ad_info:creative:asset_feed_spec:videos[4].picture, ad_info:creative:object_story_spec:link_data:child_attachments[4].image_url)::varchar

creative_headline_1​

Primary creative headline/title. On Facebook: tries creative title/child attachment/video title; on LinkedIn: uses creative title.

select case when platform = 'Facebook' then coalesce(ad_info:creative:title, 
ad_info:creative:asset_feed_spec:titles[0].text,        
ad_info:creative:object_story_spec:link_data:child_attachments[0].NAME,
ad_info:creative:object_story_spec:video_data:title) 
when platform = 'LinkedIn' then ad_info:creative_title
else null end::varchar

creative_headline_2​

Second headline/title from asset feed/child attachments, if available.

select coalesce(ad_info:creative:asset_feed_spec:titles[1].text,ad_info:creative:object_story_spec:link_data:child_attachments[1].NAME)::varchar

creative_headline_3​

Third headline/title from asset feed/child attachments, if available.

select coalesce(ad_info:creative:asset_feed_spec:titles[2].text,ad_info:creative:object_story_spec:link_data:child_attachments[2].NAME)::varchar

creative_headline_4​

Fourth headline/title from asset feed/child attachments, if available.

select coalesce(ad_info:creative:asset_feed_spec:titles[3].text,ad_info:creative:object_story_spec:link_data:child_attachments[3].NAME)::varchar

creative_headline_5​

Fifth headline/title from asset feed/child attachments, if available.

select coalesce(ad_info:creative:asset_feed_spec:titles[4].text,ad_info:creative:object_story_spec:link_data:child_attachments[4].NAME)::varchar

creative_body_1​

Primary body text. On Facebook: creative body or first asset-feed body; on LinkedIn: creative text.

select case when platform = 'Facebook' then coalesce(ad_info:creative:body, ad_info:creative:asset_feed_spec:bodies[0].text) 
when platform = 'LinkedIn' then ad_info:creative_text::varchar
else null end

creative_body_2​

Second body text from asset feed, if available.

select ad_info:creative:asset_feed_spec:bodies[1].text::varchar

creative_body_3​

Third body text from asset feed, if available.

select ad_info:creative:asset_feed_spec:bodies[2].text::varchar

creative_body_4​

Fourth body text from asset feed, if available.

select ad_info:creative:asset_feed_spec:bodies[3].text::varchar

creative_body_5​

Fifth body text from asset feed, if available.

select ad_info:creative:asset_feed_spec:bodies[4].text::varchar

creative_description​

Creative description (first description from Facebook asset feed), when present.

select ad_info:creative.asset_feed_spec.descriptions[0].text::varchar

dco_asset_type​

For Facebook DCO rows only: the dynamic asset type; otherwise null.

select case when platform = 'Facebook' and data_layer = 'DCO' then dimensions:asset_type::varchar
else null end

dco_asset_content​

For Facebook DCO rows only: the dynamic asset content/value; otherwise null.

select case when platform = 'Facebook' and data_layer = 'DCO' then dimensions:asset_content::varchar
else null end

dco_asset_format​

For Facebook DCO rows only: the dynamic asset format; otherwise null.

select case when platform = 'Facebook' and data_layer = 'DCO' then dimensions:iformat::varchar
else null end

projected_spend​

Projected spend if provided; otherwise actual spend.

select coalesce(special_metrics:projected_spend,spend)::numeric(19,3)

projected_conversions​

Projected conversions if provided; otherwise actual conversions (all).

select coalesce(special_metrics:projected_conversions,conversions_all)::numeric(19,3)

projected_value​

Projected conversion value if provided; otherwise actual conversion value (all).

select coalesce(special_metrics:projected_value,conversions_value_all)::numeric(19,3)

projection_method​

Notes whether the row is Actuals or the method used to project metrics.

select coalesce(dimensions:projection_method,'Actuals')::varchar

landing_page​

Primary landing page URL with query string removed when possible; falls back to final URLs from the ad.

select coalesce(regexp_substr(dimensions:landing_page, '[^?]+',1,1,'e')::varchar, ad_info:urls.final_urls::varchar)::varchar

bq_users​

GA4 BigQuery user_pseudo_id (user identifier in event export).

select dimensions:user_pseudo_id

bq_sessions_engaged​

Returns the ga_session_id only for engaged sessions (GA4 event param session_engaged=1); otherwise returns 0.

select CASE WHEN dimensions:event_params:session_engaged:string_value::int = 1 
THEN dimensions:event_params:ga_session_id:int_value::varchar 
ELSE 0 END

ga4_sessions_engaged​

GA4 Engaged Sessions metric.

select special_metrics:engaged_sessions::numeric

ga4_adds_to_cart​

GA4 add_to_cart conversions for that row; else 0 for non-GA4 rows.

select CASE WHEN data_source_name = 'GA4' AND conversion_id = 'add_to_cart' THEN conversions_all ELSE 0 end

ga4_pdp​

GA4 view_item conversions (product detail views) for that row; else 0.

select CASE WHEN data_source_name = 'GA4' AND conversion_id = 'view_item' THEN conversions_all ELSE 0 end

ga4_transactions​

GA4 purchase conversions for that row; else 0.

select CASE WHEN data_source_name = 'GA4' AND conversion_id = 'purchase' THEN conversions_all ELSE 0 end

ga4_revenue​

GA4 purchase conversion value for that row; else 0.

select CASE WHEN data_source_name = 'GA4' AND conversion_id = 'purchase' THEN conversions_value_all ELSE 0 end

reach​

Reach (unique users) when the platform provides it; otherwise null.

select coalesce(special_metrics:reach, null)::numeric(19,3)

item_id​

Product ID (from item dimensions).

select dimensions:item.id::varchar

item_name​

Product name (from item dimensions).

select dimensions:item.name::varchar

item_variant​

Product variant (e.g., size/color) when provided.

select dimensions:item.variant::varchar

item_brand​

Product brand when provided.

select dimensions:item.brand::varchar

item_category​

Product category (level 1).

select dimensions:item.category::varchar

item_category2​

Product category (level 2).

select dimensions:item.category2::varchar

item_category3​

Product category (level 3).

select dimensions:item.category3::varchar

item_addtocart​

Count of items added to cart.

select special_metrics:items_added_to_cart::numeric(19,3)

item_checkout​

Count of items checked out.

select special_metrics:items_checked_out::numeric(19,3)

item_purchase​

Count of items purchased.

select special_metrics:items_purchased::numeric(19,3)

item_revenue​

Revenue attributed to the item.

select special_metrics:item_revenue::numeric(19,3)

item_viewed​

Count of item views.

select special_metrics:items_viewed::numeric(19,3)

website_url​

Destination website URL from the creative (link/call-to-action) when available.

select COALESCE(
	ad_info:creative:asset_feed_spec:link_urls[0].website_url::varchar
	, ad_info:creative:object_story_spec:link_data:link::varchar
	, ad_info:creative:object_story_spec:video_data:call_to_action:value:link::varchar
	)

labels_ads​

Any labels/tags applied to the ad (platform labels).

select ad_info:labels::varchar

meta_buying_type​

Facebook/Meta campaign buying type (e.g., Auction, Reach and Frequency).

select campaign_info:buying_type::varchar

meta_campaign_objective​

Facebook/Meta campaign objective (e.g., Conversions, Leads, Reach).

select campaign_info:objective::varchar

google_sub_channel​

Google Ads sub-channel (e.g., Search Partners, Video Action, etc.).

select campaign_info:advertising_channel_sub_type

search_term​

The actual search term matched (search term view).

select dimensions:search_term_view::varchar

search_term_match_type​

Match type for the search term (e.g., Broad/Phrase/Exact or platform equivalents).

select dimensions:search_term_match_type::varchar

keyword​

The keyword text from the account (not the user query).

select dimensions_extended_info:keyword_text::varchar

google_bidding_strategy​

Google Ads bidding strategy (e.g., tROAS, tCPA, Max Clicks).

select campaign_info:bidding_strategy::varchar

inferred_funnel_position​

Rule-based funnel stage: BOF for lower-funnel (Search/PMax/Bing/sales objectives, and Google Video Action), TOF for awareness/reach/video, MOF for lead/app/clicks/engagement/message/like objectives, else Other.

select case when
(channel = 'Search' 
or channel = 'Performance_Max'
or lower(meta_campaign_objective) like '%sales%'
or platform = 'Bing')
then 'BOF'
when platform = 'Google' 
and channel = 'Video' 
and lower(google_sub_channel) = 'video_action' 
then 'BOF'
when platform = 'Facebook' 
and not lower(coalesce(meta_buying_type,'')) like 'auction' 
and not lower(coalesce(meta_buying_type,'')) like ''
then 'TOF'
when 
(lower(meta_campaign_objective) like '%awareness%'
or lower(meta_campaign_objective) like '%reach%'
or lower(meta_campaign_objective) like '%store_visits%' 
or lower(meta_campaign_objective) like '%views%')
then 'TOF'
when platform = 'Google' and channel = 'Video'                        
then 'TOF'        
when (lower(meta_campaign_objective) like '%lead%'
or lower(meta_campaign_objective) like '%app%'
or lower(meta_campaign_objective) like '%clicks%'
or lower(meta_campaign_objective) like '%engagement%'
or lower(meta_campaign_objective) like '%responses%'
or lower(meta_campaign_objective) like '%messages%'
or lower(meta_campaign_objective) like '%likes%') 
then 'MOF'
else 'Other' end