Abandoned carts without a new table: PostHog's HogQL

A few months ago I had to solve something that looked simple: for each store on PideAí, find out how many customers add products to their cart and never complete the order.

My first instinct was the usual one. An abandoned_carts table, a cron job running every hour, logic to flag a cart as abandoned after X minutes without an order, and another job to send the WhatsApp reminder. A familiar architecture, well documented and boring.

Before writing a single line, I asked myself whether I already had this information somewhere.

I did. PideAí had been sending events to PostHog for months. Every time a customer added a product to a store’s cart, an event went out. Every time they placed the order, another one did. The data was already there. What I was missing was a way to query both together.

What PostHog is

PostHog is an open source product analytics platform. Put simply, it shows you what users do inside your application.

Every action that matters (viewing a product, adding it to the cart, dropping out of checkout, placing an order) gets recorded as an event with its properties. From there you can build funnels, watch session recordings and run A/B tests instead of working from guesses.

What sets it apart from Google Analytics is the focus. Google Analytics tells you how many pages someone visited, while PostHog tells you exactly what they did inside your app.

The part fewer people know about, and the key to this whole story, is that PostHog also lets you run SQL queries against your events from your own application. They call it HogQL.

What the classic approach costs

Building abandoned carts the classic way isn’t hard. The cost lies in the part almost nobody mentions, which is maintenance.

You have to keep the cart state in your database in sync with what happens on the frontend. If the user closes the browser, if the session expires, if there’s a network error, the table goes stale. Cron jobs fail silently. And the day you want a new filter, like “abandoned carts worth more than 50 dollars”, you end up touching queries, migrations and jobs.

All of that to answer a question that boils down to this: was there an event A that wasn’t followed by an event B?

HogQL: SQL over your events

HogQL is PostHog’s SQL dialect. It isn’t limited to powering the charts in their UI. You can query it through the API from your application and use the result as if it came from your own database.

At PideAí, instead of maintaining a carts table per store, I wrote a query that joins two events:

WITH cart_events AS (
  SELECT person_id, properties.cart_value, timestamp
  FROM events
  WHERE event = 'product_added_to_cart'
    AND properties.store_id = '{storeId}'
    AND timestamp >= now() - INTERVAL {days} DAY
),
completed_orders AS (
  SELECT DISTINCT person_id, timestamp
  FROM events
  WHERE event = 'order_placed'
    AND properties.store_id = '{storeId}'
    AND timestamp >= now() - INTERVAL {days} DAY
)
SELECT
  count(DISTINCT c.person_id) as total_abandoned,
  sum(toFloat(c.cart_value)) as total_value,
  avg(toFloat(c.cart_value)) as avg_cart_value
FROM cart_events c
LEFT JOIN completed_orders o
  ON c.person_id = o.person_id
  AND o.timestamp > c.timestamp
  AND o.timestamp <= c.timestamp + INTERVAL 24 HOUR
WHERE o.person_id IS NULL

The logic is straightforward. It takes every customer who added something to a store’s cart and matches them against the ones who placed an order within the next 24 hours. Whoever doesn’t show up in that match is an abandoned cart.

There’s no table, no job and nothing to keep in sync.

What it looks like in code

In React, the hook that feeds each store’s dashboard ended up like this:

export function usePostHogAbandonedCartStats(storeId: string, days = 30) {
  return useQuery({
    queryKey: ['abandoned-cart-stats', storeId, days],
    queryFn: () => getAbandonedCartStats(storeId, days),
    refetchInterval: 5 * 60 * 1000, // refreshes every 5 minutes
    enabled: !!storeId,
  });
}

Now every store can see how many carts were abandoned, how much money was left in them, the average cart value and the recovery rate, with data that refreshes every five minutes. And the database didn’t get a single migration.

What I took away from it

You rarely need more infrastructure. What you usually need is to ask better questions of the infrastructure you already have.

If you already track events with PostHog, Mixpanel or any other analytics tool, some of the answers you’re looking for elsewhere are probably sitting there. It comes down to whether the tool lets you query that data from your app or only gives you a closed dashboard.

PostHog lets you. At PideAí, that opened up a whole category of features we built without touching the main database.

So I’ll leave you with the question I was left with: how many tables in your database exist only because you didn’t know you could query that data in another tool?