Custom Floodlight variable matching

Custom Floodlight variables are URL parameters attached to Floodlight conversions, which are managed via Google Marketing Platform properties. They allow you to capture additional information beyond what you can collect with standard parameters. Though advertisers pass a wide range of information using custom Floodlight variables, only data that can be used for matching is relevant in Ads Data Hub—such as user IDs, external cookies, or order IDs.

Importantly, custom Floodlight variables fire when a user converts. As such, custom Floodlight variable matching is only useful for answering advertising questions or building audiences where conversions occurred. Examples of these use cases include, but aren’t limited to:

• “Did my recent campaign lead to growth in the right products that I wanted to focus on?”
• “What was the incremental revenue from a campaign I ran?”
• “I want to build an audience of high-value users.”
• “I want to build an audience of users who have engaged in a meaningful way with my services.”

Warning: Google prohibits you from passing any information that could be construed as personally identifiable information.

Learn more about custom Floodlight variables

Accessing custom Floodlight variables in Ads Data Hub

Custom Floodlight variables are appended together and stored as a string in the event.other_data field in the adh.cm_dt_activities_attributed table. You’ll need to use the following regular expression to separate out individual variables, substituting u1 for whichever variable you will use for matching:

REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS u1_val

Samples

Reach and spend

This query measures the reach and total spend associated with a given campaign.

crm_data uses the following schema:

Field	Description
order_id	A unique identifier associated with an order.
order_val	The value (as a float) of the order.
order_timestamp	The timestamp associated with the completion of the order.

/* Creates a temporary table containing user IDs and order IDs (extracted u-values)
associated with a given campaign */
WITH floodlight AS (
  SELECT user_id, event.campaign_id, REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS order_id
  FROM adh.cm_dt_activities_attributed
  WHERE event.other_data LIKE "%u1%" AND event.campaign_id = 31459
)

/* Creates a temporary table where each row contains an order ID, the order's value,
and the time the order was placed */
WITH crm_data AS (
  SELECT order_id, order_val, order_timestamp
  FROM `your_cloud_project.your_dataset.crm_data`
  WHERE order_timestamp > FORMAT_TIMESTAMP('%F', TIMESTAMP_MICROS('2020-01-19 03:14:59'), @time_zone)
)

/* Joins both tables on order ID, counts the number of distinct users and sums the
value of all orders */
SELECT DISTINCT(user_id) AS reach, sum(order_val) as order_val
FROM floodlight JOIN crm_data
ON (floodlight.order_id = crm_data.order_id)

High-spending customers with previous engagement

This query builds an audience of customers who have spent over $1000 during August 2020 and previously engaged with your ads.

crm_data uses the following schema:

Field	Description
your_id	A unique identifier associated with a customer.
customer_spend_aug_2020_usd	A given customer’s cumulative spend (as a float) over August 2020.

/* Creates a temporary table containing IDs you track, alongside IDs Google tracks
for the same user */
WITH floodlight AS (
  SELECT user_id, REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS your_id
  FROM adh.cm_dt_activities_events
  WHERE event.other_data LIKE "%u1%"
)

/* Creates a temporary table containing IDs you track for customers who spent over
$1000 in August 2020 */
WITH crm_data AS (
  SELECT your_id
  FROM `your_cloud_project.your_dataset.crm_data`
  WHERE customer_spend_aug_2020_usd > 1000
)

/* Creates a list (to be used in audience creation) of customers who spent over
$1000 in August 2020 */
SELECT user_id
FROM floodlight
JOIN crm_data ON (floodlight.your_id = crm_data.your_id)

Long-haul elite-status fliers

This query builds an audience of customers who previously converted on an ad, and either flew over 100,000 miles in 2019 or had “elite” airline status during 2019.

airline_data uses the following schema:

Field	Description
your_id	A unique identifier associated with a customer.
miles_flown_2019	The total miles (as an integer) that the customer flew in 2019.
ye_2019_status	The airline status that the customer earned in 2019.

/* Creates a temporary table containing IDs you track, alongside IDs Google
tracks for the same user */
WITH floodlight AS (
  SELECT user_id, REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS your_id
  FROM adh.cm_dt_activities_events
  WHERE event.other_data LIKE "%u1%"
)

/* Creates a temporary table containing IDs you track for customers who either
flew over 100,000 miles with your airline in 2019, or earned elite status in
2019 */
WITH airline_data AS (
  SELECT your_id
  FROM `my_cloud_project.my_dataset.crm_data`
  WHERE miles_flown_2019 > 100000 or ye_2019_status = "elite"
)

/* Creates a list (to be used in audience creation) of customers who previously
converted on an ad and either earned elite status, or flew over 100,000 miles
in 2019 */
SELECT user_id
FROM floodlight
JOIN airline_data ON (floodlight.your_id = airline_data.your_id)