Description
Summary To build an affiliate tracking system for my existing Svelte E-commerce Website, connected and verified with the Database Budget: $170 (Fixed Price) There are two Database sheets involved in this project: • Database 1: Affiliate Applications - master affiliate profile sheet storing all affiliate profiles, settings and running totals. • Database 2: Affiliate Sales Orders - records all individual sales orders made via affiliate links Step 1 - Affiliate Link & Verification When a user visits the website via an affiliate link: Example: https://shop.mydomainname.com?refid=affiliateID (Domain link will be sent once the job is accepted.) The affiliateID will be verified against the Database Affiliate Applications sheet to check if it is a valid and active affiliate. 1. ✅ Valid affiliate → fire an affiliate cookie (minimum 1 day, maximum 30 days) A valid affiliate will carry the affiliateID and pass it through when a sales order is successfully purchased, which will be used in the next step for setting up the Affiliate Sales Orders record. 2. ❌ Invalid affiliate → redirect back to shop.mydomainname.com If the verification returns any empty, invalid or error data from the sheet, the user will be redirected back to the homepage at shop.mydomainname.com. Any errors within the affiliate function must not affect the live website in any way. Affiliate Applications Sheet (Database 1) will be sent once the job is accepted. Step 2 - Affiliate Cookie: The affiliate function runs entirely in the backend, no changes to the frontend UI for shop page. Cookie tracks the affiliate visit throughout the session (minimum 1 day, maximum 30 days) Step 3 - Order Record Duplication (Affiliate Sales Orders): When a purchase order is made via an affiliate link and the payment status is SUCCESS, the order record should be duplicated from the existing Online Order database with filtered data/columns only to Database 2 - Affiliate Sales Orders, tagged with the affiliateID and relevant data and commission rate from that affiliate link. If the order is not made via an affiliate link, the Affiliate Sales Orders sheet should not duplicate any order records. Affiliate Sales Orders Sheet (Database 2) will be sent once the job is accepted. Step 4 - Email Notification: After the order record is successfully duplicated into the Affiliate Sales Orders sheet, an email notification should be sent to the affiliate's registered email (retrieved from the Affiliate Applications sheet) via Resend. The email should be sent as a Congratulations - Sale Notification with the following details: 1. Order Summary — A customer purchased: [order summary] 2. Total Sales Amount — the order total for that purchase 3. Coupon Used — the coupon discount amount applied. If none, display "None" 4.. Delivery Fee — the delivery fee amount applied. If none, display "None" 5. Commission Earned — the calculated commission amount earned by the affiliate 6. Billplz Payment ID — the payment reference ID from Billplz 7. View affiliate payout summary at: https://affiliatehub.mydomainname.com/[affiliateID] Step 5 - Retrieve & Display Affiliate Payout Information: Retrieve data from Database 2 - Affiliate Sales Orders and display it on a unique affiliate page for each individual affiliate: https://affiliatehub.mydomainname.com/[affiliateID] Each affiliate can access their own unique page to view: 1. Total Sales 2. Order summary 3. Payout amount 4. Payout status 5. Payment date 6. And other relevant information At the bottom of the table, the Total Commission Earned will be displayed, calculated from all records in the Affiliate Sales Orders sheet. The Payout Status from the Database will be used to determine the outstanding balance, if the status is "Paid", the paid amount will be moved from the pending to approved column in Database 1 to show the status, payout and remaining balance to affiliate on the individual affiliate dashboard page: https://affiliatehub.mydomainname.com/[affiliateID] Auto-sync between Database 1 & Database 2: The auto-sync between Database 1 (Affiliate Applications) and Database 2 (Affiliate Sales Orders) will be triggered on every page load of the affiliate dashboard page: https://affiliatehub.mydomainname.com/[affiliateID] When the page loads, the system will: 1. Retrieve all records from Database 2 for the matching affiliateID 2. Recalculate and update the Total Payout (RM) Approved, Pending column in Database 1 3. Reflect the latest payout status, commission totals and remaining balance on the affiliate dashboard page This ensures the affiliate always sees the most up-to-date data every time they visit their dashboard. Page visibility: 1. Add (meta name="robots", content="noindex, nofollow") to hide the affiliate dashboard page from Google search results 2. Add Disallow: affiliatehub /affiliateID/ to robots.txt to block the entire /affiliateID/ page directory from search engine crawling Click Tracking - displayed on the Affiliate Dashboard page: Every time a unique user clicks an affiliate link, the click should be recorded server-side using a 24-hour cookie to track each user as only 1 unique click.