Automating OKRs with Virtual Data Source

Posted by Ivan Osmak
on February 3, 2020

At Gtmhub, we use 15 different data sources to automate our OKRs. Systems such as Chargebee (billing), HubSpot (marketing, CRM), Google Analytics (website), Jira (engineering) are all integrated, and we automatically pull data from those systems and automate progress on our own OKRs.

For simple Key Results, such as the number of leads or MRR, Gtmhub can handle everything on its own. However, like most other businesses, we have certain specifics. 

For example, we segment all of our customers in 5 tiers: Mice, Rabbits, Deer, Elephants, and Whales. Many of our OKRs will be related to this classification; the number of Whale leads, the trial to customer conversion of Mice and Rabbits, etc.

To avoid having custom logic in hundreds of metrics, we have introduced Virtual Data Source.

How Gtmhub automates OKRs?

Gtmhub connects to over 150 business systems (Salesforce, Jira, HubSpot, Amazon Redshift, etc.) and lets you use SQL or R to implement Insights quickly. Any of these Insights can then be easily attached to an OKR to automate the progress.

What we have noticed, however, is that many of the Insights will be very similar, and there will be a lot of copying and pasting. 

Issues with working directly with 3rd party data

Here are the problems that we, ourselves, have faced when automating OKRs with data coming from our billing system.

Repeating complex logic

At Gtmhub, we accept different currencies, but we report all of our numbers in USD. So, all of the dollar values coming from the billing systems have to take into account the currency exchange. As you could imagine, we have dozens and dozens of Key Results in USD, and we had to calculate currency exchange in each of those. Sometimes, we would forget.

Virtual Data Source solves this problem.

Aggregating data

Gtmhub has two legal entities through which it does business (EU and US), and in the same way, we have two instances of a billing system (Chargebee). Every Insight using data from Chargebee had to include and merge data from EU and US instances.

Virtual Data Source solves this problem.

Preparing data

To be precise, billing systems express revenue in cents. On the other hand, we define our Key Results in dollars. Every time we would automate any Key Result that comes from the billing system, we would have to divide the value with 100. Most people don’t know or expect that.

Virtual Data Source solves this problem.

What is Virtual Data Source?

Virtual Data Source is the middle-man between your Insights and 3rd party data sources.

Instead of working directly with the 3rd party data source, you can now make your Insights work with Virtual Data Sources, which can contain complex logic, aggregate various data sources and do any data preparation at one single place.

Creating a Virtual Data Source in Gtmhub

However, this is best explained with a concrete example. Imagine we want to create two different Insights so that we can automate our OKRs:

  • Number of new customers
  • Number of churned customers

Furthermore, as in our example, the data for these metrics are coming from two different instances of Chargebee billing system (EU and US).

Before Virtual Data Source

New customers

-- new customers 
with new_customers_eu as
(
select count(*) from chargebee_eu
where
gtmhub.DURING(activated_at, '%%data_range%%')
and
mrr > 0
),
with new_customers_us as
(
select count(*) from chargebee_us
where
gtmhub.DURING(activated_at, '%%data_range%%')
and
mrr > 0
)

select
(
(select * from new_customers_eu) + (select * from chargebee_us)
) as new_customers

Churned customers

-- churned customers 
with churned_customers_eu as
(
select count(*) from chargebee_eu
where
gtmhub.DURING(canceled_at, '%%data_range%%'
),
with churned_customers_us as
(
select count(*) from chargebee_us
where
gtmhub.DURING(canceled_at, '%%data_range%%' )
)

select
(
(select * from churned_customers_eu) + (select * from churned_customers_us)
) as new_customers

After Virtual Data Source

With Virtual Data Source, we will first create a new Virtual Data Source and call it billing_subs, with the following SQL:

-- billing_subs virtual data source 
(select activated_at, mrr, canceled_at from chargebee_eu)
union
(select activated_at, mrr, canceled_at from chargebee_us)

Then we will rewrite in a much simpler fashion the two Insights:

New customers

-- new customers 
select count(*) from billing_subs
where
gtmhub.DURING(activated_at, '%%data_range%%')
and
mrr > 0

Churned customers

-- churned customers 
select count(*) from billing_subs
where
gtmhub.DURING(canceled_at, '%%data_range%%')

With this new approach, not only did we simplify the way we write Insights, but we’ve also made is 10x simpler to change in the future. If we are to add an Asian billing center, we don’t need to go and change every single Insight we have, but we can modify only our billing_subs Virtual Data Source.

Summary

Given, Virtual Data Source is not a feature you will use on a daily basis. Most of the users will never even know it exists. But, enterprise software is not about happy paths and 80/20 rules. Enterprise software has to make simple easy and complex possible.

When it comes to data, Virtual Data Source makes almost anything possible.