We have the following files in Data bricks to import targeting for challenges and manually triggered offers.
We have made an effort to clean up the historical datasets and have them loaded into a single table.
\uD83D\uDCD8 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
Main table that contains all the targeting information
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 |