Spending in Healthcare? Spending in Education? How has consumer spending changed over time in the USA? Let’s find out.

For today we got to play around with a dataset about the Personal Consumption Expenditure in the U.S. dating back to 1959.


The Brief: Determine trends in USA consumer spending behaviour

Optional – Bring in any external data sources and we were allowed to use Alteryx


The whole cohort joined to help get our head around the dataset for today. Consumer Spending in the USA. The data was vetted, and aggregate data was removed, and the dollar records summarized by year.

To make the data relevant for today current value CPI values were used to calculate the adjusted value.

This was the calculation used:

((Value from Year)/(1 – CPI Value for 100))*100


External Data Sources


I used a few other sources of data to help with the normalization and to analyse the data from the angle of generations. I made a lookup table for the birth generation (I ended up discarding this because I couldn’t come up with a way to determine the breakdown of the population).

I used the CPI rate to show fluctuations over time for context with any changes in consumer spending.

Other contextual data was overlaid with the inflation rate data showing significant events that attributed to the change in inflation.

For example, the Watergate fiasco under President Nixon’s term.

External Data

The Process:

The whole cohort settled on Alex’s Alteryx export after validating the numbers for the adjusted value.

The categories of expenditure fell into an eight-level hierarchy, the first level had “Goods” and “Services” and split off to the lowest level with 221 categories.

I created eight different line charts to examine if any changes in spending jumped out.

From there I decided to create a dashboard that included this functionality for the user to explore.

Ranking the top categories over years and also in the sectors with the highest difference per previous year.

Alteryx Workflow



Multiple parameters and filters were used to interrogate the data. I decided to create something visually appealing and functional for the user. Through the analysis some ‘Levels’ increased whilst others decreased. Gambling and Pharmaceuticals increased over time. Video rentals and typewriters decreased. Both of these trends seem intuitive from a historical standpoint.

It was clear to see the effects of major events such as the global financial crisis and the recession.

Multi Level Tree

The Recap

I wanted to create a dashboard that gave the user full control to explore the data set and identify which categories are of interest to them. The final dashboard can be viewed here.

In the future, I would like to add some quick KPI’s with a highlight action, as well as a multi-level category tree.


Nicholas Hills
Author: Nicholas Hills