I’ve written some custom affiliate reports in ReportMojo - I’ve had to duplicate infusionsoft reports in my own custom reporting system as well. Orphaned and deleted records usually cause some discrepancies. For instance - I wrote an affiliate commissions report, and my commissions generated matched the affiliate ledger exactly, but there were cases where the OrderItem entry was deleted after commissions had been generated. So I had to notate that for people when appropriate.
For your question exactly - I duplicated the referral partner activity summary report exactly using the queries below. However; the “Sum Recurring” column - I could not duplicate it. I actually have no idea how that is being calculated.I tried calculating that number in several different ways, but could not match it. I came pretty close, but it wasn’t the same. But the Num Recurring, Num Orders, and Sum Orders were all duplicated pretty easily.
-- This matched "Num Recurring" exactly.
select
ro.affiliateid,
count(distinct ro.id) as num_recurring
from
recurringorder ro
where
ro.affiliateid > 0
group by
ro.affiliateid
order by
num_recurring desc
-- This matched "Num Orders" and "Sum Orders" exactly.
select
i.affiliateid,
a.affname,
count(distinct i.jobid) as num_orders,
sum(i.totalpaid) as sum_orders
from
invoice i
inner join invoiceitem ii on ii.invoiceid = i.id
inner join orderitem oi on oi.id = ii.orderitemid
inner join affiliate a on a.id = i.affiliateid
inner join job j on j.id = i.jobid
where
oi.subscriptionplanid = 0 and
i.affiliateid > 0 and
j.jobrecurringid = 0
group by
i.affiliateid,
a.affname
order by
num_orders desc