Metric Formulas for Medallion Architecture Data Platform (ASKIT JIRA Project)
customfield_15035) but not in current BigQuery datasetBased on the Summary Page requirements, these fields are needed for ASKIT project:
key - Unique ticket identifier (e.g., ASKIT-198571)summary - Ticket title/summarydescription - Full ticket descriptioncreated - Ticket creation timestampresolved - Ticket resolution timestampupdated - Last update timestampstatus - Current status (e.g., "Waiting for support", "Resolved")assignee - User assigned to the ticketreporter - User who created the ticketissue_type - Type of issue (e.g., "Service Request", "Incident")priority - Priority level (P1-P4)components - Component tagslabels - Custom labelstime_to_first_response - Duration until first agent responsetime_to_resolution - Duration until ticket resolutionfirst_response_date - Timestamp of first responseresolution_date - Timestamp of resolutionsla_breached - Boolean indicating SLA breachsla_elapsed_time - Time elapsed in SLA cyclecustomfield_17622 - Office Locationcustomfield_24301 - Asset Application ID & Namecustomfield_15035 - Customer Satisfaction (CSAT)customer_request_type - Type of customer requestdue_date - Expected completion dateticket_age - Days from creation to resolution or current datenumber_of_reopens - Count of times ticket was reopened (from changelog)assignee_team - Team mapping based on assigneereporters_hire_date - Employee hire date (from HR system join)reporters_status - Employee status (active/inactive)| Metric | Description | Formula | Required Fields | Status |
|---|---|---|---|---|
| Customer Satisfaction (CSAT) Score | Measure of customer satisfaction with support received (post-resolution surveys) |
AVG(customfield_15035)
WHERE customfield_15035 IS NOT NULL
GROUP BY partition_date
Note: CSAT is typically a 1-5 rating scale. Calculate average rating and convert to percentage:
(AVG_RATING / 5) * 100
|
customfield_15035 (Customer Satisfaction)partition_date (snapshot date)
|
❌ Not Available Field exists but not in current BigQuery dataset |
| Customer Effort Score (CES) | How easy/difficult it was for customer to resolve issue. Lower score = better experience |
SUM(effort_rating) / COUNT(DISTINCT key)
WHERE effort_rating IS NOT NULL
Challenge: CES requires a separate survey field not currently in ASKIT.
Could potentially proxy using: number of responses, ticket duration, or reopens as complexity indicators.
|
Custom survey field (not yet created) | ❌ Not Created Yet |
| Net Promoter Score (NPS) | Measure of customer loyalty and likelihood to recommend service |
-- NPS uses 0-10 scale
-- Promoters (9-10), Passives (7-8), Detractors (0-6)
WITH nps_segments AS (
SELECT
CASE
WHEN nps_score >= 9 THEN 'Promoter'
WHEN nps_score >= 7 THEN 'Passive'
ELSE 'Detractor'
END AS segment,
COUNT(*) as count
FROM tickets
WHERE nps_score IS NOT NULL
GROUP BY segment
)
SELECT
(promoter_pct - detractor_pct) AS nps
FROM (
SELECT
SUM(CASE WHEN segment = 'Promoter' THEN count ELSE 0 END) * 100.0 / SUM(count) AS promoter_pct,
SUM(CASE WHEN segment = 'Detractor' THEN count ELSE 0 END) * 100.0 / SUM(count) AS detractor_pct
FROM nps_segments
)
|
Custom NPS survey field (not yet created) | ❌ Not Created Yet |
| Metric | Description | Formula | Required Fields | Status |
|---|---|---|---|---|
| SLA Compliance | Percentage of tickets resolved within agreed SLA timeframes |
SELECT
(COUNT(CASE WHEN ttr_breached = FALSE THEN 1 END) * 100.0 /
COUNT(CASE WHEN time_to_resolution IS NOT NULL THEN 1 END))
AS sla_compliance_pct
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status IN ('Resolved', 'Closed')
AND time_to_resolution IS NOT NULL
-- Alternative using ttr_ever_breached for lifetime view:
SELECT
(COUNT(CASE WHEN ttr_ever_breached = FALSE THEN 1 END) * 100.0 /
COUNT(*)) AS sla_compliance_lifetime_pct
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status IN ('Resolved', 'Closed')
Two perspectives:
• ttr_breached - Last SLA cycle only (for recently resolved tickets)• ttr_ever_breached - Any breach in ticket history (includes reopened tickets)
|
ttr_breached (Boolean)ttr_ever_breached (Boolean)time_to_resolutionstatuspartition_date
|
✅ OK |
| First Response Time (FRT) | Average time for first agent response to new ticket |
SELECT
AVG(time_to_first_response) AS avg_frt_hours,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY time_to_first_response) AS median_frt_hours,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY time_to_first_response) AS p95_frt_hours
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND time_to_first_response IS NOT NULL
AND created >= CURRENT_DATE() - INTERVAL '30 days'
-- Group by priority for SLA compliance view:
SELECT
priority,
AVG(time_to_first_response) AS avg_frt,
COUNT(CASE WHEN tfr_breached = TRUE THEN 1 END) * 100.0 / COUNT(*) AS tfr_breach_pct
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND time_to_first_response IS NOT NULL
GROUP BY priority
|
time_to_first_responsefirst_response_datecreatedtfr_breached (calculated)priority
|
✅ OK |
| Mean Time to Resolution (MTTR) | Average time from ticket creation to full resolution |
SELECT
AVG(ticket_age) AS avg_mttr_days,
AVG(time_to_resolution) AS avg_mttr_hours,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ticket_age) AS median_mttr_days
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status IN ('Resolved', 'Closed')
AND resolved >= CURRENT_DATE() - INTERVAL '30 days'
-- By issue type for deeper insights:
SELECT
issue_type,
customer_request_type,
AVG(ticket_age) AS avg_resolution_days,
COUNT(*) AS ticket_count
FROM gold_askit_snapshots
WHERE status IN ('Resolved', 'Closed')
AND resolved >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY issue_type, customer_request_type
ORDER BY avg_resolution_days DESC
Field Calculation:
ticket_age = DATE_DIFF(resolved, created, DAY) for resolved ticketsticket_age = DATE_DIFF(partition_date, created, DAY) for open tickets
|
ticket_age (calculated)time_to_resolutioncreatedresolvedstatusissue_typecustomer_request_type
|
✅ OK |
| First Contact Resolution (FCR) Rate | Percentage of tickets resolved on first interaction |
-- Option 1: Using time threshold (e.g., resolved in < 1 hour = first contact)
SELECT
COUNT(CASE WHEN time_to_resolution < 1.0 THEN 1 END) * 100.0 / COUNT(*) AS fcr_rate
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status IN ('Resolved', 'Closed')
AND resolved >= CURRENT_DATE() - INTERVAL '30 days'
-- Option 2: Using comment count (requires comment analysis)
WITH ticket_comments AS (
SELECT
key,
COUNT(*) as comment_count
FROM jira_comments
GROUP BY key
)
SELECT
COUNT(CASE WHEN COALESCE(c.comment_count, 0) <= 1
AND t.time_to_resolution < 24 THEN 1 END) * 100.0 / COUNT(*) AS fcr_rate
FROM gold_askit_snapshots t
LEFT JOIN ticket_comments c ON t.key = c.key
WHERE t.status IN ('Resolved', 'Closed')
AND t.resolved >= CURRENT_DATE() - INTERVAL '30 days'
Challenge: JIRA doesn't have native "first contact resolution" tracking.
Need to define business rule:
• Resolution time < X hours/minutes? • Number of comments/responses ≤ 1? • No status changes beyond initial assignment? |
time_to_resolutioncomments (count from changelog)statusCustom business logic needed |
⚠️ Check with JIRA No native field; requires custom logic definition |
| Ticket Volume | Total number of incoming support tickets over period |
-- Daily volume
SELECT
DATE(created) AS ticket_date,
COUNT(*) AS daily_volume,
COUNT(CASE WHEN priority = 'P1 Critical' THEN 1 END) AS p1_count,
COUNT(CASE WHEN issue_type = 'Incident' THEN 1 END) AS incident_count
FROM gold_askit_snapshots
WHERE created >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY DATE(created)
ORDER BY ticket_date DESC
-- Trend analysis (week over week)
SELECT
DATE_TRUNC('week', created) AS week_start,
COUNT(*) AS weekly_volume,
AVG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('week', created)
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_avg_4wk
FROM gold_askit_snapshots
WHERE created >= CURRENT_DATE() - INTERVAL '90 days'
GROUP BY DATE_TRUNC('week', created)
ORDER BY week_start DESC
|
key (count)createdpriorityissue_type
|
✅ OK |
| Tickets Opened vs. Closed | Compare new tickets vs resolved tickets to identify backlog trends |
WITH daily_metrics AS (
SELECT
partition_date,
COUNT(CASE WHEN DATE(created) = partition_date THEN 1 END) AS opened_today,
COUNT(CASE WHEN DATE(resolved) = partition_date THEN 1 END) AS closed_today,
COUNT(CASE WHEN status NOT IN ('Resolved', 'Closed') THEN 1 END) AS backlog_count
FROM gold_askit_snapshots
WHERE partition_date >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY partition_date
)
SELECT
partition_date,
opened_today,
closed_today,
(opened_today - closed_today) AS net_change,
backlog_count,
(closed_today * 100.0 / NULLIF(opened_today, 0)) AS closure_rate_pct
FROM daily_metrics
ORDER BY partition_date DESC
💡 Snapshot Advantage: Daily snapshots allow point-in-time backlog tracking.
Without snapshots, you can't answer "What was our backlog on Oct 15?"
|
partition_datecreatedresolvedstatus
|
✅ OK |
| Ticket Backlog | Number of open tickets that exceeded expected resolution time |
-- Current backlog by age buckets
SELECT
CASE
WHEN ticket_age < 30 THEN '<1 MONTH'
WHEN ticket_age BETWEEN 30 AND 90 THEN '1-3 MONTHS'
ELSE '>3 MONTHS'
END AS age_bucket,
COUNT(*) AS ticket_count,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS percentage
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status NOT IN ('Resolved', 'Closed')
GROUP BY age_bucket
ORDER BY MIN(ticket_age)
-- Backlog past due date
SELECT
COUNT(*) AS past_due_count,
COUNT(CASE WHEN priority IN ('P1 Critical', 'P2 High') THEN 1 END) AS critical_past_due,
AVG(DATE_DIFF(CURRENT_DATE(), due_date, DAY)) AS avg_days_overdue
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status NOT IN ('Resolved', 'Closed')
AND due_date < CURRENT_DATE()
|
ticket_age (calculated)statusdue_dateprioritypartition_date
|
✅ OK |
| Escalation Rate | Percentage of tickets transferred from lower to higher-level support |
-- Requires changelog analysis to track assignee/status transitions
WITH escalations AS (
SELECT
t.key,
COUNT(CASE WHEN ch.field = 'status'
AND ch.from_value IN ('Waiting for support', 'In Progress')
AND ch.to_value = 'Escalated' THEN 1 END) AS escalation_count
FROM gold_askit_snapshots t
LEFT JOIN jira_changelog ch ON t.key = ch.key
WHERE t.created >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY t.key
)
SELECT
COUNT(CASE WHEN escalation_count > 0 THEN 1 END) * 100.0 / COUNT(*) AS escalation_rate
FROM escalations
-- Alternative: Track by assignee level changes
SELECT
COUNT(DISTINCT CASE WHEN ch.field = 'assignee'
AND ch.change_count > 1 THEN t.key END) * 100.0 / COUNT(DISTINCT t.key)
AS transfer_rate
FROM gold_askit_snapshots t
LEFT JOIN (
SELECT key, field, COUNT(*) as change_count
FROM jira_changelog
WHERE field = 'assignee'
GROUP BY key, field
) ch ON t.key = ch.key
WHERE t.created >= CURRENT_DATE() - INTERVAL '30 days'
Data Dependency: Requires changelog/history table to track status or assignee transitions.
Need to define what constitutes an "escalation" in your ASKIT workflow.
|
jira_changelog tablestatus transitionsassignee transitionsCustom escalation status/field |
⚠️ Needs Changelog Requires changelog data |
| Metric | Description | Formula | Required Fields | Status |
|---|---|---|---|---|
| Agent Utilization Rate | Time agents spend on productive ticket work |
-- Requires work log data from JIRA
SELECT
assignee,
SUM(work_logged_hours) AS productive_hours,
(SUM(work_logged_hours) / (COUNT(DISTINCT work_date) * 8)) * 100 AS utilization_pct
FROM jira_worklog
WHERE work_date >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY assignee
ORDER BY utilization_pct DESC
Challenge: Requires JIRA work log data (time tracking).
If work logs aren't consistently used, consider proxy metrics like assigned ticket count or resolution rate.
|
jira_worklog tablework_logged_hoursassigneeWork log tracking must be enabled |
🔮 Future |
| Average Handle Time (AHT) | Average time agent spends actively working on a ticket |
-- Using work log data
SELECT
assignee,
AVG(work_logged_hours) AS avg_handle_time_hours,
COUNT(DISTINCT key) AS tickets_handled
FROM jira_worklog
WHERE work_date >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY assignee
-- Alternative: Use resolution time as proxy
SELECT
assignee,
AVG(time_to_resolution) AS avg_resolution_time_hours,
COUNT(*) AS tickets_resolved
FROM gold_askit_snapshots
WHERE resolved >= CURRENT_DATE() - INTERVAL '30 days'
AND status IN ('Resolved', 'Closed')
GROUP BY assignee
|
jira_worklog (preferred)OR time_to_resolution (proxy)assignee
|
🔮 Future |
| Transfer Rate | Percentage of tickets transferred to another team member/department |
WITH assignee_changes AS (
SELECT
key,
COUNT(*) - 1 AS transfer_count -- Subtract 1 for initial assignment
FROM jira_changelog
WHERE field = 'assignee'
AND created >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY key
HAVING COUNT(*) > 1 -- Only tickets with reassignments
)
SELECT
COUNT(DISTINCT ac.key) * 100.0 / COUNT(DISTINCT t.key) AS transfer_rate,
AVG(ac.transfer_count) AS avg_transfers_per_ticket
FROM gold_askit_snapshots t
LEFT JOIN assignee_changes ac ON t.key = ac.key
WHERE t.created >= CURRENT_DATE() - INTERVAL '30 days'
-- Transfer back to queue specifically
SELECT
COUNT(DISTINCT key) * 100.0 /
(SELECT COUNT(*) FROM gold_askit_snapshots
WHERE created >= CURRENT_DATE() - INTERVAL '30 days') AS queue_return_rate
FROM jira_changelog
WHERE field = 'status'
AND to_value IN ('Waiting for support', 'Open')
AND from_value IN ('In Progress', 'Assigned')
AND created >= CURRENT_DATE() - INTERVAL '30 days'
|
jira_changelog tablefield = 'assignee' changesstatus changes (for queue returns)
|
⚠️ In Progress Needs changelog for assignee transitions |
| Reopened Tickets | Percentage of tickets reopened after being marked resolved |
-- Using number_of_reopens calculated field
SELECT
COUNT(CASE WHEN number_of_reopens > 0 THEN 1 END) * 100.0 / COUNT(*) AS reopen_rate,
AVG(CASE WHEN number_of_reopens > 0 THEN number_of_reopens END) AS avg_reopens_when_reopened
FROM gold_askit_snapshots
WHERE resolved >= CURRENT_DATE() - INTERVAL '30 days'
AND status IN ('Resolved', 'Closed')
-- Calculate from changelog (if number_of_reopens not available)
WITH reopen_counts AS (
SELECT
key,
COUNT(CASE WHEN field = 'status'
AND from_value IN ('Resolved', 'Closed')
AND to_value NOT IN ('Resolved', 'Closed') THEN 1 END) AS reopen_count,
MAX(CASE WHEN field = 'status'
AND from_value IN ('Resolved', 'Closed') THEN author END) AS reopened_by
FROM jira_changelog
GROUP BY key
)
SELECT
COUNT(CASE WHEN rc.reopen_count > 0 THEN 1 END) * 100.0 / COUNT(*) AS reopen_rate,
rc.reopened_by,
COUNT(*) AS tickets_reopened
FROM gold_askit_snapshots t
LEFT JOIN reopen_counts rc ON t.key = rc.key
WHERE t.resolved >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY rc.reopened_by
Capture Who Reopened: Track whether customer or agent reopened the ticket
to understand if it's a resolution quality issue vs new customer request.
|
number_of_reopens (calculated)OR jira_changelogstatus transitionsresolved → not resolved changes
|
✅ Check Changelog Needs status transition tracking |
| Metric | Description | Formula | Required Fields |
|---|---|---|---|
| New Hire View | Segment tickets by employee hire date and geography |
-- Requires join with Employee/HR data
SELECT
CASE
WHEN DATE_DIFF(CURRENT_DATE(), emp.hire_date, DAY) <= 30 THEN 'New Hire (<30 days)'
WHEN DATE_DIFF(CURRENT_DATE(), emp.hire_date, DAY) <= 90 THEN 'Recent Hire (30-90 days)'
ELSE 'Tenured (>90 days)'
END AS hire_cohort,
emp.office_location AS geography,
COUNT(t.key) AS ticket_count,
AVG(t.ticket_age) AS avg_resolution_days,
COUNT(CASE WHEN t.status NOT IN ('Resolved', 'Closed') THEN 1 END) AS open_tickets
FROM gold_askit_snapshots t
INNER JOIN employee_data emp ON t.reporter = emp.email
WHERE t.created >= CURRENT_DATE() - INTERVAL '30 days'
GROUP BY hire_cohort, geography
ORDER BY hire_cohort, ticket_count DESC
|
employee_data.hire_dateemployee_data.office_locationOR customfield_17622reporter (join key)
|
| Onboarding / Offboarding | Break out onboarding/offboarding tickets for visibility |
-- Identify onboarding/offboarding by customer_request_type or description keywords
SELECT
CASE
WHEN LOWER(customer_request_type) LIKE '%onboard%'
OR LOWER(summary) LIKE '%onboard%' THEN 'Onboarding'
WHEN LOWER(customer_request_type) LIKE '%offboard%'
OR LOWER(summary) LIKE '%offboard%'
OR LOWER(summary) LIKE '%termination%' THEN 'Offboarding'
ELSE 'Other'
END AS lifecycle_type,
COUNT(*) AS ticket_count,
AVG(time_to_resolution) AS avg_resolution_hours,
COUNT(CASE WHEN ttr_breached = TRUE THEN 1 END) * 100.0 / COUNT(*) AS sla_breach_pct
FROM gold_askit_snapshots
WHERE created >= CURRENT_DATE() - INTERVAL '30 days'
AND (LOWER(customer_request_type) LIKE '%board%'
OR LOWER(summary) LIKE '%board%')
GROUP BY lifecycle_type
-- Extract Hire Date / Last Day from description field (if structured)
SELECT
key,
REGEXP_EXTRACT(description, r'Hire Date[:\s]+(\d{4}-\d{2}-\d{2})') AS hire_date_extracted,
REGEXP_EXTRACT(description, r'Last Day[:\s]+(\d{4}-\d{2}-\d{2})') AS last_day_extracted
FROM gold_askit_snapshots
WHERE LOWER(summary) LIKE '%onboard%' OR LOWER(summary) LIKE '%offboard%'
|
customer_request_typesummarydescription (text extraction)Pattern matching for lifecycle events |
| Access Requests by System | Assess access ticket volumes and performance by application/system |
SELECT
customfield_24301 AS asset_application, -- Asset Application ID & Name
customer_request_type,
COUNT(*) AS access_request_count,
AVG(time_to_resolution) AS avg_resolution_hours,
COUNT(CASE WHEN ttr_breached = TRUE THEN 1 END) * 100.0 / COUNT(*) AS sla_breach_pct,
COUNT(CASE WHEN status NOT IN ('Resolved', 'Closed') THEN 1 END) AS pending_count
FROM gold_askit_snapshots
WHERE created >= CURRENT_DATE() - INTERVAL '90 days'
AND (LOWER(customer_request_type) LIKE '%access%'
OR LOWER(summary) LIKE '%access%'
OR LOWER(summary) LIKE '%permission%')
AND customfield_24301 IS NOT NULL
GROUP BY customfield_24301, customer_request_type
ORDER BY access_request_count DESC
-- CMDB owner/approval tracking
SELECT
customfield_24301 AS application,
cmdb.owner,
cmdb.approver,
emp.status AS approver_status,
COUNT(t.key) AS pending_approvals
FROM gold_askit_snapshots t
LEFT JOIN cmdb_applications cmdb ON t.customfield_24301 = cmdb.application_id
LEFT JOIN employee_data emp ON cmdb.approver = emp.email
WHERE t.status = 'Waiting for approval'
AND emp.status = 'Inactive' -- Highlight bottlenecks with inactive approvers
GROUP BY customfield_24301, cmdb.owner, cmdb.approver, emp.status
|
customfield_24301 (Asset Application)customer_request_typecmdb_applications table (owner/approver)employee_data (approver status)
|
These are operational dashboards to help manage metrics in real-time by surfacing tickets that impact KPIs.
| View | Purpose | Formula |
|---|---|---|
| Tickets Close to TTR Breach | Proactively identify tickets at risk of SLA breach |
SELECT
key,
summary,
assignee,
priority,
created,
time_to_resolution,
ttr_elapsed_time,
(time_to_resolution - ttr_elapsed_time) AS time_remaining_hours,
CASE
WHEN (time_to_resolution - ttr_elapsed_time) < 2 THEN 'CRITICAL - <2hrs'
WHEN (time_to_resolution - ttr_elapsed_time) < 6 THEN 'WARNING - <6hrs'
ELSE 'ATTENTION - <24hrs'
END AS urgency
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status NOT IN ('Resolved', 'Closed')
AND time_to_resolution IS NOT NULL
AND (time_to_resolution - ttr_elapsed_time) < 24 -- Less than 24 hours remaining
AND ttr_breached = FALSE
ORDER BY time_remaining_hours ASC
|
| Tickets Close to TFR Breach | Identify tickets needing immediate first response |
SELECT
key,
summary,
reporter,
priority,
created,
time_to_first_response AS tfr_sla_hours,
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), created, HOUR) AS hours_since_created,
(time_to_first_response - TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), created, HOUR)) AS hours_remaining
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND first_response_date IS NULL -- No response yet
AND status NOT IN ('Resolved', 'Closed')
AND (time_to_first_response - TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), created, HOUR)) < 2
ORDER BY hours_remaining ASC
|
| P1 Tickets | Monitor all P1/Critical tickets for executive visibility |
SELECT
key,
summary,
assignee,
status,
created,
ticket_age,
ttr_breached,
CASE
WHEN status NOT IN ('Resolved', 'Closed') AND ticket_age > 1 THEN 'OVERDUE'
WHEN status NOT IN ('Resolved', 'Closed') THEN 'ACTIVE'
ELSE 'CLOSED'
END AS p1_status
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND priority IN ('P1 Critical', 'P1')
ORDER BY created DESC
|
| Incident Tickets | Track all active incidents separate from service requests |
SELECT
key,
summary,
assignee,
status,
priority,
created,
ticket_age,
ttr_breached
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND issue_type = 'Incident'
AND status NOT IN ('Resolved', 'Closed')
ORDER BY priority, created
|
| Tickets Waiting for Support - No Activity | Identify stale tickets waiting for agent action |
SELECT
key,
summary,
assignee,
status,
created,
updated,
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), updated, HOUR) AS hours_since_activity,
ticket_age
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status = 'Waiting for support'
AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), updated, HOUR) > 24 -- No activity for 24+ hours
ORDER BY hours_since_activity DESC
|
| Tickets Waiting for Customer - No Activity | Identify tickets blocked on customer response |
SELECT
key,
summary,
reporter,
assignee,
status,
updated,
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), updated, HOUR) AS hours_waiting,
ticket_age
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status IN ('Waiting for customer', 'Pending customer')
AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), updated, HOUR) > 72 -- 3+ days no response
ORDER BY hours_waiting DESC
|
| Data Quality Dashboard | Monitor data completeness and quality issues |
-- Missing critical fields
SELECT
'Missing Assignee' AS issue_type,
COUNT(*) AS ticket_count
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND assignee IS NULL
AND status NOT IN ('Open', 'New')
UNION ALL
SELECT
'Missing Priority' AS issue_type,
COUNT(*)
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND priority IS NULL
UNION ALL
SELECT
'Missing Customer Request Type' AS issue_type,
COUNT(*)
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND customer_request_type IS NULL
UNION ALL
SELECT
'Missing SLA Fields' AS issue_type,
COUNT(*)
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND time_to_resolution IS NULL
AND created < CURRENT_DATE() - INTERVAL '7 days'
|
| View | Description | Formula | Use Case |
|---|---|---|---|
| Planned Tickets (Weekly) | Volume of tickets expected to complete this week |
-- Based on due dates
SELECT
DATE_TRUNC('week', due_date) AS week_of,
customer_request_type,
COUNT(*) AS planned_tickets,
COUNT(CASE WHEN LOWER(summary) LIKE '%offboard%' THEN 1 END) AS offboarding_count,
COUNT(CASE WHEN priority IN ('P1 Critical', 'P2 High') THEN 1 END) AS high_priority_count
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status NOT IN ('Resolved', 'Closed')
AND due_date BETWEEN CURRENT_DATE() AND CURRENT_DATE() + INTERVAL '7 days'
GROUP BY week_of, customer_request_type
ORDER BY week_of, planned_tickets DESC
-- Based on SLA resolution date
SELECT
DATE(created + INTERVAL time_to_resolution HOUR) AS expected_resolution_date,
COUNT(*) AS tickets_due,
SUM(CASE WHEN ttr_elapsed_time / time_to_resolution > 0.8 THEN 1 ELSE 0 END) AS at_risk_count
FROM gold_askit_snapshots
WHERE partition_date = CURRENT_DATE()
AND status NOT IN ('Resolved', 'Closed')
AND DATE(created + INTERVAL time_to_resolution HOUR) BETWEEN CURRENT_DATE() AND CURRENT_DATE() + INTERVAL '7 days'
GROUP BY expected_resolution_date
ORDER BY expected_resolution_date
|
Standup Planning: Daily/weekly team capacity planning |
bronze_askit_tickets_raw, bronze_jira_changelogcustomfield_17622 → office_location)silver_askit_tickets, silver_jira_changelog, silver_jira_slapartition_date)ticket_age, ttr_breached)gold_askit_snapshots (daily full snapshot)gold_askit_metrics_daily (pre-aggregated metrics)gold_askit_sla_summary (SLA compliance summary)| Gap | Impact | Mitigation |
|---|---|---|
| Changelog/History Not in Dataset | Cannot calculate: Reopens, Transfers, Escalations, Status duration |
✅ Enable JIRA changelog ingestion ✅ Create silver_jira_changelog table✅ Store status/assignee transition history |
| Custom Fields Missing from BigQuery | CSAT, Office Location, Asset Application not available |
✅ Verify custom field IDs in JIRA API ✅ Update extraction to include: customfield_15035, customfield_17622, customfield_24301 ✅ Map to readable names in Silver layer |
| Work Log Data Not Captured | Cannot calculate Agent Utilization or Average Handle Time |
⚠️ Enable JIRA time tracking/work logs ⚠️ Alternative: Use resolution time as proxy metric ⚠️ Mark as "Future" metric until work logs available |
| No Native FCR Field | First Contact Resolution requires custom business logic |
📋 Define FCR rule (e.g., resolution < 1 hour OR comment count ≤ 1) 📋 Calculate in Gold layer based on agreed definition 📋 Document assumptions clearly |
| Employee Data Join Required | New Hire metrics need HR system integration |
✅ Create dim_employee table from HR system✅ Include: hire_date, termination_date, status, office_location ✅ Daily sync to keep current |
| Team Assignment Mapping | Need assignee → team mapping for team-level metrics |
✅ Create dim_team_mapping table✅ Map assignee email to team/value stream ✅ Maintain manually or sync from org chart |