Magento Business Intelligence Help Center

Reporting on a retail calendar

In this article, we demonstrate how to set up the structure to use a 4-5-4 retail calendar within your Magento BI account. The visual report builder provides incredibly flexible time ranges, intervals and independent settings. Our team is also able to help you change the start day of the week to align with your business preferences. However, all of these settings work with the traditional monthly calendar in place.

Because many of our customers alter their calendar to use retail or accounting dates, the below steps will illustrate how to work with your data and create reports using retail dates. Though the below instructions will 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 will want to familiarize yourself with the File Uploader and ensure that you have elongated the csv file so that the dates cover all of your historical data as well as push the dates into the future.

This analysis contains advanced calculated columns that can only be created by a Magento BI analyst, so file a support request with the subject [RETAIL CALENDAR ANALYSIS], before getting started with this analysis.

A CSV version of the 4-5-4 retail calendar for retail years 2014 through 2017 can be downloaded here. After downloading the file, use the File Uploader to create a Retail Calenar table in your data warehouse.

Columns to create

  • sales_flat_order table
    • [INPUT] created_at (yyyy-mm-dd 00:00:00) 
      • Column type – “Same table -> Calculation”
      • Inputs – created_at
      • Calculation - ` case when A is null then null else to_char(A, 'YYYY-MM-DD 00:00:00') end`
      • Datatype – Datetime
    • Is order in last 182 days? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – created_at
      • Calculation - `case when A is null then null when (floor(extract(epoch from (now()-A))/86400)) > 182 then 'Yes' else 'No' end`
      • Datatype – String
  • customer_entity table
    • [INPUT] created_at (yyyy-mm-dd 00:00:00) 
      • Column type – “Same table -> Calculation”
      • Inputs – created_at
      • Calculation - `case when A is null then null else to_char(A, 'YYYY-MM-DD 00:00:00') end`
      • Datatype – Datetime
    • Is created in last 182 days? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – created_at
      • Calculation - `case when A is null then null when (floor(extract(epoch from (now()-A))/86400)) > 182 then 'Yes' else 'No' end`
      • Datatype – String
  • Retail calendar file upload table
    • Current date 
      • Column type – “Same table -> Calculation”
      • Inputs – Date
      • Calculation - `case when A is null then null else to_char(A, 'YYYY-MM-DD 00:00:00') end`
      • Datatype – Integer
    • Current retail year 
      • Column type – “Same table -> Calculation”
      • Inputs – Date
      • Calculation - `case when A is null then null else to_char(A, 'YYYY') end`
      • Datatype – Datetime
    • Current retail quarter will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Current retail month 
      • Column type – “Same table -> Calculation”
      • Inputs – Date
      • Calculation - `case when A is null then null else to_char(A, 'MM') end`
      • Datatype – Datetime
    • Current retail week will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in current retail year? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Year, Current retail year
      • Calculation - `case when A is null then null when A = B then ‘Yes’ else ‘No’ end`
      • Datatype – String
    • Included in current retail quarter? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Quarter, Current retail quarter
      • Calculation - `case when A is null then null when A = B then ‘Yes’ else ‘No’ end`
      • Datatype – String
    • Included in current retail month? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Month, Current retail month
      • Calculation - `case when A is null then null when A = B then ‘Yes’ else ‘No’ end`
      • Datatype – String
    • Included in current retail week? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Week, Current retail week
      • Calculation - `case when A is null then null when A = B then ‘Yes’ else ‘No’ end`
      • Datatype – String
    • Included in previous retail year? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Year, Current retail year
      • Calculation - `case when A is null then null when ((A – 1) = B) then ‘Yes’ else ‘No’ end`
      • Datatype – String
    • Included in previous retail quarter? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Quarter, Current retail quarter
      • Calculation - `case when A is null then null when ((A – 1) = B) then ‘Yes’ else ‘No’ end`
      • Datatype – String
    • Included in previous retail month? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Month, Current retail month
      • Calculation - `case when A is null then null when ((A – 1) = B) then ‘Yes’ else ‘No’ end`
      • Datatype – String
    • Included in previous retail week<? (Yes/No) 
      • Column type – “Same table -> Calculation”
      • Inputs – Week, Current retail week
      • Calculation - `case when A is null then null when ((A – 1) = B) then ‘Yes’ else ‘No’ end`
      • Datatype – String
  • sales_flat_order table
    • Created_at (retail year)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - Many: sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Retail calendar year
    • Created_at (retail week)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Retail calendar fiscal week #
    • Created_at (retail week # in retail month)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Week # in retail month
    • Created_at (retail month)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Retail calendar month #
    • Created_at (retail quarter)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Retail calendar quarter
    • Include in previous retail year? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in previous retail year? (Yes/No)
    • Include in previous retail quarter? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in previous retail quarter? (Yes/No)
    • Include in previous retail month? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in previous retail month? (Yes/No)
    • Include in previous retail week? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in previous retail week? (Yes/No)
    • Include in current retail year? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in current retail year? (Yes/No)
    • Include in current retail quarter? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in current retail quarter? (Yes/No)
    • Include in current retail month? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in current retail month? (Yes/No)
    • Include in current retail week? (Yes/No)
      • Column type – “One to Many -> JOINED_COLUMN”
      • Path - sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Include in current retail week? (Yes/No)
  • customer_entity table
    • You can create the same list of joined columns that you created in the sales_flat_order table above in the customer_entity table to segment customer level metrics by, if desired

Metrics

Note: No new metrics are needed for this analysis. However, make sure to add all new columns as dimensions to metrics before building new 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 previous retail year? = Yes
    • Metric B: Orders
      • Metric: Number of orders
      • Filter:
        • Include previous retail year? = Yes
    • Metric C: Avg order value
      • Metric: Avg order value
      • Filter:
        • Include previous retail year? = Yes
    • Time period: All time
    • Interval: None
    • Group by: Created_at (retail month)
    • Chart Type: Line

After compiling all the reports, you can organize them on the dashboard as you desire. The end result may look like the above sample dashboard.

If you run into any questions while building this analysis, or simply want to engage our Professional Services Team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk