Your cart is currently empty!
How to filter the sum of commission per sold product by a specific affiliate?
—
by
My client has given me the order that he can see how much an affiliate receives in commission in a period of time. but now it gets complicated, filtered per product sold by the specific affiliate.
to show an example:
for the period of june, the affiliate with the id 4 has sold
product title | sum of commission
– product a | 150 €
– product b | 70 €
– product c | 1,5 €
– product d | 20 €
the goal is to see which product is sold the most by the affiliate id 4.
unfortunately, i have not found the setting. is there perhaps an add-on for this?
i tried to solve it with sql code, but unfortunately, it didn’t work. i think `wp_aff_referral_items` is not permanently inserted into the database.
you can try it out yourself:
select sum(referral_items.amount) as `sum of commission`, posts.post_title
from `wp_aff_referral_items` as referral_items
inner join `wp_posts` as posts on referral_items.object_id = posts.id
inner join `wp_aff_referrals` as referrals on referrals.referral_id = referral_items.referral_id
where posts.post_type = ‘product’
and (date(referrals.datetime) between ‘2022-06-01’ and ‘2022-06-30’)
and referrals.affiliate_id = 4
group by referral_items.object_id
thanks for the help
Comments
One response to “How to filter the sum of commission per sold product by a specific affiliate?”
Hi Benjamin,
Welcome to our support forum and many thanks for using our affiliate marketing tools on your site.
At the moment and with the current version of Affiliates plugin, the referral reports don’t extend in such depth regarding referred sales via WooCommerce. Even though your idea and use case are reasonable this can only be possible via a custom implementation.
One approach is the query you used, it seems to be correct in logic and the result is what you’re looking for. IMO, you should go for the query and present the results accordingly with a simple template. Also, you can extend it even further for the referrals that are missing referral items entries from the db table.
The reason why you noticed missing entries from the referral items table for some of your referrals can be perhaps related to using a commission method other than Rates or if you used Affiliates WooCommerce light version. For both cases referral items are not necessary and therefore skipped.
Kind regards,
George