These SQL queries use the National Retail Foundation (NRF) calendar to conduct more meaningful period-over-period comparisons from your Direct Access data in BigQuery on the Google Cloud Platform.
- All NRF elements for a given date
- Period-over-period comparison using a custom date range
- Period-over-period comparison for a targeted period
- Pivot period comparisons across columns
- Period-over-period using SQL navigation functions
Note: Replace all instances of COMPANYNAME with the abbreviated name of your business as it appears in BigQuery.
All NRF elements for a given date
The result of this query gives you the NRF day, week, period, quarter, and year columns of that date. For a complete list of columns, see SoundCommerce Data Dictionary.
SELECT
date,
DayOfWeek, DayOfPeriod, DayOfQuarter,DayOfYear, DayName,
WeekInYearNumber, WeekInPeriodNumber, WeekName, WeekYearName,
PeriodInYear, PeriodInQuarter, PeriodYearName,
QuarterInYearNumber, QuarterName,QuarterYearName,
YearNumber
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date`
WHERE date = current_date(
Period-over-period comparison using a custom date range
When calculating metrics based on a custom date range, you must join the Gregorian dates to another NRF calendar by their NRF DayOfYear values.
This sample query compares the date range 2021-04-22 to 2021-05-06 with the previous NRF Year. The results show that we're comparing the same NRF days; however, the Gregorian dates are different.
WITH period_date_ranges AS
(
SELECT DISTINCT
cp.YearNumber,
cp.date as Date,
cp.DayOfYear,
pp.YearNumber as PrevYearNumber,
pp.date as PrevDate,
pp.DayOfYear as PrevDayOfYear
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` cp
INNER JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` pp
on pp.YearNumber = (cp.YearNumber - 1) -- previous year
and pp.DayOfYear = cp.DayOfYear -- same nrf days
WHERE cp.date BETWEEN '2021-04-22' AND '2021-05-06'
)
SELECT
YearNumber as nrf_year,
DayOfYear as nrf_day_of_year,
Date as calendar_date,
count(CASE WHEN NOT canceled THEN order_id END) as order_count,
count(CASE WHEN canceled THEN order_id END) as canceled_order_count
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
INNER JOIN period_date_ranges d
ON o.order_date = Date
GROUP BY DayOfYear, Date, YearNumber
UNION ALL
SELECT
PrevYearNumber as nrf_year,
PrevDayOfYear as nrf_day_of_year,
PrevDate as calendar_date,
count(CASE WHEN NOT canceled THEN order_id END) as order_count,
count(CASE WHEN canceled THEN order_id END) as canceled_order_count
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
INNER JOIN period_date_ranges d
ON o.order_date = PrevDate
GROUP BY PrevDayOfYear, PrevDate, PrevYearNumber
ORDER BY nrf_day_of_year DESC, nrf_year DESC;
Period-over-period comparison for a targeted period
This example compares metrics between two years for the same NRF month.
SELECT
YearNumber nrf_year,
PeriodName as nrf_period,
PeriodMonthName as nrf_month_name,
count(distinct o.order_id) as count_of_orders,
count(distinct o.cuid) as count_of_unique_customers,
count(distinct c.cuid) as count_of_new_customers
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` d
LEFT JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
ON o.order_date = d.date
LEFT JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_customers` c
ON c.first_order_date = d.date
WHERE d.YearNumber in (2020, 2021)
and d.PeriodInYear = 4
GROUP BY YearNumber, PeriodName, PeriodMonthName
ORDER BY YearNumber DESC, PeriodName DESC
Pivot period comparisons across columns
With pivoting, fiscal period values can be pivoted to columns instead of another hierarchy of rows (see Period-over-period orders by NRF year).
The built-in BigQuery PIVOT operator requires you to hard-code your column names (see line 15 in the following example). These types of queries are easier to do with a third-party reporting tool, such as Tableau, than in SQL.
WITH order_details AS
(
SELECT
QuarterName as nrf_quarter,
o.channel,
cast(round(sum(discounted_sales),0) as int64) as discounted_sales,
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
LEFT JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` d
on d.date = o.order_date
WHERE YearNumber = 2020
GROUP BY QuarterName, scope, channel
)
SELECT *
FROM (SELECT * FROM order_details)
PIVOT(sum(discounted_sales) FOR nrf_quarter IN ('Q1','Q2','Q3','Q4'))
ORDER BY channel
Period-over-period using SQL navigation functions
BigQuery provides Navigation Functions that you can use in your result to compare values from one row to another. This sample uses the LAG function to compare sales from the last three years for a specific time period. Subtracting or dividing the LAG value of sales results in the difference and percentage of sales from the previous period.
WITH numbers_by_period as
(
SELECT
d.YearNumber,
d.PeriodInYear,
d.PeriodName,
cast(round(sum(discounted_sales),0) as int64) as discounted_sales
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
LEFT JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` d
ON d.date = o.order_date
CROSS JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` cd
WHERE d.YearNumber in (cd.YearNumber, cd.YearNumber-1, cd.YearNumber-2)
AND cd.date = current_date()
AND d.PeriodInYear < cd.PeriodInYEar
GROUP BY
d.YearNumber,
d.PeriodInYear,
d.PeriodName
)
SELECT
YearNumber as nrf_year,
PeriodName as nrf_period,
format("%'d", discounted_sales) as discounted_sales,
format("%'d", discounted_sales - lag(discounted_sales) OVER period_window) as pop_sales_difference,
concat(
format("%'.2f", ((discounted_sales - lag(discounted_sales) OVER period_window) / discounted_sales) * 100),
' %') as pop_sales_percent
FROM numbers_by_period
WINDOW period_window as (PARTITION BY PeriodInYear ORDER BY PeriodInYear, YearNumber)
ORDER BY PeriodInYear DESC, YearNumber DESC
Related articles
Comments
0 comments
Please sign in to leave a comment.