-- This workbook demonstrates the use of a last touch attribution template. This workbook 
-- should be run from the provider's account.
-- This template enables businesses to measure the effectiveness of their marketing channels
-- by joining consumer and provider datasets in a Snowflake Data Clean Room.
-- Results are activated to the provider.

-- As coded here, the provider and consumer must be in the same organization and same cloud 
-- hosting region. To publish a clean room to use outside your organization, publish an 
-- EXTERNAL clean room.

-- This code provides a user form so that it can be run by a consumer using either the
-- clean rooms API or the UI.

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

-- Provider and consumer data can be generated by running the sample data generator python script
-- linked from the documentation.

-- This workbook shows how to install and run the clean room in code, but you can also install 
-- and run it using the clean rooms UI, because the provider created a UI configuration form 
-- for it.

-- Add values for placeholders in the code where indicated.

-- Create the clean room.
-- This part is standard.
USE ROLE samooha_app_role;
USE WAREHOUSE app_wh;
SET cleanroom_name = 'E2E_LAST_TOUCH_ATTRIBUTION';
SET consumer_locator = '<consumer_locator>';
SET consumer_data_sharing_id = '<consumer_data_account_sharing_id>';
CALL samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');
SET provider_table_name = '<fully_qualified_provider_table_name>'

-- Link provider data into the clean room.
-- TEMP_PUBLIC_KEY is needed for provider activation.
CALL samooha_by_snowflake_local_db.provider.link_datasets(
    $cleanroom_name, 
    [
      $provider_table_name,
      'samooha_by_snowflake_local_db.LIBRARY.TEMP_PUBLIC_KEY'
    ]
);

CALL samooha_by_snowflake_local_db.provider.set_join_policy(
    $cleanroom_name, 
    [$provider_table_name || ':HASHED_PHONE']
);

-- Analysis template.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'last_touch_attribution_analysis',
    $$
    DECLARE
        res RESULTSET;
    BEGIN
        
        CREATE OR REPLACE TABLE cleanroom.activation_data_last_touch_attribution_results AS
        WITH
        joined_events AS (
            SELECT
                
                p.{{ provider_join_key | sqlsafe }} as provider_join_key,
                p.{{ conversion_timestamp | sqlsafe }} as conversion_time,
                p.{{ conversion_value | sqlsafe }} as conversion_value,
                
                
                c.{{ consumer_join_key | sqlsafe }} as consumer_join_key,
                c.{{ touchpoint_timestamp | sqlsafe }} as touch_time,
                c.{{ attribution_dimension | sqlsafe }} as dimension
            FROM
                identifier({{ source_table[0] }}) as p 
            JOIN
                identifier({{ my_table[0] }}) as c     
                ON p.{{ provider_join_key | sqlsafe }} = c.{{ consumer_join_key | sqlsafe }}
            WHERE
               
                c.{{ touchpoint_timestamp | sqlsafe }} <= p.{{ conversion_timestamp | sqlsafe }}
        ),
        
        ranked_touches AS (
            SELECT
                *,
                
                ROW_NUMBER() OVER(
                    PARTITION BY provider_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;
    $$
);

-- Activation template.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'activation_last_touch_attribution_results',
    $$
    BEGIN
        RETURN 'last_touch_attribution_results';
    END;
    $$
);

CALL samooha_by_snowflake_local_db.provider.set_column_policy(
    $cleanroom_name, 
    [
      'last_touch_attribution_analysis:' || $provider_table_name || ':TRANSACTION_TIME',
      'last_touch_attribution_analysis:' || $provider_table_name || ':AMOUNT_SPENT'
    ]
);

-- Add a user form to enable this template to be run in the clean rooms UI
-- as well as in code.
CALL samooha_by_snowflake_local_db.provider.add_ui_form_customizations(
    $cleanroom_name,
    'last_touch_attribution_analysis',
    { 
        'display_name': 'Last Touch Attribution (Generic)',
        'description': 'Calculates last-touch attribution based on a specified channel or dimension.',
        'methodology': 'Identifies the last touchpoint (for example, click) a user had before a conversion (for example, purchase) and aggregates conversion value by that touchpoint dimension (for example, channel).',
        'warehouse_hints': {
            'warehouse_size': 'medium'
        },
        'render_table_dropdowns': {
            'render_consumer_table_dropdown': true,
            'render_provider_table_dropdown': true
        },
        'activation_template_name': 'activation_last_touch_attribution_results',
        'enabled_activations': ['provider','consumer']
    },
    { 
        'consumer_join_key': {
            'display_name': 'Consumer Join Key (for example, Hashed ID)',
            'description': 'Select the join key from your (consumer) table.',
            'group': 'Consumer Table Configuration',
            'order': 1,
            'type': 'dropdown',
            'references': ['CONSUMER_JOIN_POLICY'],
            'consumer_parent_table_field': 'my_table' 
        },
        'touchpoint_timestamp': {
            'display_name': 'Consumer Touchpoint Timestamp (for example, Click Time)',
            'description': 'Select the timestamp of the touchpoint event (for example, click, impression).',
            'group': 'Consumer Table Configuration',
            'order': 2,
            'type': 'dropdown',
            'references': ['CONSUMER_COLUMN_POLICY'],
            'consumer_parent_table_field': 'my_table'
        },
        'attribution_dimension': {
            'display_name': 'Consumer Attribution Dimension (for example, Channel)',
            'description': 'Select the column to group results by (for example, Channel, Campaign ID).',
            'group': 'Consumer Table Configuration',
            'order': 3,
            'type': 'dropdown',
            'references': ['CONSUMER_COLUMN_POLICY'],
            'consumer_parent_table_field': 'my_table'
        }, ji
        'provider_join_key': {
            'display_name': 'Provider Join Key (for example, Hashed ID)',
            'description': 'Select the join key from the provider table.',
            'group': 'Provider Table Configuration',
            'order': 4,
            'type': 'dropdown',
            'references': ['PROVIDER_JOIN_POLICY'],
            'provider_parent_table_field': 'source_table' 
        },
        'conversion_timestamp': {
            'display_name': 'Provider Conversion Timestamp (for example, Transaction Date)',
            'description': 'Select the timestamp of the conversion event (for example, purchase time).',
            'group': 'Provider Table Configuration',
            'order': 5,
            'type': 'dropdown',
            'references': ['PROVIDER_COLUMN_POLICY'],
            'provider_parent_table_field': 'source_table'
        },
        'conversion_value': {
            'display_name': 'Provider Conversion Value (for example, Amount Spent)',
            'description': 'Select the numeric column representing the conversion value (for example, revenue).',
            'group': 'Provider Table Configuration',
            'order': 6,
            'type': 'dropdown',
            'references': ['PROVIDER_COLUMN_POLICY'],
            'provider_parent_table_field': 'source_table'
        }
    },
    { 
        'default_output_type': 'BAR',
        'measure_columns': ['DIMENSION', 'TOTAL_CONVERSIONS', 'TOTAL_CONVERSION_VALUE', 'AVG_CONVERSION_VALUE']
    }
);

-- Share and publish the clean room.
CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  $consumer_locator,
  $consumer_data_sharing_id);

CALL samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', '0');

CALL samooha_by_snowflake_local_db.provider.enable_consumer_run_analysis(
    $cleanroom_name, 
    [$consumer_locator] 
);

CALL samooha_by_snowflake_local_db.provider.request_provider_activation_consent(
    $cleanroom_name, 
    'activation_last_touch_attribution_results'
);

CALL samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing($cleanroom_name);

-- SWITCH TO CONSUMER to install the clean room, run the analysis, and activate results.

-- When the consumer has activated results to your account, you can read them by running
-- the following query. Note that it can take several minutes for data to propagate from
-- the consumer account to the provider account.
SELECT *
   FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_ACTIVATION_SUMMARY
   WHERE segment = '<segment_name_specified_by_consumer>';


-- Optionally delete the clean room at the end, to clean up all your resources.
-- CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name) ;
