Recreation of an Infusionsoft "Referral Partner Activity Summary"

API Q&A
1

I’m presently trying to recreate some of the items seen in an Infusionsoft “Referral Partner Activity Summary” table. I’m including the Name, Num Orders, Num recurring and Sum recurring. Presently we have our own database tables that are synced with the data from Infusionsoft, plus I’ve been trying a few of the direct API calls to gather data and test as well. In either scenario, the data that I receive contains some discrepancies with respect to the data that I’m seeing in the Referral Partner Activity Summary - namely in the “Sum Orders” and “Sum recurring”. I’ve noted that my data agree pretty well with Referral Partner Activity Summary within the first few days of the current month. A few differences are noted here and there, but with the overall majority matching exactly. However those discrepancies increase when I use wider date ranges within the current month. If I go to , say, the previous month and look at these data using the same date range as that in Referral Partner Activity Summary, the differences can be huge.
Currently for the number/sum of recurring orders, I’m querying the RecurringOrder table , with StartDate being between (and including)the two ends of my date range. I then tally the resulting values of the subscriptions that have both an entry for a “LastBillDate” that are of “Active” status to arrive at the final value for that particular AffiliateId. For the number/sum of orders, I query the Invoice table for the specific AffiliateId using DateCreated being between(and including)the two ends of my date range. I then tally the resulting ‘TotalPaid’ data from those results which have “OrderForm” as Description to arrive at the final result.
As I said, I’ve done this process both with direct API calls as well as our tables which have been synced with the data from InfusionSoft. In both cases, I’ve not been able to get results that match exactly with those seen in Referral Partner Activity Summary. I was wondering if there’s a crucial step that I may be missing that’s preventing me from having our results match with those of Referral Partner Activity Summary on a consistent basis, irrespective of the date range and the month?

2

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