SELECT 
    i.invoice_id as activity_id
    , i.date as ts 
    
    , c.email_address as customer 
    
    , c.contact_id as anonymous_customer_id 
    
    , "received_invoice" as activity 

    , i.due_date as feature_1
    , i.type as feature_2
    , i.line_amount_types as feature_3
    
    , CAST(i.total * COALESCE(currency_rate,1.0) as FLOAT64) as revenue_impact 
    , i.url   as link 

FROM xero.invoice i
JOIN xero.contact c     
    on (i.contact_id = c.contact_id )
WHERE i.status not in ('DELETED', 'VOIDED')

SELECT 
    i.invoice_id as activity_id
    , i.fully_paid_on_date as ts 
    
    , c.email_address as customer 

    , c.contact_id as anonymous_customer_id 
    
    , "fully_paid_invoice" as activity 

    , i.due_date as feature_1
    , i.type as feature_2
    , i.line_amount_types as feature_3
    
    , CAST( i.amount_paid * COALESCE(currency_rate, 1.0) AS FLOAT64) as revenue_impact 
    , i.url   as link 

FROM xero.invoice i
JOIN xero.contact c     
    on (i.contact_id = c.contact_id )
WHERE i.status in ('PAID')
    and i.fully_paid_on_date is not null

SELECT 
    p.payment_id as activity_id
    , p.date as ts 
    
    , c.email_address as customer 

    , c.contact_id as anonymous_customer_id 
    
    , "made_payment" as activity 

    , p.payment_type as feature_1
    , NULL as feature_2
    , NULL as feature_3
    
    , CAST(p.amount* COALESCE(p.currency_rate, 1.0) as FLOAT64) as revenue_impact 
    , i.url   as link 

FROM xero.payment p
JOIN xero.invoice i 
    on (i.invoice_id = p.invoice_id)
JOIN xero.contact c     
    on (i.contact_id = c.contact_id )
 
where p.status not in ('DELETED', 'VOIDED')