🎉 Bedrock Data is now Formstack Sync! · Learn More 🎉

Jeremy Martin | March 7, 2018

Analyze & Visualize Your Fused Data Warehouse with Yellowfin


Overview of Yellowfin

Yellowfin is a visualization tool that allows you to connect data sources and quickly report in a virtualized environment.

Yellowfin users can also collaborate with stakeholders and publish content to any platform or device. For SQL beginners, selecting a visualization writes a query for you, a great feature for learning SQL and repurposing queries. 


Viewing Your Fused Data Warehouse in Yellowfin

Your Fused Data Warehouse consists of merged and standardized customer records. With data from your SaaS applications consolidated, mapped, and de-duplicated, connecting your Fused Warehouse to Yellowfin is easy with a set of Warehouse Keys.

connect to yellowfin

Once connected, you can view your fused records, links tables, and raw data. Objects in your Fused Warehouse include: 

  • Company
  • Contact
  • Opportunity
  • Activity / Task
  • Ticket
yellowfin tables

When you use most BI tools you will likely have to connect to multiple data sources -- product databases, .csv files on a network drive, an export of SaaS data. Often, it can take a long time just to figure out which tables you need to join across these datasets.

In Yellowfin, you can quit spelunking through separate databases. Just drag the tables you want onto the Yellowfin canvas, join, and save queries.



In Yellowfin, you’ll find three table types:

1. Fused: we extract the data from each connected system to create a table for each major object type. The fused records reside in tables marked with names that correspond to an object. For example:

    • fused_activity
    • fused_company
    • fused_opportunity
    • fused_ticket

2. Raw: represents the SaaS system and the object type that we support in that SaaS table. These will be marked with names like:

    • gotoweb0_session
    • hubspot0_contact
    • salesforce0_lead

3. Links: form relationships between the different objects. Using links tables you can very easily join objects in a very simple SQL query. You’ll recognize them with names like:

    • links_activity_ticket
    • links_company_contact
    • links_contact_opportunity 
yellowfin drag and drop 

You can drag these three kinds of tables onto the Yellowfin canvas using Entity Relationships and joining objects on a common key via their corresponding links table. 

In the example below, I’ve dragged fused_company to fused_opportunity, fused_contact, and fused_owner tables onto the canvas. This means I can run a query on all the companies, opportunities and owners from all my connected SaaS applications.

yellowfin joins 

The power here is that I can view of an account’s entire experience with my company. Once monstrous queries across multiple systems are now simplified.


Queries & Joins

It helps to run a quick spot check first. If I wanted to see all the active customers I had, and verify that my data was accurate and up-to-date, I could just run: 

SELECT * FROM fused_company WHERE active_customer = 1; 

But the real value of Fusion in a BI tool like Yellowfin is in the joins, by using the links tables. I can easily join the fused_company and fused_contact tables so that in a single query I have the data from both. In this query, I can see all my contacts associated with all companies.

SELECT * FROM fused_company

JOIN fused_contact_links_company_contacts

ON links_company_contact.contact;

And in your queries, you can get more granular. If you wanted to group these customers by stage, such as ‘Onboarding’ or ‘Established’, this is now possible to do using the data from all your SaaS applications. 

If you’re a SQL novice, no worries. One cool feature of Yellowfin is it turns my drag-and-drop actions into queries for me in the View SQL tab. I can now copy the query and modify or repurpose it at will.

yellowfin SQL export


Use Cases

Now let’s take a look at a few use cases. 

Analyzing Tickets Per Contact/Customer

At Bedrock we use HelpScout for support and Salesforce for our CRM. While both systems are great, we’ve still struggled to report on tickets per customer -- active customers with open support tickets, high severity tickets, average number of support tickets a month. This because HelpScout doesn’t have the concept of a company object; tickets are only associated with contacts. Salesforce has a company object and that’s where we keep the account information up-to-date.

So when we want to report on cases is export data from HelpScout, export data from Salesforce, tie it together using vlookups in a .csv, and create our visualizations. This is difficult because all of the HelpScout contacts need to exist in Salesforce as contacts attached to the correct account. And by the time we’re finished wrangling all this data, it might be weeks or months old. 

With Fusion, I can link Tickets to Contacts and Contacts to Companies using the relevant links tables. 

As a SQL query this would look something like:

yellowfin query

So now in 2017, I’d have the average number of tickets per customer in the year 2017. And because our Fused Warehouse is always up-to-date, I can be rest assured that this data isn’t weeks old. 

Visualizing Campaign Analysis

For those interested in high-level reports, visualizing your Fused Warehouse in Yellowfin’s Campaign Analysis will grant you a summary of campaigns, revenue, costs, and ROI.

yellowfin visualize campaign analysis

Campaign Analysis also provides insights on areas such as:

  • Revenue by Media Category
  • Agency Sales by Profitability
  • Profitability by Customer Age & Location
  • Breakdown Revenue by Campaign and Demographic 

To get a more in-depth view of your sales pipeline in Yellowfin, from your Fused Warehouse you can see both the Total Pipeline Value and Value Remaining based on the Forecast to Win percentage. Sales executives should love this, as they also can see their sales reps’ performance by opportunity, and leads by source. 

yellowfin sales pipeline

Getting started with Fusion is easy. It takes just four steps:

1. Connect Your Data Sources. Connect your data from SaaS applications such as Marketo, HubSpot, Salesforce and NetSuite. In just a few clicks.

2. Fuse Your Data. Automate data matching, de-duplicating, resolving data conflicts and modeling object relationships for more real-time reporting and dashboards. 

3. Warehouse Your Data Instantly. Automatically access and query your warehouse via a cloud data warehouse. 

4. Feed Analytics & BI tools. Enter your Warehouse Keys into your analytics platform and feed your favorite BI tools or dashboards -- including Tableau, Microsoft PowerBI, Amazon QuickSight, MetaBase, and more.

See for yourself what you can do with fused data.


Subscribe Here!