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= |
