Import past customers and revenue

What gets imported

Two files. contacts.csv has one row per customer: signup date, email, name, account and first-touch attribution. revenue.csv has one row per payment or refund. Import the contacts first: every revenue row needs its customer.

• Imported data appears at its true dates: signups per day, customers, paying customers, LTV, revenue over time and the Attribution report.
• Past behavioral events are not imported, and an import records none: no $signup, $revenue or $identify events, no active-user days (DAU, WAU and MAU do not move), no campaign flow enrollment, no goal conversions and no operator notifications.
• Importing the same files again changes nothing. Customers are matched by userId and payments by kind plus reference, also against the revenue your backend already sends live, so nothing is counted twice.
• Only owners and admins of the organization can import. The importer lists every import with who ran it, when, the file and its counts.

File format

A row with fewer values than the header, or with extra values that are not empty, is rejected as malformed_row (usually an unquoted comma or line break). Extra empty values, such as a trailing comma, are ignored.

contacts.csv

One row per customer. Required columns: userId and signedUpAt.

Fill the attribution columns only with what you stored when the customer signed up (the first touch: the UTM parameters, click ids, referrer and landing page of their first visit). Values longer than the limit are cut. Leave every attribution column empty when you never stored it; never guess.

revenue.csv

One row per payment or refund that actually moved money. Required columns: userId, amount, currency, reference and occurredAt.

• Export only payments that succeeded: paid invoices, captured charges. Skip drafts, open, void and uncollectible invoices and failed charges.
• Invoices of zero can be left out: a customer becomes paying only with a payment above zero.
• One row per refund, with the refunded amount (positive) and kind refund. A partial refund is a refund of the partial amount.
• Revenue belongs to a person: map an organization's invoices to the user who pays for it (its owner or billing contact), and include that user in contacts.csv.
• For a payment your backend already sent live with revenue(), use the same reference, amount and customer it sent. A different amount or customer is reported as reference_conflict, the live record is kept and nothing is counted twice.

Dates and times

signedUpAt, occurredAt and attributionCapturedAt are instants with a time zone. Write them in UTC with a Z.

Dates must fall between the years 1900 and 9999 and be at most 5 minutes after the import's clock. Daily charts group by UTC day.

Amounts and currency

amount is an integer in the currency's minor unit (cents), the unit Stripe and revenue() of @ravenkeep/node use. Digits only: no decimal point, sign, currency symbol or thousands separator. Nothing is rounded: a value with decimals is rejected.

currency must be the project's currency (shown in the importer). A row in another currency is rejected with currency_mismatch: Ravenkeep does not convert. Set the project currency before the first revenue import, because it cannot change once the project has revenue.

A payment adds its amount to the customer's lifetime value (LTV) and a refund subtracts it. A customer becomes paying at their earliest payment above zero.

Idempotency and merge rules

Import as often as you like: an import never creates a second customer and never counts a payment twice. Run the same files again after fixing rejected rows; finished rows come back as unchanged.

When the customer already exists

A customer your live integration already sent, or an earlier import created, is completed, never overwritten:

An imported customer counts as signed up: a later signup() from your backend for the same userId does not enroll them in signup flows again. Live events after the import trigger flows as usual.

Mapping a typical SaaS database

Read from a replica or with a read-only user: the export never writes to the source. The usual sources:

• Leave out internal, staff and test accounts, and payments of other products that share the billing account (filter them by their metadata).
• Keep inactive users that were real customers: their history is part of your signups and revenue. If your product deletes users for good, their history is gone: export what remains (their organization's payments go to its current payer).
• Leaving someone out of the export does not remove them from Ravenkeep: customers your live integration already sent stay.
• Sort the output (contacts by signedUpAt then userId, revenue by occurredAt then reference) so two runs write the same files.

-- psql: \copy (<this query on one line>) TO 'contacts.csv' WITH (FORMAT csv, HEADER)
SELECT u.id AS "userId",
       u.email AS "email",
       u.name AS "name",
       to_char(u.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS "signedUpAt",
       o.id AS "accountId",
       o.name AS "accountName"
FROM users u
LEFT JOIN LATERAL (
  SELECT org.id, org.name
  FROM memberships m JOIN organizations org ON org.id = m.organization_id
  WHERE m.user_id = u.id
  -- the organization the user last used, else the earliest membership
  ORDER BY (m.organization_id = u.last_organization_id) DESC, m.created_at, org.id
  LIMIT 1
) o ON true
ORDER BY u.created_at, u.id;

SELECT payer.user_id AS "userId",
       i.total AS "amount", -- the amount your live revenue() call sends
       upper(i.currency) AS "currency",
       'payment' AS "kind",
       i.stripe_invoice_id AS "reference",
       to_char(i.paid_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS "occurredAt"
FROM invoices i
JOIN LATERAL (
  -- the organization's earliest owner pays
  SELECT m.user_id FROM memberships m
  WHERE m.organization_id = i.organization_id AND m.role = 'owner'
  ORDER BY m.created_at, m.user_id
  LIMIT 1
) payer ON true
WHERE i.status = 'paid' AND i.total > 0
ORDER BY i.paid_at, i.stripe_invoice_id;

Example files

userId,email,name,signedUpAt,accountId,accountName,utmSource,utmMedium,utmCampaign
u_1001,ana@example.com,Ana Souza,2024-11-02T14:05:31Z,org_77,Acme Ltda,google,cpc,brand
u_1002,bruno@example.com,"Bruno, Jr.",2025-01-15T09:00:00Z,org_77,Acme Ltda,,,
u_1003,,Carla Dias,2025-02-20T22:47:10.250Z,org_81,"Studio ""Norte""",newsletter,email,feb-launch

userId,amount,currency,kind,reference,occurredAt
u_1001,1999,USD,payment,in_1QaB2cD3eF4gH5iJ,2024-11-02T14:07:02Z
u_1001,1999,USD,payment,in_1QkL6mN7oP8qR9sT,2024-12-02T14:07:11Z
u_1003,4990,USD,payment,in_1QuV0wX1yZ2aB3cD,2025-02-20T22:50:00Z
u_1003,4990,USD,refund,re_3QeF4gH5iJ6kL7mN,2025-02-27T10:12:45Z

Row errors

The dry run and the import report every rejected row with its line, code, column and message, and the console downloads them as a CSV to fix. The other rows of the file are imported.

Running the import

• Open the importer: Settings > Projects > your project > Import (owners and admins).
• Add the files: contacts.csv and, optionally, revenue.csv. The console checks the columns and every row in the browser.
• Dry run: counts the rows that would be created, updated, unchanged or rejected. Nothing is written.
• Import: rows go in batches of up to 1,000 rows (and about 1 MiB), contacts before revenue. Keep the tab open. If it stops (a closed tab, a network failure), import the same file again: finished rows come back unchanged.
• Fix rejected rows: download them, correct them and import that file.

Checklist for an export script

• Read only: never write to the source database or billing account.
• Write two UTF-8 files with a header row and the column names of this page, comma delimited, quoted by a CSV library.
• userId and reference are the values your live integration sends.
• Date-times in UTC with Z; amounts as integers in minor units; currency uppercase and equal to the project currency.
• One row per userId in contacts.csv; one row per payment or refund in revenue.csv; every revenue userId present in contacts.csv (or already known to Ravenkeep).
• Leave out internal and test accounts; leave attribution columns empty unless you stored them.
• Sort the rows so the output is deterministic.
• The files hold personal data: write them outside version control and delete them after the import.
• Print what was written and what was skipped (and why), so a person can compare it with the dry run.