# 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]
> **Warning:** Google prohibits you from passing any information that could be construed as personally identifiable information.

[Learn more about custom Floodlight variables](https://support.google.com/campaignmanager/answer/2823222)

## 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)