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.
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.
- How much money did we spend on Amazon in 2022?
- How much money did we spend each month in 2022?
- What were the reasons for the high expenses in June 2022?
- What category did we spend the most on in 2022?
- 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!
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.
Once I had the CSV file, I imported it into Google Sheets using Google’s import feature.
After you import the data, it should look something like this.
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.
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.
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.
- 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.
- 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.
- 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.
- 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.
Create a new Retool app, and then create a new Resource query to kick things off.
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.
That's it for this query - you can click the "Preview" or "Run" button to see the data from Amazon flowing in.
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.
Use this code for the transformer.
1// Declare and assign the results of getDataFromGoogleSheets query to variable 'sheetData'
2let sheetData = {{getDataFromGoogleSheets.data}};
3
4// Declare an array of month names for easy reference
5const monthNamesShort = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
6
7// Object to store final data
8const finalData = {};
9
10// Loop through each month
11for (let i = 0; i < 12; i++) {
12 // Filter orders for current month
13 const monthOrders = sheetData.filter(function(order) {
14 const date = new Date(order["Order Date"]);
15 return date.getMonth() === i;
16 });
17
18 // Calculate total amount for current month
19 const totalAmount = monthOrders.reduce(function(total, order) {
20 return total + order["Item Total"];
21 }, 0);
22
23 // Add current month data to finalData object
24 finalData[monthNamesShort[i]] = {
25 orders: monthOrders,
26 totalAmount: totalAmount
27 };
28}
29
30// Log and return finalData
31console.log(finalData);
32return finalData;
33Here’s the shape of the object the transformer would return, containing all orders and their total grouped by month.
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.
1// Assign the monthly orders to a variable
2let monthlyOrders = {{getOrdersByMonth.value}};
3
4// Initialize the total to zero
5let annualTotal = 0;
6
7// Loop through each month in monthlyOrders
8for (let month in monthlyOrders) {
9 // Log the total amount for the current month
10 console.log(monthlyOrders[month]["totalAmount"]);
11 // Add the total amount for the current month to the overall total
12 annualTotal += parseFloat(monthlyOrders[month]["totalAmount"]);
13}
14
15// Return the total for the year
16return annualTotal;This transformer returns a single number rather than an object, which we can reference later in the UI.
Finally, create the getOrdersByCategories query to group purchase data by category. Use this JavaScript code for the transformer.
1// Declare and assign the results of getDataFromGoogleSheets query to variable 'sheetData'
2let sheetData = {{getDataFromGoogleSheets.data}};
3
4// Get an array of unique Parent Categories from sheetData
5let uniqueParentCategories = [...new Set(sheetData.map(obj => obj["Parent Category"]))];
6
7// Object to store final data
8const finalData = {};
9
10// Loop through each unique Parent Category
11for (let i = 0; i < uniqueParentCategories.length; i++) {
12 // Filter data for current Parent Category
13 const categoryData = sheetData.filter(function(data) {
14 return data["Parent Category"] == uniqueParentCategories[i];
15 });
16
17 // Calculate total amount for current Parent Category
18 const totalAmount = categoryData.reduce(function(total, data) {
19 return total + data["Item Total"];
20 }, 0);
21
22 // Add current Parent Category data to finalData object
23 finalData[uniqueParentCategories[i]] = {
24 orders: categoryData,
25 totalAmount: totalAmount
26 };
27}
28
29// Log and return finalData
30console.log(finalData);
31return finalData;
32When you preview this transformer, the shape of the object returned should look something like this.
Here’s the final set of queries and transformers we have now.
Now that the data access tier of our application is in place, let's start building the UI.
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.
- Total Spend in 2022:
{{getAnnualSpent.value}} - Monthly Average:
{{Math.round(getAnnualSpent.value/12)}} - Average Item Cost:
{{getAnnualSpent.value/getDataFromGoogleSheets.data.length}} - Number of Items Ordered:
{{getDataFromGoogleSheets.data.length}} - 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.
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.
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.
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}}.
ℹ️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.
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.
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.
ℹ️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.
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.
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:
- Create two new columns in your spreadsheet -
ChatGPT Output, andParent Category.
2. 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"
3. 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.
4. Now paste this into the ChatGPT Output column in the spreadsheet.
5. Now, use the "Split text to columns" feature of Google sheets to split by colon, to get the Parent Category column filled out.
6. 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.
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 Propertyto `{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.
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.
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.
I shared this dashboard with my wife, and we ran through it together to get some fascinating insights.
- 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.
- 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.
- 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.
- We agreed this could serve as a good starting point to budget more effectively across categories like home, kitchen, etc., in 2023.
- The top 5 most expensive items we bought in 2022 were travel bags, gift cards, and a Slumberpod tent for the baby girl!
- The most expensive month for us was June (travel to see family), followed by December (holiday shopping).
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!
Reader
