Thread: Affiliate Tracking

Hi, I'm trying to implement affiliate tracking but there doesn't seem to be any good information in the manual. I basically need to just track how many clicks and orders are coming through from the affiliate. I have created a new affiliate (with ad tracking set to true) and added the affiliate ID to the URL.

Now, my question is where do I see the results? Is there a page that shows how many page views, orders, etc were based on the affiliate ID?


Thanks


MS 9.0.1.1

I don't think there is anything out of the box that shows reports. You could try searching for add ons such as this

http://ecommerce.compunix.us/p-43-ne...torefront.aspx they may work. But from memory there is nothing from scratch.

Though under View Manage Orders you can filter by affilaite.

Hi Skriver,

Thanks for the reply. Too bad there is nothing more detailed built in by default.

Here is an affiliate report that I wrote and put in Custom Reports. Perhaps it will help you.

Code:

WITH Names AS (
SELECT 
Customer.CustomerID
, Customer.LastName + ', ' + Customer.FirstName + ' (' + Customer.Email + ')' AS 'Customer'
FROM
Customer
)
SELECT
CASE WHEN (Affiliate.Company = LAG(Affiliate.Company, 1) OVER (ORDER BY Affiliate.Company) AND GROUPING(Affiliate.Name) < 1) THEN NULL ELSE CASE WHEN GROUPING(Affiliate.Name) < 1 THEN Affiliate.Company ELSE Affiliate.Company + ' subtotal:' END END AS 'Affiliate Company'
, CASE WHEN (Affiliate.Name = LAG(Affiliate.Name, 1) OVER (ORDER BY Affiliate.Company, Affiliate.Name) AND GROUPING(CustomerLevel.Name) < 1) THEN NULL ELSE CASE WHEN GROUPING(CustomerLevel.Name) < 1 THEN Affiliate.Name ELSE Affiliate.Name + ' subtotal:' END END AS 'Affiliate Name'
, CASE WHEN (CustomerLevel.Name = LAG(CustomerLevel.Name, 1) OVER (ORDER BY Affiliate.Company, Affiliate.Name, CustomerLevel.Name) AND GROUPING(Names.Customer) < 1) THEN NULL ELSE CASE WHEN GROUPING(Names.Customer) < 1 THEN CustomerLevel.Name ELSE CustomerLevel.Name + ' subtotal:' END END AS 'Customer Company'
, Names.Customer AS 'Customer'
, CASE GROUPING(Orders.OrderNumber) WHEN 1 THEN CASE WHEN GROUPING(Affiliate.Company) = 1 THEN 'GRAND TOTAL' ELSE 'subtotal' END ELSE CONVERT(VARCHAR,Orders.OrderNumber) END AS 'Order Number'
, '$' + CONVERT(VARCHAR,CONVERT(MONEY,SUM(Orders.OrderSubtotal)),1) AS 'Total Sales'
FROM            Orders INNER JOIN
                         Affiliate ON Orders.AffiliateID = Affiliate.AffiliateID INNER JOIN
                         Customer ON Orders.CustomerID = Customer.CustomerID INNER JOIN
                         CustomerLevel ON Customer.CustomerLevelID = CustomerLevel.CustomerLevelID INNER JOIN
						 Names ON Customer.CustomerID = Names.CustomerID
GROUP BY
Affiliate.Company
, Affiliate.Name
, CustomerLevel.Name
, Names.Customer
, Orders.OrderNumber
WITH ROLLUP
HAVING
GROUPING(Orders.OrderNumber) < 1
OR
GROUPING(Names.Customer) = 1
OR
GROUPING(Affiliate.Company) = 1
OR
GROUPING(Affiliate.Name) = 1
OR
GROUPING(CustomerLevel.Name) = 1
ORDER BY
CASE WHEN Affiliate.Company IS NULL THEN 1 ELSE 0 END
, Affiliate.Company
, CASE WHEN Affiliate.Name IS NULL THEN 1 ELSE 0 END
, Affiliate.Name
, CASE WHEN CustomerLevel.Name IS NULL THEN 1 ELSE 0 END
, CustomerLevel.Name
, CASE WHEN Names.Customer IS NULL THEN 1 ELSE 0 END
, Names.Customer