Customer Table

Dataset ID: 158ce9dd-7168-4b60-b4e0-060aa9f63ab2

Unique Composite Key(s): storeid, customer_signup_date, last_updated_at, customer_uuid

Datetime Dataset 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 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–Time Customer Signed Up	2000-01-01T00:00:00
last_updated_at	Date–Time Customer Last Updated	2000-01-01T00:00:00
last_visit	Date–Time 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	GOOGLE
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	Violations: Warning 1, Warning 2, Ban. (0 = Toggled Off , 1 = Toggled On)	0,0,0
city	Customer City	BERKELEY
State	Customer State	CA
zipcode	Customer Zip Code	94710
longitude	longitude	0
latitude	latitude	0
permit_expiration_date	Customer Permit Expiration Date	YYYY-MM-DD
tz_customer_id	Name Mapping ID	n/a
storeid	ID of Store	420
datalake_date	The date that a store was added to the datalake.	2021-12-01
lifetime_spend	amount of money that was spent by the customer over their lifetime	155.4558999136
purchases	number of purchases made by the customer.	1.0
flower	amount spent by product type	0.0
beverage	amount spent by product type	96.17
cartridge	amount spent by product type	155.45
cbd	amount spent by product type	0.0
edible	amount spent by product type	0.0
extract	amount spent by product type	70.39
merch	amount spent by product type	0.0
misc	amount spent by product type	0.0
plant	amount spent by product type	0.0
pill	amount spent by product type	0.0
preroll	amount spent by product type	0.0
prepack	amount spent by product type	0.0
tincture	amount spent by product type	0.0
topical	amount spent by product type	0.0
avg_day_between_visits	Average number of days between visits by the customer	12.0
avg_visit_time_seconds	average amount of time spent in store	541.0
avg_checkout_time_seconds	average amount of time spent with an open cart	212.0
visit_without_purchase	number of visits from the customer where they did not purchase anything.	0.0
first_visit_mix	Mixture of categories purchased on first visit	CARTRIDGE
second_visit_mix	Mixture of categories purchased on second visit	CARTRIDGE,FLOWER
third_visit_mix	Mixture of categories purchased on third visit	EDIBLE
most_freq_mix	Mixture of most frequently purchased categories based on historical purchases	CARTRIDGE
last_retrieved_timestamp	Date–Time we last retrieved data from SellTreez	2021-06-09T14:18:30