How I Used Postgresql To Diagnose An Ecommerce Revenue Decline

Ecommerce businesses rarely struggle because of one obvious problem.

Revenue falls, and the first instinct is often to blame pricing, weak products, seasonality, or “the market.” But those explanations are usually too vague to be useful.

The better question is:

What actually changed inside the business?

Did the company stop acquiring new customers?
Did returning customers buy less?
Did average order value fall?
Did top product categories lose momentum?

I built a PostgreSQL project to answer exactly those questions using transactional ecommerce data.

The business problem

This analysis started with a simple question:

Why did ecommerce revenue decline sharply in 2018-Q3 compared to the previous quarter?

That question matters because top-line revenue alone does not tell you where to look next. If a business reacts without understanding the real cause, it can waste time and money fixing the wrong thing.

For example:

If the issue is weaker acquisition, the next step may be channel, traffic, or campaign analysis.

If the issue is retention, the next step may be lifecycle email, re-engagement, or loyalty strategy.

If the issue is lower order value, the next step may be pricing, merchandising, or bundling.

If the issue is category weakness, the next step may be assortment, inventory, or positioning.

The goal of this project was to move beyond “revenue went down” and identify the most likely driver behind the decline.

The dataset

I used the Brazilian Olist ecommerce dataset, which includes over 100,000 orders across customers, orders, order items, and products.

The analysis was performed in PostgreSQL using command-line SQL.

This made it possible to evaluate:

quarter-over-quarter revenue trends

new vs returning customer revenue

average order value

product category contribution

That combination is enough to begin diagnosing where business performance is actually breaking down.

The approach

I joined customer, order, order item, and product tables in PostgreSQL and focused on four questions:

How much did revenue decline quarter over quarter?

Was the decline driven more by new customers or returning customers?

Did average order value change enough to explain the drop?

Did major product categories lose revenue contribution?

This approach matters because revenue decline is a symptom, not a diagnosis.

A business can lose revenue because of fewer customers, lower spend per order, weaker repeat behavior, or underperformance in key categories. You need to separate those factors before deciding what to do next.

What I found

The results were fairly clear.

Revenue declined by approximately 39% quarter over quarter in 2018-Q3.

The largest driver of the decline was a sharp drop in new customer revenue, which points to an acquisition slowdown.

Returning customer revenue also declined, but it was a secondary contributor rather than the main cause.

Average order value decreased only slightly, around 4–5%, which suggests pricing or basket size was not the primary issue.

High-volume product categories such as watches, health and beauty, and home goods contributed less revenue during the decline period, which supported the broader slowdown.

Business interpretation

The most important conclusion was this:

The primary problem was weaker customer acquisition, not pricing.

That distinction matters because it changes the next move.

If average order value had collapsed, the business might need pricing changes, merchandising adjustments, or bundling strategy.

If returning customer revenue had been the dominant issue, the business might need retention campaigns, win-back flows, or loyalty improvements.

But the strongest signal here pointed to reduced ability to bring in new customers.

That means the highest-leverage next step would be to investigate:

marketing channel performance

campaign efficiency

top-of-funnel traffic sources

acquisition spend leading into 2018-Q3

Retention work could still help stabilize revenue, but acquisition appears to be the main issue management should investigate first.

Why this kind of analysis matters

A lot of business reporting stops at descriptive metrics.

Revenue is down.
Orders are down.
A category is weak.

That is useful, but it is not enough.

The real value comes from translating raw data into decisions.

This kind of SQL analysis helps answer questions like:

Are sales falling because fewer new customers are being acquired?

Is repeat customer revenue weakening?

Are top product categories losing momentum?

Is average order value actually the issue?

Where should management investigate first?

That is the difference between a dashboard and a diagnosis.

What this project demonstrates

This project is a good example of how data analysis can be used to solve business problems, not just produce charts.

It demonstrates:

PostgreSQL analysis across multiple relational tables

customer segmentation into new vs returning behavior

revenue trend analysis

average order value analysis

category contribution analysis

translation of findings into business recommendations

For ecommerce operators, analysts, and founders, this is the kind of analysis that connects data work to business action.

Repo

You can view the project here:

GitHub Repo

Final thought

When an ecommerce business starts slipping, the most expensive mistake is guessing.

Revenue decline is not a diagnosis. It is a symptom.

The real job is figuring out whether the business is losing customers, losing repeat behavior, losing order value, or losing category strength.

That is where analysis becomes useful.

Need this type of analysis for your store?

I offer a fixed-price, async Ecommerce Revenue Diagnosis Audit for businesses that want clarity on what may be driving weak performance.

Start here