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 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.
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:
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. 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.
- The entire system costs less than $3,000 for the first year.
- 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:
- 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.)
- Once the source was authenticated, the consultant connected the destination of Google BigQuery. (Link to Stitch documentation.)
- Once the integration was set up, Stitch started replicating data automatically per the requested replication frequency. (Default is every 6 hours.)
The consultant validated that data tables were listed in Google BigQuery and that the format matched the Stitch documentation.
⚠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:
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.
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.
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?
- Learn more about Tableau Online: https://www.tableau.com/products/cloud-bi
- If you want a copy of the BigQuery SQL used in the illustrated LTV Dashboard specifically, please feel free to download a copy here: http://bit.ly/2vosJCg
- Stitch Data has many more integration options. Check out the full list: https://www.stitchdata.com/integrations/ Oh, and tell them Archetype sent you! J
- To learn more about Google’s free cloud pricing, please visit: https://cloud.google.com/free/
- A great article on using Predictive Analytics to increase revenue
 Tableau doesn’t offer monthly pricing options. All pricing is annual.
 As published by Google at the time of this article’s publication: August 2017.
 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.