Skip to:
- Cash Actuals & Expected
- Cash Handling Audit Log
- Credit Log
- Customer
- Discounts
- Inventory Adjustment Log
- Inventory Upsert
- Invoice Table
- Payment Table
- Ticketline Table
___________________________________________
Cash Actuals & Expected
Dataset Name: Cash Actuals And Expected
Description: This dataset provides a summary of all closed shifts by day, along with each cash drawer’s ‘actuals’ and ‘differences’ by shift.
Unique Composite Keys: account_name
, store_id
, shift_dateopen
Datetime Last Updated: last_retrieved_timestamp
Incremental Load Logic: Use the column shift_dateopen
as data appends per new cash movement action completed.
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(shift_dateopen) = date(date_add(now(), INTERVAL -1 day))"}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(shift_dateopen) >= $stored_last_update_datetime"}
stored_last_update_datetime
is your stored DateTime for the last successful data pull
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
___________________________________________
Column Name |
Definition |
Example Output |
shift_dateopen |
Datetime Shift Opened |
"2021-04-04T15:30:54" |
shift_dateclosed |
Datetime Shift Closed |
"2021-04-04T21:13:50" |
is_closed |
Is the shift currently open or closed? True or False |
true |
account_name |
Cash Drawer Name |
Register1 |
currency_type |
Datetime In Process Started |
CASH |
expected |
Datetime In Process Ended |
2705.14 |
actual |
Datetime Packed And Ready Started |
2696.40 |
storeid |
Datetime Packed And Ready Ended |
420 |
_________________________________________________________________________
Cash Handling Audit Log
Dataset Name: Cash Handling Audit Log
Dataset Description: The cash handling audit log displays a chronological list of any actions that involve money management within your shop. This includes moving funds between cash drawers and accounts, making deposits and cash drops, reimbursing expenses, and more.
Unique Composite Keys: id
, storeid
Datetime Dataset Last Updated: last_retrieved_timestamp
Incremental Load Logic: Use the column date
as data appends per new cash movement action completed.
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(date) = date(date_add(now(), INTERVAL -1 day)) "}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(date) >= $stored_last_update_datetime"}
- stored_last_update_datetime is your stored datetime for last successful data pull
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
___________________________________________
Column Name |
Definition |
Example Output |
id |
Unique ID of row |
1533908e-d528-4a25-87a1-077bd36e09ff |
date |
Date/time of account activity |
2021-01-30T21:02:46 |
user |
ID of user making the account activity |
30013 |
amount |
Amount of account activity |
1869.66 |
from_account |
Account that activity is coming from |
Main Vault |
to_account |
Account that activity is going to |
Register 1 |
to_account_type |
Type of Account |
INCOMING_SALES |
transaction_type |
Transaction Type |
CASH TO VAULT |
transaction_currency_type |
Transaction Currency type |
CASH |
transaction_notes |
Optional - notes related to account activity |
Transfer end of shift cash from CD02 |
storeid |
ID of Store |
1 |
last_retrieved_timestamp |
the time we last retrieved data from SellTreez |
2021-06-09T14:18:30 |
_________________________________________________________________________
Credit Log
Dataset Name: Credit log
Description: The Credit Log displays a chronological list of all actions involving credit usage within your shop. This includes using credits to pay for invoices, returning invoices for distributor credit, and more.
Unique Composite Keys: id
, storeid
Incremental Load Logic: Use the column shift_dateopen
as data appends per new credit action completed.
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(shift_dateopen) = date(date_add(now(), INTERVAL -1 day)) "}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(shift_dateopen) >= $stored_last_update_datetime"}
stored_last_update_datetime
is your stored DateTime for the last successful data pull
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
___________________________________________
Column Name |
Definition |
Example Output |
invoice_line_id |
Invoice Line Item ID |
bcbe583c-5019-11e9-8f30-jou7df09a125 |
id |
Credit Log ID |
e795df7a-7687-46c3-bf2e-c3aa7720fc15 |
invoice_id |
Treez Generated Invoice ID |
fd34470b-9b65-4bc9-9bb2-6f617f03f9f4 |
invoice_number |
Treez Generated Invoice Number |
1500 |
invoice_external_id |
Customer Submitted Invoice ID |
HB420 |
datetime |
Time Actions Took Place |
2019-11-20T14:58:31 |
distributor |
Distributor Name |
HERBL INC. |
action |
Action taken that impacted Credit |
DESTROYED |
reason |
Customer Generated |
‘’ |
user |
SheldonP |
|
amount |
12.4 |
|
distributor_balance |
12.4 |
|
current_distributor_balance |
85.01 |
|
storeid |
420 |
|
datalake_date |
1993-02-19 |
|
_BATCH_ID_ |
200 |
|
_BATCH_LAST_RUN_ |
2021-05-07T11:34:03 |
|
last_retrieved_timestamp |
the time we last retrieved data from SellTreez |
2021-06-09T14:18:30 |
_________________________________________________________________________
Customer
Dataset Name: Customer
Description: Provides an anonymized view of all your registered customers.
Unique Composite Keys: storeid
, customer_signup_date
, last_updated_at
customer_uuid
Datetime Last Updated:last_retrieved_timestamp
Incremental Load Logic: Use the column customer_signup_date
as data appends per new customer creation
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(customer_signup_date) = date(date_add(now(), INTERVAL -1 day)) "}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(customer_signup_date) >= $stored_last_update_datetime"}
stored_last_update_datetime
is your stored datetime for the last successful data pull.- Use the column
last_updated_at
to update rows with new data.
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
___________________________________________
Column Name |
Definition |
Example Output |
customer_uuid |
Customer Universal ID |
45a0e197-685e-330a-98c2-711776d5f08b |
customer_signup_date |
Date Customer Signed Up |
2000-01-01T00:00:00 |
last_updated_at |
Date Customer Last Updated |
2000-01-01T00:00:00 |
last_visit |
Date Customer Last Visited |
2000-01-01T00:00:00 |
signup_flag |
Is the customer signed up? (1 = Yes, 0 = No) |
1 |
gender |
Gender (M= Male, F= Female, U= Unknown) |
U |
age_group |
Customer Age Group |
60+ |
age |
Customer Age |
63 |
reward_balance |
Available Reward Points For Customer |
0 |
customer_source |
Customer Source |
|
Group Names |
Customer Group Name |
SENIOR |
visits |
Customer Visit Count |
1 |
notes |
Customer Note |
Likes Edible Gummies |
opted_in |
Marketing Opt In (Y=Yes, N = No) |
Y |
patient_type |
Customer Patient Type |
ADULTUSE |
customer_status |
Customer Status |
ACTIVE |
violations |
Violation . Warning , Warning, Ban. (0 = Toggled Off , 1 = Toggled On) |
0,0,0 |
city |
Customer City |
BERKELEY |
state |
Customer State |
CA |
zipcode |
Customer Zip Code |
94710 |
longitude |
Latitude |
0 |
latitude |
Longitude |
0 |
permit_expiration_date |
Customer Permit Expiration Date |
|
tz_customer_id |
Name Mapping ID |
n/a |
storeid |
Store ID |
230 |
_________________________________________________________________________
Discounts
Dataset Name: Discounts
Dataset Description: The discounts dataset displays every discount that may apply to a ticketline. There could be more than 1 discount applied to a single ticketline and therefore the relationship between discount to ticketline is n:1.
Unique Composite Keys: store_id
, ticketlineid
, discountlogid
Datetime Dataset Last Updated: last_retrieved_timestamp
___________________________________________
Column Name |
Definition |
Example Output |
tz_brandname |
Brand name |
LOST FARM |
tz_brand_id |
ID associated with Brand Name |
445 |
customer_uuid |
Hashed customer id |
0005da4c-1b99-3c8e-9d98-5c12a883c071 |
discount_date |
Date/time the discount was applied |
2021-03-03T19:53:56 |
dateopen |
Date that the ticket was created |
03/03/2021 |
timeopen |
Time that the ticket was created |
18:38:43 |
dateclosed |
Date that the ticket was closed |
03/03/2021 |
timeclosed |
Time that the ticket was closed |
19:53:56 |
datetimeclosed |
Date/time that ticket was closed |
2021-03-03T19:53:56 |
datelastupdate |
Date that ticket was last updated |
03/03/2021 |
timelastupdate |
Time that ticket was last updated |
19:53:56 |
ticketalphaid |
Visible ticket number that is printed on the receipt |
MQOFRG |
ticketlineid |
ID associated with ticket line, can be used to join to ticket table |
b4083885-c5bb-4201-8cba-c45c59d73053 |
ticketid |
ID associated with ticket, can be used to reference tickets within the ticket table |
1a840dc1-eb3f-4932-8ed1-323a1d505ed4 |
gender |
Optional - gender |
F |
customer_city |
Optional - city |
SANTA MONICA |
customer_state |
Optional - state |
CA |
customer_country |
Optional - country |
USA |
Cashier |
Cashier name that applied the discount |
JOHN |
cashierid |
ID associated with cashier |
3936952f-5185-4f0e-bde9-0f2b801fcae7 |
producttype |
Product Category |
FLOWER |
productsubtype |
Product Sub Category |
PREPACK |
productname |
Product Name |
ALIEN OG |
priceperunit |
Unit price of product |
45.00 |
salestax |
Sales tax amount |
0.00 |
discounttype |
Type of discount: 0 = Automatic from Discount Module 1 = Custom/Manual 2 = Rewards 3 = Tier Pricing Discount 4 = Round Down 5 = Other |
1 |
discountmethod |
PERCENT or DOLLAR discount |
PERCENT |
discountreason |
Optional - if a reason was provided for a custom discount |
FIRST TIME |
discounts |
Actual discount amount |
15.25 |
discountid |
ID associated with this discount row |
87aae755-e594-4154-9db3-728b7670e6ed |
discountlogid |
Log ID associated with applying this discount |
e053c563-997f-4b3e-bd23-820fd067eb32 |
customertype |
Type of customer |
MEDICAL-MMID |
customergroup |
Optional - group that this customer was part of |
SENIOR |
weight |
Unit amount of product |
2 |
unitofmeasure |
Unit of measure for weight |
GRAMS |
discounttitle |
Title of automated or custom discount |
20% OFF |
skus |
Inventory or product barcode associated with product |
F3o |
receiving_license |
Optional - license of retailer |
C10-0000000-LIC |
customer_mmid_expiration |
Optional - Date/time of mmid expiration |
2021-07-19 00:00:00 |
priceperunitafterdiscount |
Price per unit of product, post discount |
52.82 |
quantity |
Quantity of products that is being discounted |
1.00 |
priceperunitdifference |
Difference of price per unit, pre and post discount |
4.68 |
netsales |
Total net sales of ticketline |
35.10 |
storeid |
ID of retailer |
1 |
distributor |
Distributor name of product |
CURA |
size |
Product Size |
1G |
productbrand |
Uncleansed brand name |
SELECT |
product_id |
ID of product |
0c5f893f-b359-4483-ac69-b8d00eed86ea |
cost |
Cost of product |
12.70 |
cost_without_excise |
Cost of product, without excise tax |
10 |
last_retrieved_timestamp |
2021-06-09T14:18:30 |
the time we last retrieved data from SellTreez |
_________________________________________________________________________
Inventory Adjustment Log
Dataset Name: Inventory Adjustment Log
Dataset Description: The inventory adjustment log displays a chronological list of any actions that involve inventory movements within your shop. This includes moving inventory from Back of House to Front of House, destroying inventory, adjusting inventory, selling or returning inventory, and more.
Unique Composite Keys: id
, storeid
Datetime Dataset Last Updated: last_retrieved_timestamp
Incremental Load Logic: Use the column adjustment_datetime
as data appends per new inventory adjustment action
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(adjustment_datetime) = date(date_add(now(), INTERVAL -1 day)) "}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(adjustment_datetime) >= $stored_last_update_datetime"}
- stored_last_update_datetime is your stored datetime for last successful data pull
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
___________________________________________
Column Name |
Definition |
Example Output |
adjustment_datetime |
Date/Time that inventory adjustment was made |
2020-07-21T20:07:10 |
product_brand |
Brand name of the product |
CHOICE |
product_name |
Product Name |
LEMON SWIRL |
product_type |
Product Category |
FLOWER |
product_subtype |
Product Sub Category |
PRE-PACK |
product_id |
ID of product |
f0880425-b44f-4131-9b9d-49f0efea4e6a |
package_id |
ID of inventory batch |
8f2ea4a4-087f-4cfd-b6fd-7ee3c82095b8 |
state_tracking_id |
Visible ID of state traceability |
1A405010000A475000000005 |
batch_id |
Visible ID of internal inventory batch |
47GRO |
action |
Action of adjustment |
MOVE |
reason |
Optional - reason of inventory adjustment |
Counts Match |
original_qty |
Original quantity before adjustment |
24 |
adjustment_qty |
Adjusted quantity |
-24 |
current_qty |
Current quantity after adjustment |
0 |
adjustment_post_excise_cost |
Adjustment of inventory in cost, post excise |
-284.58 |
adjustment_pre_excise_cost |
Adjustment of inventory in cost, pre excise |
-284.58 |
adjustment_excise_cost |
Adjustment of inventory in excise tax |
-85 |
adjustment_user |
User ID of the one performing the adjustment |
JOHN |
location_name |
Inventory location name at time of adjustment |
FRONT OF HOUSE |
inventory_id |
ID of inventory batch, can be used to join to inventory table |
b47abc95-1aa9-4b0b-93dd-af88be7d3791 |
invoice_line_id |
ID of invoice line, can be used to join to invoice table |
2a3209a6-adfc-4cad-bd5b-9adf00868610 |
credit_note_id |
ID of credit note, if applicable |
|
id |
Unique ID of row |
b47abc95-1aa9-4b0b-93dd-af88be7d3791 |
storeid |
ID of retailer |
1 |
retail_brand |
Brand name of Product |
CHOICE |
last_retrieved_timestamp |
the time we last retrieved data from SellTreez |
2021-06-09T14:18:30 |
_________________________________________________________________________
Inventory Table
Dataset Name: Inventory
Description: This report shows a snapshot of all your inventory. Inventory is aggregated by the Unique Product ID, Batch ID, and Location, which means it should reflect your inventory as it appears on the Inventory Control page of your portal.
Datetime Last Updated: last_retrieved_timestamp
Unique Composite Keys: storeid
, batch_id
, location_id
, product_id
state_tracking_id
Upsert Logic: Replace All
___________________________________________
Column Name |
Definition |
Example Output |
unit_of_measure |
Unit of Measure |
GRAMS |
type |
Inventory Type |
ALL |
storeid |
Store ID |
230 |
status |
Inventory Status |
AVAILABLE |
start_date |
2020-11-06T16:12:19 |
|
source |
SELL_TREEZ |
|
sold_units |
Units Sold |
64 |
returned_units |
Units Returned |
0 |
returned_from_pos |
Units Returned from POS |
0 |
reserved_units |
Units Reserved |
0 |
productname |
Raw Product Name |
THC DESIGN | 24K | 3.5G FLOWER | 27.07% THC |
productbrand |
Raw Brand Name |
THC DESIGN |
product_id |
Product ID |
0a1f5bf4-fc23-4f8b-95fc-f2155c00b9ac |
packed_and_ready_units |
Units Packed And Ready |
0 |
package_id |
Package ID |
f247d6b7-f466-45e5-bcb8-2c1ff8754938 |
original_start_date |
2020-10-16T14:05:29 |
|
location_type |
Location Type |
LOCATION |
location_name |
Location Name |
FRONT OF HOUSE |
location_id |
Location ID |
2f3df669-a935-11e9-a749-02ba70ae261c |
last_user_id |
Last User ID |
4748ee91-7ca2-4ab0-b2ba-06bea3a4038c |
last_reconciliation_id |
Reconciliation ID |
57ebbc86-fb8f-489a-854f-6cb1a3c92012 |
last_location_id |
Location ID |
2f3e1d27-a935-11e9-a749-02ba70ae261c |
last_inventory_id |
Inventory ID |
281d27fd-0340-489a-9c1b-4fb00c485fff |
last_action |
Last Action |
COMPLETE_SALE |
is_sellable |
Is Sellable? (TRUE = YES, FALSE = NO) |
TRUE |
invoice_line_id |
Invoice Line ID |
dde92789-042f-4980-8431-9d79428b50f6 |
invoice_id |
Invoice ID |
2c7fcad9-2c27-42d0-902b-fd0cb1be9912 |
inventory_type |
Inventory Type |
ALL |
id |
Inventory ID |
0ba4101c-08a5-4c48-88c6-b745a6d9fd27 |
expiration_date |
Inventory Expiration Date |
|
excise_tax_per_unit |
Excise Tax per Unit |
4.918195 |
distributor_id |
Distributor ID |
c7d8b4ec-abf2-4482-a74d-0472bfbd6b3a |
destroyed_units |
Units Destroyed |
0 |
decision_pending_units |
Units Pending Decision |
0 |
cost_per_unit |
Cost Per Unit |
18.215538 |
converted_units |
Units Converted |
0 |
change_quantity |
Quantity Change |
1 |
batch_id |
0PAYR-001 |
Batch ID |
available_units |
Current Available Units |
0 |
adjusted_units |
Units Adjusted |
0 |
producttype |
Product Type |
FLOWER |
state_tracking_id |
State Tracking ID |
1A40603000067F9000016320 |
last_retrieved_timestamp |
the time we last retrieved data from SellTreez |
"2021-06-09T14:18:30" |
_________________________________________________________________________
Invoice Table
Dataset Name: Invoice
Description: The invoice dataset displays a chronological list of all invoice line items that have been added to your dispensary.
Unique Composite Keys: storeid
, invoice_id
, product_id
, accepted_date
Datetime Last Updated: last_retrieved_timestamp
Incremental Load Logic: Use the column accepted_date
as data appends per new invoice line item
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(accepted_date) = date(date_add(now(), INTERVAL -1 day)) "}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(accepted_date) >= $stored_last_update_datetime"}
stored_last_update_datetime
is your stored datetime for the last successful data pull
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
_________________________________________________________________________
Column Name |
Definition |
Example Output |
payment_date |
Date Payment Was Done |
2020-07-17T18:21:22 |
payment_user |
Employee ID |
1 |
payment_amount |
Amount Invoice Paid |
13,632.60 |
payment_notes |
Invoice Payment Note |
Manually closed without payment before cash handling was enabled |
transaction_id |
Invoice Payment ID (If Applicable) |
7f6cf821-aeb0-11e9-9df1-026d10f0d0f0 |
invoice_id |
Invoice ID |
d6962ba7-f864-433a-aada-1dcbf05ac0fa |
product_id |
Dispensary Product ID |
b34192da-ed7f-4dba-852f-dd563d250eca |
cost_id |
Cost ID |
5a944365-75a5-43cf-a860-e0ef62997fe5 |
invoice_number |
Invoice Number |
8004 |
created_date |
Date Invoice Created |
2020-07-17T18:21:22 |
due_date |
Date Invoice Payment Due |
2020-07-17T18:30:31 |
accepted_date |
Date Invoice Accepted |
2020-07-17T18:30:31 |
term_id |
Term ID |
636bc474-49b4-11e9-8035-774805423a5c |
distributor_id |
Distributor ID |
8ef734ee-8955-468c-8380-1ce6967f3294 |
inventory_location |
Location ID where invoice was sent |
b0c09f06-a878-11e9-936d-025c41018ccc |
external_invoice_id |
External ID |
|
invoice_cost_id |
Invoice Cost ID |
fbb90ddc-b464-4fb0-aee5-041e00ad7639 |
productname |
Product Name |
BLUE DREAM .5G |
producttype |
Product Type |
CARTRIDGE |
productsubtype |
Product Subtype |
POD |
unitofmeasure |
Unit of Measure |
GRAMS |
discount |
Discount |
0 |
base_cost |
Base Cost For Invoice Line |
10 |
qty |
Quantity For Invoice Line |
200 |
total_cost |
Total Cost For Invoice Line |
2540 |
payment_transaction_type |
Payment Transaction Type |
CASH |
fees_from_lines |
Fees From Lines |
0 |
unspread_fees |
0 |
|
unspread_eligible_fees |
0 |
|
terms |
Terms |
COD |
storeid |
Storeid |
230 |
total_final_cost |
47625 |
|
productbrand |
Product Brand |
STIIIZY |
vendorname |
Vendor Name |
JBTB HOLDINGS INC |
excise_tax |
Tax |
540 |
total_cost_final |
Total Cost Final For Invoice |
47625 |
payment_status |
Status |
OTHER |
_________________________________________________________________________
Payment Table
Dataset Name: Payments
Description: This dataset displays a chronological list of all payments applied to a ticket. Tickets with multiple payments will see the payment amounts split into multiple rows, however, they will share the same Ticket ID.
Unique Composite Keys: dateclosed
, storeid
, paymentid
Datetime Last Updated: last_retrieved_timestamp
Incremental Load Logic: Use the columndateclosed
as data appends per new payment completed.
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(dateclosed) = date(date_add(now(), INTERVAL -1 day)) "}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(dateclosed) >= $stored_last_update_datetime"}
Stored_last_update_datetime
is your stored DateTime for the last successful data pull
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
_________________________________________________________________________
Column Name |
Definition |
Example Output |
payment_date |
Date Payment Was Accepted |
2017-11-14T15:47:57 |
paymentid |
Unique Payment ID |
ff8081815fba6765015fbceea72f0093 |
paymentname |
Payment Name (Cash, Cashless ATM, Check, Check Sales, Credit Card, Custom, Debit Card, INtegrated ATM, Points, Rewards, cheque, externalapi) |
Cash |
originalamount |
Amount Paid |
326.58 |
changeamount |
Change Recieved (Original Amount - Gross Receipts Paid) |
0.0 |
grossreceiptspaid |
Ticket Total |
326.58 |
percent_of_ticket |
Null |
"" |
ticketid |
Unique Ticket ID (Non-customer facing) |
ff8081815fba6765015fbceb1695007b |
ticketalphanum |
Unique Alpha Numumeral ID (Customer Facing) |
WE4D20L |
dateclosed |
Date ticket closed |
2017-11-14 |
timeclosed |
Time ticket closed |
1970-01-01T15:47:57 |
paymenttype |
Payment Type (Cash, Cash Refund, Cashless ATM, Cashless ATM Refund, Check, Check Refund, Check Sales, Check Sales Refund, Credit Card, Credit Card Refund, Custom, Debit Card, Debit Card Refund, Integrated ATM, Points, Points Refund, Rewards, Rewards Refund, cheque, externalapi) |
"Cash" or "Cash Refund" |
customertype |
Customer Type (MEDICAL-MMID, MEDICAL-REGULAR, ADULT-USE) |
MEDICAL-REGULAR |
seconds_to_payment |
Payment Transaction Time |
233.0 |
customergroups |
Customer Group |
LOCALS |
dateopen |
Date ticket was created |
2017-11-14T00:00:00 |
timeopened |
Time ticket was created |
2019-09-05T15:44:04 |
datelastmodified |
Date ticket was last modified |
2019-09-05T00:00:00 |
timelastmodified |
Time ticket was last modified |
2019-09-05T02:52:50 |
datepaymentprocessed |
Date ticket was last modified |
2017-11-14T00:00:00 |
timepaymentprocessed |
Time ticket was last modified |
1970-01-01T15:47:57 |
authcode |
Authorization Code |
N/A |
refcode |
Reference Code |
N/A |
ticketlineid |
Null |
"" |
metrcreceipt |
Metrc Sales ID |
"" |
activity |
Null |
"" |
cashier |
Cashier |
Drew |
approver |
Cashier Approver |
Drew |
documentid |
Document ID |
LIC3660826 |
age |
Customer Age |
36 |
city |
Customer City |
WASHINGTON |
zip |
Customer Zip |
74555 |
gender |
Customer Gender |
F |
customer_uuid |
Customer Unique ID |
88deb6b9-5251-3853-b73e-20af025d4528 |
storeid |
Store ID |
230 |
week_year |
Year - Week |
2017-46 |
last_retrieved_timestamp |
the time we last retrieved data from SellTreez |
2021-06-09T14:18:30 |
_________________________________________________________________________
Ticketline Table
Dataset Name: Ticketline
Description: The ticketline dataset displays a chronological list of all ticketline items sold or returned in your shop. Each row contains product details around attributes, state tracking, cost, and revenue.
Unique Composite Keys: dateclosed
, storeid
,ticketlineid
Datetime Last Updated: last_retrieved_timestamp
Incremental Load Logic: Use the columndateclosed
as data appends per new sale completed
Pull Previous Day: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(dateclosed) = date(date_add(now(), INTERVAL -1 day)) "}
Pull Previous Successful Update: Data-Raw Append Example:
{"sql": "SELECT * FROM table WHERE date(dateclosed) >= $stored_last_update_datetime"}
Stored_last_update_datetime
is your stored datetime for the last successful data pull
TIP: As a best practice, we recommend that you save the timestamp for each of your dataset pulls as 'stored_last_update_datetime'. Note that this value is not saved in our datasets, but is a suggestion.
_________________________________________________________________________
Column Name |
Definition |
Example Output |
verification_pending_min |
Datetime Verification Started |
1/24/2020 13:24 |
verification_pending_max |
Datetime Verification Ended |
1/24/2020 13:24 |
awaiting_processing_min |
Datetime Awaiting Processing Started |
1/24/2020 13:24 |
awaiting_processing_max |
Datetime Awaiting Processing Ended |
1/24/2020 13:24 |
in_process_min |
Datetime In Process Started |
1/24/2020 13:24 |
in_process_max |
Datetime In Process Ended |
1/24/2020 13:24 |
packed_ready_min |
Datetime Packed And Ready Started |
1/24/2020 13:24 |
packed_ready_max |
Datetime Packed And Ready Ended |
1/24/2020 13:24 |
out_for_delivery_min |
Datetime Out for Delivery Started |
1/24/2020 13:24 |
out_for_delivery_max |
Datetime Out for Delivery Ended |
1/24/2020 13:24 |
channel |
Sales Channel |
eCommerce |
payment_date |
Date Payment Was Accepted |
|
pre_paid |
Order Prepaid or Not |
N |
masked_ticketlineid |
Masked Ticketline ID |
cfbc7aaf1921d1d6bc731c172fb1b46a |
masked_ticketid |
Masked Ticket ID |
d777be50cafc438ba09c1788565eb96b |
masked_ticketalphaid |
Masked Ticket Alphanumeric ID |
53c5f031dd2aa7207da88b364eed248f |
masked_storeid |
Masked Store ID |
c59db769700fc98d0d260730b89e1889 |
masked_customer_uuid |
Masked Customer Universal ID |
7164a51fe7042552ff4e50461dc65c0b |
retail_brand |
Raw Brand Name |
NORTH |
ticketid |
Ticket ID |
d7007eae-70de-43ff-be73-ba8ef3163d25 |
products_remaining |
Product Count Remaining |
0 |
register |
Cash Drawer Name |
CASH DRAWER 3-A |
customer_type |
Customer Ordering Type (Adult Use vs Medical vs MMID) |
MEDICAL-REGULAR |
type |
Order Type (Sale or Return) |
SALE |
customer_state |
Customer State |
MI |
customer_city |
Customer City |
PORTAGE |
zip_code |
Customer Zip Code |
49002 |
age |
Customer Age |
34 |
age_group |
Customer Age Group |
30-39 |
cashier |
Cashier Name |
CTEETERS |
approver |
Cashier Approver |
CTEETERS |
producttype |
Ticketline Product Type |
EXTRACT |
productsubtype |
Ticketline Product Subtype |
BUDDER |
productattributes |
Ticketline Product Attributes |
EDIBLES,CHOCOLATE |
tier |
Ticketline Product Tier |
Top Shelf Tier |
weight |
Ticletline Product Weight |
1 |
unitofmeasure |
Ticketline Unit of Measure |
EACH |
priceunit |
Ticketline Price Per Unit |
40 |
qty |
Ticketline Unit Quantity |
1 |
grosssales |
Ticketline Gross Sale (Subtotal Before Deductions) |
40 |
discounts |
Ticketline Discount |
20 |
returns |
Ticketline Return Amount (Will be 0 if it’s a sale) |
0 |
netsales |
Ticketline Net Sales (Subtotal After Discount) |
20 |
taxes |
Ticketline Tax Amount (Total Tax For Ticketline) |
1.2 |
grossreceipts |
Ticketline Gross Receipt (Grand Total Including Deductions and Taxes) |
21.2 |
cost |
Ticketline Cost w/ Excise |
22 |
cost_without_excise |
Ticketline Cose w/o Excise |
22 |
grossincome |
Ticketline Gross Profit (Net Sale - Cost) |
-2 |
avgproductmargin |
Avg Product Margin |
1945 |
invoiceid |
Ticketline Invoice Number |
165 |
ticketlineid |
Ticketline ID |
4b2bb4de-8c41-4ac9-a297-b5794320fef3 |
ticketalphaid |
Ticket Alpha Numerical ID |
3T8LXD |
dateclosed |
Ticket Date Closed |
1/24/2020 |
date_time_closed |
Ticket Date Time Closed |
2020-01-24T13:31:18 |
date_time_opened |
Ticket Date Time Opened |
2020-01-24T13:24:11 |
sku |
Product SKU |
IOrH,1A4050100001645000008250,oOz |
customer_uuid |
Customer Universal ID |
03176220-e399-3f44-86a0-25b43801417d |
customer_signup_date |
Customer Profile Creation Date |
2020-01-24T16:21:47 |
reward_balance |
Customer Reward Point Balance |
36 |
inventory_location |
Inventory Location |
FRONT OF HOUSE |
ticket_updated_date |
Ticket Last Update Date |
2020-02-04T07:25:12 |
line_updated_date |
Ticektline Last Update Date |
2020-01-24T13:30:06 |
last_visit |
Customer Last Visit Date |
1/24/2020 13:24 |
salestaxamount |
Sales Tax Amount |
1.2 |
is_arms_length |
Is Ticketline Arm Length or Non Arm's Length (1 = Yes, 0 = No) |
1 |
is_cannabis |
Is Ticketline a cannabis product or not (1 = Yes, 0 = No) |
1 |
ticket_type |
Ticket Fulfillment Type (POS, EXPRESS, etc) |
POS |
statetrackingid |
State Tracking ID |
1A4050100001645000008250 |
customer_country |
Customer Country |
|
customer_groups |
Customer Groups |
|
avgproductmarginpreexcise |
Avg Product Margin (w/o Excise) |
1945 |
product_id |
Dispensary Product ID |
c3959cab-0582-11ea-9c46-02a831a0e4d2 |
inventory_date |
Ticketline Inventory Date |
2019-11-12T11:29:37 |
customer_mmid_expiration |
Customer Medical Card Expiration Date |
|
ticket_notes |
Ticket Note |
|
gender |
Gender (M = Male, F = Female, U = Unknown) |
M |
open_date |
Date ticket was opened / created |
8/22/2019 |
tz_brand_id |
Brand Mapping ID |
10608 |
tz_product_id |
Product Mapping ID |
2 |
tz_productname |
Product Mapping Name |
~~BOGO~~SOUTH FORK KUSH 29% |
tz_brandname |
Brand Mapping Name |
NO BRAND |
storeid |
Unique Store ID |
230 |
customer_source |
Customer Source |
FRIENDS & FAMILY |
distributor |
Customer Input Distributor Name |
TREEZ |
first_visit |
Is this the customers first visit (1 = yes , 0 = no) |
1 |
repeat_purch |
Has this customer purchased this product before (1 = yes, 0 = no) |
0 |
ticket_status |
Ticket Status |
COMPLETED |
custom |
Custom Tax |
|
customname |
Custom Tax Name |
|
customtaxamount |
Custom Tax Amount |
|
dynamic |
Dynamic Tax |
|
dynamictaxname |
Dynamic Tax Name |
|
dynamicdtaxamount |
Dynamic Tax Amount |
|
excise |
Excise Tax |
|
excisetaxname |
Excise Tax Name |
|
excisetaxamount |
Excise Tax Amount |
|
sales |
Sales Tax |
SALES |
salestaxname |
Sales Tax Name |
SALES TAX |
inventory_sku |
Inventory SKU |
|
harvest_date |
Havest Date |
|
external_id |
Ticketline External Product |
|
total_mg_thc |
Ticketline Total MG THC |
|
total_mg_cbd |
Ticketlient Total MG CBD |
|
extraction_method |
Extraction Method |
|
tz_customer_id |
Customer ID mapping |
n/a |
thc_perc |
THC Percentage |
0 |
cbd_perc |
CBD Percentage |
0 |
package_size |
Package Size |
SINGLE-UNIT |
productbrand |
Semi Brand Mapping Name |
NO BRAND |
anonymized_productbrand |
Anonymized Brand Name |
NO BRAND |
productname |
Raw Product Name |
LEMON SOUR DIESEL |
classification |
Product Classificaton |
S/I |
size |
Raw Product Size |
1G |
thc_ratio |
THC Ratio |
0 |
repeat_purch_sku |
Has this customer purchased this product before (1 = yes, 0 = no) |
0 |
verification_pending_employee |
Employee Who moved order to Verification Pending |
|
awaiting_processing_employee |
Employee Who moved order to Awaiting Processing |
|
in_process_employee |
Employee Who moved order to In Process |
|
packed_ready_employee |
Employee Who moved order to Packed Ready |
|
out_for_delivery_employee |
Employee Who moved order to Out For Delivery |
|
completed_employee |
Employee who Compled Order |
|
report_last_updated |
Report Last Updated |
|
zip |
Zip Code |
49002 |
lat |
Latitude |
42.19737 |
lng |
Longitude |
-85.56095 |
income_household_median |
Avg Median Income for County |
50771 |
city |
City |
PORTAGE |
county_name |
County |
KALAMAZOO |
state_name |
State Name |
MICHIGAN |
county_fips |
26077 |
|
region |
Region |
MI |
store_state |
Store State |
MI |
year |
Year |
2020 |
month |
Month |
1 |
week_year |
Week of Year |
2020-4 |
last_retrieved_timestamp |
the time we last retrieved data from SellTreez |
"2021-06-09T14:18:30" |
_________________________________________________________________________
Comments
0 comments
Article is closed for comments.