Track your entire Marketing Funnel

Objective

User Acquisition is one of the hardest challenge that a company faces. It's really important to get the proper level of reporting in order to take the proper marketing decisions and identify the best acquisition lever and their unit economics.

Most generally, Business wants to bridge their Marketing efforts (Ads, Partnerships, Referrals) with the associated Sales impacts (revenue, MRR, etc.).

In order to track such a wide funnel, many sources must be blend together, this post will help you to understand how to track your:

• Marketing Spend

• New Marketing Qualified Leads

• New Sales Qualified Leads

• New Customers

• New Revenue

• MQL -> SQL Conversion rate

• SQL -> Customer conversion rate

• Return on Ad Spent (ROAS)

and see it breakdown per:

• Date

• Acquisition Channel (Paid Ads, Social Ads, Referral, Partnerships, Organic, etc.)

• Acquisition Source (Google Ads, Facebook Ads, ...)

• Campaign (Campaign #1, Campaign #2, etc.)

High level plan

1. We'll model individual events, ex. "Marketing Spent" / "MQL Created" / "SQL Created" / "Customer Signed"

2. We'll consolidate those events into a "Funnel table" to get a complete view

3. We'll create an Exploration and create our metrics to track our Conversion rate, etc.

4. We'll build our Dashboard to share with our stakeholders and make better decisions!

Pre-requisites

This guides assume that you have imported all the data sources of your Marketing Funnel into your Data Warehouse. Whaly offers you a number of connectors that is making this task really easy. More specifically, you'll need:

a. Your Ads sources (Google Ads, Facebook Ads, LinkedIn Ads, a Google Sheets linked to Zapier or Supermetrics for other sources)

b. Your CRM (Hubspot, Salesforce, Pipedrive)

c. (Optionally) The financial tool in which you track your revenue (Stripe, Brex, Quickbooks, Qonto, etc.)

Event models and Funnel tables

In order to track your Marketing Funnel, we'll use the "Event Models - Funnel Tables" paradigm. This pattern is very powerful as it will be very easy to add new Data Sources and define Events as your marketing stack evolves and your Funnel becomes more complex.

It means that we'll create:

Event Models

Each time that something occurs in your Marketing Funnel, we'll create an "Event". Each event has:

• an Event Date

• an Event Name ("Marketing Spend" / "MQL Created" / "Customer Created") - This taxonomy should be customised based on your Marketing Funnel + Sales Pipeline processes.

• Optional attributes. Main ones are:

  • Channel: The name of the Marketing Channel that this event should be linked to. Ex. Paid Ads / Social Ads / Referral

  • Source: The name of the marketing source related to the event. Ex. Google Ads, Facebook Ads, ...

  • Campaign: The name of the campaign related to the campaign.

  • Object IDs: If there is any related object to the event, a column to hold the IDs of the related object should be added (ex. a Facebook Campaign, a Salesforce Opportunity, a Stripe Customer). Those will be used to combine the Events with the associated objects to get insights later.

  • Revenue: If there is any revenue related to the event.

  • Spend: If there is any spent related to the event.

For revenue and spend columns, it's better to suffix those columns name with the currency in which they are tracked. Ex revenue_usd / spend_eur / revenue_eur_cents.

Otherwise, you might have discrepancy later when combining the events.

Funnel tables

Once we have properly defined and create each of the "Event Models", we'll create "Funnel Tables" that will consolidate those Events into a single table.

Those Funnel Tables can either import all your Events Models or only selected ones, depending on the reporting that you need to do.

It's often recommended to create a complete Funnel table with all the events, and more specific Funnel tables to zoom in the top or the bottom of your Funnel.

Generating Events from the different steps of your Marketing Funnel is a very great idea as those could be shared in the future to others teams of your Company such as your Data Scientist that will be able to turn them into recommendations to boost the performance of your Marketing Campaigns!

This is called a Data Mesh and Domain Events.

1. Creating our Event Models

For this guide, we'll consider that we have a very simple acquisition funnel:

• Paid Ads are running on Google Ads and Facebook Ads

• When out customers clicks on the Ads, they arrive in our website and once they fill a contact form, a MQL contact is created in the CRM

• After a Sales have called the Lead, they can classify them as being "Sales Qualified" and send them a Quote

• If the Lead accepts, he is flagged as "Customer" in our CRM and the amount billed is written as well

Hence, we'll model the following events:

1. Marketing Spend, for both Facebook and Google Ads platform

2. MQL Created

3. SQL Created

Marketing Spend model

For Google Ads

Let's imagine that we have a Table coming in our Data Warehouse called "Google Ads - Campaign Stats" with the following structure:

The schema proposed below if the one output by Whaly Google Ads connector, so you can reuse the SQL query below directly if you're a Whaly customer!

+------------+---------+-----------------+-------------+-------+
| date       | device  | ad_network_type | campaign_id | cost  |
+------------+---------+-----------------+-------------+-------+
| 2022-01-01 | MOBILE  | SEARCH          | 123         | 8.42  |
+------------+---------+-----------------+-------------+-------+
| 2022-01-01 | MOBILE  | CONTENT         | 123         | 10.25 |
+------------+---------+-----------------+-------------+-------+
| 2022-01-01 | DESKTOP | SEARCH          | 456         | 54.21 |
+------------+---------+-----------------+-------------+-------+
| 2022-01-02 | DESKTOP | SEARCH          | 456         | 48.21 |
+------------+---------+-----------------+-------------+-------+

We'll create a SQL Model called "Marketing Spend - Google Ads", this model will be in charge of:

• Removed things that are Ads specific or uninteresting for our Marketing Funnel (at least at this stage), like the device, the ad_network_type

• Keep only a Daily campaign spend

with the following query:

WITH ad_spend_info AS ( -- Reading only what we need from the DataWarehouse
    SELECT 
        date,
        campaign_id,
        cost
    FROM 
        -- When copy pasting into Whaly Workbench, please erase this value 
        -- and rewrite it to select the proper table in the auto complete
        "Google Ads"."Campaign Stats" 
), daily_spend AS ( -- Needed step to remove the device & ad_network_type granularity
    SELECT 
        date,
        campaign_id,
        sum(cost) as cost
    FROM ad_spend_info
    GROUP BY 1, 2
), event_info AS ( -- Enrich Google Ads data to create a proper Event
    SELECT 
        "Marketing Spend" as event_name,
        date as event_date,
        "Search Ads" as channel,
        "Google Ads" as source,
        campaign_id,
        cost as spend
    FROM daily_spend
)
SELECT * FROM event_info

We now have a proper event table that is looking like:

+------------+-----------------+------------+------------+-------------+-------+
| event_date | event_name      | channel    | source     | campaign_id | spend |
+------------+-----------------+------------+------------+-------------+-------+
| 2022-01-01 | Marketing Spend | Search Ads | Google Ads | 123         | 18.67 |
+------------+-----------------+------------+------------+-------------+-------+
| 2022-01-01 | Marketing Spend | Search Ads | Google Ads | 456         | 54.21 |
+------------+-----------------+------------+------------+-------------+-------+
| 2022-01-02 | Marketing Spend | Search Ads | Google Ads | 456         | 48.21 |
+------------+-----------------+------------+------------+-------------+-------+

This event table is now ready to be consumed, let's go to Facebook Ads!

For Facebook Ads

Let's imagine that we have a Table coming in our Data Warehouse called "Facebook Ads - Marketing Spend" with the following structure:

+------------+-------------+-----------+--------+
| date       | campaign_id | ad_id     | spend  |
+------------+-------------+-----------+--------+
| 2022-01-01 | 102212210   | 154214523 | 54.21  |
+------------+-------------+-----------+--------+
| 2022-01-01 | 441215442   | 451212154 | 41.25  |
+------------+-------------+-----------+--------+
| 2022-01-01 | 441215442   | 512100212 | 84.21  |
+------------+-------------+-----------+--------+
| 2022-01-02 | 102212210   | 154214523 | 214.21 |
+------------+-------------+-----------+--------+

Like for Google Ads, our model is quite easy to write:

WITH ad_spend_info AS ( -- Reading only what we need from the DataWarehouse
    SELECT 
        date,
        campaign_id,
        spend
    FROM
        -- When copy pasting into Whaly Workbench, please erase this value 
        -- and rewrite it to select the proper table in the auto complete 
        "Facebook Ads"."Marketing Spent"
), daily_spend AS ( -- Needed step to remove the ad_id granularity
    SELECT 
        date,
        campaign_id,
        sum(spend) as spend
    FROM ad_spend_info
    GROUP BY 1, 2
), event_info AS ( -- Enrich Facebook Ads data to create a proper Event
    SELECT 
        "Marketing Spend" as event_name,
        date as event_date,
        "Social Ads" as channel,
        "Facebook Ads" as source,
        campaign_id,
        spend
    FROM daily_spend
)
SELECT * FROM event_info

Which results in:

+------------+-----------------+------------+--------------+-------------+--------+
| event_date | event_name      | channel    | source       | campaign_id | spend  |
+------------+-----------------+------------+--------------+-------------+--------+
| 2022-01-01 | Marketing Spend | Social Ads | Facebook Ads | 102212210   | 54.21  |
+------------+-----------------+------------+--------------+-------------+--------+
| 2022-01-01 | Marketing Spend | Social Ads | Facebook Ads | 441215442   | 126.46 |
+------------+-----------------+------------+--------------+-------------+--------+
| 2022-01-02 | Marketing Spend | Social Ads | Facebook Ads | 102212210   | 214.21 |
+------------+-----------------+------------+--------------+-------------+--------+

MQL (=Marketing Qualified Lead) Created Event

Now, let's look into our CRM data to see how we can build the "MQL Created" Event Model. Let's imaging that you are using Hubspot, and that each new Lead is created as a Contact in Hubspot.

Let's have a look at the Hubspot data:

+------------+---------------------+--------------------------+-------------------------------------+-------------------------------------+------------------------------+
| contact_id | property_createdate | property_date_quote_sent | property_hs_analytics_source_data_1 | property_hs_analytics_source_data_2 | property_hs_analytics_source |
+------------+---------------------+--------------------------+-------------------------------------+-------------------------------------+------------------------------+
| 1255412354 | 2022-01-01          | 2022-01-06               | Facebook                            | 102212210                           | PAID_SOCIAL                  |
+------------+---------------------+--------------------------+-------------------------------------+-------------------------------------+------------------------------+
| 8442125214 | 2022-01-02          |                          | 441215442                           | SuperKeyword                        | PAID_SEARCH                  |
+------------+---------------------+--------------------------+-------------------------------------+-------------------------------------+------------------------------+
| 8774154775 | 2022-01-10          | 2022-01-25               | IMPORT                              |                                     | OFFLINE                      |
+------------+---------------------+--------------------------+-------------------------------------+-------------------------------------+------------------------------+

When having an Hubspot tracker on your Website that is used to create the contact when they submit a form, a lot of properties are automatically tracked by Hubspot.

The full documentation can be found here and here.

If you are using another CRM or if you have a custom way to create contacts into your CRM, get in touch with your CRM admin to see how you track the Marketing origin of your Leads.

By using UTMs properly both in your Marketing tools and in your Contact creation logic, you can track the origin of each of your Lead.

See this article for more information.

Now, we'll create a Model to:

a. Properly map the values and columns from Hubspot to the taxonomy used in the Marketing Spend events

b. Map the proper date to the Event Date -> when tracking new MQL, it is common to use the contact creation date as the event date

WITH contact_info AS ( -- Reading only what we need from the DataWarehouse
    SELECT 
        property_createdate as contact_creation_date,
        contact_id,
        property_hs_analytics_source as hs_origin_source,
        property_hs_analytics_source_data_1 as hs_origin_data1,
        property_hs_analytics_source_data_2  as hs_origin_data2
    FROM 
        -- When copy pasting into Whaly Workbench, please erase this value 
        -- and rewrite it to select the proper table in the auto complete
        "Hubspot"."Contact"
), cleaned_contact AS ( 
-- Needed step to map Hubspot generated value with our events taxonomy for source and channel
    SELECT 
        contact_creation_date,
        contact_id,
        CASE
            WHEN hs_origin_source = "PAID_SOCIAL" THEN "Social Ads"
            WHEN hs_origin_source = "PAID_SEARCH" THEN "Search Ads"
            ELSE hs_origin_source
        END as channel,
        CASE
            WHEN hs_origin_source = "PAID_SOCIAL" AND hs_origin_data1 = "Facebook" THEN "Facebook Ads"
            WHEN hs_origin_source = "PAID_SEARCH" THEN "Google Ads"
            ELSE "unknown"
        END as source
    FROM contact_info
), event_info AS ( 
-- Enrich Hubspot data to create a proper Event
    SELECT 
        contact_creation_date as event_date,
        "MQL Created" as event_name,
        channel,
        source,
        contact_id
    FROM cleaned_contact
)
SELECT * FROM event_info

Which results in:

+------------+-------------+------------+--------------+------------+
| event_date | event_name  | channel    | source       | contact_id |
+------------+-------------+------------+--------------+------------+
| 2022-01-01 | MQL Created | Social Ads | Facebook Ads | 1255412354 |
+------------+-------------+------------+--------------+------------+
| 2022-01-02 | MQL Created | Search Ads | Google Ads   | 8442125214 |
+------------+-------------+------------+--------------+------------+
| 2022-01-10 | MQL Created | OFFLINE    | unknown      | 8774154775 |
+------------+-------------+------------+--------------+------------+

SQL (=Sales Qualified Lead) Created Event

in our example, to stay simple, we'll say that a SQL is tracked in our CRM when a contact has a "Date Quote Sent" set.

In reality, SQL are often tracked as Deals / Opportunity in the CRM, so your SQL Event Model might be based on the Deal / Opportunity object, but the logic stay the same.

If we keep the same Hubspot table as above, our Model definition would be:

WITH contact_info AS ( -- Reading only what we need from the DataWarehouse
    SELECT 
        property_date_quote_sent as contact_quote_sent_date,
        contact_id,
        property_hs_analytics_source as hs_origin_source,
        property_hs_analytics_source_data_1 as hs_origin_data1,
        property_hs_analytics_source_data_2  as hs_origin_data2
    FROM 
        -- When copy pasting into Whaly Workbench, please erase this value 
        -- and rewrite it to select the proper table in the auto complete
        "Hubspot"."Contact"
    -- This is where we only keep contact that were sent a Quote for the SQL definition
    WHERE property_date_quote_sent IS NOT NULL 
), cleaned_contact AS ( 
-- Needed step to map Hubspot generated value with our events taxonomy for source and channel
    SELECT 
        contact_quote_sent_date,
        contact_id,
        CASE
            WHEN hs_origin_source = "PAID_SOCIAL" THEN "Social Ads"
            WHEN hs_origin_source = "PAID_SEARCH" THEN "Search Ads"
            ELSE hs_origin_source
        END as channel,
        CASE
            WHEN hs_origin_source = "PAID_SOCIAL" AND hs_origin_data1 = "Facebook" THEN "Facebook Ads"
            WHEN hs_origin_source = "PAID_SEARCH" THEN "Google Ads"
            ELSE "unknown"
        END as source
    FROM contact_info
), event_info AS ( 
-- Enrich Hubspot data to create a proper Event
    SELECT 
        contact_quote_sent_date as event_date,
        "SQL Created" as event_name,
        channel,
        source,
        contact_id
    FROM cleaned_contact
)
SELECT * FROM event_info

Which results in:

+------------+-------------+------------+--------------+------------+
| event_date | event_name  | channel    | source       | contact_id |
+------------+-------------+------------+--------------+------------+
| 2022-01-06 | SQL Created | Social Ads | Facebook Ads | 1255412354 |
+------------+-------------+------------+--------------+------------+
| 2022-01-25 | SQL Created | OFFLINE    | unknown      | 8774154775 |
+------------+-------------+------------+--------------+------------+

As you can see, the logic to convert the Hubspot "origin" columns into our Event taxonomy is duplicated in both the MQL and SQL models.

As this mapping will have to change in the future and as it's hard to deal with code duplication, a good thing would be to create a "Contact With Marketing Origin" model containing the mapping logic once and use it in both MQL Created and SQL Created Models configuration.

We'll stop here the definition of our Events, but the same logic and principles can be used to create other events of the bottom of your funnel:

• Demo done

• Contract Signed

• Upsell done

• etc.

It all depends of how your Sales Funnel is structured!

2. Build our "Funnel Table"

Now that we have our 4 Events:

• Marketing Spend (Google Ads)

• Marketing Spend (Facebook Ads)

• MQL Created

• SQL Created

We'll create a "Funnel table" that will import all those events into a consolidated timeline to do reporting and calculate conversion rates and MQL / SQL acquisition costs.

This Funnel table is once agin a Model defined as:

-- || Top of the funnel || --
-- Google Ads
SELECT
    event_date,
    event_name,
    channel,
    source,
    campaign_id,
    spend,
    'n/a' as contact_id
FROM 
    -- When copy pasting into Whaly Workbench, please erase this value 
    -- and rewrite it to select the proper table in the auto complete
    "Marketing Spend Event - Google Ads"
UNION ALL
-- Facebook Ads
SELECT
    event_date,
    event_name,
    channel,
    source,
    campaign_id,
    spend,
    -- We add fake values for the columns specific to the MQL/SQL Created events
    'n/a' as contact_id
FROM 
    -- When copy pasting into Whaly Workbench, please erase this value 
    -- and rewrite it to select the proper table in the auto complete
    "Marketing Spend Event - Facebook Ads"
UNION ALL
-- MQL Created
SELECT
    event_date,
    event_name,
    channel,
    source,
    contact_id,
    -- We add fake values into the columns that are specific to the Marketing Spend events
    'n/a' as campaign_id,
    0 as spend
FROM 
    -- When copy pasting into Whaly Workbench, please erase this value 
    -- and rewrite it to select the proper table in the auto complete
    "MQL Created"
UNION ALL
SELECT
    event_date,
    event_name,
    channel,
    source,
    contact_id,
    -- We add fake values into the columns that are specific to the Marketing Spend events
    'n/a' as campaign_id,
    0 as spend
FROM 
    -- When copy pasting into Whaly Workbench, please erase this value 
    -- and rewrite it to select the proper table in the auto complete
    "SQL Created"
-- || Bottom of the funnel || --

Which results in:

+------------+-----------------+------------+--------------+-------------+--------+------------+
| event_date | event_name      | channel    | source       | campaign_id | spend  | contact_id |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-01 | Marketing Spend | Search Ads | Google Ads   | 123         | 18.67  | n/a        |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-01 | Marketing Spend | Search Ads | Google Ads   | 456         | 54.21  | n/a        |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-02 | Marketing Spend | Search Ads | Google Ads   | 456         | 48.21  | n/a        |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-01 | Marketing Spend | Social Ads | Facebook Ads | 102212210   | 54.21  | n/a        |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-01 | Marketing Spend | Social Ads | Facebook Ads | 441215442   | 126.46 | n/a        |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-02 | Marketing Spend | Social Ads | Facebook Ads | 102212210   | 214.21 | n/a        |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-01 | MQL Created     | Social Ads | Facebook Ads | n/a         | 0      | 1255412354 |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-02 | MQL Created     | Search Ads | Google Ads   | n/a         | 0      | 8442125214 |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-10 | MQL Created     | OFFLINE    | unknown      | n/a         | 0      | 8774154775 |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-06 | SQL Created     | Social Ads | Facebook Ads | n/a         | 0      | 1255412354 |
+------------+-----------------+------------+--------------+-------------+--------+------------+
| 2022-01-25 | SQL Created     | OFFLINE    | unknown      | n/a         | 0      | 8774154775 |
+------------+-----------------+------------+--------------+-------------+--------+------------+

This single table consolidate all our Marketing Funnel events into a single view, with a consolidated taxonomy 🎉

Now that we did the "hard" part, we only have to do the "nice" job to create an Exploration on top of this table!

3. Create an Exploration

Now that we have our single table, we can create an Exploration with the following configuration:

Dimensions:

• event_date as Date

• channel as Channel

• source as Source

Metrics:

• SUM(spend) | If event_name = "Marketing Spend" as Spend

• COUNT | If event_name = "MQL Created" as MQLs

• COUNT | If event_name = "SQL Created" as SQLs

Calculated Metrics:

• SQLs / MQLs as "MQLs -> SQLs conversion rate"

• Spend / MQLs as "CAC MQLs"

• Spend / SQLs as "CAC SQLs"

4. Create a dashboard

Now you can chart your CACs, Conversion rate and funnel breakdown by Channel / Sources.

5. Next steps

This guide only covers a simple marketing funnel, however, there can be many differents direction to take from there:

• You can add other Events in the Bottom of your funnel to have a better view of where you lose Customers and Revenue per Channel / Source

• You can add events that have a "revenue" attributes (ex. Contract Signed) and create a new "revenue" column in your Funnel table. This will unlock the ROAS metric.

• If you have the Campaign IDs stored in your CRM when Leads are created, you can expose it in the funnel and get a "per Campaign" view of your Funnel

• You can even go one step further and build this funnel at the "Ad Group" / "Ad" level by adding the proper attribute to all the events on the chain (you probably have to tweak a bit the way your campaigns are tracked in your CRM when Leads are created to get this level of details)