Feedback.Meta_Satisfaction

by

Purpose

This view brings together customer feedback from multiple sources into a single, consistent structure — helping teams understand satisfaction trends, sentiment, and consent status across the customer journey. It supports unified reporting of Net Promoter Score (NPS), customer comments, and response context (such as project or milestone), giving a clear view of how customers experience BMT’s services over time.

Use Case

  • Owner: Charlotte Griffen
  • Typical Use Cases:
    • Track customer satisfaction and loyalty – Analyse Net Promoter Score (NPS) trends and distribution across different business units, regions, or customer groups.
    • Identify emerging themes in feedback – Review customer comments to uncover common topics, praise, or recurring pain points that may affect service quality.
    • Monitor consent and communication preferences – Ensure compliance and governance by tracking which customers have opted in or out of marketing communications.
    • Understand feedback in context – Connect survey responses to project details, milestones, or customer accounts to reveal patterns in satisfaction by delivery phase or engagement type.
  • Style: Each row = one attribute of a single response, typed by META_TYPE (OWNER, CUSTOMER, RESPONSE, CONTEXT). Canonical value sits in CODE_VALUE; VALUE_METADATA holds compact JSON (CODE, DESC, and occasionally extra fields like COMMENT).

Suggested dashboard insights:

  • NPS trends over time by source (Customer Thermometer, Dynamics Customer Voice, Legacy).
  • Promoter/Passive/Detractor breakdown by company, project, or milestone.
  • Consent coverage and opt-in rates by campaign or customer segment.
  • Word clouds or sentiment maps highlighting key feedback themes.

Source

Primary Source(s):

  • csms.tsl_feedbackrequests
  • csms.feedback responses
  • csms.BLAST_PARQUET (Customer Thermometer) — current
  • csms.csms_surveyresponse + csms.csms_questionresponses + csms.msfp_question + csms.msfp_surveyinvite + csms.tsl_bmtproject (Dynamics Customer Voice) — current
  • csms.Legacy_Internal_Feedback — legacy internal
  • csms.Legacy_External_Feedback — legacy external

Load Path

Bronze → Silver (aligned csms.*) → Gold/CDM (feedback.meta_codes_satisfaction)

  • Refresh cadence: Daily (TBC)

Integration with Dimension / Reference Tables

Joins used (per SQL):

  • Customer Voice enrichment: questions by name (“Additional comments and feedback”; consent variants), survey invite for respondent email & activity type, project lookup (tsl_bmtproject) for project number.

Common optional joins:

  • cdm.project_core / cdm.customer_core (to resolve SUBJECT/project codes to keys) — TBC
  • ref.country / ref.company (normalised mappings) — TBC

Sample Exec

For sample data execution, use the code:

EXEC get.myView @dataMart = 'Feedback', @viewName = 'Meta_Satisfaction', @token = '<myToken>', @version = '<202404>'

Response (Output Schema)

Columns emitted by get.myView:

Identifiers

  • OBJECT_SEQ:json (varchar) — canonical response key JSON: { "COMPANY_ID": "<code>", "RESPONSE_ID": "<id>" }.
    • Company resolution rules:
      • Customer Thermometer (BLAST_PARQUET): derive from
        • email domain in custom_3 when thermometer_name='Navigator Signature', else from custom_6 country, using mappings:
          • Domains: uk→1072, ca→5013, us→5084, apac→4004 (default 9900).
          • Countries: Australia→4003, United States→5084, Canada→5013, Belgium→2010, Singapore→3037, Netherlands→2027, United Kingdom→1072 (default 9900).
      • Customer Voice (csms_surveyresponse): constant company 1072 (per SQL) in OBJECT_SEQ.
      • Legacy Internal: constant 9900, response id prefixed INT.
      • Legacy External: constant 9900, response id prefixed EXT.
  • META_TYPE: varchar — one of OWNER | CUSTOMER | RESPONSE | CONTEXT.

Meta Codes (attributes)

  • ATTRIBUTE:varchar — varies by META_TYPE:
    • RESPONSE: CATEGORY, DATE, SCORE, COMMENT, CUSTOMER_CONSENT
    • CONTEXT: TYPE (e.g., NPS), SOURCE (e.g., CustomerThermometer, LegacyFeedback), MILESTONE, PROJECT
    • OWNER: EMAIL, COMPANY, COUNTRY
    • CUSTOMER: EMAIL
  • CODE_VALUE: varchar — canonical attribute value.
  • VALUE_METADATA: json (varchar) — compact JSON containing at least CODE and optional DESC (labels, free‑text, names). For some COMMENT rows, includes an extra COMMENT key and escapes JSON/quotes; single quotes are replaced with ^.

Audit & Status

  • CREATE_DATE: date — GETDATE() cast to date at load time.
  • EXPIRY_DATE: date — constant '2099-12-31'.
  • SOURCE_SYSTEM: varchar — e.g., {"Source":"csms.BLAST_PARQUET"}.
  • ACTIVE_FLAG: int — constant 1.
  • CHECKSUM: nvarchar(32) — MD5 of (OBJECT_SEQ, ATTRIBUTE, CODE_VALUE) (hex string).

Keys & Uniqueness

  • Primary (logical) key: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE (extend with EXPIRY_DATE if bitemporal)
  • Grain statement: One row per response attribute (META_TYPE + ATTRIBUTE).
  • De‑duplication rule(s): Prefer most recent CREATE_DATE; checksum for change detection.

Data Quality & Rules

  • Mandatory fields: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE
  • Validations:
    • SCORE is numeric; CATEGORY maps 9–10→1, 7–8→0, 0–6→-1
    • DATE castable to YYYY‑MM‑DD
    • Email fields present for OWNER/CUSTOMER where expected; legacy fallbacks to MD5‑hashed initials/names where emails are missing
    • Consent mapping: affirmative (Yes/yes/Y/y)→1, negative (No/no/N/n)→0, unknown→NULL; legacy internal uses Consent LIKE '%onfidential%'→0 else 1
  • Business rules applied:
    • Country/domain→company ID mapping as above; unmapped→9999
    • PROJECT sometimes encoded as SUBJECT with descriptive title in DESC
    • Strings normalised; single quotes replaced with ^; long comments JSON‑escaped
  • Assumptions/limitations: Mixed sources produce heterogeneous DESC contents; not all sources provide respondent names.

Security & Privacy

  • PII/Commercial sensitivity: Contains customer/owner emails and free‑text comments → Confidential. Some sources hash names to protect identity.
  • Access control: Role‑based; marketing/CS teams with consent awareness. Consider row‑level security by COMPANY_ID.
  • Retention: Align with customer data retention & consent policies (TBC).

Dependencies

  • Upstream:
    • csms.BLAST_PARQUET,
    • csms.csms_surveyresponse,
    • csms.csms_questionresponses,
    • csms.msfp_question,
    • csms.msfp_surveyinvite,
    • csms.tsl_bmtproject,
    • csms.Legacy_Internal_Feedback,
    • csms.Legacy_External_Feedback
  • Downstream: NPS scorecards, customer experience dashboards, marketing consent reports, project QA retrospectives

SQL Code & Artefacts

BMT-DWH-DEV/Feedback_Meta_Satisfaction.sql

Leave a Comment