SDI Lab / BI cockpit Demo on a made-up Swiss insurance-lead business, data as of 28.10.2026 18:00. Names, brokers and numbers are invented.

Referee

Both numbers are right. Here is what each one counts that the other doesn't. ✓ Adds up: 1'102 − 962 + 77 = 217, and both totals match the numbers on the overview. Every step comes from the two definitions: the date each one counts by, and what it filters out. Only counts can be compared this way; revenue adds up francs, not leads.

Leads (Marketing)
1'102
by creation date
Contracts signed (Sales)
217
by contract date

Marketing counts 885 more. 140 leads are in both.

Where the difference comes from

Counted by Marketing only

Counted by Sales only

How it's calculated

Rows per step

with scoped as (
  select lead_id, created_at, channel, canton, product, broker, appointment_at, appointment_status, contract_signed_at, invoiced_at, invoice_amount_chf,
         coalesce((created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_a,
         coalesce((contract_signed_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and contract_signed_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_b
  from leads
  where (created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
     or (contract_signed_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and contract_signed_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
),
classified as (
  select scoped.*,
         case when in_a and not in_b then case when contract_signed_at is null then 'minus:time_null'
                  when contract_signed_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_before'
                  when contract_signed_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_after'
                  else 'minus:filter' end
              when in_b and not in_a then case when created_at is null then 'plus:time_null'
                  when created_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_before'
                  when created_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_after'
                  else 'plus:filter' end
              when in_a then 'both'
              else 'neither' end as bucket
  from scoped
),
stepped as (
  select s.step, classified.*
  from classified
  cross join lateral (values (case when in_a then 'start' end),
                             (case when in_b then 'end' end),
                             (bucket)) as s(step)
  where s.step is not null
)
select step, count(*) as n
from stepped
group by step
order by step

Examples per step

with scoped as (
  select lead_id, created_at, channel, canton, product, broker, appointment_at, appointment_status, contract_signed_at, invoiced_at, invoice_amount_chf,
         coalesce((created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_a,
         coalesce((contract_signed_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and contract_signed_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_b
  from leads
  where (created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
     or (contract_signed_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and contract_signed_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
),
classified as (
  select scoped.*,
         case when in_a and not in_b then case when contract_signed_at is null then 'minus:time_null'
                  when contract_signed_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_before'
                  when contract_signed_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_after'
                  else 'minus:filter' end
              when in_b and not in_a then case when created_at is null then 'plus:time_null'
                  when created_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_before'
                  when created_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_after'
                  else 'plus:filter' end
              when in_a then 'both'
              else 'neither' end as bucket
  from scoped
),
stepped as (
  select s.step, classified.*
  from classified
  cross join lateral (values (case when in_a then 'start' end),
                             (case when in_b then 'end' end),
                             (bucket)) as s(step)
  where s.step is not null
)
select step, lead_id, created_at, channel, canton, product, broker, appointment_at, appointment_status, contract_signed_at, invoiced_at, invoice_amount_chf
from (
  select stepped.*, row_number() over (partition by step order by lead_id) as rn
  from stepped
  where step not in ('both', 'neither')
) ranked
where rn <= 5
order by step, lead_id

Leads (Marketing)

select count(distinct lead_id) as value
from leads
where created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich'
  and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'

Contracts signed (Sales)

select count(distinct lead_id) as value
from leads
where contract_signed_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich'
  and contract_signed_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'

The app runs these queries with parameters; here the values are written in, so they can be read or pasted into psql.