Tag Archive for: Data Visualization

Chartio is a tool for centralizing, normalizing and visualizing business data. It connects to Salesforce and a variety of databases, and it allows you to upload simple spreadsheets. Its data pipeline has enormous power for transforming the data to get it in just the right form you need.

Chartio’s dashboarding capabilities, with layout and drill-down, permit you to simply create a webpage that tells a story and lets the user explore.

When we explored the Business Intelligence (BI) tool space in Spring 2015 at Jama, we had a number of criteria:

  • Back-end ease of use and self-service
    • As SaaS as possible: We don’t have real IT sources to dedicate to this
    • Our data and the BI space are changing so quickly, so we need to have updates as soon as possible (from ourselves and them)
    • Real object/field sync vs. de novo query to get the data in
  • Front-end visualization quality and self-service
  • Data quality/trust/availability/joining
  • Creative report building control
    • We have a lot of smarts in-house and don’t want to be limited
    • Control over variables, calculations, global transformations
  • Dashboard control
  • Automatic/intuitive/controlled drill-down
  • Automatic/intuitive/controlled filtering
  • Future-proof
    • Be able to inject Jama usage and modeling, eventually
  • Publishing
    • To a URL, and be receiver-specific (including our customers)
  • Snapshotting
    • See how things change over time
  • Sharing
    • Message streams (public/private)
    • Group access control
  • Expertise
    • How much do they know and can help with our business?

We knew Chartio met these criteria at the time–except for the last three:

  • Snapshotting: Introduced a few months ago.
  • Sharing: There still is no social aspect to their system, which other vendors emphasized. We felt that we would not be using it that much. Chart links in email have met our needs.
  • Expertise: Other vendors also seemed to have in-house expertise providing best practices for analyzing, say Sales or Marketing data, which would have been helpful, but we also have many folks in-house with strong opinions on such matters. So, after reviewing a dozen vendors, implementing half of them and getting down to a two-vendor bake-off, we went with Chartio.

BIForce

Before Chartio we had our own business intelligence system we call BIForce, built in-house by yours truly. BIForce was initially developed to analyze ARR (Annual Recurring Revenue), market trend analysis and customer churn prediction. Because we were in the midst of a transition from a perpetual-license model to a Software-as-a-Service (SaaS) model, there were a lot of missing, inconsistent and sometimes just plain wrong data in our systems.

BIForce ran a lot of processes to clean up business intelligence data to essentially produce a shadow version of the data in Salesforce. These data could then be used to build models and produce reports based on now-normalized historics.

The other main source of business intelligence data, early on, was weekly updates of basic usage by Jama’s customers. These data, such as logins by license type, items created and reviews completed were sent back to Jama servers from each customer’s instance, including some On-Premises customers who had turned on their “phone-home” capability. These data could be awkwardly viewed in Kibana using an Elasticsearch back-end, but were disconnected from other information on the customer (e.g., from Salesforce).

How Chartio Fits Into Our Business

A year in, Chartio now supports every department in Jama. Here are some of the ways each department managed data before and after Chartio.

Executives & Finance

KPIs

Before

Jama did have a few dashboards in Salesforce showing straightforward numbers like total bookings from Opportunities. These dashboards hadn’t changed much since the consultants made them; as we changed our business, the dashboards were getting out of date and disconnected from each other and business reality.

Sales opportunities were also shown to some degree, but suffered from the same issues as our sales processes evolved. Moreover, as this evolution took place, different groups of people were tracking things differently and coming to meetings with different numbers.

After

We now have clear information about Jama’s past, present and future. A set of executive dashboards show our bookings numbers, of course, but also real ARR values by opportunity type (e.g., new, upsell and renewal). These charts have drill-downs to more detail if needed.

The major characteristics (e.g., SaaS vs. legacy Perpetual) and distributions (e.g., by territory) of our current customer base can all be seen in one place.

The pipeline of future business is also clearly indicated and can be easily filtered in any number of ways. However, the semantics of the underlying categorization is consistent so there is no more arguing about the numbers.  This was one reason we went with Chartio, initially–it would allow us, internally, to create a curated view of the data.

Monthly Renewals

Before

Before BIForce, the Finance team would dump into a bunch of data from every renewal opportunity into spreadsheets, down to the individual line items. The team would spend hours cleaning the data, making manual adjustments and coming up with renewal rates, churn rates, etc., each month, which would likely require other adjustments as late renewals came and other changes occurred.

Using BIForce, I could highlight opportunities where there might be issues (such as end date before the start date on a line item) and, using the shadow-Salesforce data, create spreadsheets directly from Java code which mimicked the manually-created ones, and even have a few simple line graphs. This process itself though was kicked-off manually and the spreadsheets put in a place that few people went to—and each month’s data (accumulating throughout the fiscal year) was in a separate file and hard to compare.

After

With Chartio, we have separate Quality Assurance (QA) dashboards for all major functions which highlight where there might be issues. These tables are populated only with suspect records, so it’s easy to see what needs to be investigated.

With better data, Chartio can simply show the live values for all opportunities across all due dates, and we easily churn out those who are late each month, and “un-churn” those that do end up renewing. This way, we have consistent reporting from a given month, but also a full record of what the ARR is.

These data are all live, and churn and net renewal rate (NRR) can be aggregated at a monthly, quarterly and yearly basis, with easy access directly to the opportunities themselves (which is very difficult from a spreadsheet).

In addition, the Pipeline Movement analysis (discussed below) can also be applied to Renewal Opportunities to help track down changes in open renewals and whether they accurately reflect how, say, transactional upsells should have changed open renewal amount.

ARR Projections

Before

With the shadow-Salesforce in MySQL, I was able to create a complex, year-over-year model of how new, upsell, lost, ARR and seats changed over time. I would then dump these rates into spreadsheets to do some walk-forward forecasting using, say, the average of the last six months of these rates to predict how ARR would change.  I’d then make some graphs of those and paste them into presentation decks.

With a little more work, I could do separate rates for different cohorts of customers and then recombine the individual rates again. Even with all the programming, there was still a lot of “walking” the data from one tool to another; it was nearly impossible to maintain and would take a day or two to get an updated forecast.  However, a year later I looked at some old forecasts and they were within a few percent of what actually happened, so it seemed worth it to have this “live.”

After

Using Chartio’s Data Stores, I was able to recreate all the complex queries for the pre-processing and have these update every day. These data stores feed a live dashboard which shows the forecasts. One filter allows you to choose how much data to use to get the rates, and other filters let you choose the cohorts (e.g., by territory) to see how they would grow differentially and re-combine to give an overall forecast. While the forecasts are “live” and always showing what the forecast is—for up to three years out—Chartio’s dashboard snapshotting capabilities allow us to go back in time and see how accurate they were.

Product

Trials

Trials are critical for Jama’s continued growth. They allow potential customers to work with the tool and realize its power. In addition to the back-end metrics that we gather, with hosted instances we also use Pendo to collect front-end metrics such as time on-site and feature usage. This data is primarily used by our User Experience team, but in the case of trials, we can also see who the “hot leads” are.

Before

We really were not looking at this at all before Chartio. A lot of work had to go into even being able to get the data, but knowing that we could eventually get it into Chartio for analysis and visualization made the work a higher priority.

After

We had to redo our trial provisioning system to make sure Salesforce Lead and Contact IDs were getting into the system. Then, BIForce processed all the back-end JSON files and used Pendo’s new API to gather a portion of the front-end data. While Pendo has recently added an interface to Salesforce, it can only map to one object, and for us this is the Account object, whereas for trials we need to link it to the Lead or Contact object. BIForce does this and creates a usage metrics database that Chartio tunnels to in order to get the data for all hosted activities (not just trials).

While only one lead or contact might start a trial, using the Pendo data we can see how many unique users there and what basic features they’ve used (e.g., have they imported their own data to see how it looks in Jama?). The more users are on the trial, the more likely they are to buy so we can now alert sales reps to this information.

The Product group can now also see what industries of leads or contacts are engaged in trials, which is especially useful as we create more vertical marketing materials and trial data sets geared towards certain industries.

Marketing

Before

We use Marketo synced to Salesforce to track marketing success and lead-stage progression. These tools have decent reporting capabilities for Marketing, and tactical decision-making.

After

For more strategic information, we use Chartio. While it’s good to know that a cohort of leads who saw a particular campaign became Marketing Qualified, accepted a meeting or became Sales Qualified, what we really wanted to know was whether leads of a certain industry, say Medical Devices, were more likely to transfer from one stage to the next, or to do so faster, or to invest differently than leads from other industries.

Using Lead stage information from Salesforce in Chartio, I was able to set up contrasts by which we could see the effect of the Lead’s industry on the conversion rates between stages, the speed at which they moved and the value of subsequent opportunities. These conversion-rate factors, speed-up-rate factors, and win-rate factors tell us where in the process we are succeeding or where we need more support.

Sales

Pipeline Movement is a way to see how opportunities for new business appear, grow or shrink in dollar amount and have close dates that move in and out of a particular time period. For example, you might have $10 million in pipeline, but is it the same $10 million you had last quarter and it just got pushed into this quarter? Is it going to push again into next quarter?

Before

Jama had purchased a pretty expensive Salesforce add-on which analyzed our pipeline movement. It had some nice visualizations and a few people used it on occasion.  But any changes required us to contact the company who would do things on the back end, and this would sometimes take weeks. Also, as we’ve changed processes and sales stages over the years, it was hard to make comparisons across time.

After

With some help and examples from Chartio’s support team, I set up a Data Source which reads from the Opportunity History in Salesforce, normalizes the stage names, and even “fixes” dates of the stage changes and close dates so that they make sense, despite any errors in human input.

Building on the normalized data, I was able, with Chartio, to replicate the third-party tool’s interactivity, and even expand the functionality based on user input and make the changes quickly. These data are now looked at almost daily. And, we canceled our subscription to that other tool, saving tens of thousands every year.

business intelligence

As we moved to a more ABM (Account-Based Marketing) strategy, we were able to essentially simulate what different territories would look like based on a variety of criteria. Being able to display the number of leads, contacts, accounts and potential revenue under different scenarios was essential to creating territory assignments that everyone was excited about.

Customer Success

The Customer Success team had bought a tool to “sit on top” of Salesforce to help them monitor and manage our growing customer base. It has some nice capabilities and best practices to conduct these activities efficiently. A major source of data that it relies on is usage data. Once that data is available, then CS can set up rules which would, for example, alert them to a customer that had a drop in weekly logins.

Before

For a variety of reasons, implementation and use of this tool languished for months. With BIForce I was able to get some of the basic usage data pushed into the tool on a weekly basis, but there was very little context by which to understand it. For example, a customer might have 10,000 items in their instance of Jama—is that good or bad?

After

As mentioned above, Chartio has access to the BIForce database containing all of our usage data. We can display this customer by customer to examine activity.

business intelligence

We also used Chartio to explore the patterns of usage, and how they differed between customers who later short, or became lost, versus those who renewed or expanded. We have customers over a wide range of sizes (from 1 to 1000 creators), as well as brand-new ones and some we’ve been working with for years. We are not a huge company with a massive quantity of customers, so slicing and dicing this data leaves us with precious little in each sub-group to say anything with confidence.

However, using a few data store tables in Chartio, we were able to create the distributions of, say, items per creator, and how those distributions differed between renewing and lost customers at every month of their tenure. We can then compare every current customer to where they fall in these two distributions, and create a risk score which we show in Chartio.

Chartio doesn’t have write-access to Salesforce, but you can schedule email reports and whole dashboards to any Chartio user. So, Chartio emails me a report with all the dashboard information and risk scores; I auto-forward that to another account which BIForce monitors; BIForce downloads new emails, parses the attachments with the risk scores and writes them back to Salesforce.  Thus, this critical information can be made available to the Customer Success Managers and the tools they use.  While I’m sure we could completely replace their tool with Chartio, and save more money, we haven’t yet dedicated the time for this work.

Engineering

Before

Engineering had been using Ducksboard (now owned and in sunset by New Relic) to show simple graphs of a few key metrics like up-time, regression defects, build status, etc.  In addition, Jama uses Jama internally for requirements management, tracking and collaboration between Product, Engineering, Sales and Support, primarily. While Jama itself has some reporting capabilities, that’s not it’s primary purpose.

After

Jama (the tool) is backed by a database (MySQL in our case, but we also support SQL Server). So, we now have Chartio tunnel into our instance of Jama, and through data stores we have more user-friendly, object-oriented tables, like Epic, Story, Customer, so we can use the interactive drag-and-drop chart method to quickly set up dashboards.  In addition to standard engineering metrics, since the customers’ Salesforce IDs are in Jama, we can also do things like understand exactly how much ARR might be at risk for a given defect.

Services

Before

Our Services department uses Clarizen and Intacct to track projects and customer billing. They would dump the data out weekly and monthly, do some spreadsheet “massaging,” then paste graphs into presentation decks of KPIs, such as effective bill rates and consultant utilization.

After

We need just four numbers from Intacct once a month, so for now we just update a simple spreadsheet with that value and upload it to Chartio. BIForce uses Clarizen’s SOAP API to get all the relevant data, and using Chartio’s tunnel to the BIForce database, all the data is made available to match the presentation deck material. Now that it is “live,” it only needs to be set up once, and managers can drill-down with ease into specific time frames and consultants information.

Conclusion

At Jama, Chartio is integrated into almost every department. It’s everything we had hoped for when we considered solutions a year ago, and it has unleashed our creativity in many unexpected ways, helping us answer questions we didn’t even know we had.