Columns to Create
-
sales_order table
-
INPUT
created\_at
(yyyy-mm-dd 00:00:00)- Column type: –
Same table > Calculation
- Inputs: –
created\_at
- Datatype: –
Datetime
- Calculation: -
case when A is null then null else to\_char(A, 'YYYY-MM-DD 00:00:00') end
- Column type: –
-
-
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
NOTEThenow()
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 functiongetdate()
instead ofnow()
.
-
-
Current retail year (Must be created by support analyst)
- Column type: E
vent Counter
- Local Key:
Current date
- Remote Key:
Retail calendar.Date Retail
- Operation:
Max
- Operation value:
Year Retail
- Column type: E
-
Included in current retail year? (Yes/No)
-
Column type:
Same table > Calculation
-
Inputs:
A
-Year Retail
B
-Current retail year
-
Datatype:
String
-
Calculation:
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)
-
Column type:
Same table > Calculation
-
Inputs:
A
-Year Retail
B
-Current retail year
-
Datatype: String
-
Calculation:
case when A is null or B is null then null when (A = (B-1)) then 'Yes' else 'No' end
-
-
-
sales_order table
-
Created_at (retail year)
-
Column type:
One to Many > JOINED\_COLUMN
-
Path -
- Many:
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
- One:
Retail Calendar.Date Retail
- Many:
-
Select a table:
Retail Calendar
-
Select a column:
Year Retail
-
-
Created_at (retail week)
-
Column type:
One to Many > JOINED\_COLUMN
-
Path -
- Many: sales_order.[INPUT] created_at (yyyy-mm-dd 00:00:00
- One: Retail Calendar.Date Retail
-
Select a table:
Retail Calendar
-
Select a column:
Week Retail
-
-
Created_at (retail month)
-
Column type:
One to Many > JOINED\_COLUMN
-
Path
- Many:
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
- One:
Retail Calendar.Date Retail
- Many:
-
Select a table:
Retail Calendar
-
Select a column:
Month Number Retail
-
-
Include in previous retail year? (Yes/No)
-
Column type:
One to Many > JOINED\_COLUMN
-
Path -
- Many:
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
- One: Retail
Calendar.Date Retail
- Many:
-
Select a table:
Retail Calendar
-
Select a column:
Include in previous retail year? (Yes/No)
-
-
Include in current retail year? (Yes/No)
-
Column type:
One to Many > JOINED\_COLUMN
-
Path -
- Many:
sales\_order.\[INPUT\] created\_at (yyyy-mm-dd 00:00:00)
- One: Retail
Calendar.Date Retail
- Many:
-
Select a table:
Retail Calendar
-
Select a column:
Include in current retail year? (Yes/No)
-
-
Metrics
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.
Reports
-
Weekly orders - retail calendar (YoY)
-
Metric
A
:2017
- Metric: Number of orders
- Filter:
- Created_at (retail Year) = 2017
-
Metric
B
:2016
- Metric: Number of orders
- Filter:
- Created_at (retail Year) = 2016
-
Metric
C
:2015
- Metric:
Number of orders
- Filter:
Created\_at (retail Year) = 2015
- Metric:
-
Time period:
All time
-
Interval:
None
-
Group by:
Created\_at
(retail week) -
Chart type:
Line
- Turn off
multiple Y-axes
- Turn off
-
-
Retail calendar overview (current retail year by month)
-
Metric
A
:Revenue
- Metric:
Revenue
- Filter:
- Include current retail year? :
Yes
- Include current retail year? :
- Metric:
-
Metric
B
:Orders
- Metric:
Number of orders
- Filter:
- Include current retail year? :
Yes
- Include current retail year? :
- Metric:
-
Metric
C
:Avg order value
- Metric:
Avg order value
- Filter:
- Include current retail year? :
Yes
- Include current retail year? :
- Metric:
-
Time period:
All time
-
Interval:
None
-
Group by:
Created\_at
(retail month) -
Chart type:
Line
-
-
Retail calendar overview (previous retail year by month)
-
Metric
A
:Revenue
- Metric:
Revenue
- Filter:
- Include current retail year? :
Yes
- Include current retail year? :
- Metric:
-
Metric
B
:Orders
- Metric: Number of orders
- Filter:
- Include current retail year? :
Yes
- Include current retail year? :
-
Metric
C
:Avg order value
- Metric:
Avg order value
- Filter:
- Include current retail year? :
Yes
- Include current retail year? :
- Metric:
-
Time period:
All time
-
Interval:
None
-
Group by:
Created\_at
(retail month) -
Chart type:
Line
-
Next Steps
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.