In my previous blog post, we briefly discussed website conversions, their traffic sources, and how to monitor them through one dashboard by integrating Google Analytics and Salesforce. This blog article focuses on tracking the Stripe payments made and due for the conversions recorded through Google Analytics. This adds more convenience for sales and marketing teams.
For online businesses, the primary objective is to get new users for your product through your website. Knowing the revenue figures for your site will let your marketing team and CEO identify the potential strengths and weaknesses of your landing pages and focus more on those to increase your conversion rates and revenue together. Stripe is a secure payment gateway to make and receive payments over the internet. It allows individuals and small to large businesses to use it. Let’s see how you can monitor online business revenue performance through a revenue performance dashboard integrating Google Analytics and Stripe data using Bold BI.
KPIs for website conversions and revenue
The following KPIs are enough to track customer conversions and revenue:
- Qualified leads
- Bounce rate
- Monthly recurring revenue (MRR)
- Average revenue per customer
- Gross volume by channel
- Top five landing pages by revenue
- Annual recurring revenue (ARR)
- Lifetime value (LTV)
- Successful payment
- Gross volume
- Customer churn rate
Now let’s start creating a revenue performance dashboard with these KPIs like the following.
Creating a revenue performance dashboard
- To create a new dashboard, click the + icon in the navigation bar on the left.
- The Create Dashboard window opens with four options.
- Choose Start from Scratch. A window opens to create a new dashboard.
- Enter a name and click Add and Design. Finally, the Dashboard Designer page opens with a new blank dashboard.
Now, let’s see how to prepare data using Bold BI to showcase these KPIs.
Connecting to Google Analytics and Stripe
Connect to Google Analytics and create a data source named Google Analytics – Channel with the following URL to get the campaign channel and landing page details.
https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A19544772134& start-date=2019-01-01&end-date=2019-08-26&metrics=ga%3AbounceRate%2Cga%3Apageviews%2Cga%3A pageviewsPerSession&dimensions=ga%3A landingPagePath%2Cga%3Adate%2Cga%3AchannelGrouping&max-results=10000
For more detail on establishing connections with Google Analytics, refer to this documentation.
Connect to Stripe and create a data source with appropriate APIs like following table.
|Data source Name||API|
|Stripe – Subscription||https://api.stripe.com/v1/subscriptions|
|Stripe – Payment||https://api.stripe.com/v1/payment_intents|
|Stripe – Balance (charges)||https://api.stripe.com/v1/charges|
|Stripe – Payouts||https://api.stripe.com/v1/payouts|
For more detail on establishing connections with Stripe, refer to this documentation.
Finally, the created data sources will be added to the DATA SOURCES panel.
Integrating Google Analytics and Stripe data sources
To integrate the Google Analytics and Stripe data, you need to create a new data source by joining the already-created two data sources. Bold BI allows you to join tables in two or more different data sources, provided they were of extract mode connections. As Google Analytics and Stripe work in extract mode in Bold BI, let’s create a new data source joining table GA from the Google Analytics – Channel data source and payouts from Stripe – Payouts.
Click the edit icon near the Google Analytics – Channel data source item in the DATA SOURCES panel.
Now, the editing view of the data source opens. In the left panel, the Shared Tables pane remains collapsed at the bottom.
Finally, this pane displays the data sources that you have permission to access. By default, the data sources you created before for this or any other dashboard will be listed here.
Before joining the table, create an expression column named Converted-Arrival Date with following expression in the Stripe – Payouts data source. This is to convert the Unix-time-formatted date column to normal date format.
Joining the shared tables
- First, choose the data source whose table needs to be joined. In our case, the Stripe – Payouts data source needs to be selected. Subsequently, expand it.
- After that, drag the payouts table into the design view.
- Select the Join icon in the toolbar of the table design view to open the join editor.
- Create a relationship between the two tables through the Date and Converted-Arrival Date
Then, create a new data source joining the table subscriptions from the Stripe – Subscription data source and payments from Stripe – Payments and create a relationship between the two tables through customer, as shown in the following.
Create an expression column in the Stripe – Subscription data source to calculate fields as shown in the following table.
|Leads||IF([status]=‘trialing’ AND [status (data)]=’null’,1,0)|
|Qualified Leads||IF([status]=‘Incomplete’ AND [status (data)]=’Pending’,1,0)|
|Customers||IF([status]=‘active’ AND [status (data)]=’succeeded’,1,0)|
|Average Revenue||SUM([MRR])/SUM([Active customer])|
|New Customer||IF([status]=‘trialing’ ,1,0)|
|Customer Churn Rate||SUM([Cancelled Customer])/SUM([Active customer])+ SUM([New customer])|
|LTV||[Average Revenue]/[Customer Churn Rate]|
Similarly, create an expression in the Stripe – Payment data source to calculate Successful payment as shown in the following expression.
Finally, the data sources are ready to configure with dashboard widgets.
Configuring data to the widgets in a dashboard
- Add the required widgets to the blank dashboard that you created in the beginning.
- Configure the widgets in the dashboard with their corresponding data sources, as described in the following table.
|Monthly recurring revenue (MRR)|
|Average revenue per customer|
|Gross volume by channel|
|Top 5 landing pages by revenue|
|Annual recurring revenue (ARR)|
|Lifetime value (LTV)|
|Customer churn rate|
Finally, the dashboard is ready. Now you can share this revenue performance dashboard with your sales team by enabling access permission to it. Then simply copy the URL from the address bar and share it. Refer to this overview for more detail on sharing dashboards.
We hope this article helps you monitor online business revenue performance with the integrated data from Google Analytics and Stripe through a dashboard using Bold BI. If you have any questions or need clarification, please use the comments section available below. You can also contact us by submitting your questions on the Bold BI contact page, or if you already have an account, you can log in to submit your support question. Bold BI dashboards now come with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold BI for yourself.