A step-by-step guide to help Amazon sellers track and compare their ad-driven and organic sales over a selected period, enabling better decision-making.

In this article, we will guide you through creating an interactive dashboard to track and compare sales performance from organic and paid traffic across different periods, such as weekly or monthly.

Finally, to provide a clear visual understanding of the metric, we’ll use a chart that makes it easy to see whether the relation between organic and paid sales is increasing or decreasing over time. We will create a table and chart like the one below.

One good thing about doing this in Defog’s spreadsheet is that our table automatically updates the data when Defog retrieves new data from Amazon Seller Central and Amazon Advertising. Additionally, we will use today’s date as the base for all periods; therefore, the periods will be automatically recalculated every day you open the spreadsheet.

Step 1: On your desktop browser, open your Defog spreadsheet and create a new sheet by clicking the + icon at the bottom of the Google Sheets window and name the new sheet “Organic vs. Paid Sales“.

Defining the Periods

Step 2: Add the following headings to your “Organic vs. Paid Sales” sheet 

Note that D1 is a number you can set for your analyses with the desired period in days. Say you want to see per week; add seven to D1. If you’re going to track the 30 days, add 30. Also, remember that we paint the cell with a light blue background to indicate that this cell receives input from the user.

Step 3: The dates in the Initial date and Final date rows represent the start and end dates of each period analyzed. These will serve as the timeframe for calculating total sales.

Copy and paste the following formulas on the indicated cells.

B5:

=today()-1

B4:

=B5-($D$1-1)

C5:

=B5-$D$1

Now select and drag the formula in B4 up to I4 and C5 to I5.

If you left D1 with 30, as shown below, you now have the eight most recent past periods of 30 days ending yesterday (B4-B5 has the most recent 30-day period). Your table should look similar to the one below:

Calculating Total Sales Per Period

Step 4: To calculate the total sales per period, we will use two columns on OrdersTable, column J Item Price and column AB Purchase Date (PT).

We will now use the SUMIFS formula to calculate total sales for each period based on the initial and final dates in our table above. The formula will check the Purchase Date (PT) in the OrdersTable and sum up the Item Prices that fall between the initial and final dates for each period. Here is the formula to be copied and pasted on B6:

=sumifs(OrdersTable!$L:$L,OrdersTable!$AB:$AB,">="&B4,OrdersTable!$AB:$AB,"<="&B5)

Now, select and drag the formula on B6 up to I6. Your table should look similar to the one below:

Calculating Sales from Ads

Step 5: We’re going to calculate sales from ads, which are coming from the AdsTable. We’ll use the same SUMIFS formula to sum the sales values within the specified date range.

To calculate the total advertising sales per period, we will use two columns on AdsTable: column J Sales and column C Date.

Step 6: On B7, copy and paste the formula given below:

=SUMIFS(AdsTable!$J:$J, AdsTable!$C:$C, ">="&B4, AdsTable!$C:$C, "<="&B5)

Now select and drag the B7 formula up to I7. 

Calculating Organic Sales

Step 7: To calculate the Organic Sales, we’ll subtract the Sales from Ads (row 7) from the Total Sales (row 6).

In cell B8, enter the following formula:

=B6-B7

Now, select and drag the formula from B8 to I8 to apply it across all periods in the table.

Your table should look similar to the one below:

Creating Period Legend for the Chart

Step 8: We create a period legend by concatenating each period’s initial and final dates to display in the chart. This will help clearly label each time frame being analyzed.

To create the period legend, copy and paste the formula below into B3 and drag it across to I3.

=if($D$1<>1,month(B4)&"/"&day(B4)&"/"&year(B4)&" - "&month(B5)&"/"&day(B5)&"/"&year(B5),month(B5)&"/"&day(B5)&"/"&year(B5))

After some cosmetic formatting, your table should look similar to the one below:

Creating the Visual

Step 9: Click and drag your mouse cursor to select the range from A3 to I8 in your sheet. This includes all the rows between the Period Legend and Organic Sales.

Go to the Insert ribbon at the top of Google Sheets and Click on Chart.

In the sidebar under Setup, click on the Chart Type pulldown menu and choose a Stacked Bar Chart. This chart type will help you visually compare the organic vs. ad sales.

Our chart will only need Sales from Ads and Organic Sales. Click the three-dot menu next to the Total sales series on the chart Setup sidebar, then click Remove.

After some cosmetic editing, the chart looks similar to the one below:

Step 12: Now, you can change the number of days in cell D1 to compare paid vs organic sales for different periods.

Your final sheet looks like this image below, along with a chart.

If you sell in multiple countries in the same Amazon Region (North America, Europe, or the Far East), Defog will download data from all countries (all marketplaces) to the same OrdersTable and AdsTable. Therefore, you must do this analysis per country or currency, as you cannot sum the total sales amount in different currencies.

If you want to learn what a particular column stands for on Defog’s tables, please visit our glossary.

Thank you for reading this post. If you still haven’t used Defog, you can do so for free here.

If you need any help, we are here for you.

Disclaimer: Defog is not responsible for any decisions made by the reader of this post or for the consequences of using the data, formulas, and charts provided.