Invoices: Trace Inbound to Outbound Invoice Email

Description: This query has many different functions based on what you are looking for. It can:

• serve as a report for firms who want to know which clients have not clicked which invoices
• serve as a trace for a given email as it flows inbound and then flows outbound

Note: this query starts from outbound_email, so this query is designed to only show emails that have flowed outbound from coming inbound. Also, the date range of this invoice searched for the entire past month for any invoice we received in that time frame).

Query:

SELECT
    concat(s.first_name, ' ', s.last_name) as "Staff",
    s.email as "Staff Email",
    c.name as "Client Name",
    c.key as "Client Key",
    i.invoice_number,
    action_def_instance_json ->> 'to' as "Recipient Email",
    adi.id,
    ieat.created_at as "Received Inbound Date",
    a.amount as "Current Invoice Amount",
    a.allocated "Current Invoice Paid Amount",
    delivered_dt "Delivered Timestamp",
    opened_dt "Opened Timestamp",
    clicked_dt "Clicked Timestamp"
FROM outbound_email oe
    inner join action_def_instance adi on oe.action_def_instance_id = adi.id
    inner join action_def_group_instance adgi on adi.action_def_group_instance_id = adgi.id and adgi.action_def_group_id = '1'
    inner join action_def_group_instance_ar_tran adgiat on adgi.id = adgiat.action_def_group_instance_id
    inner join ar_tran a on a.id = adgiat.ar_tran_id
    inner join client c on c.id = a.client_id
    inner join staff s on c.partner_staff_id = s.id
    inner join office o on o.id = c.office_id
    inner join invoice i on i.ar_tran_id = adgiat.ar_tran_id
    inner join ingested_email_ar_tran ieat on a.id = ieat.ar_tran_id
WHERE
--ieat.created_at::date BETWEEN '2022-09-06' and NOW() -- search from a certain date on
--date_part('month', ieat.created_at) in ('9','10') -- search for a certain few months
date_trunc('week',ieat.created_at) = date_trunc('week', current_date - interval '1 week')
and date_trunc('week',adgiat.created_at) = date_trunc('week', current_date - interval '1 week')