Marketing Intelligence Dashboard
Data Range
Upload Your Data Sources
Upload each data source to populate the dashboard. All three sources are needed for complete analysis.
Monthly Marketing Spend (enter manually)
Enter your actual monthly spend from your Google Sheets expense tracker. These are the ONLY spend numbers used in CPL and ROI calculations.
Data Sources: Smart Advocate provides case status and settlement data (column: "Status", "Referral Source", "Settlement Amount (Total)"). Juvo Leads provides lead volume, traffic source, device, and campaign attribution (columns: "Source", "Lead Type", "Device", "utm_campaign"). Spend is entered manually from your Google Sheets expense tracker. No numbers are fabricated or estimated unless explicitly labeled "ESTIMATED."
▶
How to Export Data for This Dashboard (3 sources)
Click to expand
ROI by Marketing Channel
Spend, leads, signed cases, and return for each channel
Spend Distribution
Where your marketing dollars go
Source: Manually entered spend values above. Only channels with spend entered are shown.
Leads vs Signed Cases by Source
Light bar = total leads (Juvo), dark bar = signed cases (Smart Advocate)
Leads counted from Juvo "Source" column. Signed cases counted from Smart Advocate where Status contains "PreSuit" or "Settled." These are two different systems and source categories may not match perfectly.
Channel Efficiency Ranking
Channels ranked by cost per signed case (lowest = most efficient)
Cost/Case = Manually entered channel spend / Signed cases from Smart Advocate for that source. Only shown for channels with both spend data and signed cases. Verdict thresholds: Excellent (<$3K), Good ($3K-$8K), Needs Review ($8K-$15K), Red Flag (>$15K).
Monthly Lead Volume
Blue = Juvo leads per month, Green = Smart Advocate signed cases per month
Blue bars: Count of rows in Juvo export grouped by month from "Date" column. Green bars: Count of Smart Advocate rows where Status contains "PreSuit" or "Settled" grouped by "Opened Date" month. These are independent counts from two different systems.
Weekly Lead Trend
Leads per week over time (Juvo data only)
Source: Juvo export "Date" column, grouped into calendar weeks. Each bar = one week's lead count.
Day of Week Pattern
Which days generate the most leads
Source: Juvo export "Date" column, grouped by day of week. Gray = weekends, Blue = weekdays.
Lead Type Distribution
Form vs Phone vs Chat
Source: Juvo export "Lead Type" column. Values are taken exactly as they appear in the export.
Device Breakdown
What devices leads use when contacting the firm
Source: Juvo export "Device" column. Values taken exactly as they appear. Blank entries counted as "Unknown."
Lead-to-Revenue Funnel
Conversion at each stage from initial lead to settled case
Funnel stages are defined by Smart Advocate "Status" column: Leads In = total Juvo leads OR total SA records. Signed = Status contains "PreSuit" or "Settled." Settled = Status contains "Settled" or "Awaiting Release." Conversion percentages = current stage count / previous stage count.
Funnel by Source
How each channel performs through the funnel
Source: Smart Advocate "Referral Source" column, categorized into channel groups. All counts are from Smart Advocate only. Conv% = Signed / Total for that source.
Pipeline Value
Estimated dollar value at each stage
Case Type Conversion
Which practice areas convert best from lead to signed case
Source: Smart Advocate "Type" column. Total = all records with that case type. Signed = records with that type where Status contains "PreSuit" or "Settled." Conv% = Signed / Total.
Shiner Law Group - Marketing Intelligence Dashboard
💬