Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

Jira initiative

TBC

Document Status

DRAFT

Created On

Due Date

Document Owner

Rayand Ramlal

Project Team

🤝 Introduction

Through our Digital App, Tim Hortons (“TH”) gathers a significant amount of data on loyalty guests, including purchasing and traffic information. From a data analytics perspective, this data is predominantly utilized by the Digital Loyalty Analytics team. When examining the performance of new products following introduction to market, the Category Management team typically makes use of system-wide data and, using this, is able to analyze performance metrics based on these macro performance metrics. However, the lack of guest-level data prevents the wider organization, including Category Management, from gaining a deeper understanding of uptake of the new product, as guest level data is relatively inaccessible to these teams. For example, teams are unable to examine the critical measure of rates of repurchase of a product, as they are unable to track guest-level purchases. The availability of this data would allow Category to track guest level purchases, and importantly, repurchase behaviour.

In an effort to democratize this data and make it available for exploitation by the wider organization, the Digital Loyalty Analytics team has worked with Category Management to develop a use case for utilizing digital loyalty data to gain a deeper understanding of guest purchasing behaviour following the launch of new products to market.

🌎 Project Overview

The aim of this project is to develop a dashboard that illustrates the performance of new products during their launch to market across Canada. The dashboard should allow the user to select one or more input criteria, and the dashboard should illustrate high-level product performance metrics as well as time-series visualizations for various features (e.g. cheque and tickets). The end-users of the dashboard are anticipated to be colleagues within Category Management, 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

Develop a dashboard to be used by Category Management (as well as other end-users) to examine the performance of new products following their launch into market. The principle user story of the use case is as follows:

Title

Description

Priority

Notes

Analyze performance of new products following launch to market

A user selects: a product (level 4), an analysis start date, and an end date. The user is able to filter the entire dashboard based on Region and Restaurant(s).

MUST HAVE

This product should be the equivalent of level_4_platform from the loyalty.stg.derived_master_table_new table

A user is shown high level call-out metrics for the performance of the product during this date range chosen, and is shown the uptake of the product by loyalty guests.

MUST HAVE

A user is shown a weekly, time-series chart of loyalty guest: (i) cheque-level data, (ii) product purchases, and (iii) product repurchases.

MUST HAVE

A user is shown the proportion of product mixes at the start and end dates chosen for both guests who purchased the product, as well as overall loyalty.

MUST HAVE

🔬 Detailed Requirements

The following are the detailed requirements of the dashboard, and should inform the development of the underlying data models to power the use case:

A.1. User Inputs

  1. [Start Date] A feature to allow the user to select a start date of the analysis, which is a fiscal week. 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, which is a fiscal week. This will represent the end date of the date range being examined by the user.

  3. [Product Category] Four features representing product categories 1 to 4. This will represent the product being analyzed by the user on the dashboard. For this use case, the end user should only be able to select product category level 4 (“level_4_platform”), with product category levels 1, 2 & 3 being display only.

A.2. Filters

A user should be able to filter the entire dashboard based on the following metrics:

  1. [Region] The user should be able to filter for one or more provinces.

  2. [Restaurant]. The user should be able to filter for one or more restaurant numbers. This filter should allow the user to scroll through the available list of restaurants, and also, type in and multi-select restaurant numbers.

B. Call-out Metrics

The following call-out metrics should be displayed at the top of the dashboard and are used to give the user a summary of the product performance and guest uptake during the [Start Date] and [End Date]:

  1. [Total Guests] The total number of loyalty guests who have purchased the [Product Category] selected by the user during the [Start Date] and [End Date].

  2. [Total Units] The total quantity of units (i.e. [Product Category]) purchased by loyalty guests during the [Start Date] and [End Date] range.

  3. [Repurchasing Guests] The total number of loyalty guests who repurchased the [Product Category] during the [Start Date] and [End Date] range.

  4. [Purchasing Loyalty Guest] The total nominal number of loyalty guests who made any purchase during the [Start Date] and [End Date] range.

  5. [Rate of Guest Uptake] The percentage of loyalty guests who purchased the product being analyzed, i.e. [Product Category]. This is calculated as: [Total Guests] / [Purchasing Loyalty Guests] x 100

C.1. Time-series 1 of 2: Cheque ($)

The first time-series chart, Cheque ($), is intended to illustrate, using superimposed line charts, the weekly guest cheque performance for three cohorts of loyalty guests:

  1. [In-product] This is the average weekly cheque of loyalty guests who purchased the product, [Product Category], in the specific week. The range for this time-series is from the [Start Date] to the [End Date]. For example, if the guest purchased the product in week 8, but not in week 9, the guest should only be included in the average cheque calculation of week 8.

  2. [In-category] This is the average weekly cheque of loyalty guests who purchased the category (“leve_1_food_type“) associated with the product, [Product Category], in the specific week. The range for this time-series is from the [Start Date] to the [End Date]. For example, similar to [In-product], if the guest purchased the category in week 8, but not in week 9, the guest should only be included in the average cheque calculation of week 8.

    1. To gain context for the trend formed of [In-category] cheque, for the cohort of guests in week of the [Start Date], the average weekly cheque for the preceding four weeks should be included. This period should be in a different hue to the [In-category] trend to highlight this difference to the end-user. The range for this time-series should run from [Start Date] - 4 Fiscal Weeks (“FW”) to the [Start Date].

  3. [Loyalty] This is the average weekly cheque of all loyalty guests who made purchases in the given week. The range for this time-series is from the [Start Date] - 4FW to the [End Date].

The trend of these three metrics provides end-users with the following insights:

  • The behaviour of guest cheque when purchasing the product [Product Category].

  • The behaviour of guest cheque who purchased the product, and guest cheque who purchased the category (but not necessarily the provide). Providing insights on whether guests are trading within category. This, in combination with other metrics, provides insights on product & category cannibalization.

  • The behaviour of guest cheque when purchasing the product [Product Category] as compared to the overall guest base. This provides insights on whether the new product has an influence on guest cheque.

C.2. Time-series 2 of 2: Purchase vs. Repurchase

  1. The second time series chart, Purchase vs. Repurchase, is intended to illustrate using a stacked bar chart, per fiscal week, the quantity of units of the [Product Category] purchased by guests for the first time ever, as well as the quantity of units of the [Product Category] repurchased by guests in that given fiscal week.

The time-series trend of this metric provides end-users with the following insight:

  • The behaviour of guest all-time new purchases vs. repurchases during the launch of the product, and provides insights on whether the product is continuing to attract new guests to purchase the product, and whether the product continues to remain attractive to guests, as illustrated by repurchases.

D. Product Mix Evolution

The final subset of metrics to be displayed is intended to illustrate how the proportion of product mixes (“leve_1_food_type“) evolves from the [Start Date] to the [End Date]. The categories to be included in this proportioning view should be limited to: COLD BEVERAGES, MAIN FOODS, HOT BEVERAGES, BREAKFAST, FOODS, SNACK/BAKED and DISCOUNT BUNDLE. The view here should tabulate the percentage of proportionality AND nominal sales amount (in $) of each of the aforementioned categories at both the [Start Date] AND the [End Date] for two cohorts: (1) [In-product] guests, and (2) overall Loyalty Guests. Below is an example of the view that should be illustrated for both the [Start Date] and [End Date]:

Week #

PMIX

Product

Loyalty

COLD BEVERAGES

4%

$ #.##

4%

$ #.##

MAIN FOODS

8%

$ #.##

4%

$ #.##

HOT BEVERAGES

70%

$ #.##

74%

$ #.##

BREAKFAST FOODS

16%

$ #.##

16%

$ #.##

SNACK/BAKED

1%

$ #.##

2%

$ #.##

DISCOUNT BUNDLE

1%

$ #.##

0%

$ #.##

Additionally, the user should be able to drilldown this table to one product level below (i.e. “level_2_category”)., and subsequently, drill up to the originally default level 1 categories.

E. Refresh Rate

The dashboard should be refresh daily with the most recent data being read into the dashboard metrics.


🍁 Brand Aesthetics

The dashboard should comply with Tim Hortons (“TH”) branding and aesthetics guidelines, which are available here as at the date of publishing. Ensure that the latest guidelines are applied at the time of publishing to production.

📊 Sample Dashboard View

Below is an example of a dashboard view based on the requirements above (additionally included here for reference: ). As illustrated, on the left-hand side, users would select a [Start Date], [End Date] and [Product Category]. At the top are the call-out metrics outlined above. In the central portion of the dashboard, time-series 1 (Cheque) and time-series 2 (Purchase vs. Repurchase) are shown. Note for time-series 1, the four fiscal weeks prior to the [Start Date] are illustrated. Lastly, the product mix evolution is displayed at the bottom of the dashboard, showing the product mix (“pmix”) proportions at [Start Date] for the [In-product] cohort as well as the Overall Loyalty Base. And alongside this is the product mix for both these cohorts at the [End Date].

Sample Dashboard View for Illustrative Purposes only

🖥️ 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 updates on progress.

  • Complete the data governance requirements for the project.

  • Conduct the appropriate quality assurance (“QA”) activities to ensure metrics are accurate.

  • Provide support and ongoing maintenance of the data models and dashboard.

⚠️ Risks

  1. 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.

  2. High computational load: multiple data models will need to be built to develop each metric for the dashboard. These data models can become large and difficult for the chosen visualization tool to read. Additionally, this may result in a high computational requirement, which will need to be mitigated for.

  3. Inaccurate data: as the dashboard is anticipated to influence Category Management decision-making, the accuracy of the data is paramount.


  • No labels