Create Athena tables

The following table: hist_conv_data

CREATE EXTERNAL TABLE `hist_conv_data`(
  `responsechannel` string, 
  `campaigncode` string, 
  `campaignname` string, 
  `treatmentcode` string, 
  `offercode` string, 
  `offername` string, 
  `encrypted_customerid` string, 
  `product` string, 
  `clickdatetime` date, 
  `convdatetime` date, 
  `disbursal_amount` double)
PARTITIONED BY ( 
  `advid` string, 
  `year` string, 
  `month` string, 
  `day` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<Bucket-Name>/<Campaign-Id>/conv_data/'
TBLPROPERTIES (
  'CRAWL_RUN_ID'='50415ee7-81c2-4243-bd7a-668cbc58a505', 
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0',
  'averageRecordSize'='27', 
  'classification'='parquet', 
  'compressionType'='none', 
  'objectCount'='6', 
  'partition_filtering.enabled'='true', 
  'recordCount'='124999', 
  'sizeKey'='1332437', 
  'typeOfData'='file')

The following table: hist_cov_data

CREATE EXTERNAL TABLE `hist_cov_data`(
  `campaignname` string, 
  `campaigncode` string, 
  `offercode` string, 
  `offername` string, 
  `customerid` string, 
  `responsetype` string, 
  `responsechannel` string, 
  `responsedatetime` timestamp, 
  `cost` string, 
  `product` string, 
  `revenue` double, 
  `treatmentcode` string, 
  `eventtype` string, 
  `date` string, 
  `time` string)
PARTITIONED BY ( 
  `advid` string, 
  `year` string, 
  `month` string, 
  `day` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<Bucket-Name>/<Campaign-Id>/cov_data/'
TBLPROPERTIES (
  'CRAWL_RUN_ID'='1a54a5c2-1d45-454b-adc5-d5cefdaf4e90', 
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'averageRecordSize'='24', 
  'classification'='parquet', 
  'compressionType'='none', 
  'objectCount'='23', 
  'partition_filtering.enabled'='true', 
  'recordCount'='40646147', 
  'sizeKey'='237530264', 
  'typeOfData'='file')

The following table: hist_rh_data

CREATE EXTERNAL TABLE `hist_rh_data`(
  `encrypted_customerid` string, 
  `responsedatetime` timestamp, 
  `actualreasoncode` string, 
  `reasoncode` string, 
  `responsechannel` string, 
  `actualchannel` string, 
  `vendor` string, 
  `campaigncode` string, 
  `campaignname` string, 
  `offercode` string, 
  `offername` string, 
  `product` string, 
  `treatmentcode` string, 
  `success_flg` string, 
  `ma_source` string, 
  `cost` string)
PARTITIONED BY ( 
  `advid` string, 
  `year` string, 
  `month` string, 
  `day` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<Bucket-Name>/<Campaign-Id>/RH_data/'
TBLPROPERTIES (
  'CRAWL_RUN_ID'='e49293ad-3f85-4c80-94a5-89f50d64b8e0', 
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'averageRecordSize'='23', 
  'classification'='parquet', 
  'compressionType'='none', 
  'objectCount'='16', 
  'partition_filtering.enabled'='true', 
  'recordCount'='40480625', 
  'sizeKey'='108418161', 
  'typeOfData'='file')

The following table: master_audience_segment_id

CREATE TABLE segmentdb_<Campaign-Id>.master_audience_segment_id (
  audience_id string,
  segment_id int)
PARTITIONED BY (`segment_id`)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/master_audience_segment_id/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='zstd'
);

The following table: transactions

CREATE TABLE segmentdb_<Campaign-Id>.transactions (
  mer_id string,
  cod_mcc string,
  cr_dr string,
  txn_amount double,
  txn_date timestamp,
  particulars string,
  encrypted_customerid string,
  advid string,
  year string,
  month string,
  day string,
  s3_path string,
  parquet_last_modified timestamp)
PARTITIONED BY (`advid`, `year`, `month`, `day`)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/transactions/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='snappy'
);

The following table: unicach

CREATE TABLE segmentdb_<Campaign-Id>.unicach (
  encrypted_customerid string,
  responsedatetime timestamp,
  actualreasoncode string,
  reasoncode string,
  responsechannel string,
  actualchannel string,
  vendor string,
  campaigncode string,
  campaignname string,
  offercode string,
  offername string,
  product string,
  treatmentcode string,
  success_flg string,
  ma_source string,
  cost double,
  advid string,
  year string,
  month string,
  day string,
  s3_path string,
  parquet_last_modified timestamp)
PARTITIONED BY (`advid`, `year`, `month`, `day`)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/unicach/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='snappy'
);

The following table: conversions

CREATE TABLE segmentdb_<Campaign-Id>.conversions (
  campaignname string,
  campaigncode string,
  offercode string,
  offername string,
  customerid string,
  responsetype string,
  responsechannel string,
  responsedatetime timestamp,
  cost double,
  product string,
  revenue double,
  treatmentcode string,
  eventtype string,
  date string,
  time string,
  advid string,
  year string,
  month string,
  day string,
  s3_path string,
  parquet_last_modified timestamp)
PARTITIONED BY (`advid`, `year`, `month`, `day`)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/conversions/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='zstd'
);

The following table: ctevent_property_metadata

CREATE EXTERNAL TABLE `ctevent_property_metadata`(
  `eventname` string, 
  `propertyname` string, 
  `isenumerable` boolean, 
  `covactivity` boolean, 
  `propertydatatype` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<Bucket-Name>/<Campaign-Id>/ctevent_metadata/'
TBLPROPERTIES (
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1771318532')

The following table: customer_data_sync_iceberg

CREATE TABLE segmentdb_<Campaign-Id>.customer_data_sync_iceberg (
  salutation string,
  first_name string,
  middle_name string,
  last_name string,
  birth_date timestamp,
  age double,
  gender string,
  marital_status string,
  education_level string,
  income_range string,
  occupation_code string,
  citizenship string,
  ethnicity string,
  mother_maiden_name string,
  pep_flag string,
  pep_level string,
  sanction_flag string,
  sanction_status string,
  high_risk_country_flag string,
  aml_flag string,
  aml_risk_score string,
  fraud_flag string,
  fraud_risk_score string,
  kyc_status string,
  kyc_risk_level string,
  kyc_risk_score string,
  kyc_verification_date timestamp,
  kyc_next_review_date timestamp,
  kyc_customer_due_diligence_level string,
  consent_for_data_processing string,
  consent_for_legal_compliance string,
  consent_for_fraud_processing string,
  consent_for_marketing_email string,
  consent_for_marketing_sms string,
  consent_for_marketing_phone string,
  consent_for_marketing_postal string,
  consent_for_marketing_newsletter string,
  consent_for_third_party_marketing string,
  consent_for_personalization string,
  consent_for_auto_decisioning string,
  consent_for_sensitive_data_processing string,
  consent_for_biometric_data_collection string,
  consent_for_geolocation_capture string,
  consent_for_data_sharing_affiliates string,
  consent_for_data_sharing_service_providers string,
  consent_for_data_sharing_internal string,
  email_address string,
  email_verified string,
  phone_number string,
  phone_verified string,
  mobile_number string,
  mobile_verified string,
  permanent_address_line1 string,
  permanent_address_line2 string,
  permanent_city string,
  permanent_state_province string,
  permanent_postal_code string,
  permanent_country_code string,
  permanent_address_status string,
  correspondence_address_line1 string,
  correspondence_address_line2 string,
  correspondence_city string,
  correspondence_state_province string,
  correspondence_postal_code string,
  correspondence_country_code string,
  no_of_dependents double,
  total_credit_count_90d double,
  total_debit_count_90d double,
  total_credit_transaction_amount_90d double,
  total_debit_transaction_amount_90d double,
  total_accounts double,
  total_deposit_accounts double,
  total_casa_accounts double,
  total_loan_accounts double,
  total_credit_card_accounts double,
  available_balance_casa double,
  outstanding_balance_loans double,
  outstanding_balance_cc double,
  total_available_credit_limit double,
  avg_daily_debit_transaction_count_casa_30d double,
  avg_daily_credit_transaction_count_casa_30d double,
  avg_daily_debit_transaction_amount_casa_30d double,
  avg_daily_credit_transaction_amount_casa_30d double,
  highest_transaction_value_casa_30d double,
  highest_transaction_value_cc_30d double,
  highest_transaction_value_loans_30d double,
  avg_balance_casa_7d double,
  min_balance_casa_7d double,
  max_balance_casa_7d double,
  debit_transaction_count_casa_7d double,
  credit_transaction_count_casa_7d double,
  debit_transaction_amount_casa_7d double,
  credit_transaction_amount_casa_7d double,
  min_balance_cc_7d double,
  max_balance_cc_7d double,
  debit_transaction_count_cc_7d double,
  credit_transaction_count_cc_7d double,
  debit_transaction_amount_cc_7d double,
  credit_transaction_amount_cc_7d double,
  min_balance_loans_7d double,
  max_balance_loans_7d double,
  debit_transaction_count_loans_7d double,
  credit_transaction_count_loans_7d double,
  debit_transaction_amount_loans_7d double,
  credit_transaction_amount_loans_7d double,
  avg_balance_casa_30d double,
  min_balance_casa_30d double,
  max_balance_casa_30d double,
  debit_transaction_count_casa_30d double,
  credit_transaction_count_casa_30d double,
  debit_transaction_amount_casa_30d double,
  credit_transaction_amount_casa_30d double,
  min_balance_cc_30d double,
  max_balance_cc_30d double,
  debit_transaction_count_cc_30d double,
  credit_transaction_count_cc_30d double,
  debit_transaction_amount_cc_30d double,
  credit_transaction_amount_cc_30d double,
  min_balance_loans_30d double,
  max_balance_loans_30d double,
  debit_transaction_count_loans_30d double,
  credit_transaction_count_loans_30d double,
  debit_transaction_amount_loans_30d double,
  credit_transaction_amount_loans_30d double,
  product_holdings string,
  products_held_count double,
  campaign_details string,
  total_campaign_contacts_7d double,
  total_actual_responses_received_7d double,
  failed_campaign_contacts_7d double,
  total_campaign_responses_7d double,
  email_campaign_responses_7d double,
  sms_campaign_responses_7d double,
  push_campaign_responses_7d double,
  whatsapp_campaign_responses_7d double,
  rcs_campaign_responses_7d double,
  webchannel_campaign_responses_7d double,
  total_campaign_contacts_30d double,
  total_actual_responses_received_30d double,
  failed_campaign_contacts_30d double,
  total_campaign_responses_30d double,
  email_campaign_responses_30d double,
  sms_campaign_responses_30d double,
  push_campaign_responses_30d double,
  whatsapp_campaign_responses_30d double,
  rcs_campaign_responses_30d double,
  webchannel_campaign_responses_30d double,
  date_last_contacted timestamp,
  date_last_responded timestamp,
  multi_channel_responder string,
  best_channel string,
  best_time_to_send string,
  offers_count double,
  total_no_of_conversions double,
  total_revenue double,
  total_spent double,
  updated_timestamp timestamp,
  key_type string,
  campaignid string,
  apid string,
  type string,
  ct_enc_cust_id string,
  acct_type string,
  isonline string,
  crmid string,
  actts string,
  comu_country string,
  comu_city string,
  comu_state string,
  key string,
  cust_name string,
  un_enc_cust_id string,
  valuesandbeliefs string,
  interestsandhobbies string,
  lifestylepreferences string,
  buyingmotivations string,
  personalitytraits string,
  he string,
  hm string,
  is_key_present string,
  is_country_present string,
  concated_string string,
  top_3_channel array<string>,
  top_3_time_for_push array<string>,
  top_3_time_for_sms array<string>,
  top_3_time_for_rcs array<string>,
  top_3_time_for_email array<string>,
  top_3_time_for_whatsapp array<string>,
  top_5_channel array<string>,
  top3chan_with_top5hrs array<string>,
  top_2_channel array<string>,
  city_state_country string,
  primary_channel string,
  secondary_channel string,
  tertiary_channel string,
  top_sto_for_primary_channel string,
  top_sto_for_secondary_channel string,
  top_sto_for_tertiary_channel string,
  masterid string,
  identityType string)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/customers/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='zstd',
  'format'='parquet'
);

The following table: id_mapping

CREATE TABLE segmentdb_<Campaign-Id>.id_mapping (
  latest_id string,
  old_id string)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/id_mapping/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='SNAPPY',
  'format'='parquet'
);

The following table: master_audience_segment_id

CREATE TABLE segmentdb_<Campaign-Id>.master_audience_segment_id (
  audience_id string,
  segment_id int)
PARTITIONED BY (`segment_id`)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/master_audience_segment_id/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='zstd'
);

The following table: master_events

CREATE TABLE segmentdb_<Campaign-Id>.master_events (
  properties map<string, string>,
  eventname string,
  eventdate timestamp,
  eventtime string,
  key string,
  type string)
PARTITIONED BY (`eventname`)
LOCATION 's3://<Bucket-Name>/<Campaign-Id>/master_events/'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='snappy'
);

The following table: segmentationmetadata

CREATE EXTERNAL TABLE `segmentationmetadata`(
  `advid` string COMMENT 'from deserializer', 
  `datasourcename` string COMMENT 'from deserializer', 
  `dimension` string COMMENT 'from deserializer', 
  `datatype` string COMMENT 'from deserializer', 
  `dbtype` string COMMENT 'from deserializer', 
  `dbname` string COMMENT 'from deserializer', 
  `tablecollectionname` string COMMENT 'from deserializer', 
  `isenumerable` boolean COMMENT 'from deserializer', 
  `primaryidentifier` boolean COMMENT 'from deserializer', 
  `display_dimensions` string COMMENT 'from deserializer', 
  `display_datasourcename` string COMMENT 'from deserializer', 
  `cteventidentifier` boolean COMMENT 'from deserializer', 
  `propertiesidentifier` boolean COMMENT 'from deserializer', 
  `isrts` boolean COMMENT 'from deserializer', 
  `cteventdateidentifier` boolean COMMENT 'from deserializer',
  `is_pii` boolean COMMENT 'from deserializer', 
  `macro_name` string COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'escapeChar'='\\', 
  'quoteChar'='\"', 
  'separatorChar'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://<Bucket-Name>/<Campaign-Id>/segmentationmetadata/'
TBLPROPERTIES (
  'last_modified_by'='hadoop', 
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1771247398')

Add dashboard js files in AWS S3 Bucket

By default, the JS files are available at s3://analytics-bucket/dashboard/default/.

To include custom JS files, create a folder under dashboard and upload the files to: