How to build Kajabi revenue dashboards in Metabase

# 1. Exchange your Public API key and secret for a token
TOKEN=$(curl -s -X POST https://api.kajabi.com/v1/oauth/token \
  -H 'Content-Type: application/json' \
  -d '{"grant_type":"client_credentials",
       "client_id":"'"$KAJABI_CLIENT_ID"'",
       "client_secret":"'"$KAJABI_CLIENT_SECRET"'"}' \
  | jq -r '.access_token')

# 2. Page through transactions (amounts are in cents; refunds are negative)
curl -s -G https://api.kajabi.com/v1/transactions \
  -H "Authorization: Bearer $TOKEN" \
  -H 'Accept: application/vnd.api+json' \
  --data-urlencode 'page[size]=100' \
  > kajabi-transactions.json

# 3. Flatten to CSV for the Metabase CLI
jq -r '.data[] | [
  .id, .attributes.created_at, .attributes.kind, .attributes.state,
  (.attributes.amount_in_cents / 100),
  (.attributes.sales_tax_in_cents / 100),
  ((.attributes.payment_fee_in_cents // 0) / 100),
  .attributes.currency
] | @csv' kajabi-transactions.json > kajabi-creator-orders.csv

# Install the Metabase CLI
npm install -g @metabase/cli

# Log in (opens your browser; requires Metabase v62+)
mb auth login --url https://your-metabase.example.com

# Load a transactions export — creates a table AND a model
mb upload csv --file kajabi-transactions.csv --collection root

# Refresh that same table later from a new export
mb upload replace <table-id> --file kajabi-transactions.csv

Create a polished Metabase dashboard for Kajabi course revenue analytics.
Work end to end: get the data into Metabase if it isn't there yet, then build.

Goal: Help the team understand gross vs. net revenue, product performance, refunds, student completion, and payout reconciliation from Kajabi data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for kajabi tables and
  models). If durable Kajabi data is already present — a warehouse sync, a
  direct database connection, or an earlier upload — use it and skip to Step 2.
- If nothing is there, pull a scoped, summarized export by scripting the Kajabi Public API:
  transactions, plus orders and order items, offers and products, customers and contacts.
  Write each result to a CSV, then load it with the Metabase CLI — run
  "mb upload csv --file <export>.csv" so each upload creates a table and a
  ready-to-query model. Use "mb upload replace <table-id> --file <export>.csv"
  to refresh an existing table instead of creating duplicates.

Step 2 — Inspect before querying:
Do not assume exact table or column names. Inspect available fields, products,
locations or countries, currencies, and date ranges before creating trend cards.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Kajabi — it reads a database or CLI-uploaded tables.
- Separate gross amounts, platform and processing fees, taxes, and refunds into
  distinct columns; never let one card silently mix gross and net.
- A single CSV is a point-in-time snapshot: only build trend cards if there is
  a usable date column or multiple periods have been uploaded.
- Revenue trends need complete months of order history, and cohort views need each order's enrollment date preserved.
- If a metric can't be computed from the data present, say so on the card
  instead of approximating it.

Dashboard title: Kajabi Course Revenue Overview

Sections:
1. Executive summary: Net revenue this month; MoM growth; Refund rate;
   Effective fee rate; Active students.
2. Revenue: Gross vs. net by month; revenue by course; new vs. installments.
3. Acquisition: Sales by offer and coupon; AOV by product; new vs. repeat buyers.
4. Engagement: Completion rate by course; lesson drop-off; active students.
5. Payouts: Payouts vs. net earned; fee rate; outstanding plan balance.

Filters: Date range, Product or offer, Coupon, Currency, Order type.

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. Include caveats for any metric
that cannot be calculated from the available data.

Charges, refunds, tax, and fees resolved into one honest net line.

SELECT
  date_trunc('month', created_at)::date AS month,
  ROUND(SUM(gross_amount) FILTER (
    WHERE kind IN ('charge', 'subscription_charge')
  ), 2) AS gross_revenue,
  -- Refunds and disputes arrive as negative amounts on their own rows.
  ROUND(ABS(SUM(gross_amount) FILTER (
    WHERE kind IN ('refund', 'dispute')
  )), 2) AS refunds_and_disputes,
  ROUND(SUM(sales_tax), 2) AS tax_collected,
  ROUND(SUM(payment_fee), 2) AS processing_fees,
  ROUND(
    SUM(gross_amount) - SUM(sales_tax) - SUM(payment_fee), 2
  ) AS net_revenue
FROM creator_orders
WHERE state = 'succeeded'
  AND kind NOT IN ('free_purchase', 'test')
GROUP BY month
ORDER BY month DESC;

Which offers earn, and which ones quietly hand the money back.

WITH by_product AS (
  SELECT
    p.name AS product,
    SUM(o.gross_amount) FILTER (
      WHERE o.kind IN ('charge', 'subscription_charge')
    ) AS gross,
    ABS(SUM(o.gross_amount) FILTER (WHERE o.kind = 'refund')) AS refunded,
    COUNT(*) FILTER (
      WHERE o.kind IN ('charge', 'subscription_charge')
    ) AS charges
  FROM creator_orders o
  JOIN creator_products p ON p.product_id = o.order_id
  WHERE o.state = 'succeeded'
    AND o.created_at >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY p.name
)
SELECT
  product,
  ROUND(gross, 2) AS gross_revenue,
  charges,
  ROUND(COALESCE(refunded, 0), 2) AS refunded,
  ROUND(100.0 * COALESCE(refunded, 0) / NULLIF(gross, 0), 1)
    AS refund_rate_pct
FROM by_product
ORDER BY gross_revenue DESC;

The card that catches missing money before your accountant does.

WITH earned AS (
  SELECT
    date_trunc('month', created_at)::date AS month,
    SUM(gross_amount - sales_tax - payment_fee) AS net_earned
  FROM creator_orders
  WHERE state = 'succeeded' AND kind NOT IN ('free_purchase', 'test')
  GROUP BY 1
), paid AS (
  SELECT
    date_trunc('month', paid_at)::date AS month,
    SUM(amount) AS paid_out
  FROM creator_payouts
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT
  COALESCE(e.month, p.month) AS month,
  ROUND(COALESCE(e.net_earned, 0), 2) AS net_earned,
  ROUND(COALESCE(p.paid_out, 0), 2) AS paid_out,
  -- Payouts trail earnings by the platform's settlement delay, so
  -- expect a lag rather than a match in the most recent month.
  ROUND(COALESCE(p.paid_out, 0) - COALESCE(e.net_earned, 0), 2) AS variance
FROM earned e
FULL OUTER JOIN paid p ON p.month = e.month
ORDER BY month DESC;