Enterprise Grade Reporting at a Startup Price

How Startups and Small Businesses Can Leverage the Cloud🌥 for a Modern Analytics Solution

NOTE: This was originally posted on LinkedIn, but I also wanted to post it here for the additional screenshots and better formatting. Enjoy!

Does your company need business insights with a quick implementation and manageable budget? Startup and small business companies share the common need to make decisions due to the limited resources and lean staffing model. Many startups cannot allocate precious revenue or VC funding to implement large-scale data warehouse and reporting projects. However, if the company is tech-savvy and uses cloud-based systems, the time-to-value ratio can be greatly reduced to establish the same level of reporting found in enterprise grade systems. 

Based on a True Story

boxodata_logo_sm

Boxodata is selling online storage and Data as a Service (DaaS) solutions. The company is growing quickly and looking to analytics to decrease the time to insight. To make the “right” decisions, the CEO was pursuing analytic solutions to give her information on profitability, churn, and Lifetime Value (LTV). She knew that she could more effectively target customers on Facebook, Google, and other advertising vendors and increase her revenue ("return on ad spend”) among the core segments of the market if she completed this analysis.  


 

The Challenge with Traditional Analytics Projects

In a traditional analytics implementation, an ETL tool is used to replicate data from a source system (general ledger, CRM, billing system, etc.) into a separate database called a data warehouse. Once the data is replicated, a reporting engine is used to visualize and report on the data. This system typically takes over $100K in enterprise software, 4-6 months to set up, and a team of 3+ consultants to implement billing at $175-$200 an hour. At the end of the project, a company might have spent more than $1 Million dollars before even seeing a report or visualization. Startups and small businesses do not have the time or money to dedicate to this level of a project, but they do need the level of analytics gained.

 Figure 1: A traditional analytics system.

Figure 1: A traditional analytics system.

 

Analytics for the Early 21st Century

Luckily, this CEO had a good friend consulting in the analytics industry who recommended the following solution. Since Boxodata was using Stripe as a billing system to handle the customer’s credit card transactions and the monthly product subscriptions, the system could be built using modern tools and a database in the cloud. Here is an overview of the system:

 Figure        SEQ Figure \* ARABIC     2      : A modern analytics system for enterprise-grade reporting

Figure 2: A modern analytics system for enterprise-grade reporting

Using a product called Stitch, Stripe data can be loaded into Google BigQuery for eventual visualization and analysis in Tableau. Stitch uses its proprietary API technology to extract-and-load all Stripe tables into Google BigQuery. This occurs every 6 hours by default or at the desired frequency set in Stitch by the user. Tableau Connects to Google BigQuery out of the box with either custom SQL or native database connection.

This solution has many advantages:

  • These products offer monthly subscription costs with cost savings for yearly commitments[1]. If this system doesn’t work for the company next year, it can be easily cancelled or deactivated.
  • Since the costs are monthly, the IT expenditure doesn’t need to be capitalized in the financial plan.
  • Google BigQuery comes with $300 in free credit for first time users.[2]
  • The entire system costs less than $3,000 for the first year.[3]
  • The setup of all components can be accomplished in 1 business week
  • Data is contained entirely within the cloud products. The company does not have to pay for servers or IT staff.
  • Each product has an SLA agreement and reliability over 99.99%

Implementing the System

The consultant for Boxodata set up the system as follows:

 Figure        SEQ Figure \* ARABIC     3      : Stitch dashboard of the Stripe integration process

Figure 3: Stitch dashboard of the Stripe integration process

  1. The first step in any analytics solution is connecting to the source data. For this step, the CEO used her Stripe admin account to authenticate a connection with Stitch and set up the integration. (Link to Stitch documentation.)
  2. Once the source was authenticated, the consultant connected the destination of Google BigQuery. (Link to Stitch documentation.)
  3. Once the integration was set up, Stitch started replicating data automatically per the requested replication frequency. (Default is every 6 hours.)
  4. The consultant validated that data tables were listed in Google BigQuery and that the format matched the Stitch documentation.

 Figure        SEQ Figure \* ARABIC     4      : Google BigQuery – Example Data from Stripe Events. Schema names have been redacted.

Figure 4: Google BigQuery – Example Data from Stripe Events. Schema names have been redacted.

 

 

NOTE: If you have a lot of historic data, the tables from Stripe will take some time to show up in Google BigQuery. In this example, the stripe_events table was 1.32GB and 1.9M rows and took a majority of the 24-hour initial load time. The customers, subscriptions, and other tables did not show up in BigQuery for the first 16 hours of processing time.

 

5. To create data sources in Tableau, the consultant had two options:

  • Using the SQL editor in Google BigQuery and the documentation from Stitch on append-only tables and incremental loading, the analyst can build new SQL commands for publishing in Tableau.
  • Alternatively, the data can be queried directly in Tableau and joined together via Tableau Desktop.
 Figure            SEQ Figure \* ARABIC       5        : Google BigQuery – SQL Query Editor. Schema names have been redacted

Figure 5: Google BigQuery – SQL Query Editor. Schema names have been redacted

 Figure 6: Example of a Tableau Online homepage with sample workbooks. Courtesy of  Tableau .

Figure 6: Example of a Tableau Online homepage with sample workbooks. Courtesy of Tableau.

 

6. Once the data sources were established, the consultant was able to author the visualizations and dashboards in Tableau as requested by the CEO of Boxodata.

7. Publish results to Tableau Online (as needed) for regular refreshes and publication via email.

 

Results

The results of the project were spectacular given the level of input and time commitment required from Boxodata:

  • In 1 business week, the system of Stripe/Stitch/BigQuery/Tableau was configured and provisioned for all users.
  • The initial, historical data load (approximately a year and a half of data) from Stripe was complete after 24 hours of hands-free, automated loading via Stitch. No errors were seen.
  • The development of the custom SQL queries progressed over the course of several evenings and in-person design sessions. By the end of the week, a basic Tableau visual was complete for Boxodata customer Lifetime Value.
 
 Figure 7: Lifetime Value Forecast dashboard in Tableau. Redacted amounts and volumes.

Figure 7: Lifetime Value Forecast dashboard in Tableau. Redacted amounts and volumes.

 

Conclusion

After a few weeks, the Boxodata analytics solution was put to the real test. A string of technical issues and a spike in cancelations raised concern and questions from the CEO about these cancelations, the trend over time, and the reasons why it was happening. In 15 minutes, a data analyst connected to the web database and published a dashboard to Tableau Online of customer cancelation reasons for the CEO to review. The analysis led to an overhaul in how Boxodata handled customer cancelations on the website, greatly streamlined the customer service response, and allowed the rest of the Boxodata team new visibility into fresh issues affecting customers. They were able to fix the issue and prevent it going forward.

 

Analytics in the right hands and at the right time can be very powerful. How are you leveraging your data?


Deep Dive!


Endnotes

[1] Tableau doesn’t offer monthly pricing options. All pricing is annual.

[2] As published by Google at the time of this article’s publication: August 2017.

[3] This figure assumes 2 Tableau users, a subscription to Online + Desktop, the $100 developer plan from Stitch, and the person is eligible for the Google Cloud credits.