GoAffPro Models

Copy-paste SQL models for turning raw GoAffPro data into business-ready affiliate and order reporting.

Copy and Paste these GoAffPro Models into your Data Layer to quickly go from raw data sources to business ready GoAffPro Reporting.

Please note:

Models must be added chronological order from top to bottom (so that they can reference each other).
When adding your own models you must update the reference to include your source names. For example, in the model templates below.

Base

Transforms Raw Data to Clean Tables focused on Core Concepts.

goaffpro_affiliates_base

select
    id::varchar as affiliate_id --pk
  , NULLIF(TRIM(name), '') as affiliate_name
  , LOWER(TRIM(first_name)) as first_name
  , LOWER(TRIM(last_name)) as last_name
  , LOWER(TRIM(email)) as email
  , LOWER(TRIM(ref_code)) as ref_code
  , LOWER(TRIM(status)) as status
  , group_id::varchar as group_id
  , tags as tags_json
  -- contact / social
  , NULLIF(TRIM(website), '') as website
  , LOWER(TRIM(instagram)) as instagram
  , LOWER(TRIM(state)) as state
  , LOWER(TRIM(country)) as country
  , LOWER(TRIM(payment_method)) as payment_method
  -- GoAffPro-reported lifetime totals (pass-through; recompute from orders where you need auditable numbers)
  , number_of_orders as goaffpro_number_of_orders
  , total_referral_earnings as goaffpro_total_referral_earnings
  , total_amount_paid as goaffpro_total_amount_paid
  , total_amount_pending as goaffpro_total_amount_pending
  , subtotal_revenue as goaffpro_subtotal_revenue
  -- EXAMPLE ONLY: account-specific custom fields — rename/replace or drop
  , NULLIF(TRIM(extra_1), '') as extra_1
  , NULLIF(TRIM(extra_2), '') as extra_2
  , NULLIF(TRIM(extra_3), '') as extra_3
  -- timestamps arrive as strings — cast, leave in UTC. NULLIF guards empty strings.
  , NULLIF(TRIM(approved_at), '')::timestamptz as approved_at
  , NULLIF(TRIM(blocked_at), '')::timestamptz as blocked_at
  , NULLIF(TRIM(last_login), '')::timestamptz as last_login_at
  , __updated_at as __updated_at -- EXAMPLE ONLY: your sync tool's last-synced timestamp
from {{sources.goaffpro.affiliates}}

goaffpro_orders_base

select
    id::varchar as order_id --pk
  , affiliate_id::varchar as affiliate_id
  -- EXAMPLE ONLY: strip leading '#' and a trailing brand suffix to normalize the order number.
  -- REPLACE 'UCAN' with your store's suffix, or remove this regexp if your numbers are clean.
  , NULLIF(TRIM(REGEXP_REPLACE(number, '^#|UCAN$', '', 'g')), '') as order_number
  , NULLIF(TRIM(number), '') as order_number_raw
  , LOWER(TRIM(status)) as status
  , LOWER(TRIM(type)) as order_type
  , NULLIF(TRIM(coupons), '') as coupons
  , LOWER(TRIM(customer_email)) as customer_email
  , (LOWER(TRIM(is_new_customer)) = 'true') as is_new_customer
  , total as order_total
  , subtotal as order_subtotal
  , commission as commission
  , mlm_amount as mlm_amount
  -- created arrives as a string — cast, leave in UTC
  , NULLIF(TRIM(created), '')::timestamptz as created_at
  , __updated_at as __updated_at -- EXAMPLE ONLY: your sync tool's last-synced timestamp
from {{sources.goaffpro.orders}}

Creates a “Fact” about a core concept. For GoAffPro the core concept is the affiliate: this fact rolls the affiliate's referred orders up into lifetime performance (order counts, revenue, commission, first/last referral) that gets joined downstream in output. Only approved orders count toward revenue/commission, matching how affiliate payouts actually work.

goaffpro_affiliate_order_facts

select
    a.affiliate_id as affiliate_id --pk
  , count(o.order_id) as n_orders
  , count(o.order_id) filter (where o.status = 'approved') as n_approved_orders
  , count(o.order_id) filter (where o.status = 'rejected') as n_rejected_orders
  , count(o.order_id) filter (where o.is_new_customer) as n_new_customer_orders
  , count(distinct o.customer_email) as n_distinct_customers
  -- revenue / commission on APPROVED orders only (what actually gets paid)
  , sum(o.order_total) filter (where o.status = 'approved') as approved_order_revenue
  , sum(o.order_subtotal) filter (where o.status = 'approved') as approved_order_subtotal
  , sum(o.commission) filter (where o.status = 'approved') as approved_commission
  , avg(o.order_total) filter (where o.status = 'approved') as avg_approved_order_value
  , min(o.created_at) as first_referral_at
  , max(o.created_at) as last_referral_at
from {{models.goaffpro_affiliates_base}} a
left join {{models.goaffpro_orders_base}} o on a.affiliate_id = o.affiliate_id
group by 1

Output

Final tables ready for use in explores in Switchboard. Thin, denormalized core-concept tables. goaffpro_affiliates_output is affiliate-grain (the primary reporting table); goaffpro_orders_output is order-grain with the affiliate's identity denormalized on for per-order drill-down.

goaffpro_affiliates_output

select
    a.affiliate_id as affiliate_id --pk
  , a.affiliate_name
  , a.email
  , a.ref_code
  , a.status
  , a.group_id
  , a.state
  , a.country
  , a.website
  , a.instagram
  , a.payment_method
  , a.approved_at
  , a.last_login_at
  -- recomputed-from-orders performance (auditable)
  , f.n_orders
  , f.n_approved_orders
  , f.n_rejected_orders
  , f.n_new_customer_orders
  , f.n_distinct_customers
  , f.approved_order_revenue
  , f.approved_order_subtotal
  , f.approved_commission
  , f.avg_approved_order_value
  , f.first_referral_at
  , f.last_referral_at
  -- GoAffPro-reported lifetime totals (pass-through, for reconciliation)
  , a.goaffpro_total_referral_earnings
  , a.goaffpro_total_amount_paid
  , a.goaffpro_total_amount_pending
from {{models.goaffpro_affiliates_base}} a
left join {{models.goaffpro_affiliate_order_facts}} f on a.affiliate_id = f.affiliate_id

goaffpro_orders_output

select
    o.order_id as order_id --pk
  , o.order_number
  , o.created_at
  , o.status
  , o.order_type
  , o.is_new_customer
  , o.customer_email
  , o.coupons
  , o.order_total
  , o.order_subtotal
  , o.commission
  -- affiliate identity denormalized on for drill-down
  , o.affiliate_id
  , a.affiliate_name as affiliate_name
  , a.email as affiliate_email
  , a.ref_code as affiliate_ref_code
  , a.status as affiliate_status
from {{models.goaffpro_orders_base}} o
left join {{models.goaffpro_affiliates_base}} a on o.affiliate_id = a.affiliate_id

GoAffPro Models

On this page