• Done

    • Cleanup - Challenge Targeting files

    Owned by Lavanya Davluri, created
    Last updated: Aug 29, 2023
    1 min read

    WIP – Measurement notebook to come

    We have made an effort to clean up the historical datasets and have them loaded into a single table.

     Summary

    Here are all the tables we have combined and the schema of the new table that would have all of the targeting for Challenges and manually targeted offers

    1. Main table that contains all the targeting information

      1. offers.ca_challenge_campaign_log

    offers.ca_challenge_campaign_log

    MAP TO 

    registered_account_id

    registered_account_id

    avg_basket_size

    avg_basket_size

    freq_rounded

    freq_rounded

    p_total_cost

    total_cost

    p_num_visits

    num_visits

    p_day_part

    Empty string if null 

    p_local_time

    0 if Null 

    p_cross_sell_prods

    Empty Array() if null

    p_cross_sell_costs

    Empty Array() if null

    p_challenge_type

    String

    p_reward_points

    reward_points

    segment_name

    segment_name

    test_control_flag

    test_control_flag

    campaign_id

    From offers.api_log_challenge

    offerStartDate

    DATE_FORMAT(CAST('2022-06-13' AS DATE), 'yyyyMMdd')

    offerEndDate

    DATE_FORMAT(CAST('2022-06-19' AS DATE), 'yyyyMMdd') 

    runDate

    Partition date key from offers.api_log_challenge

    runId

    runid from offers.api_log_challenge

    Cleanup Notebook : https://dbc-9601831d-0d70.cloud.databricks.com/?o=2399142386962378#notebook/3119180352079278/command/3119180352079534

    Excel for Cleanup : Historical Targeting Files Cleanup

    Run ID would be null when test_control_flag is ‘control’