DocumentationCommerceCommerce Intelligence User Guide

Reporting over Time Periods

Last update: Mon Jul 24 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • Leader
  • User
NOTE
This topic contains instructions for clients that are using the original architecture and new architecture. You are on the new architecture if you have the Data Warehouse Views section available after selecting Manage Data from the main toolbar.

The report builder allows you to easily see trends over time and change perspective for time periods you may want to compare. This topic demonstrates how to set up a dashboard to go a level deeper to allow you to create reports for week over week, month over month and year over year analysis.

Before getting started, you should review explore perspectives in more detail here and independent time options here.

This analysis contains advanced calculated columns.

Calculated Columns

  • Sales_flat_order table

  • Original architecture: the below columns are created by an analyst as part of your [YoY WoW MoM ANALYSIS] ticket

  • created_at (month-day)

  • created_at (month)

  • created_at (day of the month)

  • created_at (day of the week)

  • created_at (hour of the day)

  • New architecture: SQL listed below with a photo of an example for how to create this calculation

    • created_at (month-day) Calculation: to_char(A, ‘mm-dd’)
    • created_at (month) Calculation: to_char(A, ‘mm-month’)
    • created_at (day of the month)< Calculation: to_char(A, ‘dd’)
    • created_at (day of the week) Calculation: to_char(A, ‘d-Day’)
    • **created_at (hour of the day) Calculation: to_char(A, ‘hh24’)

Metrics

None.

NOTE
Make sure to add all new columns as dimensions to metrics before building new reports.

Reports

  • YoY chart

    • Metric: Number of orders

    • Metric: Number of orders

    • Time options: Time range (Custom): 2 years ago to 1 year ago

    • Show top/bottom: Top 100% sorted by created_at (month-day)*

  • Metric A: This year

  • Metric B: Last year

  • Time period: 1 year ago to 0 years ago

  • Interval: None

  • Group by: created_at (month-day)

  • Chart Type: Line

  • MoM chart

    • Metric: Number of orders

    • Metric: Number of orders

    • Time options: Time range (Custom): 2 months ago to 1 month ago

    • Show top/bottom: Top 100% sorted by created_at (day of month)*

  • Metric A: This month*

  • Metric B: Last month*

  • Time period: one month ago to 0 months ago

  • Interval: None

  • Group by: created_at (day of month)

  • Chart Type: Line

  • WoW chart

    • Metric: Number of orders

    • Metric: Number of orders

    • Time options: Time range (Custom): 2 weeks ago to 1 week ago

    • Show top/bottom: Top 100% sorted by created_at (day of week)

  • Metric A: This week

  • Metric B: Last week

  • Time period: 1 week ago to 0 weeks ago

  • Interval: None

  • Group by: created_at (day of week)

  • Chart Type: Line

  • DoD chart

    • Metric: Number of orders

    • Metric: Number of orders

    • Time options: Time range (Custom): 2 days ago to 1 day ago

    • Show top/bottom: Top 100% sorted by created_at (hour of day)

  • Metric A: Today

  • Metric B: Yesterday

  • Time period: 1 day ago to 0 days ago

  • Interval: None

  • Group by: created_at (hour of day)

  • Chart Type: Line

After compiling all the reports, you can organize them on the dashboard as you desire. The result may look like the image at the top of this page.

recommendation-more-help
e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc