Here is the query to create a segment of users who have abandoned their cart in Snowflake.
select * from users where "email" in (select "email" from (select events.* from (select * from cart_events where "tag" = 'added_to_cart') as events join (select "email", max("event_ts") as max_event_ts from (select * from cart_events where "tag" = 'cart_checkout') group by "email") as otherEvents on events."email" = otherEvents."email" where (events."event_ts" > otherEvents.max_event_ts and timestampdiff(minute, events."event_ts", current_timestamp()) > 2880) union (select * from (select * from cart_events where "tag" = 'added_to_cart') as events where "email" not in (select "email" from (select * from cart_events where "tag" = 'cart_checkout')) and timestampdiff(minute, events."event_ts", current_timestamp()) > 2880)))
But can non-technical folks write this query on top of Snowflake/BigQuery?
We launched our Audiences feature to solve this problem for non-technical users who still rely on the data warehouse. Here is our YC Launch
As we talked to more developers, we realised that many are trying to build products on top of their data warehouse for their customers or internal teams. But there is no easy way to make the data warehouse accessible to users who cannot write SQL.
To solve this problem, we are launching Audiences API, which enables developers to build products that generate user segments on top of data warehouses without SQL and then sync them to external destinations like Google Ads, Salesforce, etc.
Here is how the product will look once integrated with the Audience API.
select * from users where "email" in (select "email" from (select events.* from (select * from cart_events where "tag" = 'added_to_cart') as events join (select "email", max("event_ts") as max_event_ts from (select * from cart_events where "tag" = 'cart_checkout') group by "email") as otherEvents on events."email" = otherEvents."email" where (events."event_ts" > otherEvents.max_event_ts and timestampdiff(minute, events."event_ts", current_timestamp()) > 2880) union (select * from (select * from cart_events where "tag" = 'added_to_cart') as events where "email" not in (select "email" from (select * from cart_events where "tag" = 'cart_checkout')) and timestampdiff(minute, events."event_ts", current_timestamp()) > 2880)))
But can non-technical folks write this query on top of Snowflake/BigQuery?
We launched our Audiences feature to solve this problem for non-technical users who still rely on the data warehouse. Here is our YC Launch
https://www.ycombinator.com/launches/IHo-castled-build-audiences-on-your-data-warehouse-without-sql
As we talked to more developers, we realised that many are trying to build products on top of their data warehouse for their customers or internal teams. But there is no easy way to make the data warehouse accessible to users who cannot write SQL.
To solve this problem, we are launching Audiences API, which enables developers to build products that generate user segments on top of data warehouses without SQL and then sync them to external destinations like Google Ads, Salesforce, etc.
Here is how the product will look once integrated with the Audience API.
https://www.youtube.com/watch?v=yocbTTr-twU