How to Compare Shopify BFCM Sales Year Over Year in Google Sheets

The short answer: to compare this year's Black Friday Cyber Monday with last year's, line the days up by their distance from Black Friday, not by calendar date. Black Friday was Friday 28 November in 2025 and falls on Friday 27 November in 2026. Pull last year's Shopify orders once with a fixed date range, put both years in the same Google Sheet, and remember that last year's refunds are final while this year's are still coming in.

This guide walks you through doing that in Google Sheets with SyncRange, step by step. It works for a single store, and it scales to brands that run several stores or markets. If you'd rather not build it yourself, our free BFCM reporting template has a YoY tab that does all of this for you.

Do it this week. Last year's data doesn't change, so there's no reason to leave it until Black Friday, when you'll have no time to spare.

Why comparing BFCM by calendar date gives you the wrong answer

Black Friday is the day after US Thanksgiving, so its date moves every year. If you compare 27 November 2026 with 27 November 2025, you're comparing this year's Black Friday with last year's Thanksgiving Thursday. Cyber Monday is the same: 30 November 2026 lines up with Sunday 30 November 2025.

The fix is a day index: count every day relative to Black Friday (Black Friday = 0) and compare the same index in both years.

Comparing like days also keeps the weekday the same, which matters for any store whose sales follow a weekly pattern.

What you need

• A Shopify store and a SyncRange account. Start free, no credit card required, or install SyncRange from the Shopify App Store.
• A Google Sheet: your own, or a copy of the BFCM template.
• Two Shopify exports into that sheet: a scheduled one for this year and a one-time one for last year.

Step-by-step: build a Shopify year-over-year comparison in Google Sheets

Step 1: Connect Shopify and your sheet

Connect your store by following Connecting Your Shopify Store. SyncRange only asks for read-only access, so it can't change anything in your store. Then add your sheet as a destination by following Configuring Google Sheets and choosing Select an Existing Sheet.

Step 2: Set your account time zone

When you use a custom date range, SyncRange uses your account time zone if you've set one, and UTC if you haven't (see export settings). Set it to your store's time zone before you export, so late-night Thanksgiving orders land on the right day in both years.

Step 3: Create this year's export (scheduled)

In Export Builder, create a Shopify export to the sheet (see Configuring Shopify Exports):

• Tabs: Sales Metrics for revenue and Orders for order details. Sales Metrics has one row per order with Order Date, Gross Sales, Discounts, Returns and Net Sales, matching Shopify's reports view (every column is in the Shopify data reference).
• Date range: a rolling preset such as Last 90 days, so the schedule keeps running and covers both the run-up and BFCM itself.
• Data handling: Replace, so each run writes a fresh snapshot.

Run it once, check that the tabs fill, then set a schedule.

Step 4: Pull last year's BFCM once with a custom range

Create a second Shopify export to the same sheet with the same tabs, and give it a different name so its tabs don't overwrite this year's. Set the date range to Custom range, from 1 November 2025 to 15 December 2025, and click Run now.

Three things to know:

• Use Custom range, not "Last year". In Shopify exports, "Last year" means the past 365 days, not calendar 2025.
• A custom range is manual only. It can cover up to one year, and scheduled runs are paused while it's selected. That's fine here, because last year's numbers won't change. You only need to run it once.
• It's an extra export, so check your plan's export allowance on the pricing page.

Step 5: Add a day index to both years

In a helper tab (never inside the tabs SyncRange writes to, because Replace mode rewrites them), add a day index next to each order date:

• This year: =A2-DATE(2026,11,27)
• Last year: =A2-DATE(2025,11,28)

where A2 is the Order Date. If the date arrives as text, wrap it in DATEVALUE(). Black Friday is 0 in both years, Thanksgiving is -1 and Cyber Monday is +3.

Step 6: Build the comparison table

Make one row per day index from -2 to +3, and use SUMIFS and COUNTIFS on each year's helper tab:

• Orders: count of orders with that day index
• Gross sales, discounts, net sales: SUMIFS on the Sales Metrics columns
• AOV: net sales ÷ orders
• Discount rate: discounts ÷ gross sales
• Change %: this year ÷ last year − 1
• Cumulative net sales for each year, so you can see whether you're ahead overall even if one day was slower

Add a clustered column chart of net sales by day index and a line chart of cumulative net sales for both years. This is exactly what the template's YoY tab does, so if you've copied the template you can skip steps 5 and 6.

The refund catch: last year's returns are final, this year's aren't

In SyncRange's Sales Metrics, Net Sales is Gross Sales − Discounts − Returns, and the tab is filtered by the date the order was created. Returns is the refunded amount on those orders so far. A refund issued in December on a Black Friday order counts against Black Friday.

That's what you want for an honest comparison, but it creates a timing gap:

• Last year's BFCM orders have had a year to be refunded, so their Returns are final.
• This year's BFCM orders have barely started. Refunds keep arriving for weeks.

So on Cyber Monday, this year's net sales will look better against last year than they really are. Three ways to handle it:

1. Compare gross sales, orders and AOV during the event. None of these depend on refunds.
2. Compare net sales before returns (Gross Sales − Discounts) in both years, for a like-for-like revenue number.
3. Re-sync in December and again in January, then compare final net sales. Last year's returns as a share of gross sales also give you a rough idea of what to expect this year.

Here's what the gap looks like. The figures below are made-up example data, not real store figures.

On these example numbers, net sales show +21% while net sales before returns show +10% ($88,000 vs $80,000). The second number is the honest one until this year's refunds catch up.

Comparing year over year across several stores or markets

If you run more than one Shopify store, or one store selling into several markets, a single YoY total can hide what's happening underneath. You'll usually want the comparison per store or market as well as overall.

• One sheet per store. Each Shopify export reads one store, so the simplest setup is one copy of the template (or your own sheet) per store, each with its own this-year and last-year exports.
• One view across stores. Data Blends can pull several connected Shopify stores (and ad accounts) into shared tabs with the same column names, so you can build a single comparison on top.
• BigQuery for data teams. If your team already works in SQL, send each store's Sales Metrics and Orders to BigQuery with Shopify to BigQuery, pull last year's BFCM window the same way, and compute the day index in a query. Our guide to syncing Shopify to BigQuery covers the setup.
• Markets and currencies. Sales Metrics and Orders include the order Currency, and Orders has Shipping Country Code, so you can split the comparison by market. Don't add up different currencies. The template's Settings tab has a store currency filter for this reason.

The BFCM template doesn't have a geography or per-market tab, so a market-level split means adding a pivot of your own on top of the Orders data. It's a normal Google Sheet, so you can add any tabs you need.

Add Meta ad spend to the comparison (optional)

Sales up 15% on last year means something different if your ad spend went up 40%. If you advertise on Meta, add a second source:

1. Connect Meta and create a Meta export to the same sheet (see Configuring Meta exports), using the Date tab at daily granularity. It gives you one row per day for the whole ad account, including Spend (see the Meta data reference).
2. Make a one-time copy with a custom range for November and December 2025, just like the Shopify step.
3. Add spend to your day-index table and work out Shopify net sales ÷ Meta spend for each year.

The BFCM template is Shopify-only, so this goes in a tab you add yourself. Bear in mind that a ratio built on Meta spend alone flatters your efficiency if you also advertise elsewhere. For the maths, see how to calculate return on ad spend, and for the Meta setup, exporting Meta Ads to Google Sheets or the Meta to Google Sheets workflow.

Checklist: last year's BFCM data in a few minutes

• Account time zone set to your store's time zone
• This year's Shopify export: Sales Metrics + Orders, Last 90 days, Replace, scheduled
• Last year's Shopify export: same tabs, Custom range 1 Nov to 15 Dec 2025, Run now
• Day index added (Black Friday = 0 in both years)
• Comparison by day index for orders, gross sales, net sales before returns, AOV and discount rate
• Reminder set to re-sync and compare net sales again in December
• (Optional) Meta spend for both years

Frequently asked questions

When was Black Friday 2025, and when is Black Friday 2026? Black Friday 2025 was Friday 28 November (Cyber Monday was 1 December). In 2026, Thanksgiving is Thursday 26 November, Black Friday is Friday 27 November and Cyber Monday is Monday 30 November.

How do I compare Black Friday sales year over year in Google Sheets? Get both years' Shopify orders into the same sheet, give each order a day index (order date minus that year's Black Friday date), then sum orders and sales by day index for each year. Compare the same index, not the same calendar date.

Can I export last year's Shopify data with a scheduled export? Use a one-time export instead. Pick Custom range with last year's dates and click Run now. Custom ranges can cover up to a year and pause the schedule by design, which suits historical data that won't change.

Why does this year's BFCM look better than last year's on net sales? Usually it's refunds. Last year's BFCM orders have had all their refunds, and this year's haven't yet. Compare gross sales or net sales before returns during the event, and compare net sales again once this year's refunds have come through.

Can I compare year over year across several Shopify stores? Yes. Use one sheet per store, combine stores with Data Blends, or send every store's data to BigQuery and run the comparison in SQL. Keep currencies separate when your stores or markets sell in different currencies.

Do I need a paid plan? You can start on the free plan, no credit card required. The last-year pull is an extra export, so check the export allowance for your plan on the pricing page.

Get your year-over-year view ready before Black Friday

Last year's data is sitting there now. Pull it this week, and on Black Friday you'll know straight away whether you're ahead of last year.

• Get the free BFCM template (YoY tab included): BFCM reporting template
• Start free, no credit card required: Create your SyncRange account
• Prefer Shopify admin? Install SyncRange from the Shopify App Store

More on Shopify reporting: export Shopify orders to Google Sheets, the Shopify to Google Sheets workflow, SyncRange for Shopify and our other Google Sheets templates.