Breadcrumbs

Placement ID methodology

Overview

The ad_performance report combines (1) ad-level data from DSPs on which customer serves media, and (2) placement-level data from CM360 using a join on cm_placement_id and date.

This guide walks through where Placement ID comes from and how it is parsed from the respective DSPs.

Methodology by platform


The following SQL examples are for Redshift.

To see the BigQuery version, (1) replace the function regexp_substr with regexp_extract, and (2) add an r before the opening quotation mark in the 2nd argument, e.g.:

regexp_substr(arg1, 'arg2')→ regexp_extract(arg1, r'arg2')

Platform

Placement ID Source Column
(in order of priority)

Logic for Placement Id Extraction

Example SQL

Amazon DSP

Creative Name

8 to 9 digit at the end or beginning of the Creative Name.

e.g.
example_creative_name_12345678

12345678_example_creative_name
SQL
select
  coalesce(
    nullif(regexp_substr(creativename, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(creativename, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.amazondsp_insights_ad

Bidtellect

Creative Name

8 to 9 digit at the end or beginning of the Creative Name.

e.g.
example_creative_name_12345678

12345678_example_creative_name
SQL
select
  coalesce(
    nullif(regexp_substr(ndsp_client_reporting_creative_name, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(ndsp_client_reporting_creative_name, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.bidtellect_insights_hit

DV360

CM360 Placement ID

IF CM360_PLACEMENT_ID DOES NOT EXIST then check
CREATIVE_INTEGRATION_CODE
SQL
select
  coalesce(
    filter_cm360_placement_id, 
    filter_creative_integration_code
  ) as cm_placement_id 
from {alli_client}.dv360_insights_ad

DV360 Trueview (Youtube)

Trueview Ad Group Name

8 to 9 digit at the end or beginning of the YouTube Ad Group Name.

e.g.
example_ad_group_name_123456789

12345678_example_ad_group_name
SQL
select
  coalesce(
    nullif(regexp_substr(filter_trueview_ad_group, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(filter_trueview_ad, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.dv360_insights_ad_trueview

Google Ads

Ad Group Name

8 to 9 digit at the end or beginning of the Ad Group Name.

e.g.
example_ad_group_name_123456789

12345678_example_ad_group_name
SQL
select
  coalesce(
    nullif(regexp_substr(ad_group_name, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(ad_group_name, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.googleads_settings_adgroup

Ad Name

8 to 9 digit at the end of the Ad Name.

e.g.
example_ad_name_123456789

12345678_example_ad_name
SQL
select
  coalesce(
    nullif(regexp_substr(ad_name, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(ad_name, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.googleads_settings_ad

Ad Tracking URL Template

8 to 9 digit starting with a . and ending with ; after BXXXXXXXX within the Ad Tracking URL Template.

e.g. https://ad.doubleclick.net/.../BXXXXXXXX.123456789;dc_trk_aid=
SQL
select
  replace(
    regexp_substr(
      ad_tracking_url_template, '\.[0-9]{8,9}'
    ), '.', ''
  ) as cm_placement_id
from {alli_client}.googleads_settings_ad

Microsoft Bing

Ad Group Name

8 to 9 digit at the end or beginning of the Ad Group Name.

e.g.
example_ad_group_name_123456789

12345678_example_ad_group_name
SQL
select
  coalesce(
    nullif(regexp_substr(adgroupname, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(adgroupname, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.microsoftads_insights_adgroup

Ad Group Tracking URL Template

8 to 9 digit starting with a . and ending with ; after BXXXXXXXX within the Ad Group Tracking URL Template.

e.g. https://ad.doubleclick.net/.../BXXXXXXXX.123456789;dc_trk_aid=
SQL
select
  regexp_substr(
    trackingtemplate, '\.[0-9]{8,9}'
    ), '.', ''
  ) as cm_placement_id
from {alli_client}.microsoftads_insights_adgroup

Ad Tracking URL Template

8 to 9 digit starting with a . and ending with ; after BXXXXXXXX within the Ad Tracking URL Template.

e.g. https://ad.doubleclick.net/.../BXXXXXXXX.123456789;dc_trk_aid=
SQL
select
  regexp_substr(
    trackingtemplate, '\.[0-9]{8,9}'
    ), '.', ''
  ) as cm_placement_id
from {alli_client}.microsoftads_insights_ad

The Trade Desk

Ad Server Creative Placement ID

direct mapping of the ad_server_creative_placement_id
SQL
select
  ad_server_creative_placement_id as cm_placement_id
from {alli_client}.tradedesk_insights_ad

Vistar DSP

Creative Name

8 to 9 digit at the end or beginning of the Creative Name.

e.g.
example_creative_name_12345678

12345678_example_creative_name
SQL
select
  coalesce(
    nullif(regexp_substr(creative_name, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(creative_name, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.vistar_insights_ad

Yahoo DSP

Creative Custom ID

Direct Pull from the CREATIVE_CUSTOM_ID fields. If all are empty go check Creative Name.
SQL
select
  coalesce(
    nullif(creative_custom_id_1, '0'),
    nullif(creative_custom_id_2, '0'),
    nullif(creative_custom_id_3, '0')
  ) as cm_placement_id
from {alli_client}.verizon_insights_ad

Creative Name

8 to 9 digit at the end or beginning of the Creative Name.

e.g.
example_creative_name_12345678

12345678_example_creative_name
SQL
select
  coalesce(
    nullif(regexp_substr(creative_name, '[0-9]{8,9}$'), ''), 
    nullif(regexp_substr(creative_name, '^([0-9]{8,9})'), '')
  ) as cm_placement_id
from {alli_client}.verizon_insights_ad

Taboola / Yahoo Native Network

Custom ID

Direct Pull from the CUSTOM_ID fields. If all are empty go check URL.
SQL
select
  coalesce(
    custom_id,
    regexp_substr(
      url, '\.[0-9]{8,9}'
    ), '.', ''
  ) as cm_placement_id
from {alli_client}.taboola_insights_ad

URL

8 to 9 digit starting with a . and ending with ; after BXXXXXXXX within the URL.

e.g. https://ad.doubleclick.net/.../BXXXXXXXX.123456789;dc_trk_aid=

Teads

Creative External Integration Code

creative_external_integration_code
SQL
select 
  creative_external_integration_code as cm_placement_id
from {alli_client}.teads_insights_ad