Narrator Documentation

Received Invoice

Customer was billed for product or services

Type: Activity Transformation

SELECT 
    i.invoice_id as activity_id
    , i.date as ts 
    
    , c.email_address as customer 
    
    , 'xero' as source 
    , c.contact_id as source_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')



Paid Invoice

Customer paid the full invoice amount

Type: Activity Transformation

SELECT 
    i.invoice_id as activity_id
    , i.fully_paid_on_date as ts 
    
    , c.email_address as customer 
    
    , 'xero' as source 
    , c.contact_id as source_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



Made Payment

Customer made a payment

Type: Activity Transformation

SELECT 
    p.payment_id as activity_id
    , p.date as ts 
    
    , c.email_address as customer 
    
    , 'xero' as source 
    , c.contact_id as source_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')



šŸ‘

Contribute to the Narrator Community

Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.




Have questions?


Our data team is here to help! Here are a couple ways to get in touch...

šŸ’¬ Chat us from within Narrator

šŸ’Œ Email us at [email protected]

šŸ—“ Or schedule a 15 minute meeting with our data team

Updated about a month ago


What's Next

Don't see what you're looking for? Browse the Activity Index for more examples...

Activity Index

Xero


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.