Question Builder Best Practices

For example, @AdLogs is correct and @adlogs will return a run-time error. It's suggested that the table names be copied and pasted with @ aliases from the left-side pane where the dataset types are displayed.

Correct Version:

Incorrect Version:

When the dataset type is not written in a case-sensitive manner, the error message at run-time will show as Failed.

Question Name and Category are mandatory fields for saving the query and moving to the next screen.

It is possible to have duplicate question names in your list of clean room questions. To avoid duplication and confusion, create unique question names.

When two dataset types (tables) are named identically, as shown in the following screenshot, LiveRamp Clean Room maps the table referenced that is also referenced in the SQL first. The two or more originally identically named tables can be referenced in the SQL as UserData1, UserData2, and so on.

You can add runtime parameters that act as dynamic variables within your code.

Click + in the Parameters section to add a parameter.

For example, to get results for different cuts of data, your partner can specify a run-time parameter value to use for a specific question run. To enable your partner to run the same query on multiple campaigns, you could provide a @campaign_id runtime parameter. Your partner would be asked to specify the ID or set of IDs they want the question to use to filter the data.

When you click Validate Code in Query Builder, a range of important things are checked, including:

The Dimensions and Measures associated with the main Select statement must be present in the Question Builder screen's right pane.

A dimension is a field in your data that can be used to aggregate the data, such as campaign name or device type. A measure is a fact that represents the count or sum of a field, such as the count of impressions or the sum of sales.

If you don't specify dimensions and measures, the Report Configuration step will not make those fields available in charts and tables, and the Question Configuration screen will not indicate that you are missing dimensions or measures.

The Validate button and the right-hand pane won't stop you from continuing unless the right pane is empty. For example, consider a main Select statement where there are two dimensions (audience_segment_id and AUDIENCE_SEGMENT_NAME) and two measures (OVERLAP and TOTAL). In the right-side pane, only one dimension and two measures will be displayed. This will not be highlighted as an error on the Question Configuration screen and the missing field (in this case, the audience_segment_id) won't appear on the Report Configuration screen.

To automatically populate the dimensions and measures in the right-hand pane, click Extract Output Fields. For information, see "Validate Dimensions and Measures".

At least one measure and one dimension are mandatory for the query to be valid. It is important to keep both dimensions and measures in the main Select statement so they are available for Report Configuration.

The following screenshot shows the error message you will receive if either the dimensions or measures are empty.

When using field aliases in the main Select statement, all the measures must have an alias so that they get picked up by the Report Configuration layer. The following screenshot shows how measures are aliased in the main Select statement.

If you want a field to represent a dimension instead of a measure, add it to the list of dimensions. You can also move dimensions to the measures list.

If the dimensions and measures are not placed correctly in the respective groups, query results will impact the chart or the format of the fields. For example, if a campaign ID is saved as an integer, then campaign ID 1234 will appear as 1.23k.

Adding incorrect values can also create conflicts if dataset analysis rules are configured for the assigned datasets. For information, see "Set Dataset Analysis Rules".

Question Builder does not accept a space in parameter names. Errors will be displayed if spaces are included.

A user list query can be outputted with more than one field from the available tables. You can also output a mix of dimensions and measures in a user list query.

Depending on the types of clean room you are using, Question Builder supports certain SQL dialects:

COUNT (DISTINCT CID) as recognized_CIDs
FROM client_table t1
INNER JOIN mapping_table t2
    on t1.CID = t2.CID
    WHERE t2.rampid LIKE 'X%' 
      OR  t2.rampid LIKE 'h%'

Dataset owners can create dataset analysis rules to specify a minimum aggregation threshold to preserve privacy based on their organization's risk tolerance. The data owner can set the aggregation threshold for any field considered to be a user identifier field, such as a customer ID, RampID, or loyalty ID.

When you create your query, you can specify a numeric value with an aggregation clause. It is typically recommended that you use the AGG_THRESHOLD macro, which serves as a placeholder for whatever aggregation threshold is specified for the dataset. Omitting an aggregation threshold risks failing validation.

For example, assume you want the SUM of a sales figure each day in transaction data from a partner, and your partner has enforced an aggregation threshold of 75 on their loyalty_id field. Without the aggregation threshold rule, you might attempt to run a query such as the following:

SELECT
@transaction_table.@transaction_date as txn_date,
SUM(@transaction_table.@sales_amount) as total_sales
FROM @transaction_table
WHERE txn_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY txn_date
;

This query will fail validation if an aggregation threshold analysis rule is in place. You could include an aggregation of at least 75 on loyalty_id as follows:

SELECT
@transaction_table.@transaction_date as txn_date,
SUM(@transaction_table.@sales_amount) as total_sales
FROM @transaction_table
WHERE txn_date BETWEEN '2025-01-01' AND '2025-01-31' AND
GROUP BY txn_date
HAVING COUNT(DISTINCT @transaction_table.@loyalty_id) >= 75
;

This approach will pass the analysis rules validation. However, you may not always know if your partners' datasets have aggregation threshold rules in place and what their threshold values are. In such cases, the best practice is to use our alternative option for dynamically allowing a query to enforce an aggregation threshold without needing knowledge of what the threshold value should be. This uses the appended _AGG_THRESHOLD macro in place of a hard-coded integer using the following syntax:

@table_name.@field_name_AGG_THRESHOLD

Where: You replace table_name with your dataset macro and field_name with your field macro and append _AGG_THRESHOLD.

The above query would be re-written to support this like so:

SELECT
@transaction_table.@transaction_date as txn_date,
SUM(@transaction_table.@sales_amount) as total_sales
FROM @transaction_table
WHERE txn_date BETWEEN '2025-01-01' AND '2025-01-31' AND
GROUP BY txn_date
HAVING COUNT(DISTINCT @transaction_table.@loyalty_id) >= @transaction_table.@loyalty_id_AGG_THRESHOLD
;

When the _AGG_THRESHOLD macro is used, LiveRamp will check whether an aggregation threshold rule is applied to the referenced dataset. If not, we will dynamically replace this value in your query with 0, and if so, we will replace it with the value for the specified aggregation threshold. It is best practice to use the _AGG_THRESHOLD macro in all cases, so you can reuse questions across collaborations and develop questions for multiple partners who may have varying aggregation thresholds depending on their organizations' privacy policies.

When creating an analytical rule for a dataset, data owners can specify the following aggregate SQL functions that can be run against the dataset for certain fields:

For example, a dataset owner could create a dataset analysis rule that only allows a query to run if Count Distinct is applied to the specified field. In that case, your query must use Count Distinct on the specified field at least once. Otherwise, the query cannot be run. This is an added privacy protection policy many data owners put in place to limit the risk of leaking privacy for individuals whose data is included in the dataset.

If the dataset owner limits the functions you can use, you will receive an error if these functions are not applied to the specified field when generating aggregates. The failure notice will indicate the needed query modifications. To avoid such errors, communicate with the dataset owner to know what is allowed.

For information about dataset analysis rules, see "Definition of Analytical Rules".