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) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Is order in last 182 days? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
  • customer_entity table
    • [INPUT] created_at (yyyy-mm-dd 00:00:00) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Is creatd in last 182 days? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
  • Retail calendar file upload table
    • Current date will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Current retail year will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Current retail quarter will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Current retail month will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Current retail week will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in current retail year? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in current retail quarter? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in current retail month? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in current retail week? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in previous retail year? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in previous retail quarter? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in previous retail month? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
    • Included in previous retail week<? (Yes/No) will be created by an analyst as part of your [RETAIL CALENDAR ANALYSIS] ticket
  • sales_flat_order table
    • Created_at (retail year)
      • Select a definition: Joined Column
      • Create Path:
        • Many: sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00)
        • One: Retail Calendar.Date
      • Select table: Retail Calendar
      • Select column: Retail calendar year
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Created_at (retail week)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Retail calendar fiscal week #
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Created_at (retail week # in retail month)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Week # in retail month
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Created_at (retail month)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Retail calendar month #
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Created_at (retail quarter)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Retail calendar quarter
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in previous retail year? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in previous retail year? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in previous retail quarter? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in previous retail quarter? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in previous retail month? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in previous retail month? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in previous retail week? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in previous retail week? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in current retail year? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in current retail year? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in current retail quarter? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in current retail quarter? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in current retail month? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in current retail month? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
    • Include in current retail week? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
      • Select table: Retail Calendar
      • Select column: Include in current retail week? (Yes/No)
      • sales_flat_order.[INPUT] created_at (yyyy-mm-dd 00:00:00) = Retail Calendar.Date
  • 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 desried

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