59 lines
5.3 KiB
Text
59 lines
5.3 KiB
Text
|
apiVersion: v1
|
||
|
|
kind: ConfigMap
|
||
|
|
metadata:
|
||
|
|
name: {{ taskserv.name }}-cube-schema
|
||
|
|
namespace: {{ taskserv.namespace }}
|
||
|
|
labels:
|
||
|
|
app.kubernetes.io/name: {{ taskserv.name }}-cube
|
||
|
|
app.kubernetes.io/managed-by: provisioning
|
||
|
|
data:
|
||
|
|
FeedbackRecords.js: |
|
||
|
|
cube(`FeedbackRecords`, {
|
||
|
|
sql: `SELECT * FROM feedback_records`,
|
||
|
|
measures: {
|
||
|
|
count: { type: `count`, description: `Total number of feedback responses` },
|
||
|
|
uniqueRespondents: { type: `countDistinct`, sql: `${CUBE}.user_id`, description: `Unique users who provided feedback` },
|
||
|
|
uniqueResponses: { type: `countDistinct`, sql: `${CUBE}.submission_id`, description: `Unique survey submissions` },
|
||
|
|
promoterCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'nps' AND ${CUBE}.value_number >= 9` }], description: `NPS promoters (score 9-10)` },
|
||
|
|
detractorCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'nps' AND ${CUBE}.value_number BETWEEN 0 AND 6` }], description: `NPS detractors (score 0-6)` },
|
||
|
|
passiveCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'nps' AND ${CUBE}.value_number BETWEEN 7 AND 8` }], description: `NPS passives (score 7-8)` },
|
||
|
|
npsScore: {
|
||
|
|
type: `number`,
|
||
|
|
sql: `CASE WHEN COUNT(CASE WHEN ${CUBE}.field_type = 'nps' AND ${CUBE}.value_number IS NOT NULL THEN 1 END) = 0 THEN NULL ELSE ROUND(((COUNT(CASE WHEN ${CUBE}.field_type = 'nps' AND ${CUBE}.value_number >= 9 THEN 1 END)::numeric - COUNT(CASE WHEN ${CUBE}.field_type = 'nps' AND ${CUBE}.value_number BETWEEN 0 AND 6 THEN 1 END)::numeric) / COUNT(CASE WHEN ${CUBE}.field_type = 'nps' AND ${CUBE}.value_number IS NOT NULL THEN 1 END)::numeric) * 100, 2) END`,
|
||
|
|
description: `Net Promoter Score`
|
||
|
|
},
|
||
|
|
npsAverage: { type: `avg`, sql: `${CUBE}.value_number`, filters: [{ sql: `${CUBE}.field_type = 'nps'` }], description: `Average NPS rating (0-10)` },
|
||
|
|
csatCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'csat' AND ${CUBE}.value_number IS NOT NULL` }], description: `Answered CSAT responses` },
|
||
|
|
csatSatisfiedCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'csat' AND ${CUBE}.value_number >= 4` }], description: `Satisfied CSAT responses (top-2-box)` },
|
||
|
|
csatDissatisfiedCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'csat' AND ${CUBE}.value_number BETWEEN 1 AND 2` }], description: `Dissatisfied CSAT responses (bottom-2-box)` },
|
||
|
|
csatNeutralCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'csat' AND ${CUBE}.value_number = 3` }], description: `Neutral CSAT responses` },
|
||
|
|
csatScore: {
|
||
|
|
type: `number`,
|
||
|
|
sql: `CASE WHEN COUNT(CASE WHEN ${CUBE}.field_type = 'csat' AND ${CUBE}.value_number IS NOT NULL THEN 1 END) = 0 THEN NULL ELSE ROUND((COUNT(CASE WHEN ${CUBE}.field_type = 'csat' AND ${CUBE}.value_number >= 4 THEN 1 END)::numeric / COUNT(CASE WHEN ${CUBE}.field_type = 'csat' AND ${CUBE}.value_number IS NOT NULL THEN 1 END)::numeric) * 100, 2) END`,
|
||
|
|
description: `CSAT Score (% rated 4 or 5)`
|
||
|
|
},
|
||
|
|
csatAverage: { type: `avg`, sql: `${CUBE}.value_number`, filters: [{ sql: `${CUBE}.field_type = 'csat'` }], description: `Average CSAT rating (1-5)` },
|
||
|
|
cesCount: { type: `count`, filters: [{ sql: `${CUBE}.field_type = 'ces' AND ${CUBE}.value_number IS NOT NULL` }], description: `Answered CES responses` },
|
||
|
|
cesAverage: { type: `avg`, sql: `${CUBE}.value_number`, filters: [{ sql: `${CUBE}.field_type = 'ces'` }], description: `Average CES rating` },
|
||
|
|
},
|
||
|
|
dimensions: {
|
||
|
|
id: { sql: `id`, type: `string`, primaryKey: true },
|
||
|
|
sourceType: { sql: `source_type`, type: `string`, description: `Source type (e.g., nps_campaign, survey)` },
|
||
|
|
sourceName: { sql: `source_name`, type: `string`, description: `Human-readable name of the source` },
|
||
|
|
fieldType: { sql: `field_type`, type: `string`, description: `Type of feedback field (nps, text, rating)` },
|
||
|
|
fieldLabel: { sql: `field_label`, type: `string`, description: `Human-readable label of the question/field` },
|
||
|
|
fieldGroupLabel: { sql: `field_group_label`, type: `string`, description: `Label of the parent composite question` },
|
||
|
|
language: { sql: `language`, type: `string`, description: `Response language code` },
|
||
|
|
collectedAt: { sql: `collected_at`, type: `time`, description: `When the feedback was collected` },
|
||
|
|
createdAt: { sql: `created_at`, type: `time`, description: `When the record was created in Hub` },
|
||
|
|
updatedAt: { sql: `updated_at`, type: `time`, description: `When the record was last updated in Hub` },
|
||
|
|
valueNumber: { sql: `value_number`, type: `number`, description: `Numeric answer value` },
|
||
|
|
valueText: { sql: `value_text`, type: `string`, description: `Text answer value` },
|
||
|
|
valueBoolean: { sql: `value_boolean`, type: `boolean`, description: `Boolean answer value` },
|
||
|
|
valueDate: { sql: `value_date`, type: `time`, description: `Date answer value` },
|
||
|
|
responseId: { sql: `submission_id`, type: `string`, description: `Unique identifier linking related feedback records` },
|
||
|
|
userId: { sql: `user_id`, type: `string`, description: `Identifier of the user who provided feedback` },
|
||
|
|
tenantId: { sql: `tenant_id`, type: `string`, description: `Tenant ID linking to FeedbackDirectory` },
|
||
|
|
},
|
||
|
|
});
|