Sam asked Data Analytics to build a Marketing ROI command-center view from Litify monthly spend, Marketing_Expense__c, Google Ads, and Semrush, with paid search performance separated from SEO and visibility signals. The goal was not a generic marketing dashboard; it was a source-backed command-center surface that can show spend, reconciliation, paid search, and organic visibility without blending incompatible metrics.
I loaded the Projects instructions, Data Analytics build-dashboard workflow, and the registered SAIL Command Center semantic layer. I verified Data Analytics setup had a SAIL Command Center semantic layer registered and used it for source precedence and caveats.
I confirmed Salesforce CLI connectivity to LITIFY_ORG and queried live Salesforce data. For litify_mktgROI__Monthly_Marketing_Spend__c, 2026 YTD monthly spend totals $781,692.48 across 148 rows. By spend type, Advertising totals $483,213.73, Sponsorship totals $163,841.75, Production Cost totals $76,524.00, and Management Fee totals $58,113.00. Top Litify spend sources include Television Ad, Sponsorship, Billboard Ad, Google Ads, Google LSA, and SEO.
I queried Marketing_Expense__c live by Expense_Date__c for 2026. Reconciled expenses with receipts attached total $906,417.75 across 164 rows. Rejected expenses total $244,687.31 across 28 rows. Draft expenses total $108,897.94 across 21 rows. The monthly expense totals do not reconcile one-to-one with the monthly spend object, especially April 2026, where expense-date rows total $477,819.46 while monthly spend records total $82,614.06. The dashboard therefore shows reconciliation explicitly rather than blending these into one ROI denominator.
I read Google Ads account setup for customer 381-391-6687. The account is Sam Aguiar Injury Lawyers, USD, America/New_York, with active Search campaigns and paused archive/PMax campaigns. I then pulled Google Ads performance through NotFair. For the last 30 days, 2026-05-06 through 2026-06-04, Google Ads spend was $20,431.58 with 1,861 clicks, 46,925 impressions, 99 conversions, and CPA of $206.38. Monthly paid-search trend was queried separately for 2026-01-01 through 2026-06-04 to avoid using a truncated daily extract.
I queried Semrush for aguiarinjurylawyers.com in the US database. The current snapshot returned 3,599 organic keywords, 7,498 estimated organic traffic, and $575,620 organic cost. The 12-month trend shows Semrush estimated organic traffic at 6,546 in April 2026 and 7,181 in May 2026. I kept Semrush in the SEO and visibility lane, not the paid search lane.
I built, validated, and rendered a Data Analytics MCP dashboard titled SAIL Marketing ROI Command Center. The dashboard includes headline KPI cards, monthly spend vs expense reconciliation, paid-search spend trend, SEO visibility trend, top Litify spend sources, paid-search campaign performance, expense reconciliation status, and top search terms with spend and zero Google Ads conversions.
The first Google Ads wide query included a 180-day daily trend, but the daily result was truncated before it covered current months. I did not use that partial trend for the dashboard. I ran a second, narrower monthly Google Ads query using segments.month from 2026-01-01 through 2026-06-04, which returned complete monthly rows.
The Semrush discovery tool required get_report_schema before execute_report. The needed schema tool was not exposed by the first tool discovery response, so I searched for Semrush schema tooling and then called get_report_schema for domain_rank and domain_rank_history before executing the reports.
The Data Analytics artifact validator rejected several initial manifest shapes: card titles were not supported, block ids were required, table columns needed field instead of key, and chart/table sources needed embedded source query objects instead of source ids. I fixed those and validated successfully before rendering.
The Notion enhanced Markdown spec fetch failed with INVALID_ARGUMENT, so the Notion export uses conservative plain headings and paragraphs based on the create-pages tool examples.
The dashboard uses the MCP artifact surface instead of a static HTML file or local server because the user invoked Data Analytics and the build-dashboard skill recommends MCP artifacts for compact in-Codex analytical dashboards when no external BI destination is specified.
Paid search and SEO are separate lanes. Google Ads is the source of truth for spend, conversions, campaign status, search terms, CPA, CTR, and impression share. Semrush is the source of truth for organic demand and visibility estimates. The dashboard does not turn Semrush demand into paid recommendations without Ads evidence.
The dashboard treats expense reconciliation as a visible command-center lane. The live data showed Marketing_Expense__c and monthly spend do not reconcile cleanly for 2026, especially April, so the dashboard should not present a single blended ROI denominator until reconciliation rules are confirmed.
Google Ads conversions are not presented as signed-case truth. They are Google Ads conversion actions and should only become signed-case ROI after the Litify offline conversion path and source matching are verified for the same period.