-- Last touch attribution using Snowflake Collaboration Data Clean Rooms.
-- This file represents Collaborator 2, who is the analysis runner and data provider.
-- Collaborator 2 has touchpoint data (clicks/impressions).

-- Run this worksheet in Collaborator 2's Snowflake account.

-- This file demonstrates the following actions:
-- * Join a collaboration created by Collaborator 1.
-- * Register and link a data offering with touchpoint data.
-- * Run a last touch attribution analysis.
-- * Activate results to Collaborator 1.

-- This scenario involves two collaborators:
--   Collaborator 1 (other account): Owner and data provider (conversion data).
--   Collaborator 2 (this account): Analysis runner and data provider (touchpoint data).
-- Collaborator 2 runs the analysis, which joins touchpoint and conversion data
-- to determine last touch attribution. Results are activated to Collaborator 1.

-- For details about the features and use cases of this template, see the Snowflake documentation:
-- https://docs.snowflake.com/user-guide/cleanrooms/last-touch-template

-- Sample data can be generated by running the sample data generator Python script
-- linked from the documentation.

-- Replace <...> placeholders with the appropriate values.

USE WAREHOUSE APP_WH;
USE ROLE SAMOOHA_APP_ROLE;

-- Secondary roles must be disabled to call JOIN or LINK_DATA_OFFERING.
USE SECONDARY ROLES NONE;

------------------------------------------------------------
-- View and join the collaboration.
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_COLLABORATIONS();

-- Use SOURCE_NAME and OWNER_ACCOUNT values from the response.
SET collaboration_name = '<collaboration_name>';
SET owner_data_sharing_id = '<collab1_account_data_sharing_id>';

-- Review and join the collaboration.
-- Joining is asynchronous. Call GET_STATUS until the status is JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.REVIEW(
    $collaboration_name,
    $owner_data_sharing_id
);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.JOIN($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

------------------------------------------------------------
-- Register and link Collaborator 2's data offering.
-- The schema_and_template_policies section replaces the old join and column policies.
-- HASHED_PHONE is the join column. Because it is listed as a join column, it is
--   renamed to the column type.
-- CLICK_TIME and CHANNEL are passthrough columns usable in query results.
-- activation_allowed: TRUE on HASHED_PHONE enables activation on that column.
------------------------------------------------------------

-- Collaborator 2's touchpoint data table. Replace with your own table.
SET collab2_table = '<fully_qualified_collab2_table>';

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v1
    name: collab2_touchpoint_data
    datasets:
     - alias: touchpoints
       data_object_fqn: <fully_qualified_collab2_table>
       object_class: custom
       allowed_analyses: template_only
       schema_and_template_policies:
         hashed_phone:
           category: join_standard
           column_type: hashed_phone_b64_encoded
           activation_allowed: TRUE
         click_time:
           category: passthrough
         channel:
           category: passthrough
    $$
);
SET collab2_data_offering_id = '<collab2_data_offering_id>';

-- Link the data offering into the collaboration, available to both collaborators.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
    $collaboration_name,
    $collab2_data_offering_id,
    ['collab2']
);

------------------------------------------------------------
-- View available data offerings and templates.
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS(
    $collaboration_name
);

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES(
    $collaboration_name
);

------------------------------------------------------------
-- Run the analysis.
-- source_table[0] maps to Collaborator 1's conversion data.
-- source_table[1] maps to Collaborator 2's touchpoint data.
-- Replace placeholder view names with actual values from VIEW_DATA_OFFERINGS.
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    description: Last touch attribution analysis
    template: last_touch_attribution_analysis_v4
    template_configuration:
      view_mappings:
        source_tables:
          - collab1.collab1_conversion_data_v1.conversions
          - collab2.collab2_touchpoint_data_v1.touchpoints
      arguments:
        collab1_join_key: p1.HASHED_PHONE_B64_ENCODED
        collab2_join_key: p2.HASHED_PHONE_B64_ENCODED
        touchpoint_timestamp: p2.CLICK_TIME
        attribution_dimension: p2.CHANNEL
        conversion_timestamp: p1.TRANSACTION_TIME
        conversion_value: p1.AMOUNT_SPENT
    $$
);


------------------------------------------------------------
-- Activate results to Collaborator 1.
-- Run the activation template with the activation section.
-- The segment_name helps Collaborator 1 filter results to this query.
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    description: Activate last touch attribution results to Collaborator 1
    template: '<activation_last_touch_attribution_results_template>'
    template_configuration:
      view_mappings:
        source_tables:
          - '<collab1_data_offering_view_name>'
          - '<collab2_data_offering_view_name>'
      activation:
        snowflake_collaborator: collab1
        segment_name: lta_activation_segment_1
    $$
);

-- SWITCH TO Collaborator 1 to view and process the activation results.
