| Page Properties | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...
The following outlines details of each of the requested dashboard views (of which there are four in total), and should inform the development of the underlying data models to power the use case. Note that the analysis should be limited to TH Canada and Canadian Loyalty Guests.
View #1: Executive Summary - Key Digital & Loyalty Metrics
...
2. [End Date] A feature to allow the user to select an end date of the analysis, expressed in the format yyyy/mm/dd. This will represent the end date of the date range being examined by the user.
This date range - along with all others across the dashboard views - should always default to YTD, unless specifically noted otherwise.
Callout Metrics: this first dashboard view should present users with a series of callout metrics that summarize the performance & health of Tim Hortons Digital & Loyalty during users' selected reporting ranges, as defined by [Start Date] and [End Date]:
...
System-wide Sales (SWS): all sales across the Tim Hortons system Canada for the dashboard user’s selected time period; expressed as a nominal dollar amount.
...
Input range for MAU has to be at least one month
...
. If a user selects a date range less than one full calendar month, this call-out should default to “N/A”. Where a user selects a date range across multiple calendar months, this call-out should be the average of the month’s selected. By default, this figure should be the YTD average, but only for full calendar months. For example, if the current date is November 10th, the default date range for this calculation should be limited to January to October. This is to prevent the incomplete current month from pulling down the average.
Purchasing Known Diners: cumulative count of Known Diners (guests that have registered through the Tim Hortons app) that have made a purchase within the dashboard user’s selected time period; expressed as a nominal amount.
...
Average Known Diner Frequency: average visits made by purchasing guests that have registered through the Tim Hortons app within the dashboard user’s selected time period; expressed as a nominal amount.
...
In addition to callout metrics, this view should also feature an option for users to to filter for one specific metric and display it across each province in a map visualization. For example, if the user selects Known Diner Sales Penetration, the map should populate each province with a percentage representing the proportion of Known Diner Sales that each province accounts for (e.g., Ontario accounts for 32.3% of Canada’s KDS). This is done by taking the selected metric’s value for each province and dividing it by the value for Canada (e.g., Ontario KDS / Canada KDS).
...
2. [End Date] A feature to allow the user to select an end date of the analysis, expressed in the format yyyy/mm/dd. This will represent the end date of the date range being examined by the user.
This date range - along with all others across the dashboard views - should always default to YTD, unless specifically noted otherwise.
Callout Metrics: this first dashboard view should present users with a series of callout metrics that summarize the performance of the Tim Hortons loyalty program and guest engagement during the user’s selected reporting range, as defined by [Start Date] and [End Date]:
...
30 Day Active Guests: cumulative count of guests who have visited at least once in less than 30 days, within the dashboard user’s selected time periodthe last 30 days. Note that this value is fixed based on current date, and should not dynamically change based on user input of [Start Date] and [End Date].
Time Series 1 of 5: KDS Penetration Over Time
The first time-series chart, KDS Penetration Over Time, is intended to illustrate, using superimposed line charts, the evolution of KDS penetration throughout the dashboard user’s selected time period, compared to past years over the same time period.
Within this chart, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly [fiscal], monthly, quarterly, annually).
...
1) Registered loyalty guests: guests who participate in the Tim Horton’s loyalty program and have registered an account through the app, typically identified when registered_account_id contains ‘us-east’.
2) Unregistered loyalty guests: guests who participate in the Tim Horton’s loyalty program who have not registered an account through the app, typically identified when registered_account_id is null, while loyalty_customer_id begins with ‘046’.
3) Non-loyalty guests: guests who do not participate in the Tim Horton’s loyalty program.
Cheque values will be represented by three different coloured lines corresponding to the three different classes cohorts of guests. Frequency values will be represented by two different coloured bars corresponding to registered loyalty guests and unregistered loyalty guests respectively. Two separate y-vertical axes will be featured to display both cheque and frequency values on the same graph. Note that frequency values cannot be computed for non-loyalty guests, since these guests cannot be individually identified and thus are not able to have their number of visits recorded.
...
Similar to the previous chart, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly, monthly, quarterly). Total guests, comprising all three types of guests, for each cadence will form one bar in the bar chart. For example, if the user inputted their time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “monthly”, comping guests in January 2023 would be the number of guests who made a purchase both in January 2022 and January 2023; non-comping guests would be the number of guests who did not make a purchase in January 2022 but did make one in January 2023; non-returning guests would be the number of guests who made a purchase in January 2022 but did not make one in January 2023. The count of comping and non-comping guests would accumulate to form the positive portion of the bar for January, while non-returning guests would be represented as the negative portion of the January bar. This would be repeated to create a bar for each month from January to October 2023. Similar to the previous chart, this chart will also feature the average of comping, non-comping, and non-returning guests for January to October 2023 in comparison to the average of comping, non-comping, and non-returning guests for the same time period in the previous year.
...
This will include kiosk sales, Mobile Order & Payment (MO&P) sales, delivery sales, catering sales, Outdoor Digital Menu Board (ODMB) sales, Scan & Pay sales(?), total digital sales (sum of all digital channels), as well as restaurant POS and Drive Thru sales (sum of all non-digital sales). The percentages for each of these would just be computed as the total sales for that service mode/payment method within the [Start Date] and [End Date], divided by the total system-wide sales within the [Start Date] and [End Date].
...
Note, Sales per Restaurant per Day: cumulative sales made by the specified digital ordering channel within the specified reporting cadence (e.g., for each month, if specified cadence is monthly), divided by number of restaurants that actively operate the digital ordering channel, per day.
...
Discounting views
Points Issued per KDS $ spent
Net discounting
Data Engineering will
Translate requirements into a plan with engineering activities to meet due date.
Complete the data governance requirements for the project (such as availing metadata such as a data dictionary for users)
Provide support and ongoing maintenance of the data in Databricks to ensure the data continues to meet the requirements defined herein.
Risks
Project timeline overrun.