For a better reading experience, the following is the reference to different objects on Acho:
🔤: Variable | 🔘: Button | 📋: Table

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

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

Click 🔘 Filter to set the 🔤 paid column as TRUE to ensure that all invoices are valid.

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.

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.

The following is the result:

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.

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.

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.

Here is the final result:

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.

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.

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.

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.