-- Last-touch attribution using Snowflake Collaboration Data Clean Rooms.
-- This file represents Collaborator 1, who is the collaboration owner and data provider.
-- Collaborator 1 has conversion data (purchases).

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

-- This file demonstrates the following actions:
-- * Register a data offering with conversion data.
-- * Register an analysis template and an activation template for last-touch attribution.
-- * Create a collaboration that allows Collaborator 2 to run the analysis and
--   activate results back to Collaborator 1.
-- * After Collaborator 2 runs the analysis and activates results, view the activation results.

-- This scenario involves two collaborators:
--   Collaborator 1 (this account): Owner and data provider (conversion data).
--   Collaborator 2 (other 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;
USE SECONDARY ROLES NONE;

-- Collaborator 1's conversion data table. Replace with your own table.
SET collab1_table = '<fully_qualified_collab1_table>';

------------------------------------------------------------
-- Register Collaborator 1's data offering.
-- The schema_and_template_policies section replaces the old join and column policies.
-- HASHED_PHONE is the join column.
-- TRANSACTION_TIME and AMOUNT_SPENT are passthrough columns usable in query results.
-- activation_allowed: TRUE on HASHED_PHONE enables activation on that column.
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v1
    name: collab1_conversion_data
    datasets:
     - alias: conversions
       data_object_fqn: <fully_qualified_collab1_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
         transaction_time:
           category: passthrough
         amount_spent:
           category: passthrough
    $$
);
SET collab1_data_offering_id = '<collab1_data_offering_id>';

------------------------------------------------------------
-- Register the analysis template.
-- This template joins Collaborator 1's conversion data (source_table[0])
-- with Collaborator 2's touchpoint data (source_table[1]) to compute
-- last-touch attribution.
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: last_touch_attribution_analysis
version: v1
type: sql_analysis
description: Last-touch attribution analysis joining conversion and touchpoint data.
template:
  DECLARE
      res RESULTSET;
  BEGIN
      CREATE OR REPLACE TABLE cleanroom.activation_data_last_touch_attribution_results AS
      WITH
      joined_events AS (
        SELECT
            {{ collab1_join_key | sqlsafe | join_policy }} AS join_key,
            {{ conversion_timestamp | sqlsafe | column_policy }} AS conversion_time,
            {{ conversion_value | sqlsafe | column_policy }} AS conversion_value,
            {{ collab2_join_key | sqlsafe | join_policy }} AS touchpoint_join_key,
            {{ touchpoint_timestamp | sqlsafe | column_policy }} AS touch_time,
            {{ attribution_dimension | sqlsafe | column_policy }} AS dimension
        FROM
            IDENTIFIER({{ source_table[0] }}) AS p1
        JOIN
            IDENTIFIER({{ source_table[1] }}) AS p2
            ON {{ collab1_join_key | sqlsafe | join_policy }} = {{ collab2_join_key | sqlsafe | join_policy }}
        WHERE
            {{ touchpoint_timestamp | sqlsafe | column_policy }} <= {{ conversion_timestamp | sqlsafe | column_policy }}
      ),
      ranked_touches AS (
          SELECT
              *,
              ROW_NUMBER() OVER(
                  PARTITION BY join_key, conversion_time
                  ORDER BY touch_time DESC
              ) AS touch_rank
          FROM joined_events
      ),
      last_touch_events AS (
          SELECT dimension, conversion_value
          FROM ranked_touches
          WHERE touch_rank = 1
      )
      SELECT
          dimension,
          COUNT(*) AS total_conversions,
          SUM(conversion_value) AS total_conversion_value,
          AVG(conversion_value) AS avg_conversion_value
      FROM last_touch_events
      GROUP BY dimension
      ORDER BY total_conversions DESC;

      res := (SELECT * FROM cleanroom.activation_data_last_touch_attribution_results);
      RETURN TABLE(res);
  END;
$$);

SET analysis_template_id = '<analysis_template_id>';

------------------------------------------------------------
-- Register the activation template.
-- This template is used by the activation process to locate the results table.
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: activation_last_touch_attribution_results
version: v1
type: sql_activation
description: Activation template for last-touch attribution results.
template:
  BEGIN
      RETURN 'last_touch_attribution_results';
  END;
$$);
SET activation_template_id = '<activation_template_id>';

------------------------------------------------------------
-- Create the collaboration.
-- Collaborator 1 is the owner and a data provider.
-- Collaborator 2 is the analysis runner and a data provider.
-- Collaborator 2 can run both the analysis and activation templates.
-- activation_destinations allows Collaborator 2 to activate results to Collaborator 1.
-- Get account data sharing IDs:
--   SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
------------------------------------------------------------
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.INITIALIZE(
  $$
  api_version: 2.0.0
  spec_type: collaboration
  name: last_touch_attribution
  owner: collab1
  collaborator_identifier_aliases:
    collab1: <collab1_account_data_sharing_id>
    collab2: <collab2_account_data_sharing_id>
  analysis_runners:
    collab2:
      data_providers:
        collab1:
          data_offerings:
          - id: <collab1_data_offering_id>
        collab2:
          data_offerings: []
      templates:
      - id: <analysis_template_id>
      - id: <activation_template_id>
      activation_destinations:
        snowflake_collaborators:
        - collab1
  $$,
  'APP_WH'
);
SET collaboration_name = '<collaboration_name>';

-- INITIALIZE automatically joins the owner. Check status until JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

-- Verify the collaboration is visible.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_COLLABORATIONS();

------------------------------------------------------------
-- SWITCH TO Collaborator 2 to join the collaboration, add a data offering,
-- run the analysis, and activate results.
------------------------------------------------------------

-- After Collaborator 2 activates results, Collaborator 1 can view the activation.
-- Call VIEW_ACTIVATIONS until the status is SHARED, then call PROCESS_ACTIVATION.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_ACTIVATIONS($collaboration_name);

-- When the activation status is SHARED, process it.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.PROCESS_ACTIVATION(
    $collaboration_name
);

-- Query the activation results.
SELECT *
    FROM <results_table_name>
    WHERE segment = '<segment_name>';

------------------------------------------------------------
-- Clean up.
------------------------------------------------------------
-- Multi-step teardown process. Doesn't delete registered resources.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

-- When GET_STATUS reports LOCAL_DROP_PENDING, call TEARDOWN again.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);
