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: app.daily_metrics ⚠️ UPDATED

Summary Table

Metric Category	Ad Level	Campaign Level	Account Level	Status
Financial Metrics	18 metrics	18 metrics	16 metrics	✅ UPDATED - Account now has more financial metrics
Advertising Metrics	11 metrics	11 metrics	3 metrics	✅ UPDATED - Account now has basic advertising metrics
Leads Metrics	9 metrics	9 metrics	7 metrics	✅ UPDATED - Account streamlined for daily_metrics
Bookings Metrics	16 metrics	16 metrics	17 metrics	✅ UPDATED - Account has comprehensive booking metrics
Sales Metrics	7 metrics	7 metrics	20 metrics	✅ UPDATED - Account has extensive sales metrics
Applications Metrics	8 metrics	8 metrics	11 metrics	✅ UPDATED - Account has additional qualified app metrics
Cash Metrics	0 metrics	0 metrics	6 metrics	✅ NEW - Account has dedicated cash tracking
Conversion Rates	18 metrics	18 metrics	21 metrics	✅ UPDATED - Account has additional conversion rates

⚠️ IMPORTANT CHANGE

Account Level Data Source Updated: The account level alert system now uses app.daily_metrics instead of dm_dashboard.channel_daily_dashboard. This change provides:

• More comprehensive metrics from the daily_metrics table
• Better alignment with actual data structure
• Additional cash tracking metrics not available in channel_daily_dashboard
• Streamlined metric categories optimized for daily_metrics structure

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

Applications Metrics (NEW)

Metric	Ad Level	Campaign Level	Account Level	Notes
num_apps	✅	✅	✅	Total number of applications
num_apps_a	✅	✅	✅	Number of applications from rotator A
num_apps_b	✅	✅	✅	Number of applications from rotator B
num_apps_c	✅	✅	✅	Number of applications from rotator C
num_apps_unknown	✅	✅	✅	Number of applications with unknown rotator
num_qapps	✅	✅	✅	Number of qualified applications (SAL = Yes or p_sql >= 0.25)
cpa	✅	✅	✅	Cost per application (spend / num_apps)
cpqa	✅	✅	✅	Cost per qualified application (spend / num_qapps)

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
6. NEW: Applications metrics are available across all levels

❌ 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
5. Add applications metrics to alert configuration for all levels

Implementation Status

• Ad Level: ✅ Fully aligned with ad_daily_dashboard (including new applications metrics)
• Campaign Level: ✅ Fully aligned with campaign_daily_dashboard (including new applications metrics)
• Account Level: ❌ Needs fixes - advertising metrics should be removed, applications metrics should be added

New Applications Metrics Details

The following application-related metrics have been identified in all three dashboard tables:

Application Counts

• num_apps: Total number of applications
• num_apps_a: Applications from rotator A
• num_apps_b: Applications from rotator B
• num_apps_c: Applications from rotator C
• num_apps_unknown: Applications with unknown rotator

Qualified Applications

• num_qapps: Number of qualified applications (SAL = 'Yes' OR p_sql >= 0.25)

Cost Metrics

• cpa: Cost per application (spend / num_apps)
• cpqa: Cost per qualified application (spend / num_qapps)

Next Steps

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

---

Last updated: December 2024 Document version: 2.0 Added: Applications metrics analysis