There may be affiliate links in this post which means I may receive a commission if you purchase something through a link. However, please be assured that I only recommend products I have personally used and love!
Updated 28th September 2021 with a superior way of doing this if you have a WordPress website.
So I don't know about you, but one of my biggest frustrations with affiliate tools like ThriveCart and SamCart is that it doesn't allow me or my affiliates to track leads, only sales.
But I have now truly outdone myself with tech magic. ✨
I have successfully set up an auto-updating Google Sheet that says how many people have signed up for a specific freebie through each of my affiliates!
I thought this was pretty cool… but I had to check that this wasn't a widely known thing.
It seems not, so here we are with a tutorial!
Tech I Use
- ConvertKit – but this should work with any ESP that allows custom fields.
- ConvertBox – but I think it will work for any form builder that allows you to capture url parameters in a hidden field e.g. Typeform or Paperform. (Click here to see a full blog post declaring my love for ConvertBox)
- SamCart – but this will also definitely work for ThriveCart, and probably others.
Step 1 – Set up ConvertKit
Create a new custom field called ‘affiliate'.
Step 2a – Set up ConvertBox
If you use WordPress
- Install the WordPress plugin, Cookie Monster, on your site.
- If you use SamCart, put the following in the settings:
- If you use ThriveCart, put the following in the settings:
For this to work, all new subscribers need to go through a ConvertBox. This can be a popup form or an embedded form.
(You can use any type of form so long as you can capture a cookie value in a hidden field).
In the Form settings, you want to add a hidden field with the following settings:
For ‘Field name identifier‘ you want to put whatever you called the field that you created in Step 1.
For ‘Prefill field from a cookie value‘:
- If you use SamCart you want to put ‘sc_ref'
- If you use ThriveCart you want to put ‘affiliate'
This is a better way of doing it than the option in step 2b, because it doesn't require them to sign up straight away. The affiliate code is cookied to them for 30 days and will still be tracked when they sign up.
Step 2b – Set up ConvertBox
If you don't use WordPress
For this to work, all new subscribers need to go through a ConvertBox. This can be a popup form or an embedded form.
(You can use any type of form so long as you can capture url parameters in a hidden field).
In the Form settings, you want to add a hidden field with the following settings:
For ‘Field name identifier‘ you want to put whatever you called the field that you created in Step 1.
For ‘Prefill field from a custom URL parameter‘:
- If you use SamCart you want to put ‘sc_ref'
- If you use ThriveCart you want to put ‘affiliate'
Step 3 – Set up your Google Sheet
You need to set up a Google Sheet that has all your affiliate IDs in column A, and ‘0' in column B.
You should be able to get a list of your affiliates' IDs by exporting a full list of your affiliates from SamCart or ThriveCart.
If you want, you can sort your Google Sheet by the number in column B (to create a leader board of sorts).
It would also be possible to put affiliate names alongside affiliate IDs (this will be in the export), but you may choose not to for anonymity.
Step 4 – Connecting it all together with a Zap
Before you create the Zap, you want to add a subscriber to the form/tag you're going to be tracking, and put a random affiliate ID into the test subscriber's custom field that you created in Step 1. You need to do this so you've got some test data to set up the Zap.
1. Trigger
‘New Form Subscriber' or ‘New Tag Subscriber' (in ConvertKit).
You could also use ‘New Subscriber', but this will only track people who are brand new to your list, not those who were already on your list but who are signing up for something new through an affiliate link.
2. Action
‘Lookup Spreadsheet Row' in Google Sheets
For ‘Lookup Value‘, you want to select the custom field that contains the affiliate ID.
(You can leave all the other fields blank).
3. Action
‘Update Spreadsheet Row' in Google Sheets
For ‘Row‘, you want to select the Row that you ‘found' in Step 2 of the Zap (when you looked up the spreadsheet row).
For ‘No. of Leads', you want to put in the code like this ‘= [Whatever was in that row's column B cell]+1'. This acts like a formula to ‘+1' to whatever number of leads that affiliate currently has.
That's it! Now, every time someone signs up to your list through an affiliate link, the affiliate's ID will be tracked as a custom field in ConvertKit, and their no. of leads will be increased by 1 on the Google Spreadsheet.
You can share this Google Sheet with your affiliates (view only!), and give them instructions for how to find their affiliate ID.
Other Ideas
- Do this for specific launches and have a bit of a leader board with prizes.
- Add multiple columns to track number of subscribers for different freebies.
- Reset it every month (perhaps adding cumulative sign ups to a separate Sheet tab).
- Create private Google Sheets for your top affiliates which also includes the email addresses of the people who've signed up through their affiliate link.
Let me know how you get on in the comments!
If you'd like to learn more about setting up a money-making affiliate program, I can highly recommend anything and everything by Zoe Linda.
