DDL-BigQuery.sql

--BIGQUERY CREATE SCRIPT
--SCHEMA V3
CREATE OR REPLACE TABLE SISU_OUTPUTS.ANALYSIS_RESULT_WATERFALL (
    ANALYSIS_ID BIGINT,
    ANALYSIS_RESULT_ID BIGINT,
    STEP_ID INTEGER,
    STEP_TYPE STRING,
    FACTOR_0_DIMENSION STRING,
    FACTOR_0_VALUE STRING,
    FACTOR_1_DIMENSION STRING,
    FACTOR_1_VALUE STRING,
    FACTOR_2_DIMENSION STRING,
    FACTOR_2_VALUE STRING,
    STEP_IMPACT FLOAT64,
    CUMULATIVE_IMPACT_BEFORE_STEP FLOAT64,
    CUMULATIVE_IMPACT_AFTER_STEP FLOAT64,
    OVERLAPPING_IMPACT FLOAT64,
    CHANGE_IN_SIZE_SET1 FLOAT64,
    CHANGE_IN_SIZE_SET2 FLOAT64,
    CHANGE_IN_TYPE_SET1 FLOAT64,
    CHANGE_IN_TYPE_SET2 FLOAT64,
    SEGMENT_TEXT STRING,
    LOAD_TS TIMESTAMP
);

--TABLE TO HOLD TREND RESULT DETAILS
CREATE OR REPLACE TABLE SISU_OUTPUTS.TREND_RESULT_DETAIL (
    ANALYSIS_ID BIGINT,
    ANALYSIS_RESULT_ID BIGINT,
    SUBGROUP_ID BIGINT,
    FACTOR_0_DIMENSION STRING,
    FACTOR_0_VALUE STRING,
    FACTOR_1_DIMENSION STRING,
    FACTOR_1_VALUE STRING,
    FACTOR_2_DIMENSION STRING,
    FACTOR_2_VALUE STRING,
    IMPACT FLOAT64,
    START_DATE TIMESTAMP,
    END_DATE TIMESTAMP,
    INTERCEPT FLOAT64,
    SLOPE FLOAT64,
    SIZE FLOAT64,
    LOAD_TS TIMESTAMP
);

--TABLE TO HOLD ANALYSIS RESULT DETAILS
CREATE OR REPLACE TABLE SISU_OUTPUTS.ANALYSIS_RESULT_DETAIL (
    ANALYSIS_ID BIGINT,
    ANALYSIS_RESULT_ID BIGINT,
    SUBGROUP_ID BIGINT,
    CONFIDENCE STRING,
    FACTOR_0_DIMENSION STRING,
    FACTOR_0_VALUE STRING,
    FACTOR_1_DIMENSION STRING,
    FACTOR_1_VALUE STRING,
    FACTOR_2_DIMENSION STRING,
    FACTOR_2_VALUE STRING,
    FACTOR_0_DIMENSION_FRIENDLY STRING,
    FACTOR_1_DIMENSION_FRIENDLY STRING,
    FACTOR_2_DIMENSION_FRIENDLY STRING,
    FACTOR_0_VALUE_FRIENDLY STRING,
    FACTOR_1_VALUE_FRIENDLY STRING,
    FACTOR_2_VALUE_FRIENDLY STRING,
    FACTOR_0_TEXT STRING,
    FACTOR_1_TEXT STRING,
    FACTOR_2_TEXT STRING,
    SEGMENT_TEXT STRING,
    CHANGE FLOAT64,
    IMPACT FLOAT64,
    IMPACT_MAGNITUDE FLOAT64,
    IMPACT_RANK STRING,
    SET1_SIZE FLOAT64,
    SET2_SIZE FLOAT64,
    SET1_VALUE FLOAT64,
    SET2_VALUE FLOAT64,
    PERCENT_CHANGE FLOAT64,
    DIRECTION STRING,
    DIRECTION_TEXT STRING,
    ORIENTATION_MATCHES_METRIC STRING,
    SEGMENT_ORDER SMALLINT,
    SEGMENT_ORDER_TEXT STRING,
    INSIGHT_TEXT STRING,
    CHANGE_IN_SIZE FLOAT64,
    MIX_EFFECT FLOAT64,
    NET_EFFECT FLOAT64,
    NET_RELATIVE_EFFECT FLOAT64,
    PERCENT_CHANGE_IN_SIZE FLOAT64,
    RATE_EFFECT FLOAT64,
    RELATIVE_PERCENT_CHANGE FLOAT64,
    RELATIVE_CHANGE FLOAT64,
    RELATIVE_MIX_EFFECT FLOAT64,
    SEGMENT_NAME STRING,
    SEGMENT_RANK STRING,
    SEGMENT_HASH STRING,
    UNWEIGHTED_CHANGE_IN_AVERAGE FLOAT64,
    WEIGHT FLOAT64,
    WEIGHTED_CHANGE_IN_SUM FLOAT64,
    LOAD_TS TIMESTAMP
);

--TABLE TO HOLD TREND RESULT SUMMARY INFORMATION
CREATE OR REPLACE TABLE SISU_OUTPUTS.TREND_RESULT_SUMMARY (
    ANALYSIS_ID BIGINT,
    ANALYSIS_RESULT_ID BIGINT,
    REQUESTED_AT TIMESTAMP,
    COMPLETED_AT TIMESTAMP,
    RUN_STATUS STRING,
    RUN_TYPE STRING,
    CURRENT_PERIOD_CARD_LABEL STRING,
    CURRENT_PERIOD_DENOMINATOR_LABEL STRING,
    CURRENT_PERIOD_PERCENT_CHANGE FLOAT64,
    CURRENT_PERIOD_SLOPE FLOAT64,
    PREVIOUS_PERIOD_CARD_LABEL STRING,
    PREVIOUS_PERIOD_DENOMINATOR_LABEL STRING,
    PREVIOUS_PERIOD_PERCENT_CHANGE FLOAT64,
    PREVIOUS_PERIOD_SLOPE FLOAT64,
    LOAD_TS TIMESTAMP
);

--TABLE TO HOLD ANALYSIS RESULT SUMMARY INFORMATION
CREATE OR REPLACE TABLE SISU_OUTPUTS.ANALYSIS_RESULT_SUMMARY (
    ANALYSIS_ID BIGINT,
    ANALYSIS_RESULT_ID BIGINT,
    REQUESTED_AT TIMESTAMP,
    COMPLETED_AT TIMESTAMP,
    RUN_STATUS STRING,
    RUN_TYPE STRING,
    TIMEFRAME STRING,
    PREVIOUS_PERIOD_START TIMESTAMP,
    PREVIOUS_PERIOD_END TIMESTAMP,
    RECENT_PERIOD_START TIMESTAMP,
    RECENT_PERIOD_END TIMESTAMP,
    GROUP_A_NAME STRING,
    GROUP_B_NAME STRING,
    METRIC_TYPE_LABEL STRING,
    PERCENT_CHANGE FLOAT64,
    DIRECTION STRING,
    SET1_CARD_LABEL STRING,
    SET1_CATEGORY_FILTER STRING,
    SET1_AVERAGE FLOAT64,
    SET1_MIN FLOAT64,
    SET1_MAX FLOAT64,
    SET1_MEDIAN FLOAT64,
    SET1_SUM FLOAT64,
    SET1_SUMMARY_VALUE FLOAT64,
    SET1_TOTAL_SIZE FLOAT64,
    SET1_TOTAL_NUMERATOR FLOAT64,
    SET1_TOTAL_DENOMINATOR FLOAT64,
    SET1_MATCH_SIZE FLOAT64,
    SET1_WEIGHT FLOAT64,
    SET2_CARD_LABEL STRING,
    SET2_CATEGORY_FILTER STRING,
    SET2_AVERAGE FLOAT64,
    SET2_MIN FLOAT64,
    SET2_MAX FLOAT64,
    SET2_MEDIAN FLOAT64,
    SET2_SUM FLOAT64,
    SET2_SUMMARY_VALUE FLOAT64,
    SET2_TOTAL_SIZE FLOAT64,
    SET2_TOTAL_NUMERATOR FLOAT64,
    SET2_TOTAL_DENOMINATOR FLOAT64,
    SET2_MATCH_SIZE FLOAT64,
    SET2_WEIGHT FLOAT64,
    LOAD_TS TIMESTAMP
);

--TABLE TO HOLD ANALYSIS INFORMATION
CREATE OR REPLACE TABLE SISU_OUTPUTS.ANALYSIS (
    ANALYSIS_ID BIGINT,
    ANALYSIS_NAME STRING,
    ANALYSIS_TYPE STRING,
    APPLICATION_URL STRING,
    CREATED_AT TIMESTAMP,
    METRIC_ID BIGINT,
    METRIC_NAME STRING,
    METRIC_DESIRED_DIRECTION STRING,
    METRIC_UNIT STRING,
    METRIC_UNIT_IS_PERCENTAGE INTEGER,
    METRIC_UNIT_IS_SUFFIX INTEGER,
    METRIC_UNIT_SCALE INTEGER,
    METRIC_UNIT_KMB STRING,
    PROJECT_ID BIGINT,
    PROJECT_NAME STRING,
    LOAD_TS TIMESTAMP
);