-- Lookalike audience modeling: advertiser worksheet
-- Run this worksheet from the advertiser account.
--
-- The advertiser registers a data offering (seed audience), joins the
-- collaboration created by the publisher, links their data, and runs
-- the 3-step pipeline:
--   Step 1 (Train):    Train the model, review AUC and error rate
--   Step 2 (Score):    Score the population, review audience size
--   Step 3 (Activate): Send scored results to the publisher

-- The publisher and advertiser must be in the same organization and same cloud hosting
-- region, unless Cross-Cloud Auto-Fulfillment is enabled.

-- For details about this template, see the Snowflake documentation:
-- https://docs.snowflake.com/user-guide/cleanrooms/collab-lookalike-modeling

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

-- Replace all values in angle brackets <...> with your own values.

USE ROLE SAMOOHA_APP_ROLE;
USE WAREHOUSE APP_WH;
USE SECONDARY ROLES NONE;


--------------------------------------------------------------
-- Register the advertiser data offering.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
$$
api_version: 2.0.0
spec_type: data_offering
name: lal_advertiser_seed_audience
version: V0
description: >
  Advertiser seed audience with purchase amounts and audience segments,
  used as training labels for the lookalike model.
datasets:
  - alias: seed_audience
    data_object_fqn: <database.schema.LAL_ADVERTISER_SEED_AUDIENCE>
    allowed_analyses: template_only
    object_class: custom
    schema_and_template_policies:
      HASHED_EMAIL:
        category: join_custom
      SALES_DLR:
        category: passthrough
      AUDIENCE_SEGMENT:
        category: passthrough
$$);

SET advertiser_data_offering_id = '<advertiser_data_offering_id>';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS();


--------------------------------------------------------------
-- Review and join the collaboration.
--------------------------------------------------------------

SET collaboration_name = 'lookalike_modeling_collab';

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.REVIEW($collaboration_name, '<owner_data_sharing_id>');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.JOIN($collaboration_name);

-- Wait until status is JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);


--------------------------------------------------------------
-- Link the advertiser data offering to the collaboration.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
    $collaboration_name,
    $advertiser_data_offering_id,
    ['advertiser']
);


--------------------------------------------------------------
-- View available resources.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);


--------------------------------------------------------------
-- Step 1: Train the model.
-- Review the returned metrics (total_rows, AUC, error)
-- before proceeding to scoring.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    name: lookalike_train_run
    description: Train the lookalike model on the seed audience
    template: <train_template_id>
    template_configuration:
      view_mappings:
        source_tables:
          - publisher.<publisher_data_offering_id>.features
          - advertiser.<advertiser_data_offering_id>.seed_audience
      arguments:
        publisher_join_key: HASHED_EMAIL
        consumer_join_key: HASHED_EMAIL
        dimensions:
          - p.STATUS
          - p.AGE_BAND
          - p.REGION_CODE
          - p.DAYS_ACTIVE
        filter_clause: "c.SALES_DLR >= 2500"
    $$
);


--------------------------------------------------------------
-- Step 2: Score the population.
-- Review the returned audience size and average score
-- before proceeding to activation.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    name: lookalike_score_run
    description: Score the publisher population using the trained model
    template: <score_template_id>
    template_configuration:
      view_mappings:
        source_tables:
          - publisher.<publisher_data_offering_id>.features
          - advertiser.<advertiser_data_offering_id>.seed_audience
      arguments:
        publisher_join_key: HASHED_EMAIL
        consumer_join_key: HASHED_EMAIL
        dimensions:
          - p.STATUS
          - p.AGE_BAND
          - p.REGION_CODE
          - p.DAYS_ACTIVE
        score_threshold: 0.5
    $$
);


--------------------------------------------------------------
-- Step 3: Activate results to the publisher.
-- Sends the scored lookalike audience to the publisher account.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    name: lookalike_activation_run
    description: Activate lookalike audience results to the publisher
    template: <activation_template_id>
    template_configuration:
      activation:
        snowflake_collaborator: publisher
        segment_name: lal_lookalike_activation
    $$
);


--------------------------------------------------------------
-- SWITCH TO PUBLISHER to view and process the activation results.
--------------------------------------------------------------

--------------------------------------------------------------
-- Cleanup
--------------------------------------------------------------

-- This is a multi-step process; call GET_STATUS to check for LOCAL_DROP_PENDING,
-- then call LEAVE again.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LEAVE($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LEAVE($collaboration_name);
