-- This workbook demonstrates the use of a last touch attribution template. This workbook 
-- should be run from the consumer'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).

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

-- You can install and run the template either by using the API, as shown here, or by installing
-- and running it using the clean rooms UI.

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

USE ROLE samooha_app_role;
USE WAREHOUSE app_wh;
SET cleanroom_name = 'E2E_LAST_TOUCH_ATTRIBUTION'; 
SET provider_table_name = '<fully_qualified_consumer_table>';
SET consumer_table_name = '<fully_qualified_provider_table>';

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom(
    $cleanroom_name, 
    '<provider_account_locator>' 
);

CALL samooha_by_snowflake_local_db.consumer.link_datasets(
    $cleanroom_name, 
    [$consumer_table_name]
);

CALL samooha_by_snowflake_local_db.consumer.set_column_policy(
   $cleanroom_name,
   [
       'last_touch_attribution_analysis:' || $consumer_table || ':CLICK_TIME',
       'last_touch_attribution_analysis:' || $consumer_table || ':CHANNEL'
   ]

CALL samooha_by_snowflake_local_db.consumer.approve_provider_activation_consent($cleanroom_name,'activation_last_touch_attribution_results');

-- Run the analysis.
CALL samooha_by_snowflake_local_db.consumer.run_analysis(
    $cleanroom_name,         
    'last_touch_attribution_analysis',   
    [$consumer_table_name],
    [$provider_table_name], 
    object_construct(      
        'consumer_join_key', 'HASHED_PHONE',
        'touchpoint_timestamp', 'CLICK_TIME',
        'attribution_dimension', 'CHANNEL',
        'provider_join_key', 'HASHED_PHONE',
        'conversion_timestamp', 'TRANSACTION_TIME',
        'conversion_value', 'AMOUNT_SPENT'
    )
);

-- Activate data to the provider. Assign this segment label, which helps the provider filter results to this query.
SET segment_name = 'lta_activation_segment_1';
CALL samooha_by_snowflake_local_db.consumer.run_activation(
    $cleanroom_name,
    $segment_name,
    'activation_last_touch_attribution_results', 
    [$consumer_table_name], 
    [$provider_table_name], 
    object_construct(
        'consumer_join_key', 'HASHED_PHONE',
        'touchpoint_timestamp', 'CLICK_TIME',
        'attribution_dimension', 'CHANNEL',
        'provider_join_key', 'HASHED_PHONE',
        'conversion_timestamp', 'TRANSACTION_TIME',
        'conversion_value', 'AMOUNT_SPENT'
    ),
    FALSE 
);
