Compare Average Sales Amount Between Various Periods 

The most significant benefit of Defog is that it connects to Amazon Seller Central, automatically retrieving valuable data to Google Sheets. Sellers may then use the data to monitor how their sales change throughout different periods. 

In this article, we will show you how to calculate the average daily sales amount for different periods – per day for the last 8 days, per week for the previous 8 weeks, per 28 days for the last 8 periods of 28 days. With little formula editing, you may then use the same principle to compare other metrics, like the average number of units ordered or the average cost of advertising, or even analyze other periods, like quarters. You will be creating tables like the ones below:

With these tables, you can create a nice chart to monitor your sales numbers visually.

One good thing about doing this in Defog’s spreadsheet is that the tables we create below will automatically update as soon as Defog updates the data. 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: Add a new sheet to your Defog by clicking on the + symbol in the lower left corner.

Step 2: On the new sheet, create the tables’ heading column as shown below:

Defining the Periods

Step 3: We will add yesterday’s date and then calculate the other past periods based on that. Note that we don’t use today’s date because today is not finished yet. For this metric, you only want to consider closed periods; hence, we start with yesterday’s data. 

Copy and paste the following formulas to the cells indicated below:

1-day periods

On B4: Yesterday’s date.

=today()-1

On C4: The day before yesterday.

=B4-1

Now select and drag the C4 formula up to I4. This will create 8 past periods of 1 day, ending in yesterday down to yesterday’s weekday one week before (say yesterday is a Tuesday, the 8th period will be last week’s Tuesday).

7-day periods

On B10: Copy yesterday’s date.

=B4

On C10: 7 days before yesterday will be the final date of the previous week.

=B10-7

Now select and drag the C10 formula up to I10. Ending yesterday, this will create 8 previous periods of 7 days.

On B9: Calculate 6 days before yesterday. This will be the initial date of our most recent period, which will have exactly 7 days, counting yesterday.

=B10-6

Now select and drag the B9 formula up to I9. You have 8 past periods of 7 days, ending yesterday.

28-day periods

On B16: Copy yesterday’s date.

=B4

On C16: 28 days before yesterday will be the final date of the previous week.

=B10-28

Now select and drag the C16 formula up to I16. Ending yesterday, this will create 8 previous periods of 28 days.

On B15: Calculate 27 days before yesterday. This will be the initial date of our most recent period, which will have exactly 28 days, counting yesterday.

=B10-27

Now select and drag the B15 formula up to I15. You have 8 past periods of 28 days, ending yesterday.

Sales Amount Moving Average

Step 4: Now, to add the tables’ values, copy and paste the following formulas to the correct cells:

1-day periods

On B5: The most recent period sales amount.

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

Select and drag the B5 formula up to I5.

On B6: The percentage difference between two consecutive periods.

=B5/C5-1

Select and drag the B6 formula up to I6.

7-day periods

On B11: The most recent period sales amount divided by 7 (number of days)

=sumifs(OrdersTable!$L:$L, OrdersTable!$AB:$AB,">="&B9, OrdersTable!$AB:$AB,"<="&B10)/7

Select and drag the B11 formula up to I11.

On B12: The percentage difference between two consecutive periods.

=B11/C11-1

Select and drag the B12 formula up to I12.

28-day periods

On B17: The most recent period sales amount divided by 28 (number of days)

=sumifs(OrdersTable!$L:$L, OrdersTable!$AB:$AB,">="&B15, OrdersTable!$AB:$AB,"<="&B16)/28

Select and drag the B17 formula up to I17.

On B18: The percentage difference between two consecutive periods.

=B17/C17-1

Select and drag the B18 formula up to I18.

Step 5: After a few cosmetic adjustments, the tables look like the image below.

Please note that if you sell in more than one country in the same Amazon Region (North America, Europe, or Far East), Defog will download data from all countries (all marketplaces) to the same OrdersTable. You must do this moving average sales amount 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.