For a better reading experience, the following is the reference to different objects on Acho:
πŸ”€: Variable | πŸ”˜: Button | πŸ“‹: Table

Acho Studio Revenue Dashboard

Revenue is arguably the most important metric for every business to keep track of. It reflects the income generated from business operations net of discounts and deductions for returned products or services.

A real-time dashboard can help monitor the latest revenue trend on a chart. By setting up a pipeline and data sync, the dashboard can update the metrics, and distribute insights to many audiences. This way you will not have to manually create a new chart to present to stakeholders every time.

When you create a dashboard, a real-time table that updates oftentimes is needed. This table can be used, and reused for many different types of dashboards. Therefore, it actually saves a lot of time to extrapolate new insights. Also, it is a lot easier to detect anomalies and discover changes when a dynamic table is linked to your dashboards.

To sum up, the real-time revenue dashboard can:

  • Update data automatically without reproducing charts manually
  • Ensure data is from the same source and its calculation is consistent
  • Help detect changes and anomalies in real-time

Gross Revenue (GR) vs Net Revenue (NR)

Generally, there are two kinds of revenue: Gross Revenue and Net Revenue.

Gross Revenue (GR) refers to the total money that you get from customers. For example, suppose you sell drinks for $10 each. If 5 customers buy your drinks, then you can get $10 x 5 = $50 in total. Thus, GR is $50.

However, GR doesn't consider returns, discounts, and sales allowances. For example, one of the customers wants to return the drink because the bottle is broken. You return $10 to the customer, so you actually get $50 - $10 = $40 in this case. The number to deduct returns from GR is called Net Revenue(NR).

  • Gross Revenue (GR): the total amount of income from the sale of goods or services
  • Net Revenue (NR): GR deducted by returns, sales discounts, and sales allowance

How to Create a Daily Revenue Dashboard?

Here we use Stripe data as an example to demonstrate how to build a real-time pipeline to monitor your revenue.

1. Create a new project and import invoice and refund table from Stripe

Acho Studio - Create A New Project

Now, you can find the new project in your workplace.

2. Take a look at the invoice and refund tables

Go to the πŸ“‹ invoice table first and keep the necessary columns:

  • πŸ”€ date: refers to when an invoice was generated.
  • πŸ”€ charge_id: a unique id for each charge.
  • πŸ”€ paid: indicates whether a payment is successful.
  • πŸ”€ total: the total amount of a payment
Acho Studio - Stripe Invoice Table

Click πŸ”˜ Filter to set the πŸ”€ paid column as TRUE to ensure that all invoices are valid.

Acho Studio - Filter Setting

Next, switch to the πŸ“‹ refund table and only keep the πŸ”€ amount and πŸ”€ charge_id columns. Now, we can extrapolate information about each refund.

  • πŸ”€ amount (renamed as refund): the total amount of a refund
  • πŸ”€ charge_id: a unique id for each charge.
Acho Studio Transition Table

3. Combine two tables

Now, let's go back to the πŸ“‹ invoice table, click πŸ”˜ Join to merge two tables based on charge_id, and select the left outer method.

Acho Studio - Join Tables

The following is the result:

Acho Studio Transition Result Table

4. Compute GR and NR

Before any calculation is done, you may notice that some rows of πŸ”€ amount_1 are nulls. This is because not all charges have refunds. However, we have to fill these nulls with 0 by clicking πŸ”˜ Cleanse and choosing replace nulls.

Acho Studio - Replace Nulls

Next, let's calculate GR and NR by using the πŸ”˜ Formula function. Remember, the last 2 digits in πŸ”€ total and πŸ”€ refund represent US cents in Stripe, so we need to transform GR and NR into dollars by dividing them by 100. Besides, the goal is to produce a daily report, so we don't need hours, minutes, and seconds in the πŸ”€ date column. Thus, using πŸ”˜ Date() function can easily transform timestamp into a yyyy-mm-dd format.

Acho Studio - Revenue Calculation

5. Compute daily gross revenue and refunds

Next, we can make use of πŸ”˜ Pivot Table to calculate daily gross revenue and refunds. Β The πŸ”€ date_1 column in Vertical Dimensions and πŸ”€ gross_revenue and πŸ”€ net_revenues in Values means to sum up values by day.

We can the total amount of revenue and return each day through πŸ”˜ Pivot Table.

Acho Studio - Pivot Table

Here is the final result:

Acho Studio - Final Table(GR & NR)

7. Export to Visual Panel to Create a Real-time Dashboard

The last step is to produce a dashboard with your table. Acho offers a built-in tool called Visual Panel for this. You may also connect your Acho table to your favorite BI tools such as Tableau and Metabase.

Acho Studio - Revenue Dashboard 2

How to Turn on Data Sync?

First, go to Data Sync in the left sidebar, and find the table you want to set up sync. In this case, we want to set the πŸ“‹ invoice table to update data automatically.

Acho Studio - Data Sync

Next, switch the πŸ”˜ toggle to ENABLED and specify the schedule. For our daily report, you probably want to check the report two or three times a day, so it makes sense to require the table update every 6 hours. Finally, remember to click πŸ”˜ Save to activate the scheduler.

Acho Studio - Data Sync Scheduler

In addition to the πŸ“‹ invoice table, you should also set up the scheduler for the πŸ“‹ refund table because the two tables are related.

Now you have finished all these steps on Acho, you should have an automated revenue report for your team. Although we used Stripe as an example, the same steps also apply to other payment processors you use. The column names may differ, but the logic should stay the same.

If you run into any problem building this metric on Acho, feel free to reach out to us via chatbot or email.