Tutorial: How to use Mode with a data warehouse for analytics

How do you begin combining data from cloud applications with your internal databases to gain insight into your business? Maybe your organization has already standardized on Mode as your analytics tool, but you're still learning about using it with multiple data sources. In this post, we'll show you how the Mode business intelligence (BI) platform can help you deliver business insights.

Three tiers of the data analytics architecture

To analyze data from diverse sources, you need a data warehouse that consolidates all of your data in a single location. Most businesses take advantage of cloud data warehouses such as Amazon Redshift, Google BigQuery, Snowflake, or Microsoft Azure SQL Data Warehouse.

You can extract data that you have stored in SaaS applications and databases and load it into the data warehouse using an ETL (extract, transform, load) tool. Stitch is a simple, powerful ETL service for businesses of all sizes, up to and including the enterprise. It can move data from dozens of data sources to several different data warehouses. If your organization already uses a data warehouse, you can sign up and be moving data to a data warehouse in five minutes. Choose at least one of Stitch's integrations as a data source. Most businesses get more value out of their data as they integrate more data sources; for our example, we'll look at data from two separate SaaS applications.

Once you have data in your data warehouse you can use Mode for BI. Mode's data analysis platform combines a web-based SQL editor with charting tools for data visualization and sharing tools for organizing and publishing reports and dashboards.

Setting up a data warehouse

I used some of Stitch's real data to build a data visualization for this post. Specifically, I was curious to see how many support conversations came from each of our pricing tiers. To find out, I needed to use billing data stored in Salesforce and join it with support conversation data from Intercom to create a visualization of support conversations.

My first step was to set up BigQuery to hold the data I'd be replicating from Salesforce and Intercom. Following our documentation I added to my Google user the permissions that Stitch would need to load data to BigQuery.

Using Stitch for ETL

Using Stitch makes extracting data from a source and loading it into a data warehouse easy. I logged in to Stitch and added new integrations for Salesforce and Intercom. From Salesforce I selected the Account table to replicate. Stitch's Intercom integration automatically extracts data from all available tables. From both, I chose the period from which to replicate data.

Once I'd set up my integrations in Stitch, I added BigQuery as my destination and specified a BigQuery project name. Within a few minutes, Stitch had extracted the data I specified and loaded it into BigQuery.

Building a sample report

Next, I set up Mode to recognize my BigQuery data warehouse. I clicked on the settings icon next to the Definitions menu heading in the left pane, then Connections, then clicked on the Connect a database button. I specified Google BigQuery, then click Submit. I then followed Mode's instructions on how to create BigQuery credentials, entered the necessary information, and clicked Connect.

Enter BigQuery credentials

Voilà – Mode was connected with my BiqQuery data and I was presented with an empty Mode screen for creating a query.

Blank Mode report screen

The two datasets I was interested in, intercomintegration and salesforceintegration, were listed on the right. Clicking on a dataset shows a list of tables in it, and clicking on a table name brings up a list of columns. You can use those names to create a SQL query.

I spent some time in BigQuery's query editor getting my SQL code to give me exactly the data I wanted. My query brought together columns from multiple tables in two different schemas. The SQL code excludes prospects by retrieving only companies for which we have account IDs, and selects current customers by excluding companies that are marked as deleted. It also consolidates clients on annual and monthly billing programs in each of our plans into a single number for each tier:

  userco AS (
  SELECT
    users.id AS user_id,
    company.id AS company_id,
    company.company_id AS stitch_id
  FROM
    `reporting01-216119.intercomintegration.users` AS users,
    UNNEST(companies.companies) AS company
  WHERE
    company.company_id IS NOT NULL),
    
  sf AS (
  SELECT
    CAST(cid__c AS STRING) AS stitchid,
    case
      when tier__c LIKE '%Starter%' then 'Starter'
      when tier__c LIKE '%Basic%' then 'Basic'
      when tier__c LIKE '%Premier%' then 'Premier'
      when tier__c LIKE '%Custom%' then 'Enterprise'
      else tier__c
      end as tier
  FROM
    `reporting01-216119.salesforceintegration.Account` AS account
  WHERE
    account.isdeleted IS FALSE
    AND tier__c IS NOT NULL)
    
SELECT
  conversations.id AS convo_id,
  count(conversations.id) AS convo_count,
  conversations.user.id AS convo_user_id,
  userco.user_id,
  userco.company_id,
  userco.stitch_id,
  tier
FROM
  `reporting01-216119.intercomintegration.conversations` AS conversations
JOIN
  userco
ON
  conversations.user.id = userco.user_id
JOIN
  sf
ON
  stitchid = stitch_id
WHERE
  conversations.user.type != "lead"
GROUP BY tier, convo_id, convo_user_id, user_id, company_id, stitch_id
ORDER BY convo_count

I pasted that code into Mode and clicked the Run button. Mode returned a table of the fields I asked for in the bottom half of the screen.

Results table

Once I saw that the query was returning the data I wanted, I unticked the Limit 100 box at the top of the screen, which Mode ticks by default to limit the time a query takes.

The next step was to turn the table data into a chart. I clicked on the + icon under my query and chose Add Chart. Under Chart Settings I chose Bar. I dragged the tier field to the X-Axis box and convo_count to the Y-Axis. I also clicked on an icon at the top of the Bar Chart window to sort the bars in descending order by y-axis values.

Mode visualization

This chart showed the data I wanted to represent. Mode lets you specify custom labels and titles. When you have just the look you like, you can aggregate your charts into dashboards.

Put Mode and Stitch to work for you

So there you have it – a quick walk through the process of using an ETL tool like Stitch to move data from multiple sources into a data warehouse, then report on it using Mode. Sign up for a free trial of Stitch and start creating your own.