Analytics Database — Reference (PM & Developers)

This doc describes what exists in the per-project analytics DB, how it is created and populated, and the formulas and variables used for each table. Use it for reporting (Power BI / Metabase) and for future development.

---

Files involved

File	Role
app/models/db_credential.rb	Per-project analytics: create/populate for all six tables, reset, and connection handling.
app/models/project_group.rb	Group analytics DB: same table set, populate by reading from each project's DB (or Pepcore Base for score feelings).
app/admin/projects.rb	Admin batch actions: Init Analytics DB, Populate Analytics Data, Reset Analytics Data.
app/services/project_provisioning_service.rb	Provisioning: creates all analytics tables when setting up a new credential.
lib/tasks/analytics.rake	Rake tasks: analytics:list_tables[PROJECT_ID], analytics:list_records[PROJECT_ID] (and optional samples).
docs/ANALYTICS_DATABASE_REFERENCE.md	This reference. Linked from docs/menu.md under "Analytics Database".

---

1. Overview

• What: Each project can have an analytics database (e.g. analytics_78) used by Power BI / Metabase. Data is synced from Pepcore Base (and Rails) into this DB.
• Where: Tables are created and filled via Admin → Projects: Init Analytics DB (schema) and Populate Analytics Data (data). The same flow runs for ProjectGroup analytics DBs and via the scheduled sync job.
• Code: app/models/db_credential.rb (per-project), app/models/project_group.rb (group DB), app/admin/projects.rb (batch actions).

---

2. Tables in the analytics DB

Table	Purpose
checkin_submissions	Raw check-in rows (from Pepcore Base check_in_submission), with score, daily morale, tags, kiosk/grouping, etc.
morale_data	Daily morale aggregates (derived from check-ins), including individual score4 and score5 counts.
analytics_baselines	One row per project + baseline period: baseline morale (0–100).
analytics_benchmarks	One row per project + period: benchmark morale (0–100) = average of team morale across benchmark projects.
analytics_spotchecks	One row per project + spot check + answer + period: response counts and percentages.
dim_score_feelings	Lookup: score 1–5 and label (e.g. Anxious, Energised) and optional photo.

---

3. How tables are created and populated

• Init Analytics DB (Admin): Ensures the project has a DbCredential and an analytics DB; creates any missing tables and missing columns. Does not delete data.
• Populate Analytics Data (Admin or job): Runs six steps in order (see below). For checkin_submissions and morale_data it upserts (no delete). For analytics_baselines, analytics_benchmarks, analytics_spotchecks it replaces this project’s rows in that table then inserts. For dim_score_feelings it deletes all rows then inserts 5 (scores 1–5).
• Reset Analytics Data: Deletes all rows in all six tables for that project, then runs the same populate again.

---

4. Table-by-table: columns, sources, formulas, variables

4.1 analytics_baselines

Columns: id, project_id, period_start, period_end, baseline_value (integer 0–100), computed_at.

What it is: One row per project + baseline period. baseline_value = baseline morale for that period (same as Procore “Baseline” card).

How it’s created: Table/columns via DbCredential#create_analytics_baselines_table and create_analytics_baselines_attributes. Data via DbCredential#populate_analytics_baselines_table.

Data source (reference):

Variable / input	Source
Period type	DashboardSetting.baseline_calculation_period (0, 1, 3, 6, 12) → mapped with DashboardSetting::PERIOD_TO_RANGE (e.g. 3 → 'last_3_months').
Date range	BaselineRangeCalculator.calculate_date_range(baseline_range_option).
Submissions	CheckInSubmission (Pepcore Base): project_id = project.id, created_at in that date range.

Formula: MoraleCalculationService.calculate_baseline_morale_from_submissions(submissions, date_range, baseline_range_option).

• Per period (e.g. per month for last 3 months):
  
  • From 2025-01-01: weighted_sum = (score_5 × 1.5) + (score_4 × 1.0) + (score_3 × 0.5); morale = (weighted_sum / total_submissions) × 100 (capped 1–100).
    
  • Before 2025: only scores 4 and 5 count: weighted_sum = score_5 + score_4.
• baseline_value = average of those per-period morale scores (e.g. average of 3 monthly values for last_3_months).

Code: app/models/db_credential.rb (populate_analytics_baselines_table), app/services/morale_calculation_service.rb, app/services/baseline_range_calculator.rb, app/models/dashboard_setting.rb.

---

4.2 analytics_benchmarks

Columns: id, project_id, period_start, period_end, benchmark_value (integer 0–100), computed_at.

What it is: One row per project + period. benchmark_value = average of team morale across the benchmark projects (same as Procore “Benchmark” metric).

How it’s created: Table/columns via create_analytics_benchmarks_table / create_analytics_benchmarks_attributes. Data via DbCredential#populate_analytics_benchmarks_table. Skipped if DashboardSetting.benchmark is off or no benchmark project IDs.

Data source (reference):

Variable / input	Source
Benchmark on/off	DashboardSetting.benchmark.
Benchmark project IDs	DashboardSetting.benchmark_calculation_projects_array.
Period	Same as baseline: baseline_calculation_period → BaselineRangeCalculator.calculate_date_range.
Submissions	CheckInSubmission for those project IDs in that date range.

Formula: For each benchmark project, MoraleCalculationService.calculate_team_morale_from_submissions(project_subs, baseline_date_range) (same weights as above: 5×1.5, 4×1.0, 3×0.5 from 2025; pre-2025 only 4 and 5). Then benchmark_value = average(team_morale_scores), rounded.

Code: app/models/db_credential.rb (populate_analytics_benchmarks_table), app/services/morale_calculation_service.rb, app/models/dashboard_setting.rb.

---

4.3 analytics_spotchecks

Columns: id, project_id, spot_check_id, question_text, answer_label, response_count, response_percentage, total_responses, period_start, period_end, is_positive, spotcheck_title, spotcheck_start_date, spotcheck_end_date, is_default, is_scheduled, negative_question_text, positive_total_responses, negative_total_responses.

What it is: One row per project + spot check + answer + period. Used for Check-In Feedback and Spotcheck tabs (questions, answers, counts, percentages).

How it’s created: Table/columns via create_analytics_spotchecks_table / create_analytics_spotchecks_attributes. Data via DbCredential#populate_analytics_spotchecks_table.

Data source (reference):

Variable / input	Source
Spot checks	SpotCheck (Pepcore Base): project_id = project.id.
Answers	SpotCheckAnswer (Pepcore Base): spot_check_id in those spot checks.
Submissions	CheckInSubmission: project, created_at in last 3 months, and spot_check_id IN (...) or tags IS NOT NULL.
Matching	Submission counts for a spot check if spot_check_id matches or tags (answer IDs) intersects that spot check’s answer IDs.

Formula (aggregation):

• Period: Last 12 months from today (Date.current - 12.months .. Date.current).
• total_responses = count of submissions that belong to that spot check.
• response_count (per answer) = count of those submissions that have this answer ID in tags.
• response_percentage = (response_count / total_responses) × 100, rounded to 2 decimals.
• question_text = from SpotCheck (positive or negative question, locale 'us').
• answer_label = from SpotCheckAnswer (e.g. answer_text(locale: 'us')).
• is_positive = from SpotCheckAnswer when the column exists.
• spotcheck_title = from SpotCheck.title (the card title shown in the web app, e.g. "Lifting Operations").
• spotcheckstartdate = SpotCheck.created_at (when the spotcheck was created).
• spotcheckenddate = SpotCheck.created_at + 7.days (matches the 7-day window shown in the web app card).
• is_default = from SpotCheck.is_default — identifies the spotcheck used by the Check-In Feedback tab.
• is_scheduled = from SpotCheck.is_scheduled — identifies spotchecks used by the Spotcheck tab.
• negativequestiontext = negative question text from the spotcheck (used as the heading for the negative answers section in Check-In Feedback tab).
• positivetotalresponses = count of submissions that selected at least one positive answer for this spotcheck.
• negativetotalresponses = count of submissions that selected at least one negative answer for this spotcheck.

Code: app/models/db_credential.rb (populate_analytics_spotchecks_table), app/models/spot_check.rb, app/models/spot_check_answer.rb, app/models/check_in_submission.rb.

---

4.4 dim_score_feelings

Columns: score (integer 1–5), name (text), photo (text, optional).

What it is: Lookup table for sentiment labels and optional icons. One row per score 1–5. Join to checkin_submissions.score for reporting.

How it’s created: Table/columns via create_dim_score_feelings_table / create_dim_score_feelings_attributes. Data via DbCredential#populate_dim_score_feelings_table: copy from Pepcore Base ScoreFeeling.all (no formula).

Data source: ScoreFeeling (Pepcore Base score_feelings). We copy score, name (e.g. name['us'] if hash), photo.

Example rows (typical values):

score	name	photo (example)
1	Anxious	/assets/score_1.svg
2	Uncertain	/assets/score_2.svg
3	Indifferent	/assets/score_3.svg
4	Relaxed	/assets/score_4.svg
5	Energised	/assets/score_5.svg

Code: app/models/db_credential.rb (populate_dim_score_feelings_table), app/models/score_feeling.rb.

---

5. Summary: formulas and code locations

Table	Main formula / logic	Key code
analytics_baselines	MoraleCalculationService.calculate_baseline_morale_from_submissions; period from DashboardSetting + BaselineRangeCalculator.	db_credential.rb, morale_calculation_service.rb, baseline_range_calculator.rb
analytics_benchmarks	MoraleCalculationService.calculate_team_morale_from_submissions per benchmark project; then average.	db_credential.rb, morale_calculation_service.rb
analytics_spotchecks	Count submissions per answer; response_percentage = (count / total_responses) × 100.	db_credential.rb (inline), SpotCheck, SpotCheckAnswer, CheckInSubmission
dimscorefeelings	Copy from ScoreFeeling.all (no formula).	db_credential.rb, score_feeling.rb

---

6. Rake tasks (inspect analytics DB)

• List tables: rails "analytics:list_tables[PROJECT_ID]"
• List record counts: rails "analytics:list_records[PROJECT_ID]"
• Counts + all rows: rails "analytics:list_records[PROJECT_ID,all]"

Defined in lib/tasks/analytics.rake.