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.”
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
)
