Skip to main content

Data Model

Understanding the RevIQ data model is essential for working with forecasts and performance analytics.

Data Flow Overview​

Data typically flows from raw data sources through transformation layers to the tables used by the front end in Una.

Core Stored Procedures​

App_GeneratePeriodDim​

Populates the Period dimension table.

Parameters:

  • @fromYear INT
  • @toYear INT

Action: Truncates and populates Years, Quarters, and Months into the Dim_PC_Period table over the range of parameters.

Appg_UpdateModel_Main​

Updates the model with actual data.

Actions:

  1. Calculate averages for Amount KPIs to replace missing values in T_Main where all Amount KPIs are missing; load averages into S_Main for reference
  2. Delete and populate recent data (> StartDate from DataProcessingParameters) in VCohort_Main from T_Main
  3. Delete and populate Actuals in B_Main from T_Main
  4. Delete and populate C_Main with diagnostic cohort KPIs from VCohort_Main and expansion cohorts from T_Main
  5. Delete and populate recent CB_Main from T_Main
  6. Delete and populate recent actuals in M_Main from CB_Main

Appg_GenerateStatistics_Main​

Generates statistics for forecasting.

Source: Takes Actuals from B_Main and T_Main

Output: Statistics (conversion percentages and other trends) stored in S_Main under Actuals Scenario.

Additional actions:

  • Updates p_StartPeriod and p_EndPeriod in Dim_PC_Scenario for this scenario

Process:

  1. Creates statistics based on historical data, primarily conversion percentages
  2. Works sequentially with widening scope of segments (more general grouping) to have more robust stats
  3. Calculates average DaysInStage (by progress/lost/regress) and average deal size

Appg_GenerateForecast_Main​

Generates forecasts based on statistics.

Parameters:

  • @forecastScenario INT

Process:

  1. Takes stats from S_Main under the given Scenario
  2. Takes latest Actuals from B_Main
  3. Applies conversion percentages sequentially 12 times (for 12 forecast periods)
  4. Stores forecast in B_Main under the given ScenarioId
  5. Loads M_Main with forecasted KPIs (except Days), including NRR and GRR
note

The scenario for this stored procedure can be any except Actuals—validation has been added to enforce this.