My capstone project: from Excel to Rails + React

(This is a general overview of the project, if you would like to read more about the sql queries used, click here.)

Through my career I noticed how useful programming skills would be to make the work load easier. After some time, I decided to apply to Turing School of Software and Design and enroll in the backend program. I felt drawn to it because of the possibility to build tools or contributing to decisions based on data. After all, I had done it in the past with the tools I knew (Excel, Visual Basic) and was excited about what I could accomplish with this new knowledge.

In our last module at Turing, we had the opportunity to build any project we wanted, as long as it was useful, had good documentation and we used at least one new language/ framework.
We had two weeks to build it, and this is what I came up with: a Financial Assessment Tool.

What is it?

It’s a tool to evaluate customer/product contribution, their profitability, trends and investments based on financial indicators and filtered based on product category/ customers and location (if applicable).

What was I trying to solve? Why?

Sometimes, we have a lot of data and very little information, so I wanted to find a way to highlight information in an easy and clean way.

I think, that as a business owner, product manager, etc; I would like to know:

1. Who are my best buying customers, how much am I investing on them (discounts, rebates, etc) and the trend of their finance indicators — (sales, profit, discounts, etc).
2. How much are my products growing, their individual profit and their profit as a category (if necessary) with a detail of their sales, investment (discounts) and profit.
3. A ranking of my top ‘x’ customers, comparing my investment and their growth to focus business decisions on those ‘x’ customers that have the biggest impact in my business. (Pareto principle)

Overall having a tool to know if I’m getting the bang for my buck.

I previously built a similar tool using Excel, but it took about 45 seconds to open and the database (which was kept on a separate file) took 2 min and a half to load. As a user imagine staring at your screen for almost a minute waiting for a file to open. It was tedious and not practical!

My answer to this is:
Have a backend that keeps the data safe and that can handle large amounts of data; and send it to a frontend that takes care of presenting the information in a fast and appealing way.

To build it I started out thinking about the MVP:

The flow should be:

1. Do an overview of my profit & loss statement: is there anything that stands out? Are there trends? (top left view — profit and loss view)
2. Review the dashboard: Who are my biggest clients? My best sellers? Am I investing where I should? (bottom left view — dashboard view)
3. Then check top customers: Are they growing or shrinking? Am I investing? Are they shrinking even though I’m investing in them? (top right view — top customers view)

The end result!

Backend

Stack: Rails 5.1.4, Ruby 2.4.1, PostgreSQL
Building the backend was more or less easy. I decided to build one endpoint to feed each view, so I had 3 controllers (profit_and_loss_controller, dashboard_controller and top_customers_controller) and 3 serializers.

The heavy lifting was done through the models and their relationships, specifically the Client and Sale models, building SQL queries to get amounts from sales, cost of goods sold, operating expenses and discounts from specific periods of time.

For example, for the profit and loss view:

.profit_and_loss method: In the Sale model, I built a query to join the sales, discounts, cost_of_goods_sold and operating expenses tables based on the sales_id and add them based on the month/year of the transaction:

Then, in the controller, I call this.get_profit_and_loss method from the Sale model and I customize the output through the ProfitAndLossSerializer

The ProfitAndLossSerializer presents new calculated attributes: net_sales , gross_profit and net_profit ; based on the data of each month/year.

The resulting JSON looks like this:

Which is all the information we need to send to the view!

If you’d like to learn more about the queries I used to build the views, I wrote a post about it here.

Frontend

Stack: React and Recharts
It really took me a while to get used to the syntax, but coming from Object Oriented design, I really appreciated the encapsulation of react components and the possibility of reusing them.

Recharts was very simple and straightforward. I was able to build all the charts I wanted except for the scatter chart in the Top Customers view.

The Profit&Loss view was composed of TableColumn component which builds a column for every month of data and a SimpleLineChart to view trends.

The Dashboard is composed of FirstColumn which is the summary of the row, and ClusterColumn which has the information for each Cluster. Both columns are made up of DataCells.

Dashboard composition

And the Top customers view is made up of a TinyBarChart to rank the clients, and a PositiveNegativeBarChart to compare investment vs growth.

Biggest Technical Challenge

Besides using React.js for the first time, the biggest challenge was deciding wether calculating some data in the frontend was preferable over making more API calls (The dashboard has summarized and detailed information in the same table).

I decided that it was more practical to make calculations in the FE because, even though this is not the case, most of the time when we consume APIs we either have a limited number of calls or it can take a toll on the execution time, since we are waiting for information from a 3rd party.

Next Steps

• Improve styling overall and add Keys to all the graphs.
• Add tests for the frontend.

Key learnings

• How to manage ‘state’ within React: construct object.
• If a component handles information it should be a class, if not, it should be just a function.
• Because React nests components in order for it to work, it can get really messy after a while. That’s when Redux comes into play.

If you would like to review the code, click on github for the API code or click here for the live API page.
For the frontend code click on github or for production site click here.

Or check this post SQL queries for financial modeling for a detailed review of the queries used for the project.

Thank you!!