Custom Collaboration Template Sample JSON

 "name": "CustomTemplateExample",
  "description": "Example of Clean Room Customized Templates",
  "tables": {
    "tableConfigs": [
      {
        "name": "Provider",
        "description": "Provider table for overlap use case.",
        "role": "Provider",
        "columns": [
          {
            "name": "SegmentName",
            "description": "Name of the segment.",
            "type": "STRING"
          },
          {
            "name": "Category",
            "description": "Category of the segment.",
            "type": "STRING"
          },
          {
            "name": "ParentCategory",
            "description": "Parent Category of the segment.",
            "type": "STRING"
          },
          {
            "name": "SegmentSize",
            "description": "Size of the segment.",
            "type": "INTEGER"
          },
          {
            "name": "MemberId",
            "description": "Member ID, only aggregation or join is allowed.",
            "type": "STRING"
          },
          {
            "name": "HashedEmailAddress",
            "description": "Contact ID hashed with SHA256.",
            "type": "HASHED_EMAIL"
          }
        ]
      },
      {
        "name": "Consumer",
        "description": "Consumer table.",
        "role": "Consumer",
        "columns": [
          {
            "name": "SegmentId",
            "description": "ID of the Segment.",
            "type": "STRING"
          },
          {
            "name": "SegmentName",
            "description": "Name of the segment.",
            "type": "STRING"
          },
          {
            "name": "SegmentSize",
            "description": "Size of the segment.",
            "type": "NUMBER"
          },
          {
            "name": "HashedEmailAddress",
            "description": "Contact ID hashed with SHA256.",
            "type": "HASHED_EMAIL"
          }
        ]
      }
    ]
  },
  "queries": {
    "queryConfigs": [
      {
        "name": "OverlapQuery",
        "useCase": "Overlap",
        "description": "Query to calculate the overlap between consumers and providers.",
        "type": "SQL",
        "source": "SELECT  Consumer.SegmentId AS Segment_Id__c,  Consumer.SegmentName AS Segment_Name__c,  Provider.SegmentName AS Provider_Segment_Name__c,  Provider.Category AS Provider_Segment_Category__c,  Provider.ParentCategory AS Provider_Segment_Parent_Category__c,  Provider.SegmentSize AS Provider_Segment_Size__c,  Consumer.SegmentSize AS Segment_Size__c,  COUNT(DISTINCT Provider.MemberId) AS Matching_Population_Count__c FROM  Consumer JOIN  Provider ON Consumer.HashedEmailAddress = Provider.HashedEmailAddress GROUP BY Consumer.SegmentId, Consumer.SegmentName, Provider.SegmentName, Provider.Category, Provider.ParentCategory, Provider.SegmentSize, Consumer.SegmentSize HAVING COUNT(DISTINCT Provider.MemberId) >= :threshold_count",
        "queryParameters": {
          "queryParamConfigs": [
            {
              "name": "threshold_count",
              "type": "INTEGER",
              "description": "The threshold count."
            }
          ]
        },
        "referencedTables": [
          "Consumer",
          "Provider"
        ]
      }
    ]
  }
}