Track Spend, Revenue, ROAS, MER, CAC, and AOV across Google/Meta/TikTok using simple CSV uploads. Multi-tenant workspaces, secure auth, and optional Stripe subscriptions.
Status: MVP (CSV-only). Connectors (Google Ads, Meta, TikTok, GA4) are stubbed for later.
- App: https://charis-advision.lovable.app
- Test user: sign up directly (email/password)
- (Note: demo runs on Lovable free hosting, may have limited availability)
- Dashboards: Overview KPIs + time series, channel mix, top campaigns
- Attribution: Last-click (UTM) + Linear + Time-decay (toggle)
- Imports: Drag-drop CSVs for
ad_spendandorderswith validation & preview - Multi-tenant: Workspaces (orgs), roles: Owner / Admin / Analyst
- Secure by default: Supabase Auth + Row Level Security (RLS)
- Frontend: Next.js • TypeScript • Tailwind • shadcn/ui • Recharts
- Backend: Supabase (Postgres, Auth, Edge Functions)
- ORM: Prisma
- Hosting: Lovable (1-click deploy) + GitHub
- Create project on Lovable and deploy (already live at: https://charis-advision.lovable.app).
- In Settings → Environment Variables, configure:
NEXT_PUBLIC_SUPABASE_URLNEXT_PUBLIC_SUPABASE_ANON_KEYSUPABASE_SERVICE_ROLE_KEY(server only)NEXT_PUBLIC_APP_URL(your live URL)
- In Supabase Studio → SQL Editor, run the schema & RLS below (see Database Setup).
- Visit the app → Sign up → create Workspace → go to Imports → upload CSVs → open Dashboard.
git clone https://github.com/YOUR-USER/charis-advision.git
cd charis-advision
npm install
# Create .env.local with the env vars listed above
npm run dev
Database Setup (Supabase)
Run Schema & RLS first, then the KPI View. If you already ran these in Studio, you’re set.
<details> <summary><strong>Schema & RLS (click to expand)</strong></summary>
create extension if not exists pgcrypto;
-- Workspaces (orgs) & memberships
create table if not exists public.orgs (
id uuid primary key default gen_random_uuid(),
name text not null,
currency text not null default 'GBP',
timezone text not null default 'Europe/London',
created_at timestamptz default now()
);
create table if not exists public.memberships (
user_id uuid not null references auth.users(id) on delete cascade,
org_id uuid not null references public.orgs(id) on delete cascade,
role text not null check (role in ('owner','admin','analyst')),
created_at timestamptz default now(),
primary key (user_id, org_id)
);
-- Ad spend & orders
create table if not exists public.ad_spend (
id bigserial primary key,
org_id uuid not null references public.orgs(id) on delete cascade,
date date not null,
channel text not null,
campaign text,
adset text,
ad text,
impressions bigint,
clicks bigint,
spend numeric(12,2) not null default 0
);
create table if not exists public.orders (
id bigserial primary key,
org_id uuid not null references public.orgs(id) on delete cascade,
order_id text,
date date not null,
customer_id text,
revenue numeric(12,2) not null,
currency text default 'GBP',
utm_source text,
utm_medium text,
utm_campaign text
);
-- Metric cache (optional)
create table if not exists public.metric_snapshots (
id bigserial primary key,
org_id uuid not null references public.orgs(id) on delete cascade,
date date not null,
spend numeric(12,2) default 0,
revenue numeric(12,2) default 0,
clicks bigint,
impressions bigint,
ctr numeric(10,4),
cpc numeric(12,4),
cpm numeric(12,4),
roas numeric(12,4),
mer numeric(12,4),
cac numeric(12,4),
aov numeric(12,4)
);
-- RLS
alter table public.orgs enable row level security;
alter table public.memberships enable row level security;
alter table public.ad_spend enable row level security;
alter table public.orders enable row level security;
alter table public.metric_snapshots enable row level security;
-- Helper
create or replace function public.is_admin(_org uuid)
returns boolean language sql stable as $$
select exists(
select 1 from public.memberships m
where m.org_id = _org and m.user_id = auth.uid() and m.role in ('owner','admin')
);
$$;
-- Policies
create policy if not exists "members can read orgs" on public.orgs
for select using (
exists (select 1 from public.memberships m where m.org_id = orgs.id and m.user_id = auth.uid())
);
create policy if not exists "auth can create orgs" on public.orgs
for insert with check (auth.uid() is not null);
create policy if not exists "admins update orgs" on public.orgs
for update using (public.is_admin(id));
create policy if not exists "user reads own memberships" on public.memberships
for select using (user_id = auth.uid());
create policy if not exists "user inserts own membership" on public.memberships
for insert with check (user_id = auth.uid());
create policy if not exists "read ad_spend" on public.ad_spend
for select using (
exists (select 1 from public.memberships m where m.org_id = ad_spend.org_id and m.user_id = auth.uid())
);
create policy if not exists "insert ad_spend" on public.ad_spend
for insert with check (
exists (select 1 from public.memberships m where m.org_id = ad_spend.org_id and m.user_id = auth.uid())
);
create policy if not exists "update/delete ad_spend" on public.ad_spend
for all using (public.is_admin(org_id)) with check (public.is_admin(org_id));
create policy if not exists "read orders" on public.orders
for select using (
exists (select 1 from public.memberships m where m.org_id = orders.org_id and m.user_id = auth.uid())
);
create policy if not exists "insert orders" on public.orders
for insert with check (
exists (select 1 from public.memberships m where m.org_id = orders.org_id and m.user_id = auth.uid())
);
create policy if not exists "update/delete orders" on public.orders
for all using (public.is_admin(org_id)) with check (public.is_admin(org_id));
create policy if not exists "read metric_snapshots" on public.metric_snapshots
for select using (
exists (select 1 from public.memberships m where m.org_id = metric_snapshots.org_id and m.user_id = auth.uid())
);
create policy if not exists "write metric_snapshots (admins)" on public.metric_snapshots
for all using (public.is_admin(org_id)) with check (public.is_admin(org_id));
</details> <details> <summary><strong>KPI View for Charts (click to expand)</strong></summary>
drop view if exists public.kpi_daily;
create or replace view public.kpi_daily as
with dates as (
select org_id, date from public.ad_spend
union
select org_id, date from public.orders
),
sp as (
select org_id, date,
sum(spend) spend,
sum(clicks) clicks,
sum(impressions) impressions
from public.ad_spend
group by 1,2
),
ord as (
select org_id, date,
sum(revenue) revenue,
count(distinct order_id) orders
from public.orders
group by 1,2
)
select
d.org_id,
d.date,
coalesce(sp.spend,0) as spend,
coalesce(ord.revenue,0) as revenue,
coalesce(sp.clicks,0) as clicks,
coalesce(sp.impressions,0) as impressions,
case when coalesce(sp.impressions,0)>0
then coalesce(sp.clicks,0)::numeric/sp.impressions end as ctr,
case when coalesce(sp.clicks,0)>0
then coalesce(sp.spend,0)/sp.clicks end as cpc,
case when coalesce(sp.impressions,0)>0
then coalesce(sp.spend,0)*1000/sp.impressions end as cpm,
case when coalesce(sp.spend,0)>0
then coalesce(ord.revenue,0)/sp.spend end as roas,
case when coalesce(sp.spend,0)>0
then coalesce(ord.revenue,0)/sp.spend end as mer,
case when coalesce(ord.orders,0)>0
then coalesce(sp.spend,0)/ord.orders end as cac,
case when coalesce(ord.orders,0)>0
then coalesce(ord.revenue,0)/ord.orders end as aov
from dates d
left join sp on sp.org_id=d.org_id and sp.date=d.date
left join ord on ord.org_id=d.org_id and ord.date=d.date;
Sample rows
# ad_spend.csv
date,channel,campaign,adset,ad,impressions,clicks,spend
2025-08-01,google,Brand Search,UK-Core,Headline A,12000,540,185.30
2025-08-01,meta,Prospecting 1,Lookalike 2%,UGC V1,8500,310,97.45
2025-08-02,tiktok,Creators 7d,Interest-Fashion,Hook B,6400,220,61.10
# orders.csv
order_id,date,customer_id,revenue,currency,utm_source,utm_medium,utm_campaign
A1001,2025-08-01,C-001,129.99,GBP,google,cpc,Brand Search
A1002,2025-08-01,C-002,49.00,GBP,meta,paid_social,Prospecting 1
A1003,2025-08-02,C-003,79.00,GBP,tiktok,paid_social,Creators 7d