Stripe Revenue Queries

What could be more important to track than your purchase events? If you’re using Stripe, you’re in luck. Did you know it’s super SUPER easy to integrate Stripe & Keen IO? We made an embarrassingly gushing video about how to set up the integration in 60 seconds.

If video’s not your thing, the steps are:

Step 1. Look up your project ID & write key in Keen IO. Use them to construct a URL where Stripe will post your events, like this:

    https://api.keen.io/3.0/projects/PROJECT_ID/events/Stripe_Events?api_key=WRITE_KEY

Step 2. Log into Stripe and find the account settings section for webhooks. Add the URL you just created.

Step 3. Wait for events to come in! (If you want, you can create a new Stripe customer so that a new event is created immediately. It should show up in Keen IO right away, in a new collection called ‘Stripe_Events’.)

Now what to do with all this Stripe data?? Here are a few queries we use on our internal dashboard, which uses the Keen IO JavaScript library.

Sum of Last 30 Days’ Revenue

The last 30 days' revenue can serve as a quick estimate for MRR. Here’s how we calculate it and display it in dollars (e.g. $56.3k), in a div with id “revenueAmountText”.

  var client = new Keen({
    projectId: "YOUR_PROJECT_ID",
    readKey: "YOUR_READ_KEY"
  });

  Keen.ready(function(){

      // ===============================
      // Calculate Stripe Revenue Metric
      // ===============================

      var revenue = new Keen.Query('sum', {
          event_collection: 'Stripe_Events',
          timeframe: 'last_30_days',
          target_property: 'data.object.amount',
          filters: [
            {
              property_name:'type',
              operator:'eq',
              property_value:'charge.succeeded'
            }
          ]
      });


      // ===============================
      // Create a new Dataviz instance
      // ===============================

      var revMetric = new Keen.Dataviz()
        .el(document.getElementById('chart-1'))
        .chartOptions({
            prefix: '$'
        })
        .prepare(); // start spinner

      // ===============================
      // Run query and handle the result
      // ===============================

      client.run(revenue, function(err, response){
          // if (err) throw(err);
          revMetric
            .parseRawData({ result: Math.round(response.result/100) })
            .title('Last 30 Days\' Revenue')
            .render();
      });

  });

Revenue Line Chart

Wanna show your revenue growing over time? Simply use a Sum query to add up all your charge_succeeded events!

Stripe tracks money in cents; this shows how to convert it to dollars.
  var client = new Keen({
    projectId: "YOUR_PROJECT_ID",
    readKey: "YOUR_READ_KEY"
  });

  Keen.ready(function(){

      // ======================================
      // Create Revenue Over Time Line Chart
      // ======================================

      var revSeries = new Keen.Query('sum', {
          event_collection: 'Stripe_Events',
          timeframe: 'last_6_months',
          target_property: 'data.object.amount',
          interval: 'monthly',
          filters: [{
              property_name:'type',
              operator:'eq',
              property_value:'charge.succeeded'
          }]
      });

      // ===============================
      // Create a new Dataviz instance
      // ===============================

      var monthlyRevChart = new Keen.Dataviz()
        .el(document.getElementById('chart-2'))
        .chartOptions({
          hAxis: {
            slantedText: true,
            slantedTextAngle: 45
          },
          legend: { position: 'none' }
        })
        .height(451)
        .width(607)
        .prepare(); // start spinner

      // ===============================
      // Run query and handle the result
      // ===============================

      client.run(revSeries, function(err, response){
          // if (err) throw(err);
          monthlyRevChart
            .parseRequest(this)
            .title('Revenue per month')
            .call(function(){
                // Run a function within the chart context!
                // Create a new series (column)...
                this.dataset.appendColumn('Revenue in Dollars', function(row){
                  // ...where each cell is derived from the original value/100
                  return row[1]/100;
              });
            })
            .render();
      });
  });

New Customer Conversions

Here at Keen IO, we ring a gong every time an account converts into a paying account. Our internal stats page also has a line chart showing the number of “gongs” we get each month.

Here’s how we do it:

var client = new Keen({
  projectId: "YOUR_PROJECT_ID",
  readKey: "YOUR_READ_KEY"
});

Keen.ready(function(){

  // ================================================
  // Create New Customer Sales Conversions Line Chart
  // ================================================

  var newPayingCustomersSeries = new Keen.Query('count', {
      event_collection: 'Stripe_Events',
      timeframe: 'last_6_months',
      interval: 'monthly',
      filters: [{
          property_name:'type',
          operator:'eq',
          property_value:'customer.subscription.updated'
      },
      {
          property_name:'data.object.plan.amount',
          operator:'gt',
          property_value: 0
      },
      {
          property_name:'data.previous_attributes.plan.amount',
          operator:'eq',
          property_value: 0
      }]
  });

  // ===============================
  // Run query and draw the result
  // ===============================

  client.draw(newPayingCustomersSeries, document.getElementById('chart-3'), {
      chartType: 'areachart',
      title: 'New Customer Conversions',
      chartOptions: {
          hAxis: {
            slantedText: true,
            slantedTextAngle: 45
          },
          legend: { position: 'none' },
          width: 700
      }
  });
});

Total Unique Paying Customers

If you want to see your total number of paying accounts grow over time, try this visualization of the number of accounts making payments.

  var client = new Keen({
    projectId: "YOUR_PROJECT_ID",
    readKey: "YOUR_READ_KEY"
  });

  Keen.ready(function(){

      // =========================================
      // Create Unique Paying Customers Line Chart
      // =========================================

      var payingCustomersSeries = new Keen.Query('count_unique', {
          event_collection: 'Stripe_Events',
          timeframe: 'last_6_months',
          target_property: 'data.object.customer',
          interval: 'monthly',
          filters: [{
              property_name:'type',
              operator:'eq',
              property_value:'invoice.payment_succeeded'
          },
          {
              property_name:'data.object.total',
              operator:'gt',
              property_value: 0
          }]
      });

      // ===============================
      // Run query and draw the result
      // ===============================

      client.draw(payingCustomersSeries, document.getElementById('chart-4'), {
          chartType: 'areachart',
          title: 'Total Unique Paying Customers',
          chartOptions: {
              hAxis: {
                slantedText: true,
                slantedTextAngle: 45
              },
              legend: { position: 'none' },
              width: 700
          }
      });
  });

Number of New Paying Customers

Nothing is more exciting than seeing your customer base grow across time! To see the number of new paying customers paying for the first time, try this visualization.

  var client = new Keen({
    projectId: "YOUR_PROJECT_ID",
    readKey: "YOUR_READ_KEY"
  });

  Keen.ready(function(){

      // ========================================================
      // Create Line Chart of New Customers Paying for First Time
      // ========================================================

      var timeframe = 'last_6_months';

      var countNewPayingCustomers = new Keen.Query('count', {
          event_collection: 'Stripe_Events',
          timeframe: timeframe,
          interval: 'monthly',
          filters: [{
              property_name:'type',
              operator:'eq',
              property_value:'customer.subscription.updated'
          },
          {
              property_name:'data.object.plan.amount',
              operator:'gt',
              property_value: 0
          },
          {
              property_name:'data.previous_attributes.plan.amount',
              operator:'eq',
              property_value: 0
          }]
      });

      // ===============================
      // Run query and draw the result
      // ===============================

      client.draw(countNewPayingCustomers, document.getElementById('chart-5'), {
          chartType: 'areachart',
          title: 'Count of New Customers Paying for the First Time (' + timeframe + ')',
          chartOptions: {
              hAxis: {
                slantedText: true,
                slantedTextAngle: 45
              },
              legend: { position: 'none' }
          }
      });
  });

Revenue from New Customers

To see the revenue you’re deriving from these treasured NEW customers, we can create a line chart to help visualize all new revenue coming in over time.

  var client = new Keen({
    projectId: "YOUR_PROJECT_ID",
    readKey: "YOUR_READ_KEY"
  });

  Keen.ready(function(){

      // ============================================
      // Create Revenue from NEW Customers Line Chart
      // ============================================

      var timeframe = 'last_6_months';

      var newCustomerRev = new Keen.Query('sum', {
          event_collection: 'Stripe_Events',
          timeframe: timeframe,
          target_property: 'data.object.plan.amount',
          interval: 'monthly',
          filters: [{
              property_name:'type',
              operator:'eq',
              property_value:'customer.subscription.updated'
          },
          {
              property_name:'data.object.plan.amount',
              operator:'gt',
              property_value: 0
          },
          {
              property_name:'data.previous_attributes.plan.amount',
              operator:'eq',
              property_value: 0
          }]
      });

      // ===============================
      // Create a new Dataviz instance
      // ===============================

      var newCustomerRevChart = new Keen.Dataviz()
        .el(document.getElementById('chart-6'))
        .chartType('areachart')
        .chartOptions({
          hAxis: {
            slantedText: true,
            slantedTextAngle: 45
          },
          legend: { position: 'none' }
        })
        .prepare(); // start spinner

      // ===============================
      // Run query and handle the result
      // ===============================

      client.run(newCustomerRev, function(err, response){
          if (err) {
            // Display a custom error in place of the chart
            newCustomerRevChart
              .error('Uh-oh, an error occurred');
          }
          else {
            newCustomerRevChart
              .parseRequest(this)
              .title('Revenue from NEW Customers (' + timeframe + ')')
              .call(function(){
                // Run a function within the chart context!
                // Create a new series (column)...
                this.dataset.appendColumn('Revenue in Dollars', function(row){
                  // ...where each cell is derived from the original value/100
                  return row[1]/100;
                });
              })
              .render();
          }
      });

  });

Net Increase/Decrease in Revenue

Churn, the amount of customer attrition, is a naturally occuring event. To find out whether there is net increase or decrease in revenue, we can calculate the dollar amount of upsell minus the amount of churn.

  var client = new Keen({
    projectId: "YOUR_PROJECT_ID",
    readKey: "YOUR_READ_KEY"
  });

  Keen.ready(function(){

      // =============================
      // Upsell Minus Churn Line Chart
      // =============================

      var interval = 'monthly';
      var timeframe = 'last_6_months';

      var upSellSeriesStarting = new Keen.Query('sum', {
          event_collection: 'Stripe_Events',
          timeframe: timeframe,
          target_property: 'data.previous_attributes.plan.amount',
          interval: interval,
          filters: [{
              property_name: 'type',
              operator: 'eq',
              property_value: 'customer.subscription.updated'
          },
          {
              property_name: 'data.object.plan.amount',
              operator: 'gt',
              property_value: 0
          },
          {
              property_name: 'data.previous_attributes.plan.amount',
              operator: 'gt',
              property_value: 0
          }]
      });

      var upSellSeriesFinal = new Keen.Query('sum', {
          event_collection: 'Stripe_Events',
          timeframe: timeframe,
          target_property: 'data.object.plan.amount',
          interval: interval,
          filters: [{
              property_name: 'type',
              operator:'eq',
              property_value:'customer.subscription.updated'
          },
          {
              property_name: 'data.previous_attributes.plan.amount',
              operator: 'gt',
              property_value: 0
          }]
      });

      // ===============================
      // Create a new Dataviz instance
      // ===============================

      var netRevDiffChart = new Keen.Dataviz()
        .el(document.getElementById('chart-7'))
        .chartType('areachart')
        .chartOptions({
          hAxis: {
            slantedText: true,
            slantedTextAngle: 45
          },
          legend: { position: 'none' }
        })
        .prepare(); // start spinner

      // ======================================
      // Run two queries and combine the result
      // ======================================

      client.run([upSellSeriesStarting, upSellSeriesFinal], function (err, response) {
          var i = 0, combinedResult = [];

          if (err) {
              // Display a custom error message
              netRevDiffChart.error('An error occurred!')
          }
          else {

              while (i < response[0].result.length) {
                combinedResult[i] = {
                  timeframe: response[0].result[i].timeframe,
                  value: response[1].result[i].value/100 - response[0].result[i].value/100
                };
                i++;
              }

              netRevDiffChart
                .parseRawData({ result: combinedResult })
                .title('Upsell Minus Churn')
                .render();
          }

      });
  });