You can leverage the Flows feature if you have multi-step operations to execute within a clean room. A flow is a connected set of questions allowing for chained operations on data. For example, if you wanted to get the average ROAS across two of your campaigns, you could create a flow that looks like the following:
- Clean room question (CRQ) node 1: ROAS question from step 5 feeds into node 3.
- CRQ node 2: ROAS question from step 5 feeds into node 3.
- CRQ node 3: Average measures question averaging outputs of nodes 1 and 2.
The first two nodes run in parallel, and then their outputs are queried.
The following example uses the cleanrooms endpoint via the following calls:
- POST /cleanrooms/{cleanroomId}/cleanroom-flows
- GET /cleanrooms/{cleanroomId}/cleanroom-flows/{cleanroomFlowId}/flow-run-parameters
- POST /cleanrooms/{cleanroomId}/cleanroom-flows/{cleanroomFlowId}/cleanroom-flow-runs
- GET /cleanrooms/{cleanroomId}/cleanroom-flow-runs/{cleanroomFlowRunId}/status
- POST /cleanrooms/{cleanroomId}/cleanroom-flow-runs/{cleanroomFlowRunId}/resume-flow-run
- GET /cleanrooms/{cleanroomId}/cleanroom-flow-runs/{cleanroomFlowRunId}/report
# Create an additional question for use as node 3 in the flow.
query_str = """
SELECT
ADVERTISER_NAME,
CAMPAIGN_NAME,
ATTRIBUTION_MODEL,
AVG(ATTRIBUTED_GROSS_AMT_SCALED) AS AVG_ATTRIBUTED_GROSS_AMT_SCALED,
AVG(ATTRIBUTED_GROSS_AMT) AS AVG_ATTRIBUTED_GROSS_AMT,
AVG(ATTRIBUTED_CONVERSIONS) AS AVG_ATTRIBUTED_CONVERSIONS,
AVG(ATTRIBUTED_CONVERSIONS_SCALED) AS AVG_ATTRIBUTED_CONVERSIONS_SCALED,
AVG(COST) AS AVG_COST
FROM (
-- Combine the two datasets using UNION ALL
SELECT
@roas_measures_1.@ADVERTISER_NAME,
@roas_measures_1.@CAMPAIGN_NAME,
@roas_measures_1.@ATTRIBUTION_MODEL,
@roas_measures_1.@ATTRIBUTED_GROSS_AMT_SCALED,
@roas_measures_1.@ATTRIBUTED_GROSS_AMT,
@roas_measures_1.@ATTRIBUTED_CONVERSIONS,
@roas_measures_1.@ATTRIBUTED_CONVERSIONS_SCALED,
@roas_measures_1.@COST
FROM @roas_measures_1
UNION ALL
SELECT
@roas_measures_2.@ADVERTISER_NAME,
@roas_measures_2.@CAMPAIGN_NAME,
@roas_measures_2.@ATTRIBUTION_MODEL,
@roas_measures_2.@ATTRIBUTED_GROSS_AMT_SCALED,
@roas_measures_2.@ATTRIBUTED_GROSS_AMT,
@roas_measures_2.@ATTRIBUTED_CONVERSIONS,
@roas_measures_2.@ATTRIBUTED_CONVERSIONS_SCALED,
@roas_measures_2.@COST
FROM @roas_measures_2
) combined_data
GROUP BY
ADVERTISER_NAME,
CAMPAIGN_NAME,
ATTRIBUTION_MODEL;
"""
question_details = {
"questionDetails" : {
"title" : "API Tutorial: Average ROAS Query",
"category" : "API Tutorial",
"tags" : ["API Tutorial"],
"description" : "API Tutorial Avg ROAS Flow Example",
"hasDateParameters" : "false",
"hasDateFilter" : "false",
"isAnalytics" : "true",
"isUserListQuestion" : "false",
"isCleanComputeQuestion" : "false",
"outputFormat" : "REPORT",
"visualization" : "NO_CHART",
"hasMultipleOutputs" : "false",
"queryDetails" : [
{"query": query_str,
"cleanRoomType": "Hybrid",
"queryLanguage": "SQL"}
],
"dataTypes" : [
{
"macro": "roas_measures_1",
"datasetID": "",
"importDataType": "Generic",
"dataSetTypeFields": [
{
"name" : "ADVERTISER_NAME",
"type" : "STRING"
},
{
"name" : "CAMPAIGN_NAME",
"type" : "STRING"
},
{
"name": "ATTRIBUTION_MODEL",
"type": "STRING"
},
{
"name" : "ATTRIBUTED_GROSS_AMT_SCALED",
"type" : "DOUBLE"
},
{
"name" : "ATTRIBUTED_CONVERSIONS",
"type" : "LONG"
},
{
"name" : "ATTRIBUTED_CONVERSIONS_SCALED",
"type" : "DOUBLE"
},
{
"name" : "COST",
"type" : "DOUBLE"
}
]
},
{
"macro": "roas_measures_2",
"datasetID": "",
"importDataType": "Generic",
"dataSetTypeFields": [
{
"name" : "ADVERTISER_NAME",
"type" : "STRING"
},
{
"name" : "CAMPAIGN_NAME",
"type" : "STRING"
},
{
"name": "ATTRIBUTION_MODEL",
"type": "STRING"
},
{
"name" : "ATTRIBUTED_GROSS_AMT_SCALED",
"type" : "DOUBLE"
},
{
"name" : "ATTRIBUTED_CONVERSIONS",
"type" : "LONG"
},
{
"name" : "ATTRIBUTED_CONVERSIONS_SCALED",
"type" : "DOUBLE"
},
{
"name" : "COST",
"type" : "DOUBLE"
}
]
}
],
"parameters" : [],
"measures" : [
{
"name" : "AVG_ATTRIBUTED_GROSS_AMT_SCALED",
"type" : "DOUBLE",
"isIdentity" : "false",
"noise" : "false"
},
{
"name" : "AVG_ATTRIBUTED_GROSS_AMT",
"type" : "DOUBLE",
"isIdentity" : "false",
"noise" : "false"
},
{
"name" : "AVG_ATTRIBUTED_CONVERSIONS",
"type" : "LONG",
"isIdentity" : "false",
"noise" : "false"
},
{
"name" : "AVG_ATTRIBUTED_CONVERSIONS_SCALED",
"type" : "DOUBLE",
"isIdentity" : "false",
"noise" : "false"
},
{
"name" : "AVG_COST",
"type" : "DOUBLE",
"isIdentity" : "false",
"noise" : "false"
}
],
"dimensions" : [
{
"name" : "ADVERTISER_NAME",
"type" : "STRING"
},
{
"name" : "CAMPAIGN_NAME",
"type" : "STRING"
},
{
"name" : "ATTRIBUTION_MODEL",
"type" : "STRING"
}
],
"userListOutputs" : []
},
"id" : "",
"cleanRoomID" : "<YOUR_CLEANROOM_ID_HERE>",
"organizationID" : "<YOUR_ORGANIZATION_ID_HERE>",
"settings" : {
"queryValidation" : "false"
},
"cleanRoomID" : "<YOUR_CLEANROOM_ID_HERE>"
}
# Create the question via API
response = cleanroom_api.create_questions(question_details)
print(json.dumps(response, indent=4))
# Record the cleanroom question ID in the response.
Now that you've created the question, which will act as the third node in your flow, you can create the flow itself.
The following example implements a flow created with the following nodes:
- CRQ node 1: ROAS question from step 5 feeds into node 3.
- CRQ node 2: ROAS question from step 5 feeds into node 3.
- CRQ node 3: Average measures question averaging outputs of nodes 1 and 2.
Note: Flows use question display IDs rather than clean room question (CRQ) IDs.
This example will use the cleanrooms endpoint and the following call:
# Get question display IDs; be sure to note them or store them from the response.
question_ids = [
"<YOUR_QUESTION_1_ID_HERE>",
"<YOUR_QUESTION_2_ID_HERE>"
]
question_details = [
{
"cleanRoomID" : "<YOUR_CLEANROOM_ID_HERE>",
"version" : "v1"
},
{
"cleanRoomID" : "<YOUR_CLEANROOM_ID_HERE>",
"version" : "v1"
}
]
for question_id, details in zip(question_ids, question_details):
response = cleanroom_api.get_question_details(question_id,details)
print(json.dumps(response, indent=4))
# Prepare the call body with the flow parameters. Assumes all partner organizations have access to results.
# Using the '_' indicates the occurrences of a given node. For example, Q-103758_1 and Q-103758_2 indicate that the same question is used twice in the flow.
# Parent ID refers to the "upstream" node that the specified node connects to. This refers to the node it inherits inputs from.
# When you define a "dataset", this is the output of the prior node used to define the field mappings between nodes (edge mappings).
cleanroom_id = "<YOUR_CLEANROOM_ID_HERE>"
flow_details = {
"flowName" : "Average ROAS Metrics",
"description" : "Provides the average ROAS metrics across 2 campaigns.",
"nodes" : [
{
"displayID": "<YOUR_QUESTION_1_DISPLAY_ID_HERE>_1",
"settings" : {
"computeCapacity" : "DEFAULT"
}
},
{
"displayID" : "<YOUR_QUESTION_1_DISPLAY_ID_HERE>_2",
"settings" : {
"computeCapacity" : "DEFAULT"
}
},
{
"displayID" : "<YOUR_QUESTION_2_DISPLAY_ID_HERE>_1",
"datasets" : {
"roas_measures_1" : {
"parentID" : "<YOUR_QUESTION_1_DISPLAY_ID_HERE>_1",
"mappings" : {
"ADVERTISER_NAME" : "ADVERTISER_NAME",
"CAMPAIGN_NAME" : "CAMPAIGN_NAME",
"ATTRIBUTION_MODEL" : "ATTRIBUTION_MODEL",
"ATTRIBUTED_GROSS_AMT_SCALED" : "ATTRIBUTED_GROSS_AMT_SCALED",
"ATTRIBUTED_CONVERSIONS" : "ATTRIBUTED_CONVERSIONS",
"ATTRIBUTED_CONVERSIONS_SCALED" : "ATTRIBUTED_CONVERSIONS_SCALED",
"COST" : "COST"
}
},
"roas_measures_2" : {
"parentID" : "<YOUR_QUESTION_1_DISPLAY_ID_HERE>_2",
"mappings" : {
"ADVERTISER_NAME" : "ADVERTISER_NAME",
"CAMPAIGN_NAME" : "CAMPAIGN_NAME",
"ATTRIBUTION_MODEL" : "ATTRIBUTION_MODEL",
"ATTRIBUTED_GROSS_AMT_SCALED" : "ATTRIBUTED_GROSS_AMT_SCALED",
"ATTRIBUTED_CONVERSIONS" : "ATTRIBUTED_CONVERSIONS",
"ATTRIBUTED_CONVERSIONS_SCALED" : "ATTRIBUTED_CONVERSIONS_SCALED",
"COST" : "COST"
}
}
},
"settings" : {
"computeCapacity" : "DEFAULT"
}
}
]
}
# Create the Flow
response = cleanroom_api.create_cleanroom_flows(cleanroom_id,flow_details)
print(json.dumps(response, indent=4))