Skip to content

Lumanu Creator Payments

Lumanu is the platform Marketing uses to pay creators and influencers and to handle their tax compliance. The data lands in RAW_DB.LUMANU, loaded nightly by a Retool Workflow that rewrites every table in full on each run.

Start from PAYABLE — one row per payment obligation to a creator. It is the spend record. The other tables support it: PARTNER is the creator roster, PROJECT groups payables, CUSTOM_FIELD_POLICY defines Marketing's custom fields, WALLET_TRANSACTION is the underlying ledger, and FUNDING is money coming in rather than going out.

Three ways to get creator spend wrong

Every money column is an integer in minor units. AMOUNT is cents, paired with AMOUNT_DENOMINATION reading us_cents. Divide by 100. Forgetting this is wrong by a factor of a hundred, and nothing in the output looks unusual.

will_pay does not mean paid. PAYABLE carries two status columns. STATUS is the coarse commitment state and PAYABLE_STATUS the finer operational one. A payable at STATUS = 'will_pay' pairs with PAYABLE_STATUS = 'awaiting_payee' — Tin Can has committed the money and the creator has not received it. Filtering on both paid and will_pay reports money that has not left the account.

FUNDING.AMOUNT must never be summed across methods. That table mixes two structurally different records: method = 'invoice' is real money transferred into the Lumanu wallet, while method = 'balance' is an internal earmark of funds already held, where nothing moves. Summing both double-counts the same dollars. Creator spend never comes from FUNDING.

Creator spend actually paid

select sum(amount) / 100 as creator_spend_usd
from raw_db.lumanu.payable
where status = 'paid';

True cost, including Lumanu's fee

Lumanu charges a platform fee that appears only on FUNDING.FEE_AMOUNT — nowhere else in the schema, and not on the payables themselves. Creator spend alone therefore understates what Tin Can actually pays.

with paid as (
    select sum(amount) / 100 as creator_spend_usd
    from raw_db.lumanu.payable
    where status = 'paid'
),
fees as (
    select coalesce(sum(fee_amount), 0) / 100 as lumanu_fees_usd
    from raw_db.lumanu.funding
    where method = 'invoice'
      and status = 'funded'
)
select
    paid.creator_spend_usd,
    fees.lumanu_fees_usd,
    paid.creator_spend_usd + fees.lumanu_fees_usd as true_cost_usd
from paid, fees;

Both filters matter, for different reasons. balance records carry no fee, so including them adds nothing but invites the double-count described above. status = 'funded' matters because an invoice at opened has been raised but not yet settled — that fee is money Tin Can has not spent, and the creator payments behind it have not gone out either. Leaving the filter off charges a fee against a cost that has not been incurred, and the overstatement is small enough to look plausible.

Spend by creator

Join on PAYEE_LUMANU_ID, never on a name. PAYABLE.CUSTOM_FIELDS:creator_name is free text typed by whoever created the payment and disagrees with PARTNER.NAME on a meaningful share of rows. PAYEE_LUMANU_ID is Lumanu's stable identifier and resolves cleanly.

select
    pt.lumanu_id,
    pt.name as creator,
    count(*) as payables,
    sum(p.amount) / 100 as spend_usd
from raw_db.lumanu.payable p
join raw_db.lumanu.partner pt
    on p.payee_lumanu_id = pt.lumanu_id
where p.status = 'paid'
group by 1, 2
order by spend_usd desc;

PARTNER.NAME and PARTNER.EMAIL are third-party PII, as are PAYABLE.VENDOR_DISPLAY_NAME and PAYABLE.VENDOR_EMAIL. Treat them accordingly.

Spend by campaign — read this before reporting the numbers

Campaign is a free-text custom field with no validation in Lumanu and no cleansing layer in the warehouse. The same campaign appears under multiple spellings, and this will not be fixed upstream: the field is typed by hand every time a payment is created.

The consequence is that a naive GROUP BY splits one campaign across several labels and understates each of them, with nothing in the output indicating the split.

select
    p.custom_fields:campaign_name::string as campaign_label,
    count(*) as payables,
    sum(p.amount) / 100 as spend_usd
from raw_db.lumanu.payable p
where p.status = 'paid'
group by 1
order by spend_usd desc;

Always eyeball the full label list before reporting campaign totals. Look for near-duplicates — misspellings, a year suffix present on some rows and absent on others, inconsistent casing or punctuation. Reconcile them by hand, and say which labels you merged. Do not present campaign totals as authoritative without that step.

Note this affects the campaign field specifically. Creator identity is safe, because it comes from the joined PARTNER record rather than the typed-in name.

Linking creative cost to Meta media spend

Ads built from paid creator content carry the Lumanu invoice number as a utm_lpid URL parameter, added by Marketing during ad setup. That is the join between what a creator was paid and what was spent running their content — and it is the only reliable one. Campaign name will not do this job, for the reasons above.

The parameter lives in RAW_DB.FACEBOOK_ADS.AD_CREATIVES.URL_TAGS, and the path to spend runs creative to ad to daily performance.

with lpid_creatives as (
    select
        c.id as creative_id,
        regexp_substr(lower(c.url_tags), 'utm_lpid=([^&]*)', 1, 1, 'e', 1)
            as lumanu_invoice_number
    from raw_db.facebook_ads.ad_creatives c
    where lower(c.url_tags) like '%utm_lpid=%'
),
meta_spend as (
    select
        lc.lumanu_invoice_number,
        count(distinct a.id)                as meta_ads,
        sum(cap.spend::decimal(12,2))       as meta_spend_usd
    from lpid_creatives lc
    join raw_db.facebook_ads.ads a
        on a.creative:id::string = lc.creative_id
    join raw_db.facebook_ads.custom_ad_performance cap
        on cap.ad_id::string = a.id
    group by 1
)
select
    p.invoice_number,
    p.custom_fields:campaign_name::string          as lumanu_campaign,
    p.amount / 100                                 as creator_cost_usd,
    ms.meta_ads,
    ms.meta_spend_usd,
    p.amount / 100 + ms.meta_spend_usd             as total_campaign_cost_usd
from raw_db.lumanu.payable p
join meta_spend ms
    on ms.lumanu_invoice_number = p.invoice_number::string
where p.status = 'paid'
order by total_campaign_cost_usd desc;

One payable becomes many ads. A single creative purchase runs as several ads across several Meta campaigns. Never attach creator cost to an ad-level row — it would be counted once per ad. Aggregate Meta spend up to the invoice first, as above.

Creator cost does not divide by Meta campaign. That fan-out crosses campaign boundaries — the same utm_lpid appears on ads in several Meta campaigns at once — so a per-Meta-campaign creator cost figure overlaps with every other campaign running the same creative. Ask "what creator cost is behind Meta campaign X" for each campaign in turn and the answers sum to far more than was ever paid. Check the overlap before reporting any per-campaign number:

with lpid_ads as (
    select
        regexp_substr(lower(c.url_tags), 'utm_lpid=([^&]*)', 1, 1, 'e', 1)
            as lumanu_invoice_number,
        a.campaign_id
    from raw_db.facebook_ads.ad_creatives c
    join raw_db.facebook_ads.ads a
        on a.creative:id::string = c.id
    where lower(c.url_tags) like '%utm_lpid=%'
)
select
    lumanu_invoice_number,
    count(distinct campaign_id) as meta_campaigns_running_it
from lpid_ads
group by 1
order by 2 desc;

Where that count exceeds one, the invoice's creator cost belongs to no single Meta campaign. Report it at the invoice level, or state an explicit allocation rule — share of media spend is the usual one — and label the output an allocation rather than a cost. This is not a tagging fault to be fixed: Tin Can buys usage rights precisely so that one piece of creator content can run wherever it performs, and the tag stays correct wherever the ad ends up.

Grouping by the Lumanu campaign label is additive; grouping by the Meta campaign is not. A question about "the Back to School campaign" is answerable as a total. The same question about a named Meta campaign is an allocation. Say which one you have answered.

Coverage is partial by nature. utm_lpid is added by a person following the ad-setup process, so some payables are linked and others are not. Measure coverage before presenting a combined figure as complete, and say which creator payments are excluded.

The ratio is usually the interesting number. Creative cost and the media spend put behind it vary independently — a modest creative can carry many times its cost in media, and an expensive one can receive very little. Reporting only the combined total hides that.

The same parameter reaches GA4. Because utm_lpid sits on the landing URL, it appears in GA4 page locations whenever someone clicks, which connects creator cost through to sessions and orders. That makes return per creative answerable, not just cost — but note it only records clicks, so it can never account for media spend on impressions that produced none.

CUSTOM_FIELDS:usage_duration_days records how long Tin Can may use the creator's content. It is a dropdown, but the values are bare numbers with the unit only in the field's label, plus one non-numeric perpetual-rights option.

select
    p.custom_fields:usage_duration_days::string as duration_label,
    try_cast(p.custom_fields:usage_duration_days::string as number) as duration_days,
    count(*) as payables
from raw_db.lumanu.payable p
group by 1, 2
order by 2 nulls last;

Use try_cast, not cast — a plain cast breaks on the perpetual value. And be careful with avg() over the numeric form: it silently drops exactly the perpetual-rights rows, which are the ones most worth knowing about.

CUSTOM_FIELD_POLICY.DROPDOWN_OPTIONS holds the authoritative option list, including options no payable has used yet. Prefer it over selecting distinct values from the payables.

Reconciliation check

WALLET_TRANSACTION is the underlying ledger. Payments out of the wallet should tie to paid payables, which makes a quick integrity check:

select
    (select sum(amount) / 100 from raw_db.lumanu.payable
      where status = 'paid') as paid_payables_usd,
    (select sum(amount) / 100 from raw_db.lumanu.wallet_transaction
      where type = 'payment') as wallet_payments_usd;

A mismatch means either a payable changed state between loads or something is wrong with the pipeline. Use WALLET_TRANSACTION.BALANCE_CHANGE rather than AMOUNT when direction matters — it is signed.

Custom fields are dynamic

PAYABLE.CUSTOM_FIELDS is keyed by a normalized snake_case slug of each field's label. Marketing can add, rename or remove these fields at any time, and the loader rebuilds the map from whatever Lumanu returns rather than from a fixed list. Query CUSTOM_FIELD_POLICY for the current set of fields, their types and their dropdown options rather than assuming today's keys.

Because each run fully overwrites the tables, a renamed field is restated across all history and leaves no record of its former label.

Attributing funding and fees to a campaign

FUNDING carries no payable ids, so there is no key to join it to PAYABLE. The link is still recoverable, because invoice-method funding records describe what they cover in DESCRIPTION and their base amounts tie out to groups of paid payables.

Read the amounts carefully. AMOUNT on an invoice record is the gross transfer and includes Lumanu's fee. The figure that reconciles against payables is BASE_AMOUNT. Use that column rather than subtracting the fee yourself: IS_FEE_ADDITIVE decides whether the fee sits on top of the base or comes out of it, and BASE_AMOUNT is already correct under either.

select
    description,
    base_amount / 100 as base_usd,
    fee_amount / 100  as fee_usd
from raw_db.lumanu.funding
where method = 'invoice'
  and status = 'funded'
order by description;

Then compare each base against paid payables grouped by campaign:

select
    custom_fields:campaign_name::string as campaign_label,
    sum(amount) / 100                   as paid_usd
from raw_db.lumanu.payable
where status = 'paid'
group by 1
order by 1;

Each base equals the sum of one or more of those campaign groups, and total funded base equals total paid creator spend.

Two limits on how far this carries. A single funding record can cover several campaigns at once, which makes it a funding batch rather than a per-campaign record — splitting it back out is an inference from the amounts, not something the data states. And campaign labels are free text that contains misspellings, so a group that looks short may simply be spelled two ways; read the campaign-name warning above before concluding that a reconciliation failed.

Allocating a fee to a campaign therefore means apportioning a batch fee across the campaigns its base covers. That is a defensible estimate rather than a recorded fact, and it should be reported as one.

What this data cannot answer

  • Which payables a funding record covers, directly. The API returns no payable ids on a funding record, so there is nothing to join on. The link is recoverable by reconciling amounts, as described above, but that is arithmetic rather than a join and it is not guaranteed to resolve cleanly.
  • An audit trail. Every run is a full snapshot, so these tables reflect Lumanu as of the last load. Historical values are restated, not preserved.
  • Per-campaign spend with certainty, without the manual label reconciliation described above.