" %}
#### Creating a SQL Model
In order to model our current subscriptions data, we will need to create a SQL Model. This can easily done in Whaly.
You can create a new source, select the option `From warehouse` and name it `SQL`. Inside this source create a SQL Model named `Simplified SQL`.
If you have not created any models until now, it is easily done by following our [documentation on SQL Models](https://docs.whaly.io/data-management/workbench/model-data/sql-models).
### Writing down the SQL Query (BigQuery)
We will see two methods in order to model our MRR:
* A simplified method for SQL intermediates users.
* An advanced method for SQL advanced to expert users.
During these steps by steps we will rely on some specific BigQuery commands in order to manipulate dates. These steps should be adapted if used with an other warehouse.
# SQL for simplified MRR calculation
## SQL for simplified MRR calculation
*Required SQL knowledge:* WITH clause, MIN and MAX functions, INNER JOINS, BigQuery arrays manipulation.
In order to model our revenue, we will write a query which will execute the following step:
1. Retrieve all subscriptions
2. Calculate oldest and most recent subscriptions dates
3. Generate a list of all the month between our oldest and most recent dates
4. Join our months with our subscriptions
In the end, our modeled dataset will look like this:
### Retrieve all subscriptions
Let's start by querying all of our subscriptions (you may need to update the subscription table in the from command):
```sql
-- all subscriptions
with subscriptions as (
select
subscription_id,
customer_id,
monthly_amount,
start_date,
end_date,
from
${TABLES["Subscriptions"]["subscriptions"]}
)
select * from subscriptions
```
Run this query, you should see the content of your subscription table as the result.
### Calculate oldest and most recent subscriptions dates
Then we will need to calculate our oldest start date and most recent end date. Let's do it using the `min` and `max` functions:
```sql
...
-- min and max subscriptions dates
date_limits as (
select
min(start_date) as min_date,
max(end_date) as max_date
from
subscriptions
)
select * date_limits
```
Run this query, you should see the min and max subscriptions dates.
### Generate a list of all the month between our oldest and most recent dates
Now we will generate the list of months between our `min_date` and our `max_date`:
```sql
...
-- array of month between min and max subscriptions dates
months_array as (
select
GENERATE_DATE_ARRAY(
CAST(min_date as DATE),
CAST(max_date as DATE),
INTERVAL 1 month
) as arr
from
date_limits
),
-- list of month between min and max subscriptions dates
months as (
select
CAST(month as TIMESTAMP) as month
from
months_array,
UNNEST(months_array.arr) as month
)
select * from months
```
Run this query, you should see the list of months between our `min_date` and `max_date`.
### Join our months with our subscriptions
Let's write the final step, which will use an inner join between our `subscriptions` and our `months` tables in order to generate one line per active subscription month.
The entire SQL query should now be as following:
```sql
-- all subscriptions
with subscriptions as (
select
subscription_id,
customer_id,
monthly_amount,
start_date,
end_date,
from
${TABLES["Subscriptions"]["subscriptions"]}
),
-- min and max subscriptions dates
date_limits as (
select
min(start_date) as min_date,
max(end_date) as max_date
from
subscriptions
),
-- array of month between min and max subscriptions dates
months_array as (
select
GENERATE_DATE_ARRAY(
CAST(min_date as DATE),
CAST(max_date as DATE),
INTERVAL 1 month
) as arr
from
date_limits
),
-- list of month between min and max subscriptions dates
months as (
select
CAST(month as TIMESTAMP) as month
from
months_array,
UNNEST(months_array.arr) as month
),
-- months joined with active subscriptions months
subscriptions_months as (
select
months.month,
subscriptions.customer_id,
subscriptions.monthly_amount as mrr,
subscriptions.start_date,
subscriptions.end_date
from
subscriptions
inner join months on months.month >= subscriptions.start_date
and months.month < subscriptions.end_date
)
select
*
from
subscriptions_months
```
Run the query: we now have one line per active subscription month :tada:
Save the query (you can use a combination of `month` and `customer_id` as primary key).
### Build the exploration and the dashboard
By creating an exploration on top on this dataset, we will be able to build the following dashboard:
# SQL for advanced MRR calculation
## SQL for advanced MRR calculation
*Required SQL knowledge:* WITH clause, MIN, MAX LEAST and COALESCE functions, Window functions, INNER JOINS, CASE statements, BigQuery arrays manipulation, subqueries.
The main differences with the simplified version are:
* Adding inactive months for a customers, between his active subscriptions
* Identifying MRR change for a customer
* Categorising our MRR change between new, renewal, upgrade, downgrade and churn
In order to model our revenue, we will write a query which will execute the following step:
1. Retrieve all subscriptions and create a list of all the month between our oldest and most recent subscription dates
2. Calculate our customer revenue by month
3. Create our customer churn months
In the end, our modeled dataset will look like this:
### Retrieve all subscriptions and create a list of all the month between our oldest and most recent subscription dates
This step is the beginning of our SQL for simplified MRR calculate article, which is:
```sql
-- all subscriptions
with subscriptions as (
select
subscription_id,
customer_id,
monthly_amount,
start_date,
end_date,
from
${TABLES["Subscriptions"]["subscriptions"]}
),
-- min and max subscriptions dates
date_limits AS (
SELECT
MIN(start_date) AS min_date,
MAX(end_date) as max_date
FROM
subscriptions
),
-- array of month between min and max subscriptions dates
months_array AS (
SELECT
GENERATE_DATE_ARRAY(
CAST(min_date AS DATE),
CAST(max_date AS DATE),
INTERVAL 1 month
) AS arr
FROM
date_limits
),
-- list of months between min and max subscriptions dates
months as (
SELECT
CAST (month as TIMESTAMP) as month
FROM
months_array,
UNNEST(months_array.arr) AS month
),
...
```
### Calculate our customer revenue by month
Now for each of our customers we will:
1. Calculate for each customer is first and last active months.
2. Create one row per month between his first and last month.
3. Join our subscriptions with our customer months and set the MRR to 0 for months when a customer subscription is inactive.
4. Identify for each customer the `first_active_month`, `last_active_month`, and for each customer month we add a flag `is_first_month` and `is_last_month`.
```sql
...
-- determine when a given customer had its first and last (or most recent) month
customers as (
select
customer_id,
min(start_date) as date_month_start,
max(end_date) as date_month_end
from
subscriptions
group by
1
),
-- create one record per month between a customer's first and last month
customer_months as (
select
customers.customer_id,
months.month
from
customers
inner join months on months.month >= customers.date_month_start
and months.month < customers.date_month_end
),
-- join the account-month spine to MRR base model, pulling through most recent dates
-- and plan info for month rows that have no invoices (i.e. churns)
joined as (
select
customer_months.month,
customer_months.customer_id,
coalesce(subscriptions.monthly_amount, 0) as mrr
from
customer_months
left join subscriptions on customer_months.customer_id = subscriptions.customer_id
and customer_months.month >= subscriptions.start_date
and customer_months.month < subscriptions.end_date
),
customer_revenue_by_month as (
select
*,
first_active_month = month as is_first_month,
last_active_month = month as is_last_month,
from
(
select
*,
mrr > 0 as is_active,
min(case when mrr > 0 then month end) over (partition by customer_id) as first_active_month,
max(case when mrr > 0 then month end) over (partition by customer_id) as last_active_month,
from
joined
)
),
...
```
### Create our customer churn months
We add a new month after our customer last month in order to create one churn month per customer
```sql
...
customer_churn_month as (
select
cast (date_add(cast (month as date), interval 1 month) as timestamp) as month,
customer_id,
0 as mrr,
false as is_active,
first_active_month,
last_active_month,
false as is_first_month,
false as is_last_month
from
customer_revenue_by_month
where
is_last_month
),
...
```
### Calculate MRR change
Now we will:
1. Merge our `customer_revenue_by_month` table with our `customer_churn_month` table
2. Get our customer prior month MRR and calculate our current month MRR change
```sql
...
unioned as (
select
*
from
customer_revenue_by_month
union all
select
*
from
customer_churn_month
),
-- get prior month MRR and calculate MRR change
mrr_with_changes as (
select
*,
mrr - previous_month_mrr as mrr_change
from
(
select
*,
coalesce(
lag(is_active) over (partition by customer_id order by month),
false
) as previous_month_is_active,
coalesce(
lag(mrr) over (partition by customer_id order by month),
0
) as previous_month_mrr,
from
unioned
)
),
...
```
### Classify our MRR change into categories
Now we have all the data we need to categorize our MRR change. We can easily identify :
* New MRR
* Churned MRR
* Upgrade MRR
* Downgrade MRR
* Reactivation MRR
```sql
...
-- classify months as new, churn, reactivation, upgrade, downgrade (or null)
mrr_with_changes_categories as (
select
*,
case
when is_first_month then 'new'
when not(is_active) and previous_month_is_active then 'churn'
when is_active and not(previous_month_is_active) then 'reactivation'
when mrr_change > 0 then 'upgrade'
when mrr_change < 0 then 'downgrade'
end as change_category,
least(mrr, previous_month_mrr) as renewal_amount
from
mrr_with_changes
)
...
```
### Assembling the full query
Now we can assemble all the blocks into our full query:
```sql
-- all subscriptions
with subscriptions as (
select
subscription_id,
customer_id,
monthly_amount,
start_date,
end_date,
from
${TABLES["Subscriptions"]["subscriptions"]}
),
-- min and max subscriptions dates
date_limits AS (
SELECT
MIN(start_date) AS min_date,
MAX(end_date) as max_date
FROM
subscriptions
),
-- array of month between min and max subscriptions dates
months_array AS (
SELECT
GENERATE_DATE_ARRAY(
CAST(min_date AS DATE),
CAST(max_date AS DATE),
INTERVAL 1 month
) AS arr
FROM
date_limits
),
-- list of months between min and max subscriptions dates
months as (
SELECT
CAST (month as TIMESTAMP) as month
FROM
months_array,
UNNEST(months_array.arr) AS month
),
-- determine when a given customer had its first and last (or most recent) month
customers as (
select
customer_id,
min(start_date) as date_month_start,
max(end_date) as date_month_end
from
subscriptions
group by
1
),
-- create one record per month between a customer's first and last month
customer_months as (
select
customers.customer_id,
months.month
from
customers
inner join months on months.month >= customers.date_month_start
and months.month < customers.date_month_end
),
-- join the account-month spine to MRR base model, pulling through most recent dates
-- and plan info for month rows that have no invoices (i.e. churns)
joined as (
select
customer_months.month,
customer_months.customer_id,
coalesce(subscriptions.monthly_amount, 0) as mrr
from
customer_months
left join subscriptions on customer_months.customer_id = subscriptions.customer_id
and customer_months.month >= subscriptions.start_date
and customer_months.month < subscriptions.end_date
),
customer_revenue_by_month as (
select
*,
first_active_month = month as is_first_month,
last_active_month = month as is_last_month,
from
(
select
*,
mrr > 0 as is_active,
min(case when mrr > 0 then month end) over (partition by customer_id) as first_active_month,
max(case when mrr > 0 then month end) over (partition by customer_id) as last_active_month,
from
joined
)
),
-- row for month *after* last month of activity
customer_churn_month as (
select
cast (date_add(cast (month as date), interval 1 month) as timestamp) as month,
customer_id,
0 as mrr,
false as is_active,
first_active_month,
last_active_month,
false as is_first_month,
false as is_last_month
from
customer_revenue_by_month
where
is_last_month
),
unioned as (
select
*
from
customer_revenue_by_month
union all
select
*
from
customer_churn_month
),
-- get prior month MRR and calculate MRR change
mrr_with_changes as (
select
*,
mrr - previous_month_mrr as mrr_change
from
(
select
*,
coalesce(
lag(is_active) over (partition by customer_id order by month),
false
) as previous_month_is_active,
coalesce(
lag(mrr) over (partition by customer_id order by month),
0
) as previous_month_mrr,
from
unioned
)
),
-- classify months as new, churn, reactivation, upgrade, downgrade (or null)
mrr_with_changes_categories as (
select
*,
case
when is_first_month then 'new'
when not(is_active) and previous_month_is_active then 'churn'
when is_active and not(previous_month_is_active) then 'reactivation'
when mrr_change > 0 then 'upgrade'
when mrr_change < 0 then 'downgrade'
end as change_category,
least(mrr, previous_month_mrr) as renewal_amount
from
mrr_with_changes
)
select
month,
customer_id,
mrr,
mrr_change,
change_category
from
mrr_with_changes_categories
```
Run the query and save it (you can use a combination of month and customer\_id as primary key).
### Build the exploration and the dashboard
By creating an exploration on top on this dataset, we will be able to build the following dashboard:
# Marketing
# 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.
{% hint style="info" %}
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.
{% endhint %}
#### Funnel tables
Once we have properly defined and create each of the "E**vent 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.
{% hint style="info" %}
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](https://martinfowler.com/articles/data-monolith-to-mesh.html) and [Domain Events](https://martinfowler.com/eaaDev/DomainEvent.html).
{% endhint %}
## 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:
{% hint style="info" %}
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!
{% endhint %}
```
+------------+---------+-----------------+-------------+-------+
| 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:
```sql
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:
```sql
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 |
+------------+---------------------+--------------------------+-------------------------------------+-------------------------------------+------------------------------+
```
{% hint style="info" %}
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](https://knowledge.hubspot.com/contacts/understand-source-properties) and [here](https://knowledge.hubspot.com/reports/understand-hubspots-traffic-sources-in-the-traffic-analytics-tool#definitions-and-drill-down-data-of-each-source).
{% endhint %}
{% hint style="warning" %}
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.](https://www.gravitatedesign.com/blog/utm-codes-crm-tools-4-steps-better-tracking/)
{% endhint %}
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
```sql
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:
```sql
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 |
+------------+-------------+------------+--------------+------------+
```
{% hint style="info" %}
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.
{% endhint %}
{% hint style="success" %}
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!
{% endhint %}
## 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)
# Calculate your Customer Acquisition Cost
**Objective**: Getting new customers is hard and require a lot of Marketing and Sales efforts. Being sure that those costs are kept in control and that your acquisition strategy is sustainable is a matter of life and death of your company.
This core metric will be looked at by your future investors and stakeholders and should be improved until it reaches a level aligned with your business model. It is a good reporting metric for your Sales & Marketing teams.
This guide is targeting Facebook Ads, Google Ads and Hubspot CRM, but the principles can be used with other acquisition sources and CRM tools.
Required sources:
a. Marketing sources such as Google Ads, Facebook Ads, LinkedIn Ads or Airtable / Google Sheets (for offline marketing costs)
b. Your CRM or any data source containing your customers and the date they came from the Marketing channel
## High level plan
**a. We'll make sure that each new customers can be linked to a specific Day of acquisition**
**b. We'll use a "Day" table to bridge your acquisition tools cost reports with your CRM performance report**
**c. We'll create an exploration that will link together all the Marketing and CRM tables**
**d. We'll create calculated metrics to track your Customer Acquisition Cost, ROAS and other key marketing metrics**
{% hint style="info" %}
In this guide, in order to bridge the Marketing tools (Google Ads, Facebook Ads, etc.) and the Sales tools (Hubspot, Salesforce, Pipedrive), we will use the time as being the link between those system.
We do this because in most organisations there is no "Common ID" between the Spent reports coming from the Marketing tools and the CRM tools.
If you're having such IDs in your data setups (ex. Campaign Ids), you can use those to bridge your Marketing data with your Sales data.
{% endhint %}
### **a. We'll make sure that each new customers can be linked to a specific Day of acquisition**
Firstly, you need to define, within your CRM, on which day each of your customers were acquired thanks to a Marketing tool. The easiest way of doing it is by using the CRM Contact "Creation Date" if your CRM is only used by your Sales team.
If you have a more complicated acquisition strategy, you can also decide to chose the date when your Contact became "Sales Qualified Leads (SQL)".
Once you have identified the date column you wish to use as the "acquisition date" in your tables, you need to round it up to the Day granularity to be sure it map properly with your Marketing Spend reports.
You can do that by creating a new formula field in the Workbench with:
```
COHORT(property_createdate; "day")
```
**b. We'll use a "Day" table to bridge your acquisition tools cost reports with your CRM performance report**
We'll now create Relationships between the "Day" table and both the Marketing tools and the CRM data.
{% hint style="info" %}
Whaly includes a "Day" table in your Workbench by default to help you combine multiple data sources on time
{% endhint %}
In the "**Day**" object, add one **`Has Many`** relationship per Table on the different Day columns, as follows:
### **c. We'll create an exploration that will link together all the Marketing and CRM tables**
You can now create an exploration that starts from the Day table:
Click on the + icon and on "Add Related Data" to add the different tables needed for the Customer Acquisition Cost calculation. You should obtain:
### **d. We'll create calculated metrics to track your Customer Acquisition Cost, ROAS and other key marketing metrics**
On the Day table, click on the + icon to create Calculated Metric "Marketing Spent":
On the Day table, click on the + icon to create Calculated Metric "Customer Acquisition Cost":
**You should now have:**
And now, you can plot your Customer Acquisition Cost metric in time or calculate it over a period of time to track your Marketing performance 🎉
# Create a partner dashboard
**Objective**
In every partnership, it is important to share, be it in life, in love or in business. For example, if your company outsources part of its lead acquisition process to external partners, it is important for them to have an overview of how successful you are with converting their leads into deals.
In this guide, you will learn how to structure and configure a partner dashboard that enables you to securely share this information back with your partners.
### High level plan
* Understanding our partner dashboard
* Making our dashboard partner-ready
* Adding a partner filter to our dashboard
* Adding a sharing link
* Hiding the partner filter from the report
### Understanding our partner dashboard
For this guide, we will assume that we have already built our Partner Dashboard, which gives us information about:
* Our leads conversion funnel
* Number of new leads received
* Number of qualified leads
* Number of converted leads
* Our partners based generated revenue
* Our partners commission
You can take a look at our example dashboard below:
### Making our dashboard partner ready
#### Adding a partner filter to our dashboard
To make our dashboard partner ready, we will firstly need to be able to breakdown our view per partner. This steps essentially consists in adding a "Partner" filter to our dashboard, and will allow us to restrict the leads to the partner they are generated from.
Let's get to work. On Whaly, open your partner dashboard, then:
* click on Edit
* click on Add a filter
* select you partner identifier in the "Fields to filter from" list (in this example we will use `partner_name`) and give your filter a name
* in the "Tiles to update" tab, verify that the filter is applied to all the tiles you need
### Adding a sharing link
We will use the sharing link feature to create a pre-filtered view of our dashboard that we will be able to send to our partners.
In order to do so:
* Click on share
* Click on new sharing link
* Give it a name (we will use "Partner: Duff"), a password if you need to, and select set partner filter value to your partner's name.
### Hiding the partner filter from the report
Great, now we have a sharing link. But before sending it, we will probably want to prevent our partner to filter on our others partners. To do so, we will hide the partner filter from the dashboard:
* Click on edit
* Click on the partner filter settings
* Check "Hide your filter from the report" and save
That's it, now we can send our sharing links to our partners. They will be able to view the dashboard to gain insights of how the partnership is performing :tada:
# Sales
# Analyze the impact of your Sales velocity on your closing rate
**Objective**: The sales momentum don't last as prospects can find alternatives to your product/services pretty fast. Hence, it's important to not stall your Opportunities into a stage of your pipeline, otherwise it can negatively impact your close rate.
However, not all Sales Pipeline are born equal, and all stages are not as time sensitive from the other ones. In order to identify the stages that are the more sensitive to delays, to focus on being reactive during those, it's nice to make an analysis on your historical Opportunities data.
This guide is targeting Hubspot CRM, but the principles can be used with other CRM data.
## **High level plan:**
**a. We'll get the time passed in each stage of your Deal pipeline by each of your deals**
**b. We'll create "buckets" to classify the time spent by each deal in the stages that you want to analyse (ex. "Less than 1 hour", "Less than 1 day", "Less than 1 Month", "Less than 1 Year")**
**c. We'll expose those buckets as "Dimensions" in an Exploration**
**d. We'll create a metric "Close rate" so that we can track your close rate across the different buckets**
**e. We'll create some charts to get insights 🎉**
### **a. Get the time passed in each stage for each deal**
Hubspot is giving, for each deal, the time passed in each stage in a Deal property `property_hs_time_in_XXXXXX` with `XXXXXX` being the Stage Id. The column value is the number of milliseconds passed in the stage.
So, to get our analysis done, the first step would be to create a new View on the Hubspot's Deal object and show the columns associated with the Deal Stage that we want to analyse.
{% hint style="info" %}
In order to identify the proper columns, we can have a Look at the \`stage\_id\` column of the Hubspot's Deal Pipeline Stage table.
{% endhint %}
Additionally, we should filter out any deals that have not a value superior at 0 in those columns as those are the Deals that never went through those Stage (they can be from other Pipeline or they could have been lost before entering the Stages that we analyse).
Also, we should filter deals that have `property_hs_is_closed` set at `true` in order to only keep the Deal that are closed, e.g. those that are either Won or Lost.
Finally, we should display the `property_hs_is_closed_won` property. We'll need it to calculate the Close ratio metric later.
### **b. Creating bucket of time**
So, we have the number in milliseconds that deal waited in our Stage, which is great but which is not super helpful to get answers.
In order to get a proper understanding of what time does that represent, we'll create a new column to bucket those time into time period that we can understand.
We can do that by creating a new column in our view with this formula:
```
IF(property_hs_time_in_XXXXXX<3600000;"Less than 1 hour"; IF(property_hs_time_in_XXXXXX<7200000;"Between 1h and 2h"; IF(property_hs_time_in_XXXXXX<86400000;"Between 2h and 1 day"; "More than 1 Day")))
```
The values (1h => 360 000milliseconds, 2h => 7200 000ms, ....) and the labels can be tweaked to match the bucket definition that you want to get for your analysis.
### **c. Expose those buckets as "Dimensions" in an Exploration**
We should now create a new Exploration from our View created in the previous step. We should create a dimension from the formula column created in the previous step.
**d. We'll create a metric "Close rate" so that we can track your close rate across the different buckets**
In order to create a Close rate metric, we'll firstly 2 metrics: "Number of Closed Deal" and a "`Number of Closed Won Deal`" metric.
Then, we create a calculated metric with the formula: `Number of Closed Won Deal / Number of Closed Deal` and we cast it as percent.
**e. We'll create some charts to get insights 🎉**
We can now plot the Close rate per time bucket and get our insights!
### f. Bonus point: Adding a time component
We can add the close date as a Time dimension to see a breakdown of those closing ratio based on the Close time.
This is done by creating a new column in the workbench with the formula:DA
```
DATETIME_FORMAT(property_closedate;"%b %g")
```
And using this new column as the "Close date - Month" in a pivoted chart:
# Create a sales performance dashboard
**Objective**: Closely following sales performance metrics, such as time to close, conversions rates and average order amount is vital for any business. It is mandatory to have this information when working with investors. It also helps you understand when to accelerate on sales as you notice your metrics are getting better.
In this guide, we will learn how to build a report that will:
* Display our average order amount and total revenue
* Track our sales velocity over time
* Monitor our sales pipeline conversions rates
This guide is targeting Hubspot CRM, but the principles can be used with other CRM data.
Here is what we will build :
## High level plan:
1. Creating our Deal exploration
2. Creating our "new revenue" metric
3. Creating our "average order amount" metric
4. Creating our sales velocity chart
5. Creating our deal conversion funnel
6. Creating our report
### Creating our Deal exploration
Let's start with the easy stuff and create a new exploration. We will start our exploration from our Deal table as we want to see 100% of our deals in our sales dashboard.
Let's create the exploration and add the column `property_createdate` as a dimension.
### Creating our "new revenue" metric
In order to display our new revenue, we will want to count the total amount for the deals won. It's easy to create such a metric:
* Create a new metric
* Set it to `Sum` of the column `property_amount`
* In the advanced settings, set a filter on `property_dealstage` equals `closedwon`
* Set a suffix to €
### Creating our "average order amount" metric
To display the average order amount, we will will recreate the same metric as for total revenue but we will use the aggregation "average" instead of sum:
* Create a new metric
* Set it to be `Average` of the column `property_amount`
* In the advanced settings, set a filter on `property_dealstage` equals `closedwon`
* Don't forget the suffix and the name :nerd:
Now you can use these two metrics on our dashboard:
### Creating our sales velocity chart
Hubspot provides a column named `property_days_to_close` on the deal table which contains the number of days from the deal creation date to the deal closed date. To monitor our sales velocity, we will create a new metric:
* Create a new metric
* Set it to average of the column `property_days_to_close`
* In the advanced settings, set a filter on `property_dealstage` equals `closedwon`
* You can add `days` as a suffix in the advanced settings
### Creating our deal conversion funnel
In order to closely monitor our sales pipeline, we will create a conversion funnel on top of our sales process. In this guide our funnel is composed of three steps: Demo -> Trial -> Customer
First, we will create a metric for each of our pipeline steps : number of trial done, number of demo done and number of customer. Let's do it:
* Create a new metric
* Set it to count
* In the advanced settings, set a filter to on `property_hs_date_entered_in_xxx` to `is set`, where `xxx` is the corresponding stage id in Hubspot.
* Give it a name (for example, number of demo done)
{% hint style="info" %}
The matching between the pipeline stage id and the pipeline label (displayed in hubspot) in available in the Deal pipeline stages table.
{% endhint %}
This allows us to monitor our sales funnel by month:
As well as displaying our global sales funnel:
We will also calculate our conversion rate between our pipeline stage. Here we will need to create two calculated metrics:
1. Demo conversion rate, which is `number of trial done / number of demo done`
2. Trial conversion rate, which is `number of customer / number of trial done`
This will allow us to monitor our conversion rates in time:
### Creating our report
Now we have all the elements we need to build our sales performance report. Let's just assemble everything and apply some formatting to get our final result :
# Build a target oriented sales dashboard
**Objectives**:
As a sales manager, having a target oriented reporting strategy for your team is a driver of success.
Objective tracking is a must-have tool that will animate your team and enable your teammate to deliver. It's a good start for starting sales gamification!
On a higher level, it will give you a good tool to drive your 1-to-1 meetings with your Sales rep and give you an easy way to identify which teammate requires coaching and on which topic.
If you follow this article, you will be able to build the following dashboard:
## High level plan
* 🚛 Importing data into Whaly
* Importing CRM data
* Importing target data
* 🔗 Creating the relationships between the datasets
* 📆 Creating the exploration from Days
* 📈 Creating the charts
* ✨ Creating the dashboard
### 🚛 Importing data into Whaly
{% hint style="info" %}
On this article we'll use data from Airtable but the same pattern can be applied to any CRM such as [Hubspot](https://docs.whaly.io/sources/source-catalog/sales/hubspot), [Salesforce](https://docs.whaly.io/sources/source-catalog/sales/salesforce) and [Pipedrive](https://docs.whaly.io/sources/source-catalog/sales/pipedrive) 🤗
{% endhint %}
#### Importing CRM data
To get started, the first thing we are going to do is import our CRM data into Whaly. Our CRM data is composed of two tables.
One "deal" table containing information about our deals:
One "deal stage history" table containing information about our deals movements throughout our sales pipelines:
#### Importing target data
The second type of data we are going to import is the targets. We will create monthly targets for our salespersons and structure our data with the following columns:
* **owner\_id**: the owner to whom the target applies
* **type**: the current objective (revenue, number of call done, ...)
* **date**: the current objective date
* **target**: the current target amount
{% hint style="info" %}
Targets should be written in a system on which you can easily input new data. We recommend to use either [Google Sheets](https://docs.whaly.io/sources/source-catalog/no-code/google-sheets) or [Airtable](https://docs.whaly.io/sources/source-catalog/no-code/airtable) for this task.
{% endhint %}
Let's take have a look at our example target database:
### 🔗 Creating the relationships between the datasets
Before creating our relationships, we need to ensure that our data will match. In our days table, the date is a timestamp at the beginning of the day (ex: `June 27 2022, 00:00:00`). In our deal stage history table, our timestamps have hours, minutes and seconds (ex: `February 17 2020, 17:39:02`). We will need to round our dates in our deal stage history table in order to have matching data for the relationship. Let's do it:
1. Open the workbench, go the your deal stage history view
2. Click on add a column, select formula, and use the cohort formula `day` as the type in order to round the date to the current day:
As our targets and deals are going to be linked using our Days table, we will need to create relationships between:
1. Days *has many* Targets
2. Days *has many* Deal Stage History
3. Deal *has many* Deal Stage History
Let's get into the workbench, on the day table and create the following relationships:
We will also need to create the relationship between our deals and deals stage history (this is optional is you are using Whaly native CRM connectors that auto create such relationships):
### :telescope: Retrieve the missing columns in the deal stage history table
It's possible that your deal stage history table, that we will use our exploration, is missing some columns, such as our deal amount and our owner id. Fortunately we have this information in our deal table, so it's easy to bring them in the deal stage history table.
Let's do it :
1. Open the workbench, go the your deal stage history view
2. Click on add a column, select lookup, and fill the information as required:
Now let's do the same thing for our amount column:
Now that we have all our data, we can start building our exploration.
### 📆 Creating the exploration from Days
Let's get to our workspace and create a new exploration, starting from our Days table. We do this in order to be able to add our "Deal Stage history" and "Targets" tables as related data.
Let's do it:
1. Create a new exploration from `Days`
2. On the Days table:
1. add `Date` as a dimension
2. add `Deal stage history` as a related data
3. add `Targets` as a related data
3. Remove all the metrics created automatically
You should now have an exploration that looks like the following:
Now we will create our metrics for targets and current progress.
Let's get started with the bookings target:
1. Click on `Add a metric` under the `targets` table:
2. Create a sum of the column `target`
3. Filter on all rows matching your desired `owner_id` and target type `revenue`
4. Give your custom metric a name, for example: `Target bookings (owner #0)`
5. Add a currency suffix to ensure our charts will look good
6. Create the metric
Now let's do the same for the current bookings of our `Owner #0`:
* Click on `Add a metric` under the `deal stage history` table:
* Create a sum of the column `amount`
* Filter on all rows matching your desired `owner_id` and deal stage `closedwon`
* Give your custom metric a name, for example: `Bookings (owner #0)`
* Add a currency suffix to ensure our charts will look good
* Create the metric
{% hint style="info" %}
If the columns amount and owner\_id are missing from the deal stage history table you can easily add them using a lookup column in the workbench. See our guide here:
{% endhint %}
We just have to repeat these steps for each of our owners and metrics that we want to follow. For example, with 2 owners and with revenue, Nb call done, Nb demo done, Nb trials done, we should build the following exploration:
### 📆 Creating the charts
In order to create the charts, we will:
1. select the `Metric` chart type
2. add both our current KPI and target to our query builder, for example Bookings `(owner #0)` and `Target bookings (owner #0)`
3. add our `Date` dimension as the time field,
4. select a relevant time range
5. set `Gauge` as metric type
6. run the query
### ✨ Creating the dashboard
We can repeat the previous step as many times as necessary in order to build our dashboard. For our example, with 4 KPIs and two different salespersons we can build the following report:
And voilà, that's it :tada:
# SQL Fanout
If you're a SQL user, some of the first SQL concepts you probably learned about were joins and aggregate functions (such as `COUNT` and `SUM`). One thing that is not always taught is how these two concepts can interact and sometimes produce incorrect results. In this article we'll discuss what to look out for, the concept of a "fanout," and why it matters to SQL writers.
This is one of the reason why there is an old wisdom that "JOINs are tricky to get right", even for experience SQL writer.
## Starting with a simple join
Let's start off with a simple example, where we'll join together a couple of tables. Our first table will show our customers' names and the number of visits each customer has made to our e-commerce website:\
**customer**
| customer\_id | first\_name | last\_name | visits |
| ------------ | ----------- | -------------- | ------ |
| 1 | Jean | de la Fontaine | 2 |
| 2 | Émile | Zola | 2 |
| 3 | René | Descartes | 4 |
\
Our second table will include all the orders that those customers have placed. You can see that each order is linked to the customer who placed it by the customer's ID.\
**order**
| order\_id | amount | customer\_id |
| --------- | ------ | ------------ |
| 1 | 25.00 | 1 |
| 2 | 50.00 | 1 |
| 3 | 75.00 | 2 |
| 4 | 100.00 | 3 |
\
Joining these tables together in SQL would be pretty simple:
```
SELECT
*
FROM
customer
LEFT JOIN order
USING (customer_id)
```
\
The result of that query would be this table:
| customer\_id | first\_name | last\_name | visits | order\_id | amount |
| ------------ | ----------- | -------------- | ------ | --------- | ------ |
| 1 | Jean | de la Fontaine | 2 | 1 | 25.00 |
| 1 | Jean | de la Fontaine | 2 | 2 | 50.00 |
| 2 | Émile | Zola | 2 | 3 | 75.00 |
| 3 | René | Descartes | 4 | 4 | 100.00 |
## Aggregate functions gone bad
\
Now that we have a joined table, we need to be careful about how we use aggregate functions like `COUNT` and `SUM`.\
### Aggregate functions on a single table
\
Let's consider the customer table all by itself again. If we want to know the total number of customers, we can execute a simple query like this:
```sql
SELECT
COUNT(*) as count
FROM customer
```
SQL will count up the rows in the table as follows:\
**customer**
| `count` | customer\_id | first\_name | last\_name | visits |
| -------------- | ------------ | ----------- | -------------- | ------ |
| + 1 | 1 | Jean | de la Fontaine | 2 |
| + 1 | 2 | Émile | Zola | 2 |
| + 1 | 3 | René | Descartes | 4 |
| **Results ⤵️** | | | | |
| `count` | 3 (✅) | | | |
\
We'll get a count of 3, which is correct 👍
Or, if we want to know the total number of customer visits, we can execute another straightforward query like this:
```sql
SELECT
SUM(visits) as sum
FROM
customer
```
SQL will add up the number of visits in the table as follows:
**customer**
| customer\_id | visits | sum | first\_name | last\_name |
| -------------- | ------ | --- | ----------- | -------------- |
| 1 | 2 | + 2 | Jean | de la Fontaine |
| 2 | 2 | + 2 | Émile | Zola |
| 3 | 4 | + 4 | René | Descartes |
| **Results ⤵️** | | | | |
| `sum` | 8 (✅) | | | |
\
We'll get a result of 8, which is also correct 👍\
### Aggregate functions on the joined table
\
So far, so good. However, if we try to use the same aggregate functions on either of our joined tables, we'll start to see incorrect results.
Running a basic count on the joined table, we will no longer get the correct number of customers:
```sql
SELECT
COUNT(*) as count
FROM
customer
LEFT JOIN order
USING (customer_id)
```
SQL will count up the rows in the table as follows:
| | | | | | | |
|---|
count
| customer_id | first_name | last_name | visits | order_id | amount | customer_id |
| + 1 | 1 | Jean | de la Fontaine | 2 | 1 | 25.00 | 1 |
| + 1 | 1 | Jean | de la Fontaine | 2 | 2 | 50.00 | 1 |
| + 1 | 2 | Émile | Zola | 2 | 3 | 75.00 | 2 |
| + 1 | 3 | René | Descartes | 4 | 4 | 100.00 | 3 |
| Results ⤵️ | | | | | | | |
count | 4 (❌) | | | | | | |
\
We'll get a result of 4, even though there are really only 3 customers. You can see that Jean is counted twice.
Similarly, if we try to sum the number of visits, we will no longer get the correct result:
```sql
SELECT
SUM(visits) as sum
FROM
customer
LEFT JOIN order
USING (customer_id)
```
SQL will add up the number of visits in the table as follows:
| customer_id | visits | sum | first_name | last_name | order_id | amount | customer_id |
|---|
| 1 | 2 | + 2 | Jean | de la Fontaine | 1 | 25.00 | 1 |
| 1 | 2 | + 2 | Jean | de la Fontaine | 2 | 50.00 | 1 |
| 2 | 2 | + 2 | Émile | Zola | 3 | 75.00 | 2 |
| 3 | 4 | + 4 | René | Descartes | 4 | 100.00 | 3 |
| Results ⤵️ | | | | | | | |
SUM | 10 (❌) | | | | | | |
\
We'll get a result of 10, even though there are only 8 visits. Jean's 2 visits are added twice.\
## A fanout happened while you weren't looking
\
In the example we've been looking at, the primary table (customer) had only three rows. The "primary table" is the table that is in the `FROM` clause of our SQL queries. After the join, we now have 4 rows. Since the joined table has more rows than the primary table, we say that a *fanout* has occurred.
To avoid a fanout, we can write the join in the opposite order. So, instead of this:
```sql
SELECT *
FROM customer
LEFT JOIN order
USING (customer_id)
```
We would do this:
```sql
SELECT *
FROM order
LEFT JOIN customer
USING (customer_id)
```
Now our joined table looks like this:
| order\_id | amount | customer\_id | first\_name | last\_name | visits |
| --------- | ------ | ------------ | ----------- | -------------- | ------ |
| 1 | 25.00 | 1 | Jean | de la Fontaine | 2 |
| 2 | 50.00 | 1 | Jean | de la Fontaine | 2 |
| 3 | 75.00 | 2 | Émile | Zola | 2 |
| 4 | 100.00 | 3 | René | Descartes | 4 |
\
The eagle-eyed observer will recognize that this is exactly like our other joined table, except for the order of the columns. That is true, and it's why most writers of SQL think of our two different joins as exactly the same.
However, when we do the join in this order, we do not have a fanout. Our original table (order) had four rows, and our joined table also has four rows, so there has been no fanout.
{% hint style="info" %}
Herein lies a key point: To help avoid fanouts, begin your joins with the most granular table.
{% endhint %}
### Fanouts, schmanouts, who cares?
\
In both the fanout and the non-fanout cases, we still need to worry about the accuracy of aggregate functions. However, there is a subtle difference in the type of problems we're going to see:
* **No fanout** 😨 — You can trust aggregate functions on your primary table but not necessarily on your joined tables.
* **Fanout 😱** — You cannot necessarily trust aggregate functions on either your primary table or your joined tables.
To drive home this point, let's take a look at our previous examples.
### Fanout example
\
If you'll recall from the previous example, the following join resulted in a fanout, because while the customer table only had three rows, the joined table had four rows.
```sql
SELECT *
FROM customer
LEFT JOIN order
USING (customer_id)
```
| customer\_id | first\_name | last\_name | visits | order\_id | amount |
| ------------ | ----------- | -------------- | ------ | --------- | ------ |
| 1 | Jean | de la Fontaine | 2 | 1 | 25.00 |
| 1 | Jean | de la Fontaine | 2 | 2 | 50.00 |
| 2 | Émile | Zola | 2 | 3 | 75.00 |
| 3 | René | Descartes | 4 | 4 | 100.00 |
\
Since we are in a fanout situation, we cannot trust that aggregate functions will work on the primary table (customer). As we saw previously, `SUM(visits)` will give us a value of 10, even though only 8 visits have actually occurred.\
### No fanout example
\
When we reversed the join, we did not get a fanout, **because the order table had four rows, and the joined table also had four rows.**
```sql
SELECT *
FROM order
LEFT JOIN customer
USING (customer_id)
```
| order\_id | amount | customer\_id | first\_name | last\_name | visits |
| --------- | ------ | ------------ | ----------- | -------------- | ------ |
| 1 | 25.00 | 1 | Jean | de la Fontaine | 2 |
| 2 | 50.00 | 1 | Jean | de la Fontaine | 2 |
| 3 | 75.00 | 2 | Émile | Zola | 2 |
| 4 | 100.00 | 3 | René | Descartes | 4 |
\
Without any fanout, we can trust that aggregate functions will work on the primary table (order).
For example, `SUM(amount)` will give us a value of 250.00, which is the correct amount of money collected 👍
However, we can't trust any aggregation done on the secondary table (customer), such as `SUM(visits)` = 10 (❌)\
## Two friendly joins, one frenemy join
\
If we want to avoid fanouts, it's important that we understand the three different types of joins.
### One-to-one (friendly)
If one row of your primary table only ever matches up with one and only one row of your joined table, you have a one-to-one join. This type of join will not result in a fanout, and aggregate functions will be accurate no matter where you use them.
**Example**: Suppose you have a person table and a DNA table. Since only one person can be matched with one DNA record and one DNA record with one person, this is a one-to-one join.
### Many-to-one / Belongs to (friendly)
If many rows of your primary table match up with the same row in your joined table, you have a many-to-one join. This type of join will also not result in a fanout, and aggregate functions will at least be accurate on the primary table.
**Example**: Suppose you have a order table and a customer table. Since many order can be made from a single customer, you have a many-to-one relationship.
### One-to-many (frenemy)
If one row of your primary table can match up with multiple rows in your joined table, you have a one-to-many join. This type of join can result in a fanout, and aggregate functions are not necessarily accurate anywhere.
**Example**: Suppose you have a customer table and an order table. Since one customer can have more than one order, this is a one-to-many join.
## A fanout witch hunt
### Understand your join type
The first, and preferred, method to check for a fanout is to understand the type of join that is occurring. One-to-one and many-to-one joins won't ever result in a fanout. However, if you know you are in a one-to-many situation, then there will always be the risk of a fanout. Even if a fanout has not already occurred, it will be a risk in the future if new rows are added.
### Count rows before and after the join
The second method you can use to check for a fanout is to query a `COUNT` before and after the join. The queries would look like this:
```
SELECT COUNT(*)
FROM my_primary_table
SELECT COUNT(*)
FROM my_primary_table
LEFT JOIN my_joined_table
USING (my_join_column)
```
If the count increases between the two queries, we know that a fanout has occurred. Since we're looking for an increase, it's important that the second query use a `LEFT JOIN`. We don't want to artificially decrease the number of rows being reported just because a row in the primary table doesn't have a corresponding row in the joined table.
Unfortunately, this method cannot tell you if there is a risk of a future fanout. To know that, you need to understand the type of join that is occurring. This is lies in how defined is your business models and what is the functional relationships between your tables.
## Whaly to the rescue
\
If you're one of the lucky folks who use Whaly, we got you covered.
Thanks to:
* the type of the relationships (Has Many / Belongs To / One to One) that you defined when creating your models
* to the Primary Keys that can help deduplicate the records in your tables
Whenever Whaly needs to generate a SQL query that will contains a fan-out, our SQL engine generator will avoid it by generating dynamics sub-queries that are using the DISTINCT keywords to avoid getting false results for your aggregates calculations.
This way, any JOINs that are generated through Explorations and Related Data configured inside it will be safe 🤗
## Quick summary
\
To summarize everything we've just covered:
* Aggregate functions like `SUM` and `COUNT` can misbehave if used against joined tables.
* If a join has a one-to-one relationship, aggregate functions will work just fine.
* If a join has a many-to-one relationship, aggregate functions will work on the primary table but might not work on the joined tables.
* If a join has a one-to-many relationship, aggregate functions may not work anywhere.
* If you're using Whaly and joining tables in your Explore, you don't need to think about any of that again 🤗 🐳
# Backup your data using BigQuery
**Objective:** in this guide we will learn how to configure BigQuery in order to create daily data backups. This might prove useful if you want to keep an history of your tools data, like your CRM or finance software.
**Prerequisite**: in oder to follow this guide, you will need to have an admin access to the google.
## Backup a table using BigQuery :
### 1. Enable the BigQuery data transfer API
The first thing we will need to do is enable the BigQuery data transfer API. This allows to schedule queries on a regular basis.
In order to activate the service:
1. Head over to the API page
2. Click on "Enable API"
{% hint style="info" %}
There might be some propagation delay before this service is seen as activated for Google BigQuery
{% endhint %}
### 2. Create a new dataset in BigQuery
Now we will need to create a new dataset in BigQuery:
1. Open the BigQuery Console
2. Create a new dataset in your desired project
1. Give it a name, for example if we want to backup our `jaffle_shop` dataset, we will name it `jaffle_shop_backup`
2. Set the region to the same one as your initial table
3. Click on create
3. Create a table in the new dataset
1. Give it a name, for example if we want to backup our `customer` table, we will name it `customer_history`
2. Click on create
### 3. Write the backup query
Let's write the query that we will use to backup our table:
* In the BigQuery console, open the table you want to backup
* Click on "Query"
* You should now see a Query looking like the following
```sql
SELECT FROM "whaly-temp-migration-guide.jaffle_shop.customer"LIMIT 1000
```
* Edit it in order to remove the limit, select every columns in our table and add a new column named `snapshot_ts` that will be populated with the backup timestamp. The final query should look like
```sql
SELECT *, @run_time as snapshot_ts FROM "whaly-temp-migration-guide.jaffle_shop.customer"
```
### 4. Schedule the backup
Now we will add our query to the scheduler:
* Click on Schedule -> Create a new scheduled query
* Give it a name
* Set the schedule according to your needs
* Set the dataset and table to the ones created during step 2
* Set Append to table under Destination table write preference: this will tell BigQuery to create a full backup of the table each day
* Click on save 💾
### 5. Verify the query execution
Now that our query is scheduled, we should check that it's running correctly:
* Open the schedule query panel from the left menu bar:
* Check that your query is listed in the summary table
* If you see any error, follow google debugging info to fix them
That's it :tada:
# Embedding reports in Salesforce
This guide aims at explaining how to embed a report in salesforce on the home page or on a specific object page.
In order to embed Whaly reports in Salesforce, you need to:
* Be an developer on your Salesforce org
* Have at least a report created in your Whaly instance
## Setting up your Salesforce org
First of all you need to create the necessary component in your salesforce org to embed Whaly. In order to speed up the process we have created a git repository with all the code required to embed Whaly.
{% embed url="" %}
## Adding your app to your home or object page
In order to add your newly created app to your home page or record page, you should navigate to the page you wish to embed your report on and click on settings and edit page as shown below.
Once you are on the edition menu you can drag and drop the WhalyEmbed app and enter your credentials as show below.
## Forwarding context to your report
By default the app that we created on salesforce pass three filters
* userId: Contains the connected user ID from Salesforce
* userEmail: Contains the connected user email from Salesforce
* recordId: When displaying your report on a record page you will get the current recordId otherwise you should expect it to be null
Therefore on Whaly side you can set up filters with a api name to either userId, userEmail, or objectId to take advantage of those prebuilt features.
## To go further
You can read our documentation on how our embedding mechanism works there:
{% embed url="" %}
Learn how to deploy a lightning app
[
]()
# Useful SQL operations
In the section you will find examples of use sql queries that are used in data projects
# Flattening categories
## Input
When dealing with categories it is common to encounter data that may be under such format :
| id | toppings |
| -- | --------------------- |
| 1 | salad;tomatoes;onions |
| 2 | cheese;bacon |
| 3 | null |
## Output
Ideally, the structure we would like to have to enable data analysis on such data format would be the following :
| id | toppings |
| -- | -------- |
| 1 | salad |
| 1 | tomatoes |
| 1 | onions |
| 2 | cheese |
| 2 | bacon |
\
Example query
-------------
Let's see an example in order to achieve this result :
{% tabs %}
{% tab title="BigQuery" %}
```sql
-- this is our fake database
WITH
database AS (
SELECT
1 AS id,
"salad;tomatoes;onions" AS toppings
UNION ALL
SELECT
2 AS id,
"cheese;bacon" AS toppings
UNION ALL
SELECT
3 AS id,
null AS toppings
)
-- this is the output of the model
-- 1. we start by selecting our two columns "id" and "toppings"
-- 2. then we split the toppings using the ; delimiter character
-- this will generatate an array
-- https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split
-- 3. Then we unnest the array, and flatten it using unnest and cross join
-- this will give us the expected output format
-- https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays
SELECT
id,
toppings
FROM
database
CROSS JOIN UNNEST(SPLIT(toppings, ";")) as toppings
```
{% endtab %}
{% tab title="Snowflake" %}
```sql
with
database as (
select
1 as id,
'salad;tomatoes;onions' as toppings
union all
select
2 as id,
'cheese;bacon' as toppings
union all
select
3 as id,
null as toppings
)
select d.id, f.value::string as toppings
from database d,
lateral flatten
(input=>split(d.toppings, ';')) f
```
{% endtab %}
{% endtabs %}