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 29 Next »

Glossary of Digital & Loyalty Metrics

\uD83D\uDCD8 Metrics List

Metric and Definition

Query

Comments and Business Use

Known Diner Sales (“KDS”)

All sales that are made by recognizable guests, i.e. guests that have registered through the app (identified by their registered account id beginning with ‘us-east’)

SELECT TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS

FROM STG.DERIVED_MASTER_TABLE_NEW

WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

This metric is used as a baseline to see loyalty sales penetration against system wide sales.

System Wide Sales (“SWS”)

Total sales across TH

SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
SUM(AMOUNT) AS SWS

FROM STG.DERIVED_MASTER_TABLE_NEW 
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'

GROUP BY 1,2

Note: Some forecasts may use Hyperion sales interchangeable in lieu of SWS sourced from STG.DERIVED_MASTER_TABLE_NEW.

Cheque

Average sales value ($) of an individual transaction

SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
SUM(AMOUNT) AS SWS,
SUM(AMOUNT)/COUNT(DISTINCT TICKET_ID) AS CHEQUE

FROM STG.DERIVED_MASTER_TABLE_NEW 
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'

GROUP BY 1,2

Can calculate this on a system level or down to an individual guest level. Can filter on loyalty or non-loyalty.

Frequency

Average loyalty guest visits in a given time period

SELECT
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
COUNT(DISTINCT TICKET_ID) AS TRXNS,
COUNT(DISTINCT REGISTERED_ACCOUNT_ID) AS GUESTS,
COUNT(DISTINCT TICKET_ID)/COUNT(DISTINCT T1.REGISTERED_ACCOUNT_ID) AS FREQUENCY

FROM STG.DERIVED_MASTER_TABLE_NEW T1
      
WHERE 
PARTITION_DATE_KEY BETWEEN '<START DATE>' AND '<END DATE>'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

Usually look at this metric for a week, month, or year.

White Label Delivery (“WL”)

Delivery sales initiated from the TH mobile app

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN '$START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('WHITE LABEL DELIVERY','DELIVERY')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

Included in known diner sales & digital sales.

Sales of our internal app delivery platform.

Mobile Order & Pay (“MO&P”)

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('MOBILE ORDER DRIVE THRU', 'MOBILE ORDER EAT IN', 'MOBILE ORDER TAKE OUT')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

Included in known diner sales & digital sales.

Loyalty Scans

Sales made by known diners and includes eat-in, takeout & drive thru

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA"
AND SERVICE_MODE_CD IN ('TAKEOUT', 'EATIN','DRIVETHRU')
AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'

GROUP BY 1,2

Included in known diner sales & digital sales.

3P Delivery

Delivery sales initiated and fulfilled by third-party delivery providers, such as UberEats, SkipTheDishes and DoorDash

SELECT 
A.PARTITION_DATE_KEY,
SUM(A.AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW A 

INNER JOIN TLOG.TLOG_SALE_TICKET_TENDERS B 
ON A.TICKET_ID = B.TICKET_ID

WHERE A.PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = 'CANADA'
AND TENDER_NAME IN ("UBER EATS CREDIT","Credit Uber Eats","Uber Eats Credit","CRÉDIT UBER EATS","SKIP CREDIT","Skip Credit","CRÉDIT de DoorDash","CRÉDIT DE DOORDASH","DoorDash Credit","DOORDASH CREDIT","Crédit de DoorDash","Doordash")

GROUP BY 1

Included in digital sales

Kiosk

Sales via Kiosk; can be split into registered and un-registered Kiosk sales using

SELECT
PARTITION_DATE_KEY,
SUM(AMOUNT) AS SALES

FROM STG.DERIVED_MASTER_TABLE_NEW 

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND SERVICE_MODE_CD IN ('KIOSK', 'KIOSK TAKEOUT', 'KIOSK EATIN')
AND IS_PASS_THROUGH = 0 
AND COUNTRY_NM = "CANADA" 

GROUP BY 1

Included in digital sales

-- Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) = 'us-east'
-- Un-Registered Kiosk
AND LEFT(COALESCE(REGISTERED_ACCOUNT_ID,''),7) != 'us-east'

Catering

Catering sales

SELECT 
PARTITION_DATE_KEY,
SUM(ITEMTOTALPRICE)AS SALES,

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND COUNTRY_NM = 'CANADA'
AND IS_PASS_THROUGH = 0 
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND DININGTYPE = 'CT'

GROUP BY 1

Included in digital sales

Restaurants Reporting Any Menu Item (“RRAMI“)

WITH RESTAURANTS AS (

SELECT 

TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
PARTITION_DATE_KEY,
COUNT(DISTINCT REST_NO) AS RESTAURANTS

FROM STG.DERIVED_MASTER_TABLE_NEW

WHERE PARTITION_DATE_KEY BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM = 'CANADA' 

GROUP BY 1,2,3
ORDER BY 1)

SELECT 
TH_FISCAL_YEAR,
TH_FISCAL_WEEK,
SUM(RESTAURANTS) AS RRAMI

FROM RESTAURANTS

GROUP BY 1,2

Scan & Pay (“S&P”)

Transactions where Scan & Pay feature was used

SELECT 
STOREDATE,
COUNT(DISTINCT TRANSACTIONID) AS TICKETS,
SUM(ITEMTOTALPRICE) AS SALES 

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE STOREDATE BETWEEN 'START_DATE' AND 'END_DATE'
AND IS_PASS_THROUGH = 0
AND COUNTRY_NM IN ('CANADA')
AND LEFT(REGISTEREDACCOUNTID,7) = 'us-east'
AND SCANANDPAY IS TRUE

GROUP BY 1

Often tracked as a % of registered transactions

Scan & Pay Penetration Formula: (Scan & Pay Transactions) / (Registered Transactions)

Loyalty Redemptions

Products that were redeemed using loyalty points

CACHE TABLE DAILY_REDEMPTIONS AS

WITH TRANSACTIONS_WITH_OFFER_ID AS (

SELECT 
LEFT(PERIOD_DT, 10) AS PERIOD_DT,
REGISTEREDACCOUNTID,
TRANSACTIONID, 
EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS

FROM PRODRT.CURATED_TRANS_EVENTS_NEW

WHERE DATE_KEY >= '20230606'
AND COALESCE(REGISTEREDACCOUNTID,'') IS NOT NULL
AND APPLIEDOFFERS IS NOT NULL
AND COUNTRY_NM = 'CANADA')

SELECT 
PERIOD_DT,
COUNT(DISTINCT TRANSACTIONID) AS TRXNS

FROM TRANSACTIONS_WITH_OFFER_ID AS A

INNER JOIN (SELECT DISTINCT OFFERID FROM DYDB.WEEKLYOFFERS
             
            UNION ALL
            
            SELECT DISTINCT OFFERID
            FROM DYDB.OFFERS
            WHERE CONTAINS(description, 'LOYALTY')
            AND DESCRIPTION LIKE 'CA L%'
            AND DESCRIPTION NOT IN ('CA LR Registered Default (same as L1)-LOYALTY', 'CA LU Unregistered (same as 102) ONE-TIME-LOYALTY') ) B
            
ON A.EXPLODED_OFFERS = B.OFFERID 

LEFT JOIN DYDB.OFFERS AS C 
ON A.EXPLODED_OFFERS=C.OFFERID

WHERE EXPLODED_OFFERS IS NOT NULL 
AND C.OFFERID IS NOT NULL

GROUP BY 1

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.

Loyalty drag formula = $ value of redeemed items / SWS $

Monthly Active Users (“MAU”)

Number of guests that visited the app each month

WITH EVENTS AS (
SELECT DATE
       , _TIMHORTONS.INTERACTION.ELEMENT.NAME
       , _TIMHORTONS.LOYALTY.ID AS GUEST_ID
FROM   loyalty.events.adobe_app_events
WHERE  EVENTTYPE IN ('app_open','app_launch')    
AND    _TIMHORTONS.PLATFORM IN ('app') 
AND    LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND    DATE <= CURRENT_DATE )

SELECT     LAST_DAY(EVENTS.DATE) AS DTE
          , COUNT(DISTINCT EVENTS.GUEST_ID) AS ACTIVE_USER
FROM      EVENTS  
GROUP BY  1
ORDER BY  1 DESC

Used to track the unique number of guests who opened or launched the app per month. Limitation of the dataset is: (1) data only exists from Nov 1st, 2023, and (2) Prior to mm/dd/yyyy (TBD), dataset did not contain guests using app versions prior to 7.1.187.

Digital Sales

Pulled using the individual queries listed above.

Consists of WL, MO&P, Loyalty Scans, 3P Delivery, Kiosk & Catering.

Weekly Offer Redemptions

WITH REDEMPTION_SUMMARY AS (
  WITH OFFER_REDEMPTION_TRXNS AS (
    WITH TRANSACTIONS_WITH_OFFER_ID AS (
      SELECT
        TH_FISCAL_YEAR,
        TH_FISCAL_WEEK,
        CAST(WEEK_START_DT AS DATE) AS WEEK_START_DT,
        DATE_FORMAT(
          CAST(
            UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
          ),
          'yyyyMMdd'
        ) AS WEEK_START,
        CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
        REGISTEREDACCOUNTID,
        TRANSACTIONID,
        EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS
      FROM
        PRODRT.CURATED_TRANS_EVENTS_NEW
      WHERE
        PARTITION_DATE_KEY BETWEEN 'START DATE' AND 'END DATE'
        AND REGISTEREDACCOUNTID LIKE 'us-east%'
        AND COUNTRY_NM = 'CANADA'
    )
    SELECT
      DISTINCT A.TH_FISCAL_YEAR,
      A.TH_FISCAL_WEEK,
      A.WEEK_START_DT,
      A.PERIOD_DT,
      A.REGISTEREDACCOUNTID,
      A.TRANSACTIONID,
      C.NAME,
      C.DESCRIPTION,
      A.EXPLODED_OFFERS,
      1 AS VOLUME
    FROM
      TRANSACTIONS_WITH_OFFER_ID A
      INNER JOIN DYDB.WEEKLYOFFERS B ON A.EXPLODED_OFFERS = B.OFFERID
      LEFT JOIN DYDB.OFFERS C ON A.EXPLODED_OFFERS = C.OFFERID
  )
  SELECT
    WEEK_START_DT,
    A.DESCRIPTION AS OFFER_DESCRIPTION,
    A.EXPLODED_OFFERS AS OFFERID,
    SUM(VOLUME) AS REDEMPTION_VOLUME
  FROM
    OFFER_REDEMPTION_TRXNS A
  GROUP BY
    1,
    2,
    3
)
SELECT
  a.WEEK_START_DT AS WKDT,
  OFFER_DESCRIPTION,
  OFFERID,
  SUM(REDEMPTION_VOLUME)
FROM
  REDEMPTION_SUMMARY A
  GROUP BY 1,2,3

Used to track the volume of redemptions for each offer in a given week.

Offer Challenge

with TRXN_OFFER_CHALLENGES_REDEMPTIONS as (
      WITH EXPLODED AS (
        SELECT
          TH_FISCAL_YEAR,
          TH_FISCAL_WEEK,
          DATE_FORMAT(
            CAST(
              UNIX_TIMESTAMP(LEFT(WEEK_START_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
            ),
            'yyyyMMdd'
          ) AS WEEK_START_MAPPING,
          CAST(PERIOD_DT AS DATE) AS PERIOD_DT,
          REGISTEREDACCOUNTID,
          loyaltyCustomerId,
          TRANSACTIONID,
          EXPLODE(APPLIEDOFFERS) AS EXPLODED_OFFERS,
          REST_TYP_NM,
          OPS_DIV_NM
        FROM
          PRODRT.CURATED_TRANS_EVENTS_NEW -- 1.	Challenge duration: 26/09 to 02/10
        WHERE
          DATE_KEY BETWEEN '${myapplication.Offer_Start_Date}'
          AND '${myapplication.Offer_End_Date}'
          AND COUNTRY_NM = 'CANADA'
          AND REGISTEREDACCOUNTID IS NOT NULL
          AND TRANSACTIONID IS NOT NULL -- AND REST_TYP_NM = "STANDARD"
      )
      SELECT
        DISTINCT A.TH_FISCAL_YEAR,
        A.TH_FISCAL_WEEK,
        A.WEEK_START_MAPPING,
        A.PERIOD_DT,
        A.REGISTEREDACCOUNTID,
        A.loyaltyCustomerId,
        A.TRANSACTIONID,
        A.REST_TYP_NM,
        A.OPS_DIV_NM,
        B.DESCRIPTION,
        1 AS REDEMPTIONS
      FROM
        EXPLODED AS A
        LEFT JOIN DYDB.OFFERS AS B ON A.EXPLODED_OFFERS = B.OFFERID
      WHERE
        EXPLODED_OFFERS = '76290e2e-1783-442f-8ff6-a2b16900a34e'
    ),
    points_issued as (
      Select
        *,
        WEEKOFYEAR(to_date(partition_date_key, "yyyyMMdd")) AS WEEK_START,
        regexp_extract(barcode, '(\\d+)|(\\d+)', 0) as lid
      From
        prodrt.curated_points_events as points
      where
        partition_date_key BETWEEN '${myapplication.Offer_Start_Date}'
        AND '${myapplication.Offer_End_Date}'
        and tag = 'PRODUCT_CHALLENGED_COMPLETED'
    ),
    completed as(
      Select
        a.TH_FISCAL_YEAR as TH_FISCAL_YEAR,
        a.TH_FISCAL_WEEK as TH_FISCAL_WEEK,
        a.WEEK_START_MAPPING as WEEK_START_MAPPING,
        a.PERIOD_DT as PERIOD_DT,
        DATE_FORMAT(
          CAST(
            UNIX_TIMESTAMP(LEFT(PERIOD_DT, 10), 'yyyy-MM-dd') AS TIMESTAMP
          ),
          'yyyyMMdd'
        ) as transaction_dt,
        a.REGISTEREDACCOUNTID as REGISTEREDACCOUNTID,
        a.loyaltyCustomerId as loyaltyCustomerId,
        a.TRANSACTIONID as TRANSACTIONID,
        a.REST_TYP_NM as REST_TYP_NM,
        a.OPS_DIV_NM as OPS_DIV_NM,
        a.DESCRIPTION as DESCRIPTION,
        a.REDEMPTIONS as REDEMPTIONS,
        b.transactionID as completion_transactionID,
        b.pointsEarned as pointsEarned,
        b.restaurant as restaurant,
        b.partition_date_key as offer_completion_dt,
        b.WEEK_START as offer_completion_week,
        b.lid as lid
      from
        TRXN_OFFER_CHALLENGES_REDEMPTIONS a
        left join points_issued b on a.loyaltyCustomerId = b.lid
        and b.WEEK_START = a.TH_FISCAL_WEEK
    )
    Select
      TH_FISCAL_YEAR,
      TH_FISCAL_WEEK,
      WEEK_START_MAPPING,
      REGISTEREDACCOUNTID,
      loyaltyCustomerId,
      offer_completion_week,
      count(
        distinct(
          case
            when offer_completion_dt is not NULL
            and transaction_dt <= offer_completion_dt then TRANSACTIONID
          end
        )
      ) as trnx_before_completion,
      count(distinct(TRANSACTIONID)) as total_trnx,
      count(
        distinct (
          case
            when lid is not Null then lid
          end
        )
      ) as completed
    from
      completed
    group by 1, 2, 3, 4, 5, 6

Count of guests who completed a specific Offer Challenge

Games

NHL Hockey Challenge & Tims Word Challenge

SELECT    YEAR(TIMESTAMP) AS YR
          , MONTH(TIMESTAMP) AS MTH 
          , COUNT(DISTINCT GUESTS) AS GUESTS FROM (
-- WORD CHALLENEGE PLAYERS

SELECT    DISTINCT TIMESTAMP, _TIMHORTONS.LOYALTY.ID AS GUESTS
FROM      loyalty.events.adobe_app_events 
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH) IN ('/timswordchallenge')
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) = 'play'
AND       _TIMHORTONS.PLATFORM IN ('app')
AND       LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       DATE >= DATE '2023-11-01' 

UNION
-- HOCKEY PLAYERS

SELECT    DISTINCT TIMESTAMP,_TIMHORTONS.LOYALTY.ID  AS GUESTS
FROM      loyalty.events.adobe_app_events
WHERE     TRIM(_TIMHORTONS.INTERACTION.PATH) = '/hockey_challenge'
AND       TRIM(_TIMHORTONS.INTERACTION.ELEMENT.NAME) IN ('submit_picks')
AND       _TIMHORTONS.PLATFORM IN ('app')
AND       LEFT(_TIMHORTONS.LOYALTY.ID,7) = 'us-east'
AND       DATE >= DATE '2023-11-01' 
)
GROUP BY 1,2
ORDER BY 1 DESC, 2

Count of guests who played Games (NHL Hockey Challenge and Tims Word Challenge).

Limitation: Data only available from Nov 1, 2023.

Total Sales and Ticket Count by Tender Type

WITH LOYALTY_TENDERS AS
(
    WITH TENDERS AS
    (
      SELECT
      REPLACE(UPPER(TRIM(TENDER_NAME)), '.', '') AS TENDER_NAME,
      TICKET_ID

      FROM loyalty.tlog.tlog_sale_ticket_tenders

      WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
      AND LEFT(REST_NO,2) = 10
    ),

    LOYALTY AS
    (
      SELECT 
      TICKET_ID AS TICKET_ID

      FROM STG.DERIVED_MASTER_TABLE_NEW

      WHERE PARTITION_DATE_KEY BETWEEN 20230101 AND 20230731
      AND COUNTRY_NM = 'CANADA'
      AND IS_PASS_THROUGH = 0 
      AND LEFT(REGISTERED_ACCOUNT_ID,7) = 'us-east'
    )

    SELECT
    A.*

    FROM TENDERS A

    INNER JOIN LOYALTY B
    ON A.TICKET_ID = B.TICKET_ID
    )

SELECT
CASE
          WHEN UPPER(TENDER_NAME) IN ('CASH', 'COMPTANT', 'EFECTIVO', 'US CASH', 'CANADIAN CASH', 'CDN CASH', 'ROUNDED CASH', 'ROUNDED COMPTANT') THEN 'CASH'

          WHEN UPPER(TENDER_NAME) IN ('DEBIT CARD', 'DEBIT', 'CARTE DEBIT', 'DEBITO', 'DÉBIT', 'DO DEBIT') THEN 'DEBIT'

          WHEN UPPER(TENDER_NAME) IN ('VISA', 'MASTERCARD', 'MASTER CARD', 'AMEX', 'AMERICAN EXPRESS', 'CREDIT CARD', 'CREDIT CARDS', 'DISCOVER', 'M/C', 'DISCOVER CARD', 'DIGITAL AMEX', 'DIGITAL DISCOVER', 'DIGITAL MASTER CARD', 'DIGITAL MASTERCARD', 'DIGITAL AMERICAN EXPRESS', 'DIGITAL VISA', 'DO VISA', 'DO MASTERCARD') THEN 'CREDIT'

          WHEN UPPER(TENDER_NAME) IN ('TIM CARD', 'DIGITAL TIM CARD', 'CARTE TIM', 'MOBILE TIM CARD', 'DIGITAL CARTE TIM', 'TIM CARTE', 'TIMS GIFT CARD', 'DIGITAL TIMS GIFT CARD', 'CARTE-CADEAU TIM', 'DIGITAL CARTE-CADEAU TIM') THEN 'TIMCARD'

          WHEN UPPER(TENDER_NAME) LIKE '%SKIP%' THEN 'SKIP'

          WHEN UPPER(TENDER_NAME) LIKE '%UBER%' THEN 'UBER'

          WHEN UPPER(TENDER_NAME) LIKE '%DOOR%' THEN 'DOORDASH'

          WHEN UPPER(TENDER_NAME) IN ('SCAN AND PAY VISA', 'SCAN AND PAY MASTERCARD', 'SCAN AND PAY TIMCARD', 'SCAN AND PAY AMEX', 'SCAN AND PAY DISCOVER', 'SCAN AND PAY TIM CARD', 'NUMERISEZ ET PAYEZ – VISA', 'SCANTOPAY', 'NUMERISEZ ET PAYEZ – MASTERCARD', 'NUMERISEZ ET PAYEZ – CARTE TIM', 'NUMÉRISEZ ET PAYEZ – VISA', 'NUMÉRISEZ ET PAYEZ – MASTERCARD', 'NUMÉRISEZ ET PAYEZ – CARTE TIM', 'NUMERISEZ ET PAYEZ – AMEX', 'NUMÉRISEZ ET PAYEZ – AMEX', 'SCAN AND PAY TIMS GIFT CARD') THEN 'SCANANDPAY'

          WHEN UPPER(TENDER_NAME) IN ('HST', 'HST1', 'TVQ', 'TPS', 'GST', 'TAX', 'PST', 'HST TAXABLE SALES', 'SALES TAX' 'HST 1', 'H.S.T.1', 'H.S.T', 'HST 13% TAXABLE SALES', 'GST TAXABLE SALES', 'TAX 1', 'HST 1', 'GST# 75696 6891 RT0001', 'HST # 897258141', 'HST5%', 'HST8%', 'QST', 'SALES TAX', 'MEAL PLAN CARD - PREPAID TAX', 'CARTE PLAN REPAS - TAX PREPAYEE', 'GST # 121071781RT0001', 'TVH', 'TVH1', 'VAF', 'H.S.T.') THEN 'TAX'

          WHEN ((UPPER(TENDER_NAME) LIKE '%ROUND%') OR (UPPER(TENDER_NAME) LIKE '%ARRONDIS %')) THEN 'ROUND UP'

          ELSE 'OTHER'
END AS TENDER,
COUNT(DISTINCT TICKET_ID) AS TRXNS

FROM LOYALTY_TENDERS

GROUP BY 1

Total sales $ and ticket count organized by tender type (debit, credit, cash, Tims Card, tax) for a given restaurant

  • No labels