Ticketline Table

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 Key(s): dateclosed, storeid, ticketlineid

Datetime Dataset Last Updated: last_retrieved_timestamp

Incremental Load Logic: Use the column dateclosed 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

YYYY-MM-DD

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

Ticketline 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 Alphanumeric ID

3T8LXD

dateclosed

Ticket Date Closed

1/24/2020

date_time_closed

Ticket Date–Time Closed

2020-01-24T13:31:18

date_time_opend

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–Time

2020-02-04T07:25:12

line_updated_date

Ticektline Last Update Date–Time

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

USA

customer_groups

Customer Groups

VETERAN

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

2019-11-12T11:29:37

ticket_notes

Ticket Note

Customer 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

BOGOSOUTH 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

CUSTOM

customname

Custom Tax Name

LOCAL TAX

customtaxamount

Custom Tax Amount

1.0

dynamic

Dynamic Tax

DYNAMIC

dynamictaxname

Dynamic Tax Name

SALES TAX - CUPERTINO, SANTA CLARA

dynamicdtaxamount

Dynamic Tax Amount

1.0

excise

Excise Tax

EXCISE

excisetaxname

Excise Tax Name

EXCISE TAX

excisetaxamount

Excise Tax Amount

1.1

sales

Sales Tax

SALES

salestaxname

Sales Tax Name

SALES TAX

inventory_sku

Inventory Sku

bhK

harvest_date

Havest Date

YYYY-MM-DD

external_id

Ticketline External Product

391882

total_mg_thc

Ticketline Total MG THC

0.15

total_mg_cbd

Ticketline Total MG CBD

0.1

extraction_method

Extraction Method

SOLVENT

tz_custtomer_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 Classification

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

F16REK

awaiting_processing_employee

Employee Who moved order to Awaiting Processing

F16REK

in_process_employee

Employee Who moved order to In Process

F16REK

packed_ready_employee

Employee Who moved order to Packed Ready

F16REK

out_for_delivery_employee

Employee Who moved order to Out For Delivery

F16REK

completted_employee

Employee who Compled Order

F16REK

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

FIPS code for county

26077

region

Region

MI

store_state

Store State

MI

year

Year

2021

month

Month

1

week_year

Week of Year

2020-4

last_retrieved_timestamp

the Date–Time we last retrieved data from SellTreez

2021-06-09T14:18:30


Did this page help you?