How I Built a Dashboard to Visualize My Amazon Spending using Retool and Google Sheets

My wife and I became parents last year, and let me tell you - babies aren’t cheap. Amazon has profited handsomely from our baby business in recent months. As the little brown boxes piled up, we wondered how much we'd spent on Amazon in general, and on baby supplies in particular. What categories did we spend the most on? What was our biggest baby-related expense? (Spoiler - it was the “SlumberPod 3.0 Portable Privacy Pod Blackout Canopy Crib”)

To answer these questions, I had some mystery-solving to do. I found that Amazon provides a download of your purchase history as a CSV file. With that data at my fingertips, I wondered if I could build a simple “Amazon Purchases Insights Dashboard,” which, at a glance, would provide insights like the total money we spent on Amazon last year, money spent on each category, most expensive month, and more. With Retool, it turned out to be pretty simple.

An Amazon spending data dashboard, powered by Retool

In this post, I'll walk you through how I built this dashboard using a combination of Google Sheets, Retool, and even a splash of ChatGPT. Together, we will build a dashboard that can help us answer the following questions.

1. How much money did we spend on Amazon in 2022?
2. How much money did we spend each month in 2022?
3. What were the reasons for the high expenses in June 2022?
4. What category did we spend the most on in 2022?
5. How many items did we return or refund in 2022?

Whether you are a curious parent or just a data nerd, buckle up for a wild ride through Amazon spending data and visualization in Retool. For the impatient - feel free to simply download the JSON definition of this Retool app, which you can import into your own free Retool instance. Let’s get started!

Getting purchase history data from Amazon

This part was pretty easy. Amazon provides a way for you to download your purchase history in CSV format through the “Order History Report” page. For this analysis, I went with “Items" as the report type and chose the start date and end dates as Jan 1, 2022, to Dec 31, 2022. The reports usually take only a few seconds to generate, but if you have lots of orders, it may take longer.

A cutting edge and gorgeous UI for generating Amazon spending data

Once I had the CSV file, I imported it into Google Sheets using Google’s import feature.

Google Sheets allows you to import Sheet data from a CSV on your filesystem

After you import the data, it should look something like this.

We now have a Google Sheet with our Amazon purchase history

Connect Retool with Google Sheets

Now that the data is loaded into a Google Sheet, we are ready to connect it to Retool. Login to Retool and create a new Resource using the built-in Google Sheets integration. Follow the prompts to authenticate with Google. Here’s what the resource would look like once you have successfully authenticated.

Creating a Retool resource for the Google Sheet that contains your Amazon purchases

Create a new Retool app

Now that our purchase data is ready in Retool, we need to build the actual user interface to display it. Our Retool app will need a JavaScript query to fetch and format the Amazon data, which we will then need to access in several user interface components. Let's grab the data first.

Writing a JavaScript query to fetch our data

Before building the front-end, I did a quick mapping exercise to determine what data I would need for the insights I was trying to get. It turned out I could build the entire dashboard with a single resource query to load the data from Google Sheets, and three JavaScript transformers to format the data conveniently for my dashboard.

1. getDataFromGoogleSheets (query): This is the master query that pulls all the data I need from Google Sheets. It will act as the base for my transformers, which will aggregate specific insights.
2. getOrdersByMonth (transformer): This transformer will help us answer the question - "How much money did we spend each month?" It will return the total amount spent and a list of orders for each month.
3. getAnnualSpent (transformer): This will help us answer the question - "What's the total amount of money we spent in 2022?". It will return the total amount spent each month. We can add them together to get the annual spending.
4. getOrdersByCategories (transformer): This will help us answer the question - "How much money did we spend on each category?". It will return the total amount spent and a list of orders for each category.

Let’s create these four parts of the app.

Querying for all the data in our Google Sheet

Create a new Retool app, and then create a new Resource query to kick things off.

Creating a new resource query for our Google Sheet data

Next, select the Google Sheets resource you created earlier from the Resource dropdown, and then select the actual spreadsheet that contains your Amazon data. Name this query getDataFromGoogleSheets.

Selecting the Google Sheets Resource created earlier

That's it for this query - you can click the "Preview" or "Run" button to see the data from Amazon flowing in.

Create a transformer to group purchase data by month

Next, create a transformer called getOrdersByMonth - this transformer will reference the results of the getDataFromGoogleSheets query, and then return a JavaScript object that contains data in a convenient format for our UI components.

Creating a JavaScript transformer

Use this code for the transformer.

// Declare and assign the results of getDataFromGoogleSheets query to variable 'sheetData'
let sheetData = {{getDataFromGoogleSheets.data}};

// Declare an array of month names for easy reference
const monthNamesShort = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

// Object to store final data
const finalData = {};

// Loop through each month
for (let i = 0; i < 12; i++) {
  // Filter orders for current month
  const monthOrders = sheetData.filter(function(order) {
    const date = new Date(order["Order Date"]);
    return date.getMonth() === i;
  });

  // Calculate total amount for current month
  const totalAmount = monthOrders.reduce(function(total, order) {
    return total + order["Item Total"];
  }, 0);

  // Add current month data to finalData object
  finalData[monthNamesShort[i]] = {
    orders: monthOrders,
    totalAmount: totalAmount
  };
}

// Log and return finalData
console.log(finalData);
return finalData;

Here’s the shape of the object the transformer would return, containing all orders and their total grouped by month.

Output of the getOrdersByMonth transformer

Create a transformer to display total annual spend

Next, we will create a transformer that calculates and returns our total annual spend. Use the same process as before to create a transformer called getAnnualSpent, and use the following JavaScript code to define it.

// Assign the monthly orders to a variable
let monthlyOrders = {{getOrdersByMonth.value}};

// Initialize the total to zero
let annualTotal = 0;

// Loop through each month in monthlyOrders
for (let month in monthlyOrders) {
  // Log the total amount for the current month
  console.log(monthlyOrders[month]["totalAmount"]);
  // Add the total amount for the current month to the overall total
  annualTotal += parseFloat(monthlyOrders[month]["totalAmount"]);
}

// Return the total for the year
return annualTotal;

This transformer returns a single number rather than an object, which we can reference later in the UI.

Create a transformer to group spend by category

Finally, create the getOrdersByCategories query to group purchase data by category. Use this JavaScript code for the transformer.

// Declare and assign the results of getDataFromGoogleSheets query to variable 'sheetData'
let sheetData = {{getDataFromGoogleSheets.data}};

// Get an array of unique Parent Categories from sheetData
let uniqueParentCategories = [...new Set(sheetData.map(obj => obj["Parent Category"]))];

// Object to store final data
const finalData = {};

// Loop through each unique Parent Category
for (let i = 0; i < uniqueParentCategories.length; i++) {
  // Filter data for current Parent Category
  const categoryData = sheetData.filter(function(data) {
    return data["Parent Category"] == uniqueParentCategories[i];
  });

  // Calculate total amount for current Parent Category
  const totalAmount = categoryData.reduce(function(total, data) {
    return total + data["Item Total"];
  }, 0);

  // Add current Parent Category data to finalData object
  finalData[uniqueParentCategories[i]] = {
    orders: categoryData,
    totalAmount: totalAmount
  };
}

// Log and return finalData
console.log(finalData);
return finalData;

When you preview this transformer, the shape of the object returned should look something like this.

Output of the getOrdersByCategories transformer

Here’s the final set of queries and transformers we have now.

Our final list of one query and three transformers

Now that the data access tier of our application is in place, let's start building the UI.

Connecting our data to UI components

Now that we have the data queries and transformers in place, we can begin designing the dashboard and connecting the UI to this data. To build the UI, let's focus on the questions we posed earlier, and show an answer to each of them.

Question 1: How much money did we spend on Amazon in 2022?

The "quick insights" bar of our Amazon Shopping Analysis dashboard

To build the "quick insights" bar at the top of this application, I used a combination of Statistic and Icon UI components. To bind these Statistic components to the appropriate data sources, change their Primary Value property to the following expressions.

1. Total Spend in 2022: {{getAnnualSpent.value}}
2. Monthly Average: {{Math.round(getAnnualSpent.value/12)}}
3. Average Item Cost: {{getAnnualSpent.value/getDataFromGoogleSheets.data.length}}
4. Number of Items Ordered: {{getDataFromGoogleSheets.data.length}}
5. Average Item Cost: {{getAnnualSpent.value/getDataFromGoogleSheets.data.length}}

ℹ️

Analysis: How much money did we spend on Amazon in 2022?

In 2022, we emptied our wallets and spent a whopping $12,000 on Amazon, an average of 42 items per month with an average cost of $25 per item. That’s a lot of brown boxes every month.

Question 2: How much money did we spend each month?

Also, how many items did we order each month, and what was the average item cost? To answer these questions, I decided to build a section that shows the amount of money spent each month and a button that lists all the items for that month in the table below. It also shows a positive/negative trend compared to the previous month.

Monthly view of our Amazon Shopping Analysis Dashboard

To build this section, I used the Statistic component again and made these changes to its properties.

• Label: January
• Primary Value: {{getOrdersByMonth.value.Jan.totalAmount}}
• Caption: Average Item Cost: ${{Math.round(getOrdersByMonth.value.Jan.totalAmount/getOrdersByMonth.value.Jan.orders.length)}}
• Number of Items Ordered: {{getDataFromGoogleSheets.data.length}}
• Average Item Cost: {(getAnnualSpent.value/getDataFromGoogleSheets.data.length}}
• Enable trend color: On

I then repeated this for the other months, replacing “Jan” with “Feb”, “Mar”, “Apr”, and so on.

Setting the Primary value of the Statistics component to the total spend of the month

To add the positive/negative trends for Statistics Components, I turned on “Enable trend color” property for Secondary value, and set the “Positive Trend” property for each of the Statistics components (except January, since it's the first month) to {{self.secondaryValue < 0}}.

_Enabling the trend color property for Statistic Component's Secondary value _

ℹ️

Analysis: Most expensive month

The most expensive month for us turned out to be June, followed by December (which makes sense with the holiday shopping) - but what happened in June? We'll explore that further below.

Question 3: Why were expenses so high in June?

I decided to add a button for each month, which includes the number of items in the button text. When clicked, the button would filter and display the items in the table below.

Clicking on the "XX Items" button in a month will change the contents of the table below.

To do this, add a Button component and change the text to this:

{{getOrdersByMonth.value.Jan.orders.length}} Items

Then add an event handler for that button to control the table, setting its data property to this:

{{getOrdersByMonth.value.Jan.orders}}

Duplicate this button for other months, replacing “Jan” in each of those snippets with “Feb”, “Mar”, and so on.

Configuration for each of our Item buttons in Monthly view

ℹ️

Analysis: What happened in June?

Looking at the items ordered in June, I recalled that we traveled internationally with the baby for the first time, which explains the travel-related shopping. The relatively high amount in January also made sense, given that we were getting ready for the baby’s arrival.

Question 4: What category did we spend the most on?

Also, how much did we spend on the baby category? To answer these questions, I built a “Category view” that shows the amount of money spent each month, along with a button that lists all the items for that month in the table below.

Using ChatGPT to group into broader categories

Amazon includes a pretty wide and very niche set of categories. There were over 185 unique categories in my report, including things like towel, toothbrush, lamp, table, etc. This makes the grouping by category pretty useless.

I wanted to regroup the items into broader "parent" categories, which would be much easier to visualize. I decided on 12 parent categories - Health, Baby, Kitchen, Food, Home, Electronics, Office, Clothes, Books, Toys and Games, Personal, Care, Clothing, Gift.

Instead of trying to do the frustrating task of regrouping 500+ records manually, I ran them through ChatGPT.

Here are the steps I followed to do this for 500+ items:

1. Create two new columns in your spreadsheet - ChatGPT Output, and Parent Category.

1. Log in to ChatGPT, and use the prompt below to pass the categories for regrouping. Since ChatGPT has character limits on the replies, I found it most effective to pass 50-60 items at a time, which is still much better than doing it manually.

"Group the following items purchased from Amazon into broader categories such as "Baby", "Kitchen", and "Home". Repeat it for each item even if they're not unique. Use this format - Diaper: Baby"

1. Copy the output and paste it into a text editor (or pastebin.com) to remove any extra bit of formatting and ensure each item is on a new line.

Pasting output from ChatGPT into a simple text editor to remove any formatting

1. Now paste this into the ChatGPT Output column in the spreadsheet.

Pasting the raw ChatGPT output into the spreadsheet in the ChatGPT Output column

1. Now, use the "Split text to columns" feature of Google sheets to split by colon, to get the Parent Category column filled out.

Using the "Split text to columns" feature to get the Parent Category

1. Repeat this step for other items.

💡

Some items, like Gift Cards, may not have a category assigned in the data provided by Amazon. To avoid corrupting the order of the data returned by ChatGPT, you may want to filter these out before passing them to ChatGPT.

We're now ready to build the category view of our dashboard using the new Parent Category column.

Building the Category View

Category view of our Amazon Shopping Analysis Dashboard

To build this section, I used the Statistic Components again and changed the Primary Value for the Statistic Component to {{getOrdersByCategories.value.Baby.totalAmount}}, and Button Component's Text Property to `{getOrdersByCategories.value.Baby totalAmount}}

Similar to the monthly view, I added a button for each category and changed the text to {{getOrdersByCategories.value.Baby.orders.length}} Items. I then added the event handler to control the table and set the data to {{getOrdersByCategories.value.Baby.orders}} when clicked.

I then repeated this for the other categories, replacing “Baby” with “Home”, “Kitchen”, and so on.

Configuration for each of our Item buttons in Category View

Question 5: How many items did we return/refund?

Refunds view using a new Resource query getRefundDataFromGoogleSheets

To get the answer to this, I had to download the “Refunds” report from Amazon (instead of the “Items” report we downloaded earlier). I followed the same process to create a new Resource Query - getRefundDataFromGoogleSheets, and two new transformers called getRefundsByMonth and getAnnualRefunds.

Adding a new query and two additional transformers for Refunds data

The process to create these items is nearly identical to the process for the rest of the dashboard - to see the precise configuration I used, download the JSON definition of this Retool app, which you can import into your own free Retool instance to inspect for yourself.

Final analysis

I shared this dashboard with my wife, and we ran through it together to get some fascinating insights.

1. Overall, we spent an average of $838 per month , with a return rate of 20%. That was a reminder to be careful of the “Buyer’s remorse,” especially with that easy-to-hit “Buy now” button.
2. We ordered a total of 506 items at an average of 42 items per month , with an average per-item cost of $24.88. That’s a lot of brown boxes. This was a bit of a shocker. With online shopping so easy, it’s easy to forget how much we’re ordering.
3. Not surprisingly, the category we spent the most on was baby-related. The category we spent the least on was books, which makes sense given that we had very little time to sleep, let alone read books. We would like to change that this year by reading a bit more.
4. We agreed this could serve as a good starting point to budget more effectively across categories like home, kitchen, etc., in 2023.
5. The top 5 most expensive items we bought in 2022 were travel bags, gift cards, and a Slumberpod tent for the baby girl!
6. The most expensive month for us was June (travel to see family), followed by December (holiday shopping).

Wrapping up and boxing our data visualization adventure

We journeyed through the depths of Amazon purchases and emerged victorious with our "Amazon Purchases Insights Dashboard." We uncovered all sorts of juicy insights, like how much money we spent on baby gear (a lot). Along the way, we learned how to use Retool to create queries and connect them to UI components. If you’d like to build a similar dashboard, here’s the JSON you can import. Also, here’s a sample Google Sheet you can use to start.

I hope this has been helpful and has encouraged you to build your own dashboard. If you end up building it or have any questions, please reach out to me. I would love to help and would be curious to see what insights you uncover.

Happy analyzing!