This topic demonstrates how to set up the structure to use a 4-5-4 retail calendar within your Adobe Commerce Intelligence account. The visual report builder provides incredibly flexible time ranges, intervals, and independent settings. However, all of these settings work with the traditional monthly calendar in place.
Because many customers alter their calendar to use retail or accounting dates, the below steps illustrate how to work with your data and create reports using retail dates. Though the below instructions reference the 4-5-4 Retail calendar, you can alter them for any specific calendar your team uses, whether it be financial or just a custom time frame.
Before getting started, you should review the File Uploader and ensure that you have elongated the .csv
file. This ensures that the dates cover all of your historical data and push the dates into the future.
This analysis contains advanced calculated columns.
You can download a .csv
version of the 4-5-4 retail calendar for retail years 2014 through 2017. You may need to adjust this file according to your internal retail calendar and extend the date range to support your historical and current time frame. After downloading the file, use the File Uploader to create a Retail Calendar table in your Commerce Intelligence Data Warehouse. If you are using an unaltered version of the 4-5-4 retail calendar, ensure that the structure and data types of the fields in this table match the following:
Column Name | Column Datatype | Primary Key |
---|---|---|
Date Retail |
Date & Time |
Yes |
Year Retail |
Whole Number |
No |
Quarter Retail |
Whole Number |
No |
Month Number Retail |
Whole Number |
No |
Week Retail |
Whole Number |
No |
Month Name Retail |
Text (Up to 255 Characters) |
No |
Week Number of Month Retail |
Whole Number |
No |
sales_order table
INPUT
created\_at
(yyyy-mm-dd 00:00:00)
Same table > Calculation
created\_at
Datetime
case when A is null then null else to\_char(A, 'YYYY-MM-DD 00:00:00') end
Retail calendar file upload table
Current date
Column type: Same table > Calculation
Inputs: Date Retail
Datatype: Datetime
Calculation: case when A is null then null else to\_char(now(), 'YYYY-MM-DD 00:00:00') end
The now()
function above is specific to PostgreSQL. Although most Commerce Intelligence data warehouses are hosted on PostgreSQL, some may be hosted on Redshift. If the calculation above returns an error, you may need to use the Redshift function getdate()
instead of now()
.
Current retail year (Must be created by support analyst)
vent Counter
Current date
Retail calendar.Date Retail
Max
Year Retail
Included in current retail year? (Yes/No)
Same table > Calculation
A
- Year Retail
B
- Current retail year
String
case when A is null or B is null then null when A = B then 'Yes' else 'No' end
Included in previous retail year? (Yes/No)
Same table > Calculation
A
- Year Retail
B
- Current retail year
case when A is null or B is null then null when (A = (B-1)) then 'Yes' else 'No' end
sales_order table
One to Many > JOINED\_COLUMN
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
Retail Calendar.Date Retail
Retail Calendar
Year Retail
One to Many > JOINED\_COLUMN
Retail Calendar
Week Retail
One to Many > JOINED\_COLUMN
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
Retail Calendar.Date Retail
Retail Calendar
Month Number Retail
One to Many > JOINED\_COLUMN
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
Calendar.Date Retail
Retail Calendar
Include in previous retail year? (Yes/No)
One to Many > JOINED\_COLUMN
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
Calendar.Date Retail
Retail Calendar
Include in current retail year? (Yes/No)
Note: No new metrics are needed for this analysis. However, make sure to add the new columns you built in the sales_order table as dimensions for all metrics on the sales_order table before continuing to the reports.
Weekly orders - retail calendar (YoY)
A
: 2017
B
: 2016
C
: 2015
Number of orders
Created\_at (retail Year) = 2015
All time
None
Created\_at
(retail week)Line
multiple Y-axes
Retail calendar overview (current retail year by month)
A
: Revenue
Revenue
Yes
B
: Orders
Number of orders
Yes
C
: Avg order value
Avg order value
Yes
All time
None
Created\_at
(retail month)Line
Retail calendar overview (previous retail year by month)
A
: Revenue
Revenue
Yes
B
: Orders
Yes
C
: Avg order value
Avg order value
Yes
All time
None
Created\_at
(retail month)Line
The above describes how to configure a retail calendar to be compatible with any metric built on your sales\_order
table (such as Revenue
or Orders
). You can also extend this to support the retail calendar for metrics built on any table. The only requirement is that this table has a valid datetime field that can be used to join to the Retail Calendar table.
For example, to view customer level metrics on a 4-5-4 retail calendar, create a Same Table
calculation in the customer\_entity
table, similar to \[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
described above. You can then use this column to reproduce the One to Many
JOINED_COLUMN calculations (like Created_at (retail year)
) and Include in previous retail year? (Yes/No)
by joining the customer\_entity
table to the Retail Calendar
table.
Do not forget to add all new columns as dimensions to metrics before building new reports.