frankie-pnl

name: frankie-pnl description: Build cash-basis P&L for Ben's businesses. Two modes — (1) basic 30-day P&L across all 4 Stripe accounts with Mercury cost categorization and Wise reconciliation, or (2) monthly channel P&L over multiple months per product (ISK/CLCP/SE) with revenue, Google Ads spend, UGC creator payouts, and Datafast traffic mix. Use when Ben asks "what's our profit", "run the P&L", "30 day P&L", "monthly P&L", "P&L by channel", "how much did we make/spend", "channel breakdown", "are we profitable", "what's our burn", "how are channels trending", or any question requiring a cross-account income statement.

Frankie — P&L Builder

Use this any time Ben asks for a profit/loss view. Output is always cash-basis, grounded in real bank/Stripe data, not estimates.

Two modes available:

Mode 1: Basic cash P&L (pnl.py)

Single time window. All 4 Stripe accounts → revenue. All Mercury accounts → categorized OPEX. Wise reconciliation. 6-mo Chase CC normalization. Best for "how did we do last month" or "P&L for April."

python3 skills/frankie-pnl/scripts/pnl.py            # last 30 days (default)
python3 skills/frankie-pnl/scripts/pnl.py 60         # last 60 days
python3 skills/frankie-pnl/scripts/pnl.py 2026-04-01 2026-04-30   # explicit window

Output saved to memory/pnl-<startdate>-<enddate>.json.

Mode 2: Monthly channel P&L (pnl-channels.py)

Trending view — N calendar months × 3 products. Per product: Revenue, Google Ads spend, UGC creator payouts, allocated Chase CC OPEX, profit, margin. Plus Datafast visitor mix per channel (Google Ads / UGC / Organic / Direct / etc.) for the latest month.

python3 skills/frankie-pnl/scripts/pnl-channels.py            # last 3 months (default)
python3 skills/frankie-pnl/scripts/pnl-channels.py 6          # last 6 months

Output saved to memory/pnl-channels-<startmonth>-to-<endmonth>.json.

Datafast filter syntax — CRITICAL gotcha

The Datafast API silently ignores filter params that use the wrong name. The correct syntax for ALL filters is:

?filter_<key>=<operator>:<value>

Examples:

• ?filter_goal=is:payment → visitors who completed payment goal
• ?filter_utm_source=is:fb → visitors whose first touch was utm_source=fb
• ?filter_country=is_not:France → exclude France
• ?filter_utm_campaign=is:launch,black-friday → multi-value (comma-separated)

Valid operators: is, is_not, contains, does_not_contain (last two only for page/entry_page).

Wrong syntax silently returns full unfiltered data. Old code using ?utm_source=fb looked like it was running but was ignored.

Reference: the official Datafast CLI source at node_modules/@datafast/cli/dist/index.js line 2491 — it generates filter_<key>=<op>:<vals> query strings. Verified by Ben's screenshot of dashboard UI showing per-UTM payment data working correctly.

Channel attribution via filter_goal=is:payment

The trick that makes channel ROI work:

• /analytics/campaigns?filter_goal=is:payment → returns per-UTM payer counts (visitors who completed the payment goal)
• Combined with unfiltered /analytics/campaigns → gives conversion rate per channel
• Combined with total revenue from /analytics/timeseries?filter_goal=is:payment → ARPU
• payers_per_channel × ARPU = estimated channel revenue (rough — assumes equal deal size)
• spend_per_channel / payers_per_channel = real CAC per channel

The revenue field on per-campaign breakdowns still returns $0 (likely a separate Datafast bug or attribution-not-wired-up-per-source issue), so channel revenue is estimated via payers × ARPU, not direct.

UGC cost — paid vs accrued (CRITICAL gotcha)

The default pnl-channels.py script counts UGC by creator_payouts.paid_at (when money actually left). This lags real liability because:

1. Payments are weekly cycles. A creator earns work in week N (Mon-Sun) but gets paid on the following Wednesday. So early-month paid_at reflects prior month's work.
2. Milestone bonuses have a 15-day lock period before they can be paid. Bonuses earned in the last 2 weeks are real liability but won't appear in creator_payouts yet.

For TRUE accrued UGC cost per month, use the Frankie API:

GET https://getviralytics.com/api/ugc/payouts/frankie?org_id=<org>&week_start=<YYYY-MM-DD>
Header: x-frankie-key: $FRANKIE_PAY_SECRET

Returns per-creator per-week: base_payout, bonus_payout, misc_paid, eligible_posts (with views).

Caveat: bonus_payout is LOCK-FILTERED — only shows bonuses past their 15-day lock window. To get TRUE bonus accrual including locked-but-earned bonuses:

• Pull creator_payment_groups.milestone_tiers + creator_group_memberships from Supabase
• For each post in eligible_posts, compute milestone_bonus(views, tiers) = highest tier crossed
• Sum across posts for true unfiltered bonus

Or simpler: trust Ben's Viralytics UI as canonical for current-week ground truth. The web dashboard shows base + bonus including locked bonuses.

The local computation typically matches the UI within ~$50-200 per week (small discrepancies from post-view sync timing).

Other caveats

1. CLCP has no Viralytics org → UGC spend shows as $0 (correct — CLCP doesn't use UGC currently).
2. Only ISK runs Google Ads currently → CLCP/SE G.Ads show $0 (correct).
3. Chase CC normalized across the period and allocated by revenue share. Raw Chase CC payoffs are spiky because of manual EPAY catch-up payments — see Mode 1 docs.
4. Meta Ads not pulled (near-zero historical spend). Add if Marco starts a campaign.
5. Google Ads VND account converted to USD using a hard-coded FX rate (~0.0000391). Refresh periodically.

What it does

1. Stripe revenue — pulls successful charges + refunds across all 4 connected accounts using /v1/charges with expand[]=data.balance_transaction (gives gross, fees, net per charge). Viralytics uses its direct restricted key from /Users/benbroch/Desktop/New Code Products/viralytics/.env.local (the org key sees that account but the dedicated key is more reliable).
2. Mercury outflows — pulls all txns across the 3 Mercury accounts (Checking ••9064, Savings ••7906, Checking ••2212), categorizes them.
3. Wise reconciliation — pulls Wise transfers to figure out actual creator payouts vs internal balance moves.
4. 6-month Chase CC trend — flags whether the current period's Chase CC payoff is a timing anomaly vs the steady-state.
5. Flags unknown inflows — anything that isn't Stripe, Chase, savings transfer, or interest gets surfaced for manual review.

Mercury Categorization — THE GOTCHAS

These are the rules. Get them wrong and the P&L is wildly off (I learned the hard way).

Outflows (in priority order)

1. kind == "internalTransfer" → Internal: Mercury transfer out
   • MUST check this BEFORE any "mercury" in description substring match.
   • Otherwise creator payments via Send Money (description: "Send Money transaction initiated on Mercury") get false-matched as internal and disappear from creator cost.
2. "chase credit" in desc OR "chase card" in desc → OPEX: Chase Credit Card payoff (vendor card spend)
   • This is paying off the business credit card. The card itself is where AI APIs, infra, and ads live. NOT a personal draw.
3. "chase" in desc (after rule 2) → WITHDRAWAL: Chase Checking (profit out)
   • Specifically transfers to Chase Checking ••6195. This IS the personal draw.
4. "wise" in desc OR "transferwise" in desc → Wise FUNDING (NOT creator cost yet — see Wise reconciliation below)
5. kind == "outgoingPayment" OR "send money transaction" in desc → COGS: Creator payouts (Mercury Send Money)
   • These are ACH payments to US creators (Ashlee, Adam, Simas, Isaac, Jake, Griffin, Abigail, Noah, Devin, Luke, etc.)
6. Vendor keyword matches (OpenAI, Anthropic, AWS, Vercel, Supabase, Google, Meta, etc.) → OPEX: <vendor>
7. kind == "debitCardTransaction" → OPEX: Card spend (other)
8. Default → Outflow: Other (flag for manual review)

Inflows

1. "stripe" in desc → REVENUE: Stripe payout (cash basis — what hit the bank, lags accrual by 2-5 days)
2. kind == "internalTransfer" OR "mercury savings" in desc → Internal: from Savings/Mercury
3. "interest" in desc → Interest
4. Anything else → Inflow: Other (REVIEW) — these need manual classification. Past examples:
   • LIBA FABRICS COR ($13,287 on Apr 29 2026) — pass-through reimbursement for a fabric purchase that hit the Chase card. Both the inflow AND the corresponding Chase CC payoff portion should be excluded from the P&L.

Wise Reconciliation — THE OTHER BIG GOTCHA

Wise outflows from Mercury are funding transfers, not creator payouts. The actual creator payouts happen inside Wise.

If you treat Mercury → Wise as the creator cost, you'll over-count because:

• A single $5,000 Mercury → Wise transfer can fund multiple weeks of small India creator payouts.
• The leftover sits in Wise balance (pre-paid creator cost).

To get the real Wise creator cost, query /v1/transfers on Wise business profile 73285247:

• Real creator payouts = transfers where recipient name is an individual (Bharat Bedi, Pinki Soni, Rahul Rana, Sunita Lanjiwar, Tanishka Tomar, Mahek Khatri, Yashu, Riddhi Srivastava, Sanyam Gohil, Rishu Ganoliya, Aditi Chouhan, Leena, Avani Jethwa, etc.) — typically INR for India.
• NOT creator payouts = transfers where recipient is "Cover Letter Copilot, LLC" (the Wise profile's own name). These are internal Wise jar/sub-wallet/conversion accounting moves and never leave Wise.

Sanity check: (Mercury → Wise funding) − (Wise → real creators) + (start Wise balance) = current Wise balance. If it doesn't, dig.

Chase Credit Card — Timing Anomaly Detection

The Chase CC has a steady-state autopay of ~$2-4k/month (every ~1st of month, "AUTOPAYBUS"). When you see big manual "EPAY" payments, those are catch-up payments for accumulated balance — they cover 60-90 days of card spend, not 30.

Always pull 6-month history of Chase CC payoffs. If the current 30d window contains a manual EPAY catch-up, also show the normalized 6-mo average as a second view so the P&L doesn't get distorted by timing.

Output format (Telegram-ready)

Always present TWO views:

View 1: Strict 30d cash basis — use raw Chase CC payoff. Subtract any flagged pass-through inflows (like LIBA) on both sides.

View 2: Normalized OPEX — replace Chase CC payoff with 6-mo avg.

Then show:

• What Ben actually did with the cash (Chase Checking draw, Wise balance growth, Mercury net change).
• Caveat: still can't see what's on the Chase card itself without Chase API/CSV access.

Account Reference

Stripe (env: STRIPE_ORG_KEY)

Mercury (env: MERCURY_RW_KEY)

Mercury IP whitelist: The Mercury API token is IP-whitelisted. If you hit ipNotWhitelisted, ask Ben to whitelist the IP shown in the error message. The Claude Code egress IP can rotate, so this may happen periodically.

Wise (env: WISE_API_KEY)

• Business profile: 73285247 (Cover Letter Copilot, LLC)

Chase

• No API access. Only visible via Chase web UI / CSV export. The Mercury → Chase CC payoff is the only proxy until Ben provides a Chase data source.

When NOT to use this skill

• Single-payment lookups ("did Bharat get paid?") — use Frankie's normal Wise/Mercury queries
• Per-creator analysis — that's Mia's creator_payouts table
• Future forecasting — this is backward-looking cash basis only
• Tax planning — this gives raw cash numbers, not accrual or tax-categorized