| Page Properties | Jira Initiative | ||||||
|---|---|---|---|---|---|---|---|
Document Status |
| ||||||
Created On |
| Due Date | Document Owner |
Project Team
...
Digital Loyalty
🌎 Project Overview
The aim of this project is to develop a dashboard that illustrates the high-level performance of Tim Horton’s digital/loyalty program. Currently, executive leadership across Tim Horton’s seeks insights on digital health metrics at a higher frequency than the Digital & Loyalty Analytics team currently provides to them. Furthermore, the existing process to provide these metrics ad hoc requires significant manual intervention by the analytics team. Thus, the dashboard should display key metrics that the digital team already tracks, in one location. The end-users of the dashboard are anticipated to be high-level executives, who will monitor essential business metrics to effectively guide business activities, as well as members of the Digital Loyalty Analytics team. However, based on the rate of uptake, users across the broader organization may utilize the dashboard on an ad hoc basis.
✍️ Requirements
The following outlines details of each of the requested the dashboard views (of which there are four in total), and should inform the development of the underlying data models to power the use case.
View #1: Executive Summary - Key Digital & Loyalty Metrics
...
As depicted in the illustrative sample view above, this dashboard view should include:
User Inputs: Dashboard users will be able to input their desired reporting range by selecting the following:
1. [Start Date] A feature to allow the user to select a start date of the analysis, expressed in the format yyyy/mm/dd. This will represent the start date of the date range being examined by the user.
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.
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 the user’s selected reporting range, as defined by [Start Date] and [End Date]:
Known Diner Sales Penetration: cumulative Known Diner Sales (all sales that are made by guests that have registered through the Tim Hortons app), expressed as a percentage of cumulative system-wide sales for the dashboard user’s selected time period. This metric is used as a baseline to see loyalty sales penetration to our system-wide sales, and is a function of the following two metrics (KDS and SWS).
Known Diner Sales: (KDS) cumulative Known Diner Sales (all sales that are made by guests that have registered through the Tim Hortons app) for the dashboard user’s selected time period; expressed as a nominal dollar amount.
System-wide Sales (SWS): all sales across the Tim Hortons system for the dashboard user’s selected time period; expressed as a nominal dollar amount.
Average App Monthly Active Users (MAU): the count of distinct guests that visited the app within a month, where visit is app open or app launch, averaged across all the months within the dashboard user’s selected time period; expressed as a nominal amount of users. This metric is used to track amount of guests who visit the app.
Input range for MAU has to be at least one month
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 Cheque: average sales of an individual transaction across all known diners for the dashboard user’s selected time period; expressed as a dollar amount.
Average Known Diner Frequency: average visits made by guests that have registered through the Tim Hortons app within the dashboard user’s selected time period; expressed as a nominal amount.
Map Visualization: Metrics by Province
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, 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).
View #2: Loyalty Guest Engagement
...
As depicted in the illustrative sample view above, this dashboard view should include:
User Inputs: Dashboard users will be able to input their desired reporting range by selecting the following:
1. [Start Date] A feature to allow the user to select a start date of the analysis, expressed in the format yyyy/mm/dd. This will represent the start date of the date range being examined by the user.
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.
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]:
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.
KDS Penetration: cumulative Known Diner Sales (all sales that are made by guests that have registered through the Tim Hortons app), expressed as a percentage of cumulative system-wide sales for the dashboard user’s selected time period
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 period
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, monthly, quarterly, annually).
Time Series 2 of 5: Loyalty Cheque and Frequency Over Time
...
1) Registered loyalty guests: guests who participate in the Tim Horton’s loyalty program and have registered an account through the app.
2) Unregistered loyalty guests: guests who participate in the Tim Horton’s loyalty program who have not registered an account through the app.
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 of guests. Frequency values will be represented by two different coloured bars corresponding to registered loyalty guests and unregistered loyalty guests. Two separate y-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, annually). Cheque and frequency values for each guest type will be averaged for each reporting cadence within the user’s selected time period. For example, if the user inputted the time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “monthly”, the lines and bars would represent average cheque and frequency amounts for January through October 2023. In addition to periodic averages, the chart will also feature the average cheque and frequency for the user’s selected time period in comparison to the average cheque and frequency for the same time period in the previous year.
Within this chart, users should be able to filter for the different classes of guests using a multi-select option. Users can select just one, two, or all three guest classes to view. For example, if the user is only interested in looking at loyalty guests, they would toggle on “Registered Loyalty” and “Unregistered Loyalty” to view only those respective bars and lines, without seeing the information for non-loyalty guests.
Time Series 3 of 5: First Time Loyalty Scans Over Time
Note: First Time Loyalty Scans: count of guests who have scanned the QR code or barcode associated with their loyalty account for the first time ever
The third time-series chart, First Time Loyalty Scans Over Time, is intended to illustrate the changes in number of first time loyalty scans in each reporting cadence (e.g., first loyalty scan in a given week, month, year, etc.) of the dashboard user’s selected time period. The cumulative number of first time scans within each reporting cadence will be represented as bars. For example, if the user inputted the time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “monthly”, the bars would show the cumulative first time loyalty scans that occurred in each month, from January through October 2023.
Similar to the previous time series chart, this chart will also feature the average of first time loyalty scans for the user’s selected time period in comparison to the average of first time loyalty scans for the same time period in the previous year.
Time Series 4 of 5: New vs. Existing Guest Cheque and Frequency Over Time
The fourth time-series chart, New vs. Existing Guest Cheque and Frequency Over Time, is intended to illustrate the evolution of respective average cheque and frequency amounts of two types of guests:
New guest: guest who made their first purchase ever in the specific reporting cadence.
Existing guest: guest who made their first purchase ever before the specific reporting cadence.
Similar to the second time series chart, cheque values will be represented by two different coloured lines corresponding to the two types of guests. Frequency values will be represented by two different coloured bars corresponding to the two types of guests. Two separate y-axes will be featured to display both cheque and frequency values on the same graph. Again, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly, monthly, quarterly, annually). Cheque and frequency values for each guest type will be averaged for each reporting cadence within the user’s selected time period. For example, if the user inputted the time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “monthly”, the lines and bars would represent average cheque and frequency amounts for January through October 2023. In addition to periodic averages, the chart will also feature the average cheque and frequency for the user’s selected time period in comparison to the average cheque and frequency for the same time period in the previous year.
Time Series 5 of 5: Same Customer Stats Over Time
The fifth time series, Same Customer Stats Over Time, is intended to show the cumulative number of customers within a specified reporting cadence (e.g., week, month) that fall into the following categories, using a stacked bar chart:
Comping: customers who made a purchase during the specified cadence (e.g., during each month if the specified cadence is monthly) during the user’s selected reporting year and the previous year; expressed as a positive nominal amount (positive bar).
Non-comping: customers who made a purchase during the specified cadence during the user’s selected reporting year but not in the same cadence in the previous year; expressed as a positive nominal amount (positive bar).
Non-returning: customers who did not make a purchase during the specified cadence during the user’s selected reporting year but did make a purchase in the same cadence of the previous year; expressed as a negative nominal amount (negative bar).
...
| Page Properties | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Project Team
Digital Loyalty | |
|---|---|
Engineering |
🌎 Project Overview
The aim of this project is to develop a dashboard that illustrates the high-level performance of Tim Horton’s digital/loyalty program. Currently, executive leadership across Tim Horton’s seeks insights on digital health metrics at a higher frequency than the Digital & Loyalty Analytics team currently provides to them. Furthermore, the existing process to provide these metrics ad hoc requires significant manual intervention by the analytics team. Thus, the dashboard should display key metrics that the digital team already tracks, in one location. The end-users of the dashboard are anticipated to be high-level executives, who will monitor essential business metrics to effectively guide business activities, as well as members of the Digital Loyalty Analytics team and will be displayed on the Digital & Loyalty screens (therefore dashboard layout, perspective & dashboard should align to screen dimensions). However, based on the rate of uptake, users across the broader organization may utilize the dashboard on an ad hoc basis.
✍️ Requirements
The following outlines details of each of the requested dashboard views (of which there are c.6 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
...
As depicted in the illustrative sample view above, this dashboard view should include:
User Inputs: Dashboard users will be able to input their desired reporting range by selecting the following:
1. [Start Date] A feature to allow the user to select a start date of the analysis, expressed in the format yyyy/mm/dd. This will represent the start date of the date range being examined by the user.
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]. Each callout metric will also be accompanied by the previous year’s metric for the same time period to provide a benchmark for progress (as shown by the “PY” figures beneath each metric). For example, where a user inputs Start and End Dates of 20-Jan-2020 & 29-Feb-2020, the PY values should display the values for the periods 1-year earlier, i.e. 20-Jan-2019 & 28-Feb-2019 (take note of leap years).
Known Diner Sales Penetration: cumulative Known Diner Sales (all sales that are made by guests that have registered through the Tim Hortons app), expressed as a percentage of cumulative system-wide sales ($) for the dashboard user’s selected time period. This metric is used as a baseline to see loyalty sales penetration against system-wide sales, and is a function of the following two metrics (KDS and SWS).
Known Diner Sales: (KDS) cumulative Known Diner Sales (all sales that are made by guests that have registered through the Tim Hortons app) for the dashboard user’s selected time period; expressed as a nominal dollar amount ($).
System-wide Sales (SWS): all sales across the Tim Hortons Canada for the dashboard user’s selected time period; expressed as a nominal dollar amount.
Average App Monthly Active Users (MAU): the count of distinct guests that visited the app within a month, where visit is app open or app launch, averaged across all the months within the dashboard user’s selected time period; expressed as a nominal amount of users. This metric is used to track amount of guests who visit the app.
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.
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. These guests are typically identified where registered_account_id begins with “us-east”.
Average Known Diner Cheque: average sales of an individual transaction across all known diners for the dashboard user’s selected time period; expressed as a dollar amount.
Average Known Diner Cheque = Total Known Diner Sales / Count of Known Diner Transactions (Tickets)
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.
Average Known Diner Frequency = Count of Known Diner Transactions (Tickets) / Count of Known Diners
Map Visualization: Metrics by Province
In addition to callout metrics, this view should also feature an option for users 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).
View #2: Loyalty Guest Engagement
...
As depicted in the illustrative sample view above, this dashboard view should include:
User Inputs: Dashboard users will be able to input their desired reporting range by selecting the following:
1. [Start Date] A feature to allow the user to select a start date of the analysis, expressed in the format yyyy/mm/dd. This will represent the start date of the date range being examined by the user.
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]:
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.
KDS Penetration: cumulative Known Diner Sales (all sales that are made by guests that have registered through the Tim Hortons app), expressed as a percentage of cumulative system-wide sales for the dashboard user’s selected time period
30 Day Active Guests: cumulative count of guests who have visited at least once in the 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. Each line represents a different year’s KDS over the user’s selected reporting range. 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). For example, if the user inputted the time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “weekly”, the chart would show weekly KDS penetration overtime for January to October, in 2021, 2022, and 2023.
Time Series 2 of 5: Loyalty Cheque and Frequency Over Time
The second time-series chart, Loyalty Cheque and Frequency Over Time,is intended to illustrate the evolution of respective average cheque and frequency amounts of three classes of guests:
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 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 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 [fiscal], monthly, quarterly, annually). Cheque and frequency values for each guest type will be averaged for each reporting cadence within the user’s selected time period. For example, if the user inputted the time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “monthly”, the lines and bars would represent average cheque and frequency amounts for January through October 2023. In addition to periodic averages, the chart will also feature the average cheque and frequency for the user’s selected time period in comparison to the average cheque and frequency for the same time period in the previous year.
Within this chart, users should be able to filter for the different classes of guests using a multi-select option. Users can select just one, two, or all three guest classes to view. For example, if the user is only interested in looking at loyalty guests, they would toggle on “Registered Loyalty” and “Unregistered Loyalty” to view only those respective bars and lines, without seeing the information for non-loyalty guests.
Time Series 3 of 5: First Time Loyalty Scans Over Time
Note: First Time Loyalty Scans: count of guests who have scanned the QR code or barcode associated with their loyalty account for the first time ever
The third time-series chart, First Time Loyalty Scans Over Time, is intended to illustrate the changes in number of first time loyalty scans in each reporting cadence (e.g., first loyalty scan in a given week, month, year, etc.) of the dashboard user’s selected time period. The cumulative number of first time scans within each reporting cadence will be represented as bars. For example, if the user inputted the time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “monthly”, the bars would show the cumulative first time loyalty scans that occurred in each month, from January through October 2023.
Similar to the previous time series chart, this chart will also feature the average of first time loyalty scans for the user’s selected time period in comparison to the average of first time loyalty scans for the same time period in the previous year.
Time Series 4 of 5: New vs. Existing Guest Cheque and Frequency Over Time
The fourth time-series chart, New vs. Existing Guest Cheque and Frequency Over Time, is intended to illustrate the evolution of respective average cheque and frequency amounts of two types of guests:
New guest: guest who made their first purchase ever in the specific reporting cadence.
Existing guest: guest who made their first purchase ever before the specific reporting cadence.
Similar to the second time series chart, cheque values will be represented by two different coloured lines corresponding to the two types of guests. Frequency values will be represented by two different coloured bars corresponding to the two types of guests. Two separate y-axes will be featured to display both cheque and frequency values on the same graph. Again, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly, monthly, quarterly, annually). Cheque and frequency values for each guest type will be averaged for each reporting cadence within the user’s selected time period. For example, if the user inputted the time range as [Start Date] 2023/01/01 to [End Date] 2023/10/01 and drilled down the reporting cadence to “monthly”, the lines and bars would represent average cheque and frequency amounts for January through October 2023. In addition to periodic averages, the chart will also feature the average cheque and frequency for the user’s selected time period in comparison to the average cheque and frequency for the same time period in the previous year.
Time Series 5 of 5: Same Customer Stats Over Time
The fifth time series, Same Customer Stats Over Time, is intended to show the cumulative number of customers within a specified reporting cadence (e.g., week, month) that fall into the following categories, using a stacked bar chart:
Comping: customers who made a purchase during the specified cadence (e.g., during each month if the specified cadence is monthly) during the user’s selected reporting year and the previous year; expressed as a positive nominal amount (positive bar).
Non-comping: customers who made a purchase during the specified cadence during the user’s selected reporting year but not in the same cadence in the previous year; expressed as a positive nominal amount (positive bar).
Non-returning: customers who did not make a purchase during the specified cadence during the user’s selected reporting year but did make a purchase in the same cadence of the previous year; expressed as a negative nominal amount (negative bar).
Purchasing Gusts | Previous Year | Current Year |
|---|---|---|
Comping | Yes | Yes |
Non-Comping | No | Yes |
Non-Returning | Yes | No |
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 time period within the selected 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.
View #3: Digital Ordering & Payment
...
As depicted in the illustrative sample view above, this dashboard view should include:
User Inputs: Dashboard users will be able to input their desired reporting range by selecting the following:
1. [Start Date] A feature to allow the user to select a start date of the analysis, expressed in the format yyyy/mm/dd. This will represent the start date of the date range being examined by the user.
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 show users the respective sales of each digital ordering or payment method, represented as a percentage of system-wide sales, during the user’s selected reporting range, as defined by [Start Date] and [End Date].
This will include:
Kiosk sales as % of SWS, where
kiosk sales is the sum of sales where service_mode_cd in ('KIOSK', ‘KIOSK TAKEOUT’, ‘KIOSK EATIN’)
Mobile Order & Payment (MO&P) sales as % of SWS, where
MO&P sales is the sum of sales whereservice_mode_cd in ('MOBILE ORDER DRIVE THRU', 'MOBILE ORDER EAT IN', 'MOBILE ORDER TAKE OUT')
Delivery sales (3P and white label) sales as % of SWS, where
delivery sales is the sum of sales whereservice_mode_cd in ('DELIVERY', ‘WHITE LABEL DELIVERY’, ‘THIRD PARTY DELIVERY’)
Catering sales as % of SWS, where
catering sales is the sum of sales wherediningtype = ‘CT’ (from PRODRT.CURATED_TRANS_EVENTS_NEW)
Scan & Pay sales as % of SWS, where
Scan & Pay sales is the sum of sales where SCANANDPAY = ‘TRUE’ (from PRODRT.CURATED_TRANS_EVENTS_NEW)
Total digital ordering sales (sum of all digital channels) as a % of SWS, where
total digital ordering sales is the sum of sales from all the above service modes
In-Restaurant and Drive Thru sales (sum of all non-digital sales) as a % of SWS, where
In-restaurant and Drive Thru sales is the sum of all sales where service_mode_cd in ('TAKEOUT', ‘DRIVETHRU’, ‘EATIN’)
The percentages for each of these would just be computed as the total sales for that service mode within the [Start Date] and [End Date], divided by the total system-wide sales within the [Start Date] and [End Date].
There should also be callouts for Scan & Pay Loyalty Penetration (sum of Scan & Pay sales as a percentage of all loyalty sales), where Scan & Pay sales is the same as the previously-used definition above, and loyalty sales is the sum of all sales where LEFT(LOYALTY_CUSTOMER_ID,3) = '046'.
Time Series 1 of 3: Digital Ordering Channel Sales (% of SWS) Over Time
The first time series in this view, Digital Ordering Channel Sales (% of SWS) Over Time, is intended to illustrate the sales penetration of systemwide sales for each digital ordering channel over the user’s selected time range. This data will be represented as a stacked bar chart, where each portion of the stacked bar corresponds to the average sales as a % of systemwide sales for each digital ordering channel. The sum of the sales as a % of systemwide sales of each digital ordering channel should amount to the total digital sales as a % of systemwide sales, which is represented by the value of the entire bar.
Similar to the other charts, users will have the option to drill up or down on the reporting cadence (e.g., daily, weekly, monthly, quarterly, annually); average CY YTD data will also be featured in a bar at the end, as pictured in the sample view.
Time Series 2 of 3: Ordering Channel Cheque Comparison Over Time
The second time series, Service Mode Cheque Comparison Over Time, is intended to compare the average cheque amount across all digital ordering channels over time using a clustered bar chart. Similar to the other charts, users will 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.
View #3: Digital Ordering & Payment
...
, annually). The x-axis will display the days/weeks/months of the user’s selected reporting range, depending on which reporting cadence that the user drills down to. Average cheque for each ordering channel will be represented by a bar in the cluster. This chart will also feature a multi-select option, allowing the user to select the specific ordering channels they want to view (from Drive-Thru, In-restaurant, MO&P, Kiosks, Delivery, Catering, ODMB)
Time Series 3 of 3: Loyalty Penetration by Ordering Channel Over Time
Note, Loyalty Penetration: count of transactions made by a guest with a loyalty account, as a percentage of the count of total transactions.
The third time series, Loyalty Penetration by Service Mode Over Time, is intended to illustrate the evolution of loyalty penetration in each digital ordering channel over the course of the user’s selected time range. For example, loyalty penetration within delivery would be represented as the count of transactions made through the delivery channel by a guest with a loyalty account, as a percentage of the count of total transactions made through the delivery channel. Loyalty penetration will be represented as a series of superimposed line charts, with each line corresponding to a different channel. Again, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly, monthly, quarterly, annually).
View #4: Offers & Points
...
As depicted in the illustrative sample view above, this dashboard view should include:
...
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 userthe 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 show users the respective sales of each digital ordering or payment method, represented as a percentage of system-wide sales, series of callout metrics that summarize the performance of the Tim Hortons offers and rewards program during the user’s selected reporting range, as defined by [Start Date] and [End Date].
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].
Time Series 1 of 3: Digital Ordering Sales Over Time
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.
The first time series in this view, Digital Ordering Sales Over Time, is intended to illustrate the sales per restaurant per day for each digital ordering channel over the user’s selected time range, as a stacked bar chart, where each portion of the stacked bar corresponds to the sales of each digital ordering channel.
Time Series 2 of 3: Service Mode Cheque Comparison Over Time
The second time series, Service Mode Cheque Comparison Over Time, is intended to illustrate
Time Series 3 of 3: Loyalty Penetration by Service Mode Over Time
View #4: Offers & Points
As depicted in the illustrative sample view above, this dashboard view should include:
User Inputs: Dashboard users will be able to input their desired reporting range by selecting the following:
1. [Start Date] A feature to allow the user to select a start date of the analysis, expressed in the format yyyy/mm/dd. This will represent the start date of the date range being examined by the user.
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.
Callout Metrics: this first dashboard view should show users series of callout metrics that summarize the performance of the Tim Hortons offers and rewards program during the user’s selected reporting range, as defined by [Start Date] and [End Date].
Unique Guests who Received Offers:
Unique Purchasing Guests:
Unique Guests who Activated Offers:
Unique Offer-Using Guests:
Purchasing Guest Offer Penetration:
Time Series 1 of 3: Top 10 Offers (by Sends/Activation Rate/Redemption Rate)
Time Series 2 of 3: Points Issued per Known Diner $ Over Time
Time Series 3 of 3: Guest x Offer Interaction Over Time
KPIs:
RRAMI: Restaurants Reporting Any Menu Item
Just show number (no chart)
...
Week start date
...
Fiscal Year
...
Fiscal Week
...
Province
...
RRAMI
...
yyyy/dd/mm
...
yyyy
...
xx
...
xxxxx
Loyalty Redemptions: products that were redeemed using loyalty points
Used for tracking how many free items we’re giving away and the value of them.
Often viewed as a percentage of SWS or KDS.
Organized by tier
Stacked bar chart
...
Week start date
...
Fiscal Year
...
Fiscal Week
...
yyyy/dd/mm
...
yyyy
...
xx
Loyalty Cheque: average sales of an individual transaction by a loyalty program member
...
Week start date
...
Fiscal Year
...
Fiscal Week
...
Province
...
Registered loyalty sales
...
Registered loyalty tickets
...
Non-loyalty sales
...
Non-loyalty tickets
...
Registered loyalty cheque
...
Unregistered loyalty cheque
...
Non-loyalty cheque
...
System cheque
...
yyyy/dd/mm
...
yyyy
...
xx
...
$
...
$
...
$
Loyalty Frequency: average loyalty guest visits in a time period
...
Week start date
...
Fiscal Year
...
Fiscal Week
...
Province
...
Registered guests
...
Registered tickets
...
Registered loyalty frequency
...
Unregistered loyalty tickets
...
Unregistered loyalty frequency
...
System frequency
...
yyyy/dd/mm
...
yyyy
...
xx
KDS: all sales that are made by recognizable guests, i.e. guests that have registered through the app
^map based on this
...
Week start date
...
Fiscal Year
...
Fiscal Week
...
Province
...
Known Diner Sales (Nominal)
...
SWS
...
KDS Penetration
...
yyyy/dd/mm
...
yyyy
...
xx
...
$
...
$
...
%
Month to Date (MTD) MAU: Monthly Active Users; distinct guests that visited the app, where visit is app open or app launch
DAU: number of unique guests that have visited in the given day (partition date key), where visit is app open or app launch
MAU MTD: cumulative unique guests per month
Incremental DAU: number of unique guests that visited the app in the given day for the first time in the given month
...
Partition date key
...
Year
...
Month
...
DAU (Daily Active Users)
...
MAU MTD
...
Incremental DAU
...
yyyy/dd/mm
...
xx
...
xx
Daily S&P penetration
Same as existing dashboard, exclude new users by day, show fewer days
Show the past 7 days and the latest weekly number
Loyalty Penetration %: A/B
Systemwide Penetration %: A/C
Daily S&P guests: count of people who have made a purchase using S&P in the given day
Weekly S&P guests: count of people who have made a purchase using S&P in the given week
S&P toggled on: cumulative count of people who have S&P toggled on by the given day
Systemwide tickets: all transactions
...
Partition date key
...
Fiscal year
...
Fiscal week
...
Count of Scan & Pay transactions > $0 (A)
...
Count of registered transactions > $0 and are not MO&P or delivery (B)
...
Systemwide tickets (C)
...
Loyalty Penetration %
...
Systemwide Penetration %
...
Daily S&P guests
...
Weekly S&P guests
...
S&P toggled on
...
yyyy/dd/mm
...
yyyy
...
xx
...
xx
S&P Deep Dive
Guest Impact (cheque & frequency)
First Time Scans over time
Turn Offs
Same customer stats
Digital sales % (of SWS): total of all digital sales (MOP, delivery, catering, kiosks), including both registered and unregistered loyalty guests, as a percentage of total system-wide sales
add province
...
Date
...
Fiscal Year
...
Fiscal Week
...
Province
...
Digital Sales (Nominal)
...
SWS
...
Digital Sales Penetration
...
yyyy/dd/mm
...
yyyy
...
xx
...
$
...
$
...
%
Digital ordering channels
Kiosks, MOP, delivery, ODMB
Nominal tickets
Stacked bar chart
*define digital tickets
...
Date
...
Fiscal Year
...
Fiscal Week
...
Kiosk tickets
...
MO&P tickets
...
Delivery tickets
...
Catering tickets
...
ODMB tickets
...
Digital tickets
...
Systemwide tickets
...
yyyy/dd/mm
...
yyyy
...
xx
Digital Ordering vs. In-Restaurant & Drive-Thru
...
Date
...
Fiscal Year
...
Fiscal Week
...
Digital Sales
...
Digital Tickets
...
Digital Cheque
...
In-Restaurant Sales
...
In-Restaurant Tickets
...
In-Restaurant Cheque
...
Drive Thru Sales
...
Drive Thru Tickets
...
Drive Thru Cheue
...
yyyy/dd/mm
...
yyyy
...
xx
Loyalty Penetration by Service Mode
...
Date
...
Fiscal Year
...
Fiscal Week
...
Kiosk tickets
...
MO&P tickets
...
Delivery tickets
...
Catering tickets
...
ODMB tickets
...
Digital tickets
...
Systemwide tickets
...
yyyy/dd/mm
...
yyyy
...
xx
Top Offers
Top 4 offers redeemed in week (nominally)
Top 4 offers by redemption rate
Bar chart
...
Fiscal Year
...
Fiscal Week
...
Offer
...
Offers sent
...
Offers redeemed
...
Redemption rate
...
yyyy
...
xx
...
%
Unique Loyalty Accounts: count of unique registered loyalty accounts
...
Fiscal Year
...
Month
...
Registered Loyalty Accounts
...
yyyy
...
xx
Spend/Guest: the average amount of money that loyalty guests spend within a defined date range
...
Date
...
Fiscal Year
...
Fiscal Week
...
Registered Loyalty Sales
...
Registered Loyalty Guests
...
Registered Loyalty Spend
...
yyyy/dd/mm
...
yyyy
...
xx
Discounting views
Points Issued per KDS $ spent
Net discounting
...
, as defined by [Start Date] and [End Date]:
Unique Guests who Received Offers: count of registered loyalty guests who received at least one offer within the user’s selected reporting range
Unique Purchasing Guests: count of registered loyalty guests who made at least one purchase within the user’s selected reporting range
Unique Guests who Activated Offers: count of registered loyalty guests who activated at least one offer within the user’s selected reporting range
Unique Offer-Using Guests: count of registered loyalty guests who redeemed at least one offer on a purchase within the selected reporting range
Purchasing Guest Offer Penetration: count of offer-using guests as a percentage of the total count of purchasing guests
Time Series 1 of 3: Top 10 Offers (by Sends/Activation Rate/Redemption Rate)
In this dashboard view, the first time series, Top 10 Offers (by Sends/Activation Rate/Redemption Rate), is intended to show the user the top 10 offers for their selected reporting range, ordered either by number of sends, activation rate, or redemption rate, depending on which one the user chooses to filter the chart by. The top 10 offers will be listed on the x-axis (e.g., $3 for 2 Medium Iced Coffees), with each bar representing the number of sends, activation rate, or redemption rate, according to the applied filter. This chart will not feature the option to drill up or down on the reporting cadence; instead, it will just show the total offers sent, activation rates, or redemption rates for each offer within the user’s selected reporting range.
Time Series 2 of 3: Points Issued per Known Diner $ Over Time
The second time series, Points Issued per Known Diner $ Over Time, is intended to demonstrate loyalty drag over time using a bar chart displaying the number of points that are issued within the user’s selected reporting range, divided by the total amount of known diner sales (sales made by a registered loyalty guest) in that same time period. Similar to the previous chart, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly [fiscal], monthly, quarterly, annually).
Time Series 3 of 3: Guest x Offer Interaction Over Time
The third time series, Guest x Offer Interaction Over Time, is essentially intended to provide a more thorough view of the main callouts on the dashboard, by showing the evolution of offers sent, offers activated, and offers redeemed over time. Similar to the previous chart, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly [fiscal], monthly, quarterly, annually). This chart will feature a line overlaying a clustered bar chart; one of the bars will represent guests that were sent offers (the total count of guests who had at least one offer sent to them within the user’s selected reporting range); another bar will represent guests who activated offers (the total count of guests who activated at least one offer within the user’s selected reporting range); the last bar will represent guests who redeemed offers (the total count of guests who redeemed at least one offer within the user’s selected reporting range); the line will represent the number of guests who redeemed at least one offer, as a percentage of the number of guests who made a purchase within the user’s selected reporting range.
The chart will include two separate y-axes for the bars and the line, and the x-axis will represent the cadence that the user chooses to drill down to. 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”, the bars will show the number of guests who were sent at least one offer, activated at least one offer, and redeemed at least one offer in each month from January to October 2023. The line will show the percentage of guests who redeemed at least one offer out of the guests who made a purchase, for each month from January to October 2023.
View #5: Scan & Pay
...
As depicted in the illustrative sample view above, this dashboard view should include:
User Inputs: Dashboard users will be able to input their desired reporting range by selecting the following;
1. [Start Date] A feature to allow the user to select a start date of the analysis, expressed in the format yyyy/mm/dd. This will represent the start date of the date range being examined by the user.
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 should always default to 1-Oct-2022 to Current Date, unless specifically noted otherwise.
Time Series 1 of 4: Scan & Pay Penetration
The first time series, Scan & Pay Penetration, is intended to show the user the amount of Scan & Pay transactions that take place within their selected reporting range, as a percentage of all loyalty transactions and total systemwide transactions. This chart features a lines overlaying a bar chart, with the bars representing the cumulative count of Scan & Pay guests for a cadence that the user specifies (daily, weekly, monthly, etc.), and the lines representing Scan & Pay transactions as a percentage of loyalty transactions and Scan & Pay transactions as a percentage of systemwide transactions. Similar to the previous chart, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly [fiscal], monthly, quarterly, annually).
Time Series 2 of 4: Scan & Pay Impact on Guest Cheque and Frequency
The second time series, Scan & Pay Impact on Guest Cheque and Frequency, is intended to demonstrate how using Scan & Pay influences guest cheque and frequency over time. This will feature three superimposed line charts, with each line representing cheque lift, frequency lift, and spend lift that occurs 3 days, 7 days, 14 days, and 21 days after the guest first uses Scan & Pay.
Time Series 3 of 4: Scan & Pay Turn Offs
The third time series, Scan & Pay Turn Offs, is intended to show the evolution of Scan & Pay turn offs over time; that is, whether more people are toggling off the Scan & Pay feature in the Tim Hortons app over time. Similar to other charts, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly [fiscal], monthly, quarterly, annually). This chart will also feature bars representing the nominal number of Scan & Pay turn-offs for each cadence that the user drills down to (eg: each month, if they choose “monthly”) across the user’s selected reporting range. 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”, the bars will show the nominal number of Scan & Pay turn offs for each month from January to October 2023.
Time Series 4 of 4: Scan & Pay First Time Purchasers
Note, First Time Scan & Pay Purchaser: guest who is using Scan & Pay to make a purchase for the first time ever.
The fourth time series, Scan & Pay First Time Purchasers, is intended to show the evolution of Scan & Pay adoption over time; that is, whether more people are using Scan & Pay for the first time, overtime. Similar to the previous chart, users should have the option to drill up or down on the reporting cadence (e.g., daily, weekly [fiscal], monthly, quarterly, annually). This chart will also feature bars representing the nominal number of Scan & Pay first time purchasers for each cadence that the user drills down to (eg: each month, if they choose “monthly”) across the user’s selected reporting range. 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”, the bars will show the nominal number of guests who are using Scan & Pay to make a purchase for the first time ever in each month, from January to October 2023.
View #6: Digital Metrics
To follow
View #7: Loyalty Metrics
https://rbictg.atlassian.net/wiki/x/aYBmCQE
Technical Specification
To meet the objective of the initiative, Data Engineering will support with the following:
Translate requirements into a plan with engineering activities to meet due date.
Guest cohort is limited to digital loyalty guests with purchases in Canada, and should exclude charity related purchases.
Engage with project team to clarify any assumptions or initiative objectives, as well as to provide the project team with regular & ongoing updates on progress.
Complete the data governance requirements for the project (such as availing metadata such as a data dictionary for users).
Conduct the appropriate quality assurance (“QA”) activities to ensure metrics are accurate.
Provide support and ongoing maintenance of the data in Databricks to ensure the data continues to meet the requirements defined hereinmodels and dashboards.
...
⚠️ Risks
Project timeline overrun: as a high-priority project, with executive-level interest, adherence to the overall project timeline is the most significant risk of the project.
Inaccurate data: as the dashboard is anticipated to influence TH Digital & Loyalty decision-making, the accuracy of the data is paramount.