How to Create an Affiliate Marketing Earnings Tracker Using Formulas in Google Sheets

If you’re into affiliate marketing, tracking your earnings is key to understanding your progress and making smart decisions. Google Sheets offers a flexible and free way to keep all of this important information in one place.

What Is Affiliate Marketing?

Affiliate marketing is a win-win advertising strategy where businesses reward individuals, known as affiliates, for driving customers their way through creative marketing efforts. Think of it as a partnership: companies get to widen their reach and boost sales, while affiliates earn commissions for every sale or action—like clicks or form submissions—that comes from their referrals.

This setup helps merchants gain valuable exposure and allows affiliates to monetize their passion for promoting products or services. It’s an appealing avenue for anyone looking to generate passive income or even make it a primary source of revenue, especially for bloggers, content creators, and influencers who thrive on sharing what they love.

Step 1: Set Up Your Sheet

Start by opening a new Google Sheet and label the columns with the details you want to track. Common column headers might include:

• Date of sale
• Affiliate partner name
• Product sold
• Generated Sales (in $)
• Commission Rate (%)
• Earnings (in $)
• Payment status (Paid/Unpaid)
• Notes

This layout provides a clear view of each transaction, including who made the sale, how much was earned, and whether payment has been received.

Step 2: Enter Basic Data

Fill in the basic data such as the date, affiliate partner, commission rate, and generated sales. These are essential details to keep track of which products are generating the most revenue through your affiliate partnerships.

Step 3: Calculate Total Commissions

Now, let’s add a formula to automatically calculate earnings based on the commission rate. In column E (Commission rate), input the agreed commission percentage (e.g., 10% or 0.10).

In column F (Earnings), enter the formula to calculate commission based on the sale amount and the commission rate:

=D2*E2

Drag this formula down for each row to ensure it calculates earnings for every sale.

Step 4: Track Your Total Earnings Over Time

To track your total earnings across all affiliates and products, you can use the SUM function at the bottom of the Earnings column. In the cell below the last earnings figure, type:

=SUM(F5:F25)

This will give you the total revenue generated from all affiliate sales.

Step 5: Add a Dropdown Menu

You can use the Dropdown menu feature of Google Sheets to easily manage recurring information, such as Product Sold and Payment Status, without manually inputting the values.

To do this, select the cells where you want the dropdown. Go to Data > Data validation. Under Criteria, select “Dropdown” and type in your options.

This feature helps you monitor your financial obligations, ensuring you stay on top of payments to your affiliate partners.

Step 6: Track Monthly Earnings

To see your earnings every month, you can set up a separate section or sheet where monthly totals are displayed. Use the SUMIF function to pull in data based on the month:

In a new sheet, list months in one column (e.g., January, February). In the next column, calculate the total earnings for each month using:

=SUMIF(Summary!A5:A25, “>=”&DATE(2024,8,1), Summary!F5:F25) – SUMIF(Summary!A5:A25, “>”&DATE(2024,8,31), Summary!F5:F25)

Adjust the dates according to the month you’re calculating for. This formula will clearly show how much your business earned through affiliate sales each month.

You can also add a product sales summary to your first spreadsheet to track the total product-generated sales. Then, use the formula:

=SUMIF(C16:C36,”Product 1″,D16:D36)

Step 7: Visualize Your Earnings with Charts (Optional)

To better understand your earnings trends, create charts to visualize your data. This can help you spot patterns, such as which months bring in the most revenue or which products perform best.

Highlight the Date and Earnings columns. Go to Insert > Chart. Choose the chart type that best suits your needs, such as a line chart for tracking earnings over time.

Get the Free Affiliate Marketing Earnings Tracker

Get a copy of the free Affiliate Marketing Earnings Tracker. I’ve already populated some cells as examples, but you can customize them as needed.

Final Thoughts

Using Google Sheets to track my affiliate marketing earnings has been a game-changer for me. With just a few simple formulas, I’ve set up a dynamic system that saves me time and gives me valuable insights into how my campaigns are performing. As my earnings grow, I can easily tweak and scale my tracker to meet my needs, making it super flexible and efficient.

Frequently Asked Questions

Can I calculate total earnings for a specific period?

Yes, to calculate earnings for a period, say a specific month, you could use =SUMIFS(E2:E, A2:A, “>=start_date”, A2:A, “<=end_date”), replacing start_date and end_date with the actual dates in quotation marks.

What if I want to track the earnings from a particular affiliate partner?

You can use the SUMIF function. For example, if you have the partner’s name in column F and you want to sum the earnings, you can use =SUMIF(F2:F, “Partner Name”, E2:E).

Is there a way to see my highest earning sale?

Absolutely, you can use the MAX function to find the highest number in a range of cells. Just type =MAX(E2:E) and it will show you the largest value, i.e., your highest earning from a single sale.