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
  • 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

        NOTE
        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)

      • Column type: Event Counter
      • Local Key: Current date
      • Remote Key: Retail calendar.Date Retail
      • Operation: Max
      • Operation value: Year Retail
    • 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
      • 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
      • 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
      • 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
      • 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
    • Time period: All time

    • Interval: None

    • Group by: Created\_at (retail week)

    • Chart type: Line

      • Turn off multiple Y-axes
  • Retail calendar overview (current retail year by month)

    • Metric A: Revenue

      • Metric: Revenue
      • Filter:
        • Include current retail year? : Yes
    • Metric B: Orders

      • Metric: Number of orders
      • Filter:
        • Include current retail year? : Yes
    • Metric C: Avg order value

      • Metric: Avg order value
      • Filter:
        • Include current retail year? : Yes
    • 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
    • Metric B: Orders

      • Metric: Number of orders
      • Filter:
        • Include current retail year? : Yes
    • Metric C: Avg order value

      • Metric: Avg order value
      • Filter:
        • Include current retail year? : Yes
    • 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.

recommendation-more-help