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 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_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–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 | |
| 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_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 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 |
| datalake_date | Date that a store was added to the datalake | 2021-05-20 |
| last_retrieved_timestamp | the Date–Time we last retrieved data from SellTreez | 2021-06-09T14:18:30 |
| og_productname | The name of the product that was added to Treez | MELONADE .5G |
Updated about 1 year ago