Spend grain with maverick/non-PO flags — no approval comment bodies.
CREATE TABLE curated_fct_coupa_invoice AS
SELECT
i.invoice_id,
i.invoice_number,
i.po_id,
i.supplier_id,
i.status,
i.invoiced_at,
i.total_amount,
i.currency,
i.requires_po,
CASE
WHEN i.requires_po = TRUE AND i.po_id IS NULL THEN TRUE
ELSE FALSE
END AS is_maverick_non_po,
p.total_amount AS po_total_amount,
p.is_non_catalog,
p.contract_id,
CASE
WHEN p.po_id IS NOT NULL
AND ABS(i.total_amount - p.total_amount) / NULLIF(p.total_amount, 0) > 0.05
THEN TRUE
ELSE FALSE
END AS amount_mismatch_flag
FROM raw_coupa_invoice i
LEFT JOIN raw_coupa_purchase_order p ON p.po_id = i.po_id
WHERE i.status NOT IN ('draft', 'voided');
Curated dim supplier / user / PO fact
Supplier/user without email; PO fact for catalog/contract compliance.
CREATE TABLE curated_dim_coupa_supplier AS
SELECT
supplier_id,
supplier_number,
supplier_name,
active
-- omit contact_email from default analytics dims
FROM raw_coupa_supplier;
CREATE TABLE curated_dim_coupa_user AS
SELECT
user_id,
login,
active
-- omit email from default analytics dims
FROM raw_coupa_user;
CREATE TABLE curated_fct_coupa_purchase_order AS
SELECT
po_id,
po_number,
supplier_id,
requester_user_id,
status,
ordered_at,
total_amount,
currency,
contract_id,
is_non_catalog,
CASE WHEN contract_id IS NULL OR is_non_catalog = TRUE THEN TRUE ELSE FALSE END AS maverick_indicator
FROM raw_coupa_purchase_order
WHERE supplier_id IS NOT NULL;
Curated measure SELECTs
Invoice spend, maverick rate, PO spend — adapt period filters.
-- Invoice spend in period
SELECT SUM(total_amount) AS invoice_spend
FROM curated_fct_coupa_invoice
WHERE invoiced_at BETWEEN :period_start AND :period_end;
-- Maverick / non-PO invoice rate
SELECT
SUM(CASE WHEN is_maverick_non_po THEN 1 ELSE 0 END) * 1.0
/ NULLIF(COUNT(*), 0) AS maverick_invoice_rate
FROM curated_fct_coupa_invoice
WHERE invoiced_at BETWEEN :period_start AND :period_end;
-- PO spend with maverick indicator
SELECT
SUM(total_amount) AS po_spend,
SUM(CASE WHEN maverick_indicator THEN total_amount ELSE 0 END) AS maverick_po_spend
FROM curated_fct_coupa_purchase_order
WHERE ordered_at BETWEEN :period_start AND :period_end;
Tools
Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.