/
Interac Debit vs. Databricks Debit

Interac Debit vs. Databricks Debit

Owned by Adrian Monk
yesterday at 2:26 pm
2 min read

Author: Adrian Monk

 

Goal:

Get as close as possible to Interac debit data with Databricks sales data.

 

Notes:

  • All data from Q4 2022

 

 

Case

Sales

Transactions

Cheque

Sales Difference

Description

 

Case

Sales

Transactions

Cheque

Sales Difference

Description

1

 $985,852,353

132,549,857

 $7.44

 

Interac debit data.

2

 $1,046,164,274

128,455,904

 $8.14

6%

From tender table, summing total amount on ticket_id's with one of the debit tenders. Trying to get tax as well by taking total amount on the ticket in the tender table. Found that this includes many tender names other than debit and tax.

3

 $874,745,960

128,454,144

 $6.81

-11%

Getting debit ticket_id's from tender table, then joining to master_derived and summing sales there. This does not include tax, but does include when is_pass_through = 1 in attempt to include Tim Card reloads.

4

 $988,462,935

128,454,144

 $7.70

0%

Same case 3 but with 13% tax included. This is including tax on Tim Card reloads which is not correct.

5

 $843,570,589

128,078,448

 $6.59

-14%

Same case 3 but with is_pass_through = 0. No tax included in this amount.

6

 $953,234,766

128,078,448

 $7.44

-3%

Case 5 with 13% tax included. This does not include Tim Card reloads.

7

 $85,561,561

127,782,664

 $0.67

 

Only taking the tax amount of tender table on debit transactions.

8

 $960,307,521

128,454,144

 $7.48

-3%

Sum of case 3 and 7. This includes cases when is_pass_through = 1 but is not Tim Card reloads.

9

 $21,098,866

588,636

 $35.84

 

Sales from master derived where is_pass_through = 1 and menu item is Tim Card Reload, joined with the tender table for only debit transactions.

10

 $950,231,016

128,667,084

 $7.39

-4%

Sum of case 5, 7, and 9.

11

 $6,067,798

1,266,689

 $4.79

 

Sum of non-debit, non-tax tenders on debit tickets in tender table.

12

 $615,444

1,113,008

 $0.55

 

Tax rate on case 11, using same tax rate as above (case 7 / case 5).

13

 $943,547,775

128,667,084

 $7.33

-4%

Case 10 minus case 11 and 12.

Potential reasons for 4% sales difference:

 

  1. Restaurants not reporting data to Databricks

    1. Approximately 250 non-traditional POS restaurants do not report to Databricks.

    2. Sales from these restaurants comes out to ~2% of SWS, so can be expected that half of the 4% difference is due to this

  2. Can’t split out Scan & Pay debit transactions

    1. Scan & Pay debit transactions would fall under one of the Scan & Pay credit tender types

    2. It is difficult to estimate what % of Scan & Pay this is, but can assume it would shrink the remaining 2% difference

  3. Refunds

    1. Assuming that refunds flow into Interac data, Databricks does not add a tender to refunds, causing Databricks numbers to be slightly inflated