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.

Dataset ID: 25a062ab-573b-4c48-b9ab-7808e0377261

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 NameDefinitionExample Output
verification_pending_minDatetime Verification Started1/24/2020 13:24
verification_pending_maxDatetime Verification Ended1/24/2020 13:24
awaiting_processing_minDatetime Awaiting Processing Started1/24/2020 13:24
awaiting_processing_maxDatetime Awaiting Processing Ended1/24/2020 13:24
in_process_minDatetime In Process Started1/24/2020 13:24
in_process_maxDatetime In Process Ended1/24/2020 13:24
packed_ready_minDatetime Packed And Ready Started1/24/2020 13:24
packed_ready_maxDatetime Packed And Ready Ended1/24/2020 13:24
out_for_delivery_minDatetime Out for Delivery Started1/24/2020 13:24
out_for_delivery_maxDatetime Out for Delivery Ended1/24/2020 13:24
channelSales ChanneleCommerce
payment_dateDate Payment Was AcceptedYYYY-MM-DD
pre_paidOrder Prepaid or NotN
masked_ticketlineidMasked Ticketline IDcfbc7aaf1921d1d6bc731c172fb1b46a
masked_ticketidMasked Ticket IDd777be50cafc438ba09c1788565eb96b
masked_ticketalphaidMasked Ticket Alphanumeric ID53c5f031dd2aa7207da88b364eed248f
masked_storeidMasked Store IDc59db769700fc98d0d260730b89e1889
masked_customer_uuidMasked Customer Universal ID7164a51fe7042552ff4e50461dc65c0b
retail_brandRaw Brand NameNORTH
ticketidTicket IDd7007eae-70de-43ff-be73-ba8ef3163d25
products_remainingProduct Count Remaining0
registerCash Drawer NameCASH DRAWER 3-A
customer_typeCustomer Ordering Type (Adult Use vs Medical vs MMID)MEDICAL-REGULAR
typeOrder Type (Sale or Return)SALE
customer_stateCustomer StateMI
customer_cityCustomer CityPORTAGE
zip_codeCustomer Zip Code49002
ageCustomer Age34
age_groupCustomer Age Group30-39
cashierCashier NameCTEETERS
approverCashier ApproverCTEETERS
producttypeTicketline Product TypeEXTRACT
productsubtypeTicketline Product SubtypeBUDDER
productattributesTicketline Product AttributesEDIBLES,CHOCOLATE
tierTicketline Product TierTop Shelf Tier
weightTicketline Product Weight1
unitofmeasureTicketline Unit of MeasureEACH
priceunitTicketline Price Per Unit40
qtyTicketline Unit Quantity1
grosssalesTicketline Gross Sale (Subtotal Before Deductions)40
discountsTicketline Discount20
returnsTicketline Return Amount (Will be 0 if it’s a sale)0
netsalesTicketline Net Sales (Subtotal After Discount)20
taxesTicketline Tax Amount (Total Tax for Ticketline)1.2
grossreceiptsTicketline Gross Receipt (Grand Total Including Deductions and Taxes)21.2
costTicketline Cost w/ Excise22
cost_without_exciseTicketline Cose w/o Excise22
grossincomeTicketline Gross Profit (Net Sale - Cost)-2
avgproductmarginAvg Product Margin1945
invoiceidTicketline Invoice Number165
ticketlineidTicketline ID4b2bb4de-8c41-4ac9-a297-b5794320fef3
ticketalphaidTicket Alphanumeric ID3T8LXD
dateclosedTicket Date Closed1/24/2020
date_time_closedTicket Date–Time Closed2020-01-24T13:31:18
date_time_openedTicket Date–Time Opened2020-01-24T13:24:11
skuProduct SKUIOrH,1A4050100001645000008250,oOz
customer_uuidCustomer Universal ID03176220-e399-3f44-86a0-25b43801417d
customer_signup_dateCustomer Profile Creation Date2020-01-24T16:21:47
reward_balanceCustomer Reward Point Balance36
inventory_locationInventory LocationFRONT OF HOUSE
ticket_updated_dateTicket Last Update Date–Time2020-02-04T07:25:12
line_updated_dateTicektline Last Update Date–Time2020-01-24T13:30:06
last_visitCustomer Last Visit Date1/24/2020 13:24
salestaxamountSales Tax Amount1.2
is_arms_lengthIs Ticketline Arm Length or Non Arm's Length (1 = Yes, 0 = No)1
is_cannabisIs Ticketline a cannabis product or not (1 = Yes, 0 = No)1
ticket_typeTicket Fulfillment Type (POS, EXPRESS, etc)POS
statetrackingidState Tracking ID1A4050100001645000008250
customer_countryCustomer CountryUSA
customer_groupsCustomer GroupsVETERAN
avgproductmarginpreexciseAvg Product Margin (w/o Excise)1945
product_idDispensary Product IDc3959cab-0582-11ea-9c46-02a831a0e4d2
inventory_dateTicketline Inventory Date2019-11-12T11:29:37
customer_mmid_expirationCustomer Medical Card Expiration Date2019-11-12T11:29:37
ticket_notesTicket NoteCustomer note
genderGender (M = Male, F = Female, U = Unknown)M
open_dateDate ticket was opened / created8/22/2019
tz_brand_idBrand Mapping ID10608
tz_product_idProduct Mapping ID2
tz_productnameProduct Mapping NameBOGOSOUTH FORK KUSH 29%
tz_brandnameBrand Mapping NameNO BRAND
storeidUnique Store ID230
customer_sourceCustomer SourceFRIENDS & FAMILY
distributorCustomer Input Distributor NameTREEZ
first_visitIs this the customers first visit (1 = yes , 0 = no)1
repeat_purchHas this customer purchased this product before (1 = yes, 0 = no)0
ticket_statusTicket StatusCOMPLETED
customCustom TaxCUSTOM
customnameCustom Tax NameLOCAL TAX
customtaxamountCustom Tax Amount1.0
dynamicDynamic TaxDYNAMIC
dynamictaxnameDynamic Tax NameSALES TAX - CUPERTINO, SANTA CLARA
dynamicdtaxamountDynamic Tax Amount1.0
exciseExcise TaxEXCISE
excisetaxnameExcise Tax NameEXCISE TAX
excisetaxamountExcise Tax Amount1.1
salesSales TaxSALES
salestaxnameSales Tax NameSALES TAX
inventory_skuInventory SkubhK
harvest_dateHavest DateYYYY-MM-DD
external_idTicketline External Product391882
total_mg_thcTicketline Total MG THC0.15
total_mg_cbdTicketline Total MG CBD0.1
extraction_methodExtraction MethodSOLVENT
tz_customer_idCustomer ID mappingn/a
thc_percTHC Percentage0
cbd_percCBD Percentage0
package_sizePackage SizeSINGLE-UNIT
productbrandSemi Brand Mapping NameNO BRAND
anonymized_productbrandAnonymized Brand NameNO BRAND
productnameRaw Product NameLEMON SOUR DIESEL
classificationProduct ClassificationS/I
sizeRaw Product Size1G
thc_ratioTHC Ratio0
repeat_purch_skuHas this customer purchased this product before (1 = yes, 0 = no)0
verification_pending_employeeEmployee Who moved order to Verification PendingF16REK
awaiting_processing_employeeEmployee Who moved order to Awaiting ProcessingF16REK
in_process_employeeEmployee Who moved order to In ProcessF16REK
packed_ready_employeeEmployee Who moved order to Packed ReadyF16REK
out_for_delivery_employeeEmployee Who moved order to Out For DeliveryF16REK
completted_employeeEmployee who Compled OrderF16REK
report_last_updatedReport Last Updated
zipZip Code49002
latLatitude42.19737
lngLongitude-85.56095
income_household_medianAvg Median Income for County50771
cityCityPORTAGE
county_nameCountyKALAMAZOO
state_nameState NameMICHIGAN
county_fipsFIPS code for county26077
regionRegionMI
store_stateStore StateMI
yearYear2021
monthMonth1
datalake_dateDate that a store was added to the datalake2021-05-20
last_retrieved_timestampthe Date–Time we last retrieved data from SellTreez2021-06-09T14:18:30
og_productnameThe name of the product that was added to TreezMELONADE .5G