Vendor Spend Pareto (80/20)

What data it expects

Connect QuickBooks Online or drop a CSV / Excel export with a similar layout — the AI adapts the workflow if your columns differ.

How it works — every step, readable

1. 011) Load QuickBooks General Ledger

   SELECT * FROM read_csv_auto('QBO_GeneralLedger.csv', header=true)

2. 022) Clean GL: Payee + Non-Zero Amount

   filters to the relevant rows

   WITH normalized AS (
     SELECT
       TRIM(CAST(account AS VARCHAR)) AS account,
       TRY_CAST("date" AS DATE) AS txn_date,
       NULLIF(TRIM(CAST(name AS VARCHAR)), '') AS raw_payee_name,
       NULLIF(TRIM(CAST(memo_description AS VARCHAR)), '') AS memo_description,
       COALESCE(TRY_CAST(REPLACE(CAST(amount AS VARCHAR), ',', '') AS DOUBLE), 0) AS amount
     FROM input_1
   ), cleaned AS (
     SELECT
       account,
       txn_date,
       COALESCE(
         raw_payee_name,
         NULLIF(TRIM(regexp_extract(memo_description, '-\s*(.+)$', 1)), '')
       ) AS payee_name,
       memo_description,
       amount
     FROM normalized
   )
   SELECT
     account,
     txn_date,
     payee_name,
     memo_description,
     amount
   FROM cleaned
   WHERE payee_name IS NOT NULL
     AND txn_date IS NOT NULL
     AND ABS(amount) > 0

3. 033) Monthly Spend by Vendor

   aggregates rows into summary totals · buckets values by condition · filters to the relevant rows

   SELECT
     payee_name AS "Vendor",
     ROUND(SUM(CASE WHEN strftime(txn_date, '%Y-%m') = '2026-05' THEN -amount ELSE 0 END), 2) AS "May 2026",
     ROUND(SUM(CASE WHEN strftime(txn_date, '%Y-%m') = '2026-06' THEN -amount ELSE 0 END), 2) AS "Jun 2026",
     ROUND(SUM(CASE WHEN strftime(txn_date, '%Y-%m') = '2026-07' THEN -amount ELSE 0 END), 2) AS "Jul 2026",
     ROUND(SUM(-amount), 2) AS "Total Spend"
   FROM input_1
   WHERE amount < 0
   GROUP BY payee_name
   HAVING SUM(-amount) > 0
   ORDER BY "Total Spend" DESC

4. 044) Vendor Pareto Analysis

   computes running / windowed totals · filters to the relevant rows · sorts the output

   WITH ranked AS (
     SELECT
       "Vendor",
       "Total Spend",
       SUM("Total Spend") OVER () AS grand_total,
       SUM("Total Spend") OVER (ORDER BY "Total Spend" DESC, "Vendor" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_spend
     FROM input_1
     WHERE "Total Spend" > 0
   ), pareto AS (
     SELECT
       "Vendor",
       "Total Spend",
       ROUND("Total Spend" / NULLIF(grand_total, 0) * 100, 2) AS "% of Total",
       ROUND(cumulative_spend / NULLIF(grand_total, 0) * 100, 2) AS "Cumulative %",
       CASE
         WHEN (cumulative_spend - "Total Spend") / NULLIF(grand_total, 0) < 0.80 THEN 'Core 80%'
         ELSE ''
       END AS "Core 80%"
     FROM ranked
   )
   SELECT
     "Vendor",
     ROUND("Total Spend", 2) AS "Total Spend",
     "% of Total",
     "Cumulative %",
     "Core 80%"
   FROM pareto
   ORDER BY "Total Spend" DESC