The key to closed loop reporting or full funnel reporting is being able to track leads, originating from marketing automation and/or CRM systems, through to their outcomes - opportunities in the CRM system.
And increasingly analysts need flexibility with measuring this correlation. Is it based on the original contact turning into an opportunity? Are you looking at all resulting opportunities at an account level? Are you bounding the opportunities by a specific date range?
In this post, we’ll show you how to connect a Fused Contact (Lead) to a Fused Opportunity and Fused Company (Account) table using Links tables. The use case being, when a contact (lead) from your marketing automation system converts to an opportunity in your CRM, you want to be able to identify which:
- Contacts (Leads) became opportunities
- Opportunities are associated to a specific Company (Account)
- Opportunities are associated to a given Company (Account)
This post assumes you’ve already connected your Fusion Warehouse to a BI tool. If you’ve yet to do so, please refer to any of these articles.
Now let’s get started!
The first thing you should know is that the links tables allow you to join two fused tables together.
If, for example, you’d like to join the Fused Contact and Fused Opportunity tables, you would refer to the links_contact_opportunity table.
SELECT * FROM links_contact_opportunity;
This query should return three id's: contact_id, opportunity_id, andlinks_contact_opportunity.
Notice that the links_contact_opportunity table contains the primary key for both the Fused Contact and Fused Opportunity tables, contact_id and company_id, as well as a concatenated key, links_contact_opportunity_id.
Contacts Resulting in Opportunity
If you want to see whether a specific contact resulted in an opportunity, you could map a custom field like ‘is_converted’ from your CRM to your Fused Contacts table. For example:
You can create many other custom fields for your Fused Contacts table such as Campaign.
But we only want to see which opportunities closed. Plus, we want to see which contacts resulted in opportunities within the last 90 days, perhaps even over a certain deal amount, like 10000, so we might add parameters until we get a query like this one:
SELECT fused_contact.is_converted AS `Converted`,
fused_contact.campaign AS `Campaign`
fused_opportunity.deal_stage AS `Stage`,
fused_opportunity.close_date AS `Date`
JOIN links_contact_opportunity ON fused_contact.contact_id = links_contact_opportunity.contact_id
WHERE fused_opportunity.amount >= '10000'
AND fused_contact.is_converted = 'true'
AND fused_opportunity.close_date <= '2018-09-09'
GROUP BY `Converted`, `Campaign`, `Stage`, `Date`;
Keep in mind that when you map a custom field from your CRM or marketing automation system, yours might look different than the query above.
Opportunities Associated to a Company (Account)
Say I have an account in mind. What if I want to know all the opportunities associated to that account?
This is easy with the Fused Company and Fused Opportunity tables. Similar to what we saw earlier, we’ll bind the two tables using the links_company_opportunity table.
First, I’ll want to identify an account, or company, that matters to me. For the sake of demonstration, I’ll choose my account based on the amount of the deal by looking at the Fused Opportunity table.
SELECT opportunity_id, amount FROM fused_opportunity
GROUP BY amount DESC;
Based on what that returns, I’ll select my account by opportunity_id, which I’ll use to bind with the links_company_opportunity table.
But to do that, I’ll need a company_id.
SELECT * FROM links_company_opportunity WHERE opportunity_id='•••••••••••••••••••••••••••••••••••••••••••••••';
Which returns the company ID associated with the account for that opportunity ID.
With the company_id and opportunity_id for a given account, you can then join the two tables as you wish, including custom fields you map in Fusion’s interface or using the default for your CRM and marketing automation system.
But if you’re just looking to join all three tables together, this is easy.
INNER JOIN links_contact_opportunity ON fused_contact.contact_id = links_contact_opportunity.contact_id)
INNER JOIN fused_opportunity ON fused_opportunity.opportunity_id = links_contact_opportunity.opportunity_id);
Once you join your tables, you can then filter on by any field, such as funnel stage, campaign, or create date.