Revealing the hidden depths of complicated data

How can we help a retailer use data to make great business decisions?

Opportunity was knocking for an online outdoor apparel retailer. They’d picked up a life-changing new sales territory, and as such, they needed to make smart decisions when ordering their winter stock.

Retail is all about predicting what will be popular, months before it happens. To succeed in the online retail space means to pore over sales metrics and the true cost of shipping and returns. As such, the outdoor apparel retailer knew the value of data-driven decisions, and had been using Microsoft Power BI to visualize and report on their Amazon sales and shipping data. 

But they needed more detailed data about individual products in order to make strategic decisions. If the data showed that a certain coat was being returned more often than others, for example, they would know to pull it and re-examine their approach to selling it. But their existing data reporting was too high-level to be useful. 

Amazon data is complicated to process. It’s always changing, the order of the fields seems random, and historical data disappears when an item is deleted. The retailer needed access to live product and shipping data that they could both manually analyze and visualize without being beholden to Amazon’s XML structure. And nobody had been able to crack the complicated data structure yet. Nobody knew how much valuable information might be buried there, and the Pixo team was determined to help our retailer partners by uncovering it.

Building a custom data warehouse solution

We set out to untangle a lot of complicated, yet incomplete data by creating a custom data warehouse that integrates with both Power BI and Amazon Web Services.

The moment comes to re-evaluate

When you’re dealing with large amounts of data that are thoughtlessly structured, you don’t always know what you don’t know. While coding with another dev, we realized the structure of the data was far more complicated than we originally anticipated. There were many levels of heavily nested data that was difficult to extract into a coherent table.

Our engineers knew they needed to shift gears to make sure they got the data architecture right from the start, avoiding costly technical debt. They sketched a tentative data structure together to ensure they’d considered all angles and agreed on an approach before continuing. This first step, taking a breather to re-evaluate the plan, led to a series of productive conversations with the client to more richly understand the data landscape and its constraints.

The power of live, accurate data at your fingertips

The retailers were used to tallying customer acquisition costs, calculating shipping options, and comparing vendors. At this milestone of Pixo’s engagement, they’re finally able to thrive at those routine tasks, with timely, complete data to guide them. 

Their new data warehouse saves their data from the real-time Amazon API. It runs throughout the day, ensuring they have an up-to-date picture of exactly what’s happening with their retail operation. 

And that data picture is finally fully revealed. “There’s a guy whose whole business is this, and he couldn’t figure it out,” the retailer’s co-owner said when the Amazon data structure was finally mapped out. 

But the best rewards will come when we see the retailers thrive in the future, knowing that our work gave them measurably better chances to be successful. With their new data warehouse and the ability to transform raw data into a format that Power BI can easily use, they can make better-informed business decisions and take on their new customer territory with confidence.

Need more from your data?

If you think the data you collect could work harder for your operations, sales, or inventory — get in touch! We’d love to explore how a custom data app could solve your unique challenge.