Performance Marketing, computed in the database
An open-source reference app that answers the only question that matters — which campaigns actually make money — by scoring true ROAS against 90-day LTV, not the day-0 number the ad platforms report.
Day-0 ROAS lies. LTV doesn’t.
The number every ad platform optimizes to — return on ad spend on the day of the click — is the number that’s easiest to game and least connected to your P&L. A discount-led TikTok creative posts a great day-0 ROAS and buys a customer who never comes back. An education-led search ad looks mediocre on day-0 and buys the highest-LTV customer you have.
This app puts both rankings side by side and draws the line between them, so the reversal is impossible to miss — then it turns the report into a decision with an in-database agent. Every number is live SQL over a warehouse that holds ad spend, first-party clickstream, identity, conversions, orders, and the ad creative as 384-dimension embeddings. One engine, one connection, one auth token.
What the app shows — and what powers it
No vector database, no separate attribution pipeline, no notebook. Every surface below is one SQL statement (or one agent call) against the same engine.
The ROAS Flip
Rank the same campaigns by day-0 ROAS and by 90-day LTV:CAC. The order reverses — the ad platform's best performer is often your worst.
Two live SQL rankings over the LTV scorecard
Campaign Scorecard
Spend, CAC, day-0 ROAS, 90-day LTV, LTV ROAS and margin ROAS per campaign — contribution-true, not platform-reported.
A CTE over spend + attribution + orders
The Click Truth
Platform-reported clicks vs. what your own site actually recorded. The silent gap that corrupts every CPC you look at.
A first-party clickstream join
The Creative Angle
Each ad's copy scored against a discount-&-urgency vector vs. the LTV it bought. The relationship is inverse — and visible.
EMBED + COSINE_SIMILARITY on the creative
Attribution Ledger
Last-touch credit across all platforms — correcting the cross-platform steal each channel claims in its own dashboard.
ROW_NUMBER over the clickstream
Ask the Database
An agent reads the live scorecard with its own SQL tool and recommends the budget move — cut this, scale that, and why.
AGENT_RUN (deterministic fallback with no LLM)
It’s a real application, not a static demo — full auth, RBAC, and a warehouse you can fill.
In the UI
- One-click sample dataset (recipe 121)
- Per-source CSV upload — Facebook, TikTok, Google, clickstream, identity, conversions, orders, creative
- A table explorer over the whole warehouse
For your pipeline
- An ETL ingestion REST API —
POST /api/v1/ingest/:source - API-key auth, JSON or NDJSON
- Derived attribution + scorecard rebuild on write
Open source. Self-hostable in minutes.
Apache-2.0, built on @synapcores/app-framework — the same auth / RBAC / UI / agent / audit primitives behind the SynapCores SOAR and AML apps. Clone it, docker compose up, and load the sample data.