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!
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.
Step 1 – Set up ConvertKit
Create a new custom field called ‘affiliate’.
Step 2 – Set up ConvertBox
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 leaderboard 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.
‘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.
‘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).
‘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.
- 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.