-- This workbook demonstrates the use of an audience lookalike modeling 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/lookalike-audience-modeling-template).

-- Sample 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.

USE ROLE samooha_app_role;
USE WAREHOUSE app_wh;

SET cleanroom_name = 'LAL_ACTIVATION'; 
SET provider_deployment = '<provider_account_locator>'; 

-- Name of table generated by sample data generator.
-- Replace with your own source table.
SET consumer_table = '<fully_qualified_consumer_table_name>';
SET consumer_join_key = 'HASHED_EMAIL';

SET provider_features_table = '<fully_qualified_provider_features_table_name>'; 
SET provider_join_key = 'HASHED_EMAIL';

-- Install and configure the clean room.
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom(
    $cleanroom_name, 
    $provider_deployment
);

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

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

-- Run the analysis.
CALL samooha_by_snowflake_local_db.consumer.run_analysis(
    $cleanroom_name,         
    'lookalike_analysis_for_activation',   
    [$consumer_table],
    [$provider_features_table],                                             
    object_construct(
        'provider_join_key', $provider_join_key,
        'consumer_join_key', $consumer_join_key,
        'dimensions', array_construct('p.STATUS', 'p.AGE_BAND', 'p.REGION_CODE', 'p.DAYS_ACTIVE'),
        'filter_clause', 'c.SALES_DLR >= 2500',
        'score_threshold', 0.5
    )
);

-- Activate the results to the provider, labeled with this segment name.
-- Use the segment name in the provider to filter results.
SET segment_name = 'LAL_GENERIC_ACTIVATION';
CALL samooha_by_snowflake_local_db.consumer.run_activation(
    $cleanroom_name,
    $segment_name,
    'activation_lookalike_results', 
    [$consumer_db || '.' || $consumer_schema || '.' || $consumer_table], 
    [$provider_features_table],                                             
    object_construct(
        'provider_join_key', $provider_join_key,
        'consumer_join_key', $consumer_join_key,
        'dimensions', array_construct('p.STATUS', 'p.AGE_BAND', 'p.REGION_CODE', 'p.DAYS_ACTIVE'),
        'filter_clause', 'c.SALES_DLR >= 2500',
        'score_threshold', 0.5
    ),
    FALSE 
);
