How I Built a Dashboard to Visualize My Amazon Spending using Retool and Google Sheets
Tags: csv
Published: 2023-02-02 23:04:58

---
title: How I Built a Dashboard to Visualize My Amazon Spending using Retool and Google Sheets
published: true
date: 2023-02-01 19:33:13 UTC
tags: csv
canonical_url: https://retool.com/blog/visualize-csv-data-and-build-a-dashboard-to-track-your-amazon-spending/
---

 ![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/dashboard-4.png)

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_](https://www.amazon.com/SlumberPod-3-0-Portable-Blackout-Sleeping/dp/B0B7PBRZX5?th=1)”)

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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-29-at-23.01.03-1.png)
_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](https://gist.github.com/ajot/44cec830fe05e885669ca95fad12cff6), which you can [import](https://docs.retool.com/docs/import-export-apps) 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](https://www.amazon.com/b2b/reports)” 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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh6.googleusercontent.com/GIYPBM_mUPnyX2AricsCCNv7kuWRfIylrNNEmKuArpnT6cW1A_RzhnFPUrAA2bEEZxW_sR-OqkGzGlrGX8YxL2HD_72IXqCmLjnpet4aNvNujaTifDXVzVwb5otxSRoqAx4roZDRFTDA8IXQRxKJ48P8fnbVNZI50CRd1bycxbkTdUrZXGZRCp5iHu0VxA)
_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](https://support.google.com/docs/answer/40608?hl=en&co=GENIE.Platform%3DDesktop).

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/BBowNTjlF6IQJmA1ShwgMbmQq3-yJC2XfMeINg7i3FIa0Xfvnd2FtWUoV_NSOTHrBtfNwkOcKPY3YSnaqVgcvZVxCpgK-Jb6ejZ4AmiasXOgfg8VdJWl7mOfzFALe5YfwGGNMcE0Ne8E7LJrrUswEe10J4J7nzpAB9nVbKvLOdNiZZXPnVTXvH6ByhI_cw)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh4.googleusercontent.com/wXUZmGnkFjueLCIK7E7cL-kQAslbvyW5jnkvb5TV-dWeqo0xCM-L9UahR6q8S1jVsEqd7LroGu4rfRjQ0DmFVp7_57vg_w2JvZO-qAx18w7N3nFGDdxIcNHufEtG0NpffmFsYCcOF1xOILr918_V3D2VtwrsgdvEZJqfmgBa5b9TtCrlodAlAeTvttmogw)
_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](https://docs.retool.com/docs/resources) using the built-in [Google Sheets integration](https://docs.retool.com/docs/google-sheets-integration). Follow the prompts to authenticate with Google. Here’s what the resource would look like once you have successfully authenticated.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/eD9mivdzbxR4TGOkRCKgotCOv3DsS0b1GlLXPLWcnD0SsvvsNLMdRVCHfvXIdprCRi6bJJl4jm9JCME-Qb2zO53K6uSPZ9G-L0EeA4C1YnFml9vUT83bknFz8SV6q5Zs5ocwjh_B2eVjDYWg_CDTxfoe0NCfZ12YEQ_dgM2qpwGymUT79paPQAQBobfH8A)
_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](https://docs.retool.com/docs/google-sheets-integration#query-google-sheets) to load the data from Google Sheets, and three [JavaScript transformers](https://docs.retool.com/docs/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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/Ybul3nU3jUizx0MeoQIkzovKcdMA0fH4m6rbX0nd2WKDbHXlyw3vvCILeRTcFDHMfYTdEP194kOAD21mvKRAck7tGRlG3JTrBDlPQK-p6OlS6sCd783ZXKAJc0JuGGHsdvlfVcqjg2FqS3G0VCKysE2Bah6xV1Ex8RGdcIgXjZasMkpSrLnCAtZui61W1w)
_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`.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh4.googleusercontent.com/81xvzkIodayXgRqptsaWK2TuMqp0uMqHT5jcPen7QkPNoKDniL7hnLUP7NQZwKfdfICA0GITzKikqCgU12j6HrerbBhi3snxH9sVb_dQwO4-VKQdBHJZZvWQfFV2bxqFopOeAj47YkxSeTathfwIwwYf0EbhXRVe0MO0KMnsZCFEOl5nUPCFA140TdhCkA)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-15.14.15@2x.png)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh4.googleusercontent.com/do8C2TQzGEUpX0WDp_r_9ImeP86SgGR9ruEThx2EKFStAIHcjbGQe2QDLDW4kCbhOv2_6lMTGhrkhdUCZ7n9e0nYWMMJUtL6JZjlU7TQ1VutPVlIFNIywZX_YHRHdxJ_pNF95-JaXeEqjZsUrEuabI2nripOUe1JQNbeJy-U48ei8FrClyDSMb034Tr8Wg)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/oEvfsG6BgQc2YhAnJOjnsSjepBojSRscR4jhmJMUUxEgAYfw8BFgKKr5TxQfRmeqI5b3ITLrzhGjmdeXuftI9ZftItE8Zkyohn6SzuwESNAFIK7CZ728eIc0WRdlwVKe-xe0UDtZhFnWs7TWKP3_Ko_Xz2lWs1dwd4LljbQzhCD1NASrPUQrS_6_fjomFQ)
_Output of the getOrdersByCategories transformer_

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

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/x-vrImgx5QeuZFH7LWmj2VNIW2Wwi0TMIQpeqiS2Z1bf6_HDCJWD3u-qojAn6i_hdHhFCt7ZuU_LZMpMnyMdqPNADCetkWNd0JJenX3UbuCQ6ghW0R8wrUdT8qSNKiq_x81EKF_8SL67r95X-KIYlkHLlr3hK-Z7wvy_nyGoy3gFuTcV9IpvEzNka9_JCA)
_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?

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/_8WxNUeSKN9uWuZh2IkmNXdzlyMjSyd-Kfo9KxkEDwCrhb6NTSvBSTB-o0-8drlRs9vRfyJPoCUAzjrfr8kzIrBxNwgA0Rh_zsT1KLRe3mwj-UNMWn3NqZauYprRNI76F8X2i7zGZm_dDFfXhkuVhskbr5NXNv47448NOEjDR5S0iNBZeMntd4h_y65YDw)
_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](https://retool.com/components/statistic) and [Icon](https://retool.com/components/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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-29-at-23.01.03-2.png)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/LaMWGp-QwHGgMhgdsMGayxjbcYhu_RKZWU7H1TooFY5i_q41okEdkvLqJWqLTG03rWLjKF5vxeD72GUgZ0UYd_fINiOQQe7W--i2osXw9L7qWhag1TvZOyAsPPooB2WCVQUeNMLrbWF0Zum_cp_yH5klXFgx9mQLNTkkFJJABJQXYrwP4K3Iad028tod9g)
_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}}`.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh4.googleusercontent.com/Flh9vQHfeHtYCNkSrg76goBJ9uI51hwVAOvRAeV9Q3c5cNEnrTpw4vY-CHtfYsZ_ADRSB7dcBZ2NrcoVQPY6mSYf58y8RXy45QPEZyLu20sInm3yzViUnjxIipNUSR5riPZ7DXjTlwuYMYCyavgrfA4RTd4D626vOlJSiPKTx9iRGqpzr4fspLIRZkpf7g)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh6.googleusercontent.com/rElwA_vUKKAgw0zCKuFza6xxF0lywW6QQaUvhRvAa51XnUwoJeQ_g-f5W6zmLkgHXeev6DuHyr8UdSpQ1aFTEiQnNQQg1I6ChrWfIOQCnIbOmUQVn52F0hGnptB9BrZsqvBHvumUA0jF5aL9A7_gB9Mg9568XvKMz-dfoMEgGPbFHKYNlGWHyilHD1O64A)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh3.googleusercontent.com/eFbopACnYwT_3Spklwy1M8CaojmiQTud4BuJk7sQ67oBql5kUNroCwF1-OcGjhdETBBGCfIkLD88YUghNRAgHE-gfQj_l7EXMhMWdrUUDjiiPnivh_yTzj9LFBLdtlvPVGBT3A9XjsPQDO712CeByNJ-Okps7j4QOxcwG3xR1rxC9WW1CriGhNLP9OfZng)
_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](https://chat.openai.com/chat).

**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`. 

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-17.21.40@2x.png)

2. Log in to [ChatGPT](https://chat.openai.com/chat), 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"

 ![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-17.02.04@2x-2.png)

 ![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-17.02.13@2x-2.png)

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

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-17.58.50@2x.png)
_Pasting output from ChatGPT into a simple text editor to remove any formatting_

4. Now paste this into the `ChatGPT Output` column in the spreadsheet.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-17.39.41@2x.png)
_Pasting the raw ChatGPT output into the spreadsheet in the ChatGPT Output column_

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

 ![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-17.56.24@2x.png)

 ![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-31-at-17.57.37@2x.png)

_Using the "Split text to columns" feature to get the Parent Category_

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.

#### Building the Category View

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://retool.com/blog/content/images/2023/01/CleanShot-2023-01-29-at-23.01.38-1.png)
_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.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh3.googleusercontent.com/bvgcjXKOFYJahwCfZeErkt9fah6gJzBD8XVAmDtoeMimBEaOuthsuXF6-x4OmEZhyV-ztyCjA9jtI9fQvEd-1O9pt1Kpq1wDbjrp8_Qej_oMhxYENqXWubPRvew1D7RmFKFWG_1ul0Hnl-ZXEhH1FCymEnxtIphEPMcNUN0v5T6JfFRwE5cW7hi6ouzV7w)
_Configuration for each of our Item buttons in Category View_

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

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh5.googleusercontent.com/9AIlHVZWvrNCEw-csZKlqvtvSpx9oLZQhLeUwk_wn5uR-uB_uyjfiP5AyUKvWzSvEOgvUDFRJj4DvHE8duhw7Ay9KrSgDy4cQpXmvnQPLWSHLnn7KmSQCUW5ctEIXMlNRoVZwWoI3KjeCPy62VWatn3nnKaWwuZ9ytAcEOTirQiA_pHKsR1zUXqd99MOKQ)
_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`.

![Visualize CSV Data and Build a Dashboard to Track Your Amazon Spending](https://lh3.googleusercontent.com/6RJjelrVRB66e8vlJxsgPMuiRghg1qW6ebDHi6d5FBHgG4SswA7TlVyQ7VWEEzr01dCuQgzsuoQ1Jjjh8vmi6nCN2Q-h7xydUTAY0hFyXuzS2SPdbKzkUpuGVzYX72Ek1nzrLnul18wzfTyHhXVAuzAGNxg0vr0GAETT2qtukdryyZZat_O4SQTGf_cUxQ)
_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](https://gist.github.com/ajot/44cec830fe05e885669ca95fad12cff6), which you can [import](https://docs.retool.com/docs/import-export-apps) 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](https://gist.github.com/ajot/44cec830fe05e885669ca95fad12cff6) you can [import](https://docs.retool.com/docs/import-export-apps). Also, here’s a [sample Google Sheet](https://docs.google.com/spreadsheets/d/1vvDW9JQq10JRBpa8xz0p8c5WyLZTqJ4z-7oOcs8wsfA/edit?usp=sharing) 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](https://twitter.com/amit) to me. I would love to help and would be curious to see what insights you uncover.

Happy analyzing!