Alert Level Metrics vs Dashboard Table Columns Comparison

Overview

This document provides a comprehensive comparison between the metrics available in the alert creation system and the actual columns available in the corresponding dashboard tables. This analysis helps ensure that alert configurations are accurate and that users can only create alerts for metrics that actually exist in the data.

Data Sources

• Ad Level: dm_dashboard.ad_daily_dashboard
• Campaign Level: dm_dashboard.campaign_daily_dashboard
• Account Level: dm_dashboard.channel_daily_dashboard

Summary Table

Metric Category	Ad Level	Campaign Level	Account Level	Status
Financial Metrics	16 metrics	16 metrics	11 metrics	✅ Ad/Campaign match, Account has fewer
Advertising Metrics	11 metrics	11 metrics	0 metrics	❌ Account level missing advertising metrics
Leads Metrics	9 metrics	9 metrics	11 metrics	✅ Account has additional MQL metrics
Bookings Metrics	16 metrics	16 metrics	17 metrics	✅ Account has additional MTO/LTO metrics
Sales Metrics	7 metrics	7 metrics	8 metrics	✅ Account has additional MTO metrics
Costs Metrics	5 metrics	5 metrics	6 metrics	✅ Account has additional MQL not set metric
Conversion Rates	18 metrics	18 metrics	20 metrics	✅ Account has additional MTO/LTO rates

Detailed Comparison

Financial Metrics

Metric	Ad Level	Campaign Level	Account Level	Notes
spend	✅	✅	✅	Total advertising spend
net_cash	✅	✅	✅	Net cash collected (including refunds)
net_cash_FE	❌	❌	✅	Net cash from front-end products (Account only)
net_cash_BE	❌	❌	✅	Net cash from back-end products (Account only)
projected_spend	✅	✅	✅	Projected advertising spend for the period
projected_net_cash	✅	✅	✅	Projected net cash for the period
projected_roas	✅	✅	✅	Projected return on ad spend
e_cash_l30	✅	✅	✅	Expected cash from bookings in last 30 days
e_cash_30d	✅	✅	✅	Expected cash from bookings within 30 days
e_roas_l30	✅	✅	✅	Expected ROAS from last 30 days
e_roas_30d	✅	✅	✅	Expected ROAS within 30 days
e_roas_2d	✅	✅	❌	Expected ROAS for bookings in next 2 days (Missing in Account)
e_roas_7d	✅	✅	❌	Expected ROAS for bookings in next 7 days (Missing in Account)
roas_30d	✅	✅	✅	30-day Return on Ad Spend
roas	✅	✅	✅	Overall Return on Ad Spend
ltv	✅	✅	✅	Customer Lifetime Value
acv	✅	✅	✅	Average Contract Value
cac	✅	✅	✅	Customer Acquisition Cost

Advertising Metrics

Metric	Ad Level	Campaign Level	Account Level	Notes
impressions	✅	✅	❌	MISSING in Account level
cpm	✅	✅	❌	MISSING in Account level
link_clicks	✅	✅	❌	MISSING in Account level
cpc	✅	✅	❌	MISSING in Account level
ctr	✅	✅	❌	MISSING in Account level
landing_page_view	✅	✅	❌	MISSING in Account level
cplpv	✅	✅	❌	MISSING in Account level
post_engagement	✅	✅	❌	MISSING in Account level
cppe	✅	✅	❌	MISSING in Account level
video_p50_watched	✅	✅	❌	MISSING in Account level
frequency	✅	✅	❌	MISSING in Account level

Leads Metrics

Metric	Ad Level	Campaign Level	Account Level	Notes
num_leads	✅	✅	✅	Total number of leads generated
num_qualified_leads	✅	✅	✅	Number of leads marked as qualified (SAL)
num_mqls	✅	✅	✅	Number of Marketing Qualified Leads
num_mqls_set	❌	❌	✅	Number of MQLs with set appointments (Account only)
num_mqls_not_set	❌	❌	✅	Number of MQLs without set appointments (Account only)
p_lead_to_mql	✅	✅	✅	Conversion rate from lead to MQL
median_time_from_lead_to_booking_creation	✅	✅	✅	Median time from lead creation to booking creation
median_time_from_application_to_booking_creation	✅	✅	✅	Median time from application to booking creation
avg_time_mql_to_sql_in_hours	✅	✅	✅	Average time from MQL to SQL in hours
num_pending_mqls	✅	✅	✅	Number of pending MQLs
e_sqls	✅	✅	✅	Expected number of SQLs

Bookings Metrics

Metric	Ad Level	Campaign Level	Account Level	Notes
num_bookings	✅	✅	✅	Total number of bookings created
num_bookings_a	✅	✅	✅	Number of bookings from rotator A
num_bookings_b	✅	✅	✅	Number of bookings from rotator B
num_bookings_c	✅	✅	✅	Number of bookings from rotator C
num_bookings_a_2d	✅	✅	✅	Number of bookings from rotator A within 2 days
num_bookings_b_2d	✅	✅	✅	Number of bookings from rotator B within 2 days
num_bookings_c_2d	✅	✅	✅	Number of bookings from rotator C within 2 days
num_bookings_a_7d	✅	✅	✅	Number of bookings from rotator A within 7 days
num_bookings_b_7d	✅	✅	✅	Number of bookings from rotator B within 7 days
num_bookings_c_7d	✅	✅	✅	Number of bookings from rotator C within 7 days
median_time_from_booking_creation_to_start	✅	✅	✅	Median time from booking creation to start
num_scheduled	✅	✅	✅	Number of AE bookings scheduled
num_past	✅	✅	✅	Number of AE bookings that have passed
num_past_hto	✅	✅	✅	Number of past HTO bookings
num_past_mto	❌	❌	✅	Number of past MTO bookings (Account only)
num_past_lto	❌	❌	✅	Number of past LTO bookings (Account only)
num_taken	✅	✅	✅	Number of AE bookings taken
num_unknown	✅	✅	✅	Number of AE bookings with unknown SQL status

Sales Metrics

Metric	Ad Level	Campaign Level	Account Level	Notes
num_sqls	✅	✅	✅	Number of Sales Qualified Leads
num_sqls_a	✅	✅	✅	Number of SQLs from rotator A
num_sqls_b	✅	✅	✅	Number of SQLs from rotator B
num_sqls_c	✅	✅	✅	Number of SQLs from rotator C
num_won	✅	✅	✅	Number of won deals
num_won_hto	✅	✅	✅	Number of won HTO deals
num_won_mto	❌	❌	✅	Number of won MTO deals (Account only)
num_won_lto	✅	✅	✅	Number of won LTO deals

Costs Metrics

Metric	Ad Level	Campaign Level	Account Level	Notes
cpl	✅	✅	✅	Cost per lead (spend / num_leads)
cpmql	✅	✅	✅	Cost per MQL (spend / num_mqls)
cpmql_not_set	❌	❌	✅	Cost per MQL without set appointments (Account only)
cpb	✅	✅	✅	Cost per booking (spend / num_bookings)
cpsql	✅	✅	✅	Cost per SQL (spend / num_sqls)
cpe_sqls	✅	✅	✅	Cost per expected SQL

Conversion Rates

Metric	Ad Level	Campaign Level	Account Level	Notes
p_show	✅	✅	✅	Show rate (taken/scheduled)
p_show_a	✅	✅	✅	Show rate for rotator A
p_show_b	✅	✅	✅	Show rate for rotator B
p_show_c	✅	✅	✅	Show rate for rotator C
p_qual	✅	✅	✅	Qualification rate (SQL/taken)
p_sql	✅	✅	✅	SQL rate (SQL/past scheduled)
p_sql_a	✅	✅	✅	SQL rate for rotator A
p_sql_b	✅	✅	✅	SQL rate for rotator B
p_sql_c	✅	✅	✅	SQL rate for rotator C
p_close	✅	✅	✅	Close rate (won/SQL)
p_close_a	✅	✅	✅	Close rate for rotator A
p_close_b	✅	✅	✅	Close rate for rotator B
p_close_c	✅	✅	✅	Close rate for rotator C
p_win	✅	✅	✅	Win rate (won/past)
p_win_hto	✅	✅	✅	Win rate for HTO
p_win_mto	❌	❌	✅	Win rate for MTO (Account only)
p_win_lto	❌	❌	✅	Win rate for LTO (Account only)
p_win_a	✅	✅	✅	Win rate for rotator A
p_win_b	✅	✅	✅	Win rate for rotator B
p_win_c	✅	✅	✅	Win rate for rotator C

Key Findings

✅ Correctly Implemented

1. Ad and Campaign levels have identical metrics (as expected)
2. Account level has additional MTO (Medium Ticket Offer) metrics
3. Account level has frontend/backend cash breakdown
4. Account level has MQL appointment tracking metrics
5. Account level has additional LTO (Low Ticket Offer) metrics

❌ Critical Issues Found

1. Account level is missing ALL advertising metrics (impressions, clicks, CTR, etc.)
2. Account level is missing e_roas_2d and e_roas_7d metrics
3. Alert configuration includes advertising metrics for account level that don't exist in the data

🔧 Recommended Fixes

1. Remove advertising metrics from account level alert configuration
2. Add missing financial metrics (e_roas_2d, e_roas_7d) to account level if they should exist
3. Verify if account level should have advertising metrics or if they're intentionally excluded
4. Update alert creation UI to only show available metrics for each level

Implementation Status

• Ad Level: ✅ Fully aligned with ad_daily_dashboard
• Campaign Level: ✅ Fully aligned with campaign_daily_dashboard
• Account Level: ❌ Needs fixes - advertising metrics should be removed

Next Steps

1. Update the CreateAlertWizard.tsx component to remove advertising metrics from account level
2. Verify if missing financial metrics should be added to account level
3. Test alert creation for all levels to ensure only valid metrics are available
4. Update this document when changes are implemented

---

Last updated: December 2024 Document version: 1.0