Partner Revenue & Stripe Reporting

This guide outlines how billing, credit top-ups, and partner revenue reporting operate across the platform and within Stripe.

1. Overview & Universal Metadata Tagging

All Stripe billing objects—including Customers, Checkout Sessions, Payment Intents, Subscriptions, and Invoices—are stamped with standardized metadata tags to enable direct partner-level revenue aggregation.

Metadata Schema

Metadata Key	Description	Example
`pid`	Partner Identifier code	`CAR`, `NST`, `SDL`, `TST`, `FRE`
`_id`	Account / User Client ID	`adlarsoco`, `befinchco`
`user_id`	Clean Client ID export tag	`adlarsoco`, `befinchco`
`ssv2_user_id`	Legacy Client ID tag	`adlarsoco`, `befinchco`
`domain`	Primary domain for purchase/subscription	`larsonjewelers.com`, `finch.com`

2. Generating Partner Revenue Reports in Stripe

You can extract partner revenue directly from Stripe without secondary databases.

A. Stripe Dashboard CSV Export

Log in to the Stripe Dashboard.
Navigate to Payments or Invoices in the left sidebar.
Apply the desired date range filter (e.g. Last Month or Custom Range).
Click Export in the top right corner.
In the exported CSV, you will find:
Customer Metadata: pid (or customer_metadata_pid)
Customer Metadata: user_id
Customer Metadata: domain
Amount & Status
Group by Customer Metadata: pid and sum the Amount column to calculate total revenue per partner.

B. Stripe Sigma SQL Query

If using Stripe Sigma, run the following SQL query to aggregate monthly revenue by Partner ID:

SELECT
  c.metadata['pid'] AS partner_id,
  DATE_TRUNC('month', in_table.created) AS revenue_month,
  COUNT(DISTINCT in_table.id) AS total_invoices,
  COUNT(DISTINCT c.id) AS active_paying_accounts,
  SUM(in_table.amount_paid) / 100.0 AS gross_revenue_usd
FROM invoices in_table
JOIN customers c ON in_table.customer = c.id
WHERE in_table.status = 'paid'
  AND in_table.created >= TIMESTAMP '2026-08-01 00:00:00'
  AND in_table.created < TIMESTAMP '2026-09-01 00:00:00'
GROUP BY 1, 2
ORDER BY gross_revenue_usd DESC;

C. Stripe Dashboard Search

Quickly find all transactions, subscriptions, or customers associated with a partner using metadata syntax:

metadata["pid"]:"CAR"

3. Account Provisioning & Synchronization

When accounts are created or updated via the Partner API or Admin UI: - Search-First Creation: The backend checks Stripe by email. If an existing customer is found, it binds to that record and patches any missing metadata (pid, _id, user_id, domain). - New Customer Creation: If no customer exists, a new Stripe customer is created with all metadata pre-stamped. - Continuous Sync: Updates to an account's partner association (pid) or attached domains automatically synchronize to Stripe in the background.

4. Administrative Backfill API

To synchronize or audit Stripe customer metadata across all accounts, use the administrative sync endpoint:

# Dry-run audit (non-destructive)
http POST https://beacon.socialsignal.ai/api/v2/core/admin/stripe/sync-metadata 'x-api-key:YOUR_ADMIN_KEY' apply:=false --ignore-stdin

# Live synchronization
http POST https://beacon.socialsignal.ai/api/v2/core/admin/stripe/sync-metadata 'x-api-key:YOUR_ADMIN_KEY' apply:=true --ignore-stdin