Objective: Deploy customized mapping under the MadML options. This is provided on all mapping configuration types today in the app.

This document details the latest boilerplate example for each mapping configuration on the platform.

Conversion Mapping

-- Create the table that will contain the conversions 
DROP TABLE IF EXISTS tmp_email_model_conversion;
CREATE TEMP TABLE tmp_email_model_conversion
(
email VARCHAR(256),
conversionid VARCHAR(256),
conversion_model VARCHAR(256),
conversion_date TIMESTAMP,
closedate TIMESTAMP,
amount NUMERIC(30,5),
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT GETDATE()
);
; -- Salesforce conversions - Closed Won 
INSERT INTO tmp_email_model_conversion
SELECT
c.email,
o.id AS conversionid,
'Closed Won' AS conversion_model,
MIN(o.created_date) AS conversion_date,
MIN(o.a_closedate::TIMESTAMP) AS closedate,
MAX(COALESCE(o.a_amount::FLOAT,0)) AS amount
FROM
crm_contacts AS c
INNER JOIN
crm_opportunities AS o
ON COALESCE(c.a_convertedaccountid, c.a_accountid) = o.a_accountid
-- ensure the lead was not added to an existing opp or account
AND c.created_date <= o.created_date
AND COALESCE(o.a_amount::FLOAT,0)>=0 -- keep only positive opps
-- business rules on conversion definition
AND LOWER(o.a_type) IN (
'new business', 'MadKuduDummyLastItem'
)
AND o.a_probability::FLOAT = 100
WHERE
c.source_system = 'salesforce'
GROUP BY
c.email,
o.id
;
; -- Salesforce conversions - SQO 
INSERT INTO tmp_email_model_conversion
SELECT
c.email,
o.id AS conversionid,
'SQO' AS conversion_model,
MIN(o.created_date) AS conversion_date,
MIN(o.a_closedate::TIMESTAMP) AS closedate,
MAX(COALESCE(o.a_amount::FLOAT,0)) AS amount
FROM
crm_contacts AS c
INNER JOIN
crm_opportunities AS o
ON COALESCE(c.a_convertedaccountid, c.a_accountid) = o.a_accountid
-- ensure the lead was not added to an existing opp or account
AND c.created_date <= o.created_date
INNER JOIN
crm_accounts AS a
ON a.source_key_value=o.a_accountid
WHERE
c.source_system = 'salesforce'
AND COALESCE(o.a_amount::FLOAT,0)>=0 -- keep only positive opps
-- business rules on conversion definition
AND LOWER(o.a_type) IN (
'new business', 'MadKuduDummyLastItem'
)
-- insert here the SQO filters from the UI (starting from SQO filters, not with opportunity a_type)
AND (COALESCE(CAST(o.a_probability AS FLOAT), 0) >= 20
AND COALESCE(CAST(o.a_amount AS FLOAT), 0) >= 3000)
GROUP BY
c.email,
o.id
;
; -- Salesforce conversions - Open Opp 
INSERT INTO tmp_email_model_conversion
SELECT
c.email,
o.id AS conversionid,
'Open Opp' AS conversion_model,
MIN(o.created_date) AS conversion_date,
MIN(o.a_closedate::TIMESTAMP) AS closedate,
MAX(COALESCE(o.a_amount::FLOAT,0)) AS amount
FROM
crm_contacts AS c
INNER JOIN
crm_opportunities AS o
ON COALESCE(c.a_convertedaccountid, c.a_accountid) = o.a_accountid
-- ensure the lead was not added to an existing opp or account
AND c.created_date <= o.created_date
AND COALESCE(o.a_amount::FLOAT,0)>=0 -- keep only positive opps
-- business rules on conversion definition
AND LOWER(o.a_type) IN (
'new business', 'MadKuduDummyLastItem'
)

WHERE
c.source_system = 'salesforce'
GROUP BY
c.email,
o.id
;
; -- HubSpot conversions - Create table to map all contacts to deals 
DROP TABLE IF EXISTS contacts_to_opportunities;
CREATE TABLE contacts_to_opportunities AS
with NS AS (
select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27 union all
select 28 union all
select 29 union all
select 30 union all
select 31 union all
select 32 union all
select 33 union all
select 34 union all
select 35
)
SELECT
REPLACE(REPLACE(REPLACE(TRIM(SPLIT_PART(o.a_associatedvids, ',', NS.n)),'"',''), '[',''), ']','') AS contact_vid,
o.a_associatedvids AS original_contactvids,
id As opp_id
FROM
NS
INNER JOIN
crm_opportunities o ON
NS.n <= REGEXP_COUNT(o.a_associatedvids, ',') + 1
WHERE
source_system = 'hubspot'
; -- HubSpot conversions - Closed Won 
INSERT INTO tmp_email_model_conversion
SELECT
c.email,
o.id AS conversionid,
'Closed Won' AS conversion_model,
MIN(o.created_date) AS conversion_date,
MIN((timestamp 'epoch' + CAST(c.a_closedate AS BIGINT)/1000 * interval '1 second')::DATE) AS closedate,
MAX(COALESCE(o.a_hs_closed_amount::FLOAT,0)) AS amount
FROM
contacts AS c
INNER JOIN
contacts_to_opportunities AS cto
ON c.source_key_value=cto.contact_vid
INNER JOIN crm_opportunities AS o
ON cto.opp_id = o.id
AND c.created_date <= o.created_date
AND o.source_system = 'hubspot'
WHERE
c.source_system = 'hubspot'
AND COALESCE(o.a_hs_closed_amount::FLOAT,0)>=0 -- keep only positive opps
AND LOWER(o.a_dealtype) IN ('newbusiness', 'MadKuduDummyLastItem')
AND o.a_hs_deal_stage_probability::FLOAT = 1
GROUP BY
1,2
;
; -- HubSpot conversions - SQO 
INSERT INTO tmp_email_model_conversion
SELECT
c.email,
o.id AS conversionid,
'SQO' AS conversion_model,
MIN(o.created_date) AS conversion_date,
MIN((timestamp 'epoch' + CAST(c.a_closedate AS BIGINT)/1000 * interval '1 second')::DATE) AS closedate,
MAX(COALESCE(o.a_hs_closed_amount::FLOAT,0)) AS amount
FROM
contacts AS c
INNER JOIN
contacts_to_opportunities AS cto
ON c.source_key_value=cto.contact_vid
INNER JOIN crm_opportunities AS o
ON cto.opp_id = o.id
AND c.created_date <= o.created_date
AND o.source_system = 'hubspot'
AND COALESCE(o.a_hs_closed_amount::FLOAT,0)>=0 -- keep only positive opps
-- Left join the Companies as not all opps are attached to a company
LEFT JOIN crm_accounts AS a
ON a.source_key_value = c.a_associatedcompanyid
AND a.source_system = 'hubspot'
WHERE
c.source_system = 'hubspot'
AND LOWER(o.a_dealtype) IN ('newbusiness', 'MadKuduDummyLastItem')
AND (COALESCE(CAST(o.a_hs_deal_stage_probability AS FLOAT), 0) >= 0.2
AND COALESCE(CAST(o.a_hs_closed_amount AS FLOAT), 0) >= 3000)
GROUP BY
1,2
;
; -- Open opp 
INSERT INTO tmp_email_model_conversion
SELECT
c.email,
o.id AS conversionid,
'Open Opp' AS conversion_model,
MIN(o.created_date) AS conversion_date,
MIN((timestamp 'epoch' + CAST(c.a_closedate AS BIGINT)/1000 * interval '1 second')::DATE) AS closedate,
MAX(COALESCE(o.a_hs_closed_amount::FLOAT,0)) AS amount
FROM
contacts AS c
INNER JOIN
contacts_to_opportunities AS cto
ON c.source_key_value=cto.contact_vid
INNER JOIN crm_opportunities AS o
ON cto.opp_id = o.id
AND c.created_date <= o.created_date
AND o.source_system = 'hubspot'
WHERE
c.source_system = 'hubspot'
AND COALESCE(o.a_hs_closed_amount::FLOAT,0)>=0 -- keep only positive opps
AND LOWER(o.a_dealtype) IN ('newbusiness', 'MadKuduDummyLastItem')

GROUP BY
1,2
;
; -- Stripe conversions - Closed Won 
; -- Stripe conversions - SQO 
; -- Stripe conversions - Open Opp 
; -- Analytics conversions - Closed won 
; -- Analytics conversions - SQO 
; -- Analytics conversions - Open Opp 
; -- Create the table that will contain the conversions 
DROP TABLE IF EXISTS email_model_conversion_stage;
CREATE TABLE email_model_conversion_stage
(
email VARCHAR(256),
conversionid VARCHAR(256),
conversion_model VARCHAR(256),
conversion_date TIMESTAMP,
closedate TIMESTAMP,
amount NUMERIC(30,5),
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT GETDATE()
);
; -- make sure we only have one conversion per email/model (conversionid, amount and dates might be from separate conversions) 
INSERT INTO email_model_conversion_stage
SELECT
email,
MIN(conversionid) AS conversionid,
conversion_model,
MIN(conversion_date) AS conversion_date,
MIN(closedate) AS closedate,
MAX(amount) AS amount
FROM
tmp_email_model_conversion
GROUP BY
email,
conversion_model
; -- swap stage and prod 
DROP TABLE IF EXISTS email_model_conversion;
ALTER TABLE email_model_conversion_stage RENAME TO email_model_conversion;
;

Audience Mapping

-- Create the table that will contain all the conditions to use 
DROP TABLE IF EXISTS email_traits;
CREATE TABLE email_traits AS
SELECT MIN(sl.a_field) AS Salesforce_Lead_a_field,
c.email
FROM contacts_computations AS c -- only join with the source systems and objects that are specified in the UI
-- Salesforce Lead
LEFT JOIN crm_contacts AS sl ON c.email=sl.email
AND sl.source_system='salesforce'
AND sl.source_system_object='Lead'
-- Salesforce Contact
LEFT JOIN crm_contacts AS sc
ON sc.email = c.email
AND sc.source_system='salesforce'
AND sc.source_system_object='Contact'
-- Salesforce Account
-- through the domain
-- or through the accountid
-- Salesforce Opportunity
-- if link on account id
-- if link on opportunity id
GROUP BY c.email
; 

-- Insert into the audience table with the conditions 
INSERT INTO email_audience_stage
SELECT
email,
'inbound' AS audience_name
FROM email_traits AS et
WHERE
et.Salesforce_Lead_a_field IN('value1', 'value2')
;

Attribute Mapping

-- Create the Email to Salesforce acccount table to get the domain for biz email , and accountid for personal email 
-- Salesforce email to account
DROP TABLE IF EXISTS email_to_salesforce_account;
CREATE TEMP TABLE email_to_salesforce_account AS
SELECT scon.email,
scon.id as contactid,
COALESCE(sabiz.id, sapers.id) AS accountid,
REPLACE(REPLACE(REPLACE(LOWER(COALESCE (sabiz.a_website, sabiz.domain, sapers.domain)), 'https://', ''),'http://', ''), 'www.', '') as domain
FROM crm_contacts AS scon
LEFT JOIN
contacts_computations AS cc
ON scon.id = cc.contactid
-- Salesforce business account
LEFT JOIN
crm_accounts AS sabiz
ON cc.domain = REPLACE(REPLACE(REPLACE(LOWER(COALESCE (sabiz.a_website, sabiz.domain)), 'https://', ''),'http://', ''), 'www.', '')
AND cc.is_personal = 0
AND sabiz.source_system = 'salesforce'
AND sabiz.source_system_object = 'Account'
-- Salesforce personal account
LEFT JOIN
crm_accounts AS sapers
ON sapers.source_key_value = COALESCE(scon.a_convertedaccountid, scon.a_accountid)
AND cc.is_personal = 1
AND sapers.source_system = 'salesforce'
AND sapers.source_system_object = 'Account'
; -- Create the Email to HubSpot acccount table to get the domain for biz email , and accountid for personal email 
-- HubSpot email to account
DROP TABLE IF EXISTS email_to_hubspot_account;
CREATE TEMP TABLE email_to_hubspot_account AS
SELECT hcon.email,
hcon.id as contactid,
COALESCE(habiz.id, hapers.id) AS accountid,
REPLACE(REPLACE(REPLACE(LOWER(COALESCE (habiz.a_website, habiz.domain, hapers.domain)), 'https://', ''),'http://', ''), 'www.', '') as domain
FROM contacts AS hcon
LEFT JOIN
contacts_computations AS cc
ON hcon.id = cc.contactid
-- HubSpot business account
LEFT JOIN
accounts AS habiz
ON cc.domain = REPLACE(REPLACE(REPLACE(LOWER(COALESCE (habiz.a_website, habiz.domain)), 'https://', ''),'http://', ''), 'www.', '')
AND cc.is_personal = 0
AND habiz.source_system = 'hubspot'
AND habiz.source_system_object = 'company'
-- HubSpot personal account
LEFT JOIN
accounts AS hapers
ON hapers.source_key_value = hcon.a_associatedcompanyid
AND cc.is_personal = 1
AND hapers.source_system = 'hubspot'
AND hapers.source_system_object = 'company'
; -- Create the table of Attribute mapping 
DROP TABLE IF EXISTS email_computations_custom_stage;
CREATE TABLE email_computations_custom_stage AS
SELECT
MAX(sa.a_arr__c) AS salesforce_arr,
MAX(sa.a_numberofemployees) AS salesforce_employees_custom,
MAX(sa.a_billingcity) AS salesforce_billing_city_custom,
MAX(sa.name) AS salesforce_name_custom,
MAX(sa.a_type) AS salesforce_account_type,
MAX(sa.a_is_target_account__c) AS salesforce_account_is_target,
MAX(sa.a_plg_target_account__c) AS salesforce_account_is_plg_target,
MAX(sa.a_account_tier__c) AS salesforce_account_tier,
MAX(sa.a_mk_customer_fit_segment__c) AS salesforce_account_customer_fit_segment,

MAX(ha.a_country) AS hubspot_country_custom,

MAX(split_part(c.email, '@', 2)) as domain,
c.email
FROM
contacts_computations AS c
-- Salesforce Lead
LEFT JOIN
crm_contacts AS sl
ON sl.email = c.email
AND sl.source_system='salesforce'
AND sl.source_system_object='Lead'
-- Salesforce Contact
LEFT JOIN
crm_contacts AS sc
ON sc.email = c.email
AND sc.source_system='salesforce'
AND sc.source_system_object IN ('Contact', 'Account') -- Object Account is to enrich the Fake contact of the account fit
-- Salesforce Account
-- through the domain
LEFT JOIN
email_to_salesforce_account AS esc
ON esc.contactid = c.contactid
LEFT JOIN
crm_accounts AS sa
ON sa.id = esc.accountid
AND sa.source_system='salesforce'
AND sa.source_system_object='Account'
-- or through the accountid
-- Salesforce Opportunity
-- if link on account id
LEFT JOIN
crm_opportunities AS so
ON so.a_accountid=sa.source_key_value
AND so.source_system='salesforce'
AND so.source_system_object='Opportunity'
-- if link on opportunity id
-- Hubspot Contact
LEFT JOIN contacts AS hc
ON hc.email=c.email
AND hc.source_system='hubspot'
AND hc.source_system_object='contact'
-- Hubspot Deal
LEFT JOIN crm_opportunities AS ho
ON ho.a_associatedvid=hc.source_key_value
AND ho.source_system='hubspot'
AND ho.source_system_object='deal'
-- Hubspot Company
LEFT JOIN
email_to_hubspot_account AS ehc
ON ehc.contactid = c.contactid
LEFT JOIN
accounts AS ha
ON ha.id = ehc.accountid
AND ha.source_system='hubspot'
AND ha.source_system_object='company'
GROUP by c.email
; -- Creating a temp table to gather & clean all the domains that will be needed 
DROP TABLE IF EXISTS temp_accounts_all;
CREATE TEMP TABLE temp_accounts_all AS
SELECT
REPLACE(REPLACE(REPLACE(LOWER(COALESCE (ha.a_website, ha.domain)), 'https://', ''),'http://', ''), 'www.', '') as domain
FROM
accounts AS ha
GROUP BY
REPLACE(REPLACE(REPLACE(LOWER(COALESCE (ha.a_website, ha.domain)), 'https://', ''),'http://', ''), 'www.', '')
-- the union selects distinct values per default
UNION
SELECT
REPLACE(REPLACE(REPLACE(LOWER(COALESCE(sa.a_website, sa.domain)), 'https://', ''),'http://', ''), 'www.', '') AS domain
FROM
crm_accounts AS sa
WHERE
sa.source_system = 'salesforce'
AND sa.source_system_object = 'Account'
-- making sure the domain looks like a domain and not a name
AND REPLACE(COALESCE(sa.a_website, sa.domain), 'www.', '') LIKE '%.%'
GROUP BY
REPLACE(REPLACE(REPLACE(LOWER(COALESCE(sa.a_website, sa.domain)), 'https://', ''),'http://', ''), 'www.', '')
; -- Create the table of Attribute mapping only with attributes needed for real time scoring 
DROP TABLE IF EXISTS account_computations_custom_stage;
CREATE TABLE account_computations_custom_stage AS
SELECT MAX(sa.a_numberofemployees) AS salesforce_employees_custom,
MAX(sa.a_billingcity) AS salesforce_billing_city_custom,
MAX(sa.name) AS salesforce_name_custom,
MAX(sa.a_type) AS salesforce_account_type,
MAX(sa.a_is_target_account__c) AS salesforce_account_is_target,
MAX(sa.a_plg_target_account__c) AS salesforce_account_is_plg_target,
MAX(sa.a_mk_customer_fit_segment__c) AS salesforce_account_customer_fit_segment,

MAX(ha.a_country) AS hubspot_country_custom,

taa.domain
-- with this code, we get the information from accounts that have a domain -- known limitation: with this code, we will not get the information from accounts that do not have a domain & have a contact in crm_contacts with a domain FROM temp_accounts_all AS taa
LEFT JOIN crm_accounts AS sa
ON taa.domain = REPLACE(REPLACE(REPLACE(LOWER(COALESCE(sa.a_website, sa.domain)), 'https://', ''),'http://', ''), 'www.', '')
AND sa.source_system = 'salesforce'
AND sa.source_system_object = 'Account'
-- making sure the domain looks like a domain and not a name
AND REPLACE(COALESCE(sa.a_website, sa.domain), 'www.', '') LIKE '%.%'
LEFT JOIN accounts AS ha
ON taa.domain = REPLACE(REPLACE(REPLACE(LOWER(COALESCE (ha.a_website, ha.domain)), 'https://', ''),'http://', ''), 'www.', '')
AND ha.source_system = 'hubspot'
AND ha.source_system_object = 'company'
-- making sure the domain looks like a domain and not a name
AND REPLACE(REPLACE(REPLACE(LOWER(COALESCE (ha.a_website, ha.domain)), 'https://', ''),'http://', ''), 'www.', '') LIKE '%.%'
-- we want to select the accounts that do not have a personal domain -- we exclude the domains from contacts_computations having personal domain -- we cannot only select the ones with personal_domain = 0 since we also want to keep the domains from crm_accounts that are not in contacts_computations > in case there is no contact tied to the account LEFT JOIN contacts_computations cc
ON cc.domain = taa.domain
AND COALESCE(cc.is_personal,'0') = '1'
WHERE cc.contactid IS NULL
GROUP BY taa.domain
; -- swap stage table and live table 
DROP TABLE IF EXISTS account_computations_custom; ALTER TABLE account_computations_custom_stage RENAME TO account_computations_custom;
; -- For each salesforce account get its domain 
DROP TABLE IF EXISTS salesforce_account_domain;
CREATE TEMP TABLE salesforce_account_domain AS
SELECT a.source_system,
a.id,
a.source_key_value,
SPLIT_PART(REPLACE(REPLACE(REPLACE(LOWER(COALESCE(a_website, a.domain)), 'https://', ''), 'http://', ''), 'www.', ''), '/', 1) as account_domain
FROM crm_accounts a WHERE a.source_system = 'salesforce'
AND a.source_system_object = 'Account'
AND COALESCE(a_website,a.domain) LIKE '%.%' -- looks like a website ; 
; -- For each account get the domain and number of emails 
DROP TABLE IF EXISTS account_domain;
CREATE TEMP TABLE account_domain AS
WITH cte AS (
SELECT
a.source_system,
a.id,
a.source_key_value,
a.account_domain,
count(distinct c.email) as cnt_emails
FROM salesforce_account_domain a
LEFT JOIN crm_contacts c
ON COALESCE(c.a_convertedaccountid, c.a_accountid) = a.source_key_value
AND c.source_system_object IN ('Lead', 'Contact')
AND c.source_system = 'salesforce'
GROUP BY 1,2,3,4
)
SELECT
source_system,
id,
source_key_value,
account_domain,
cnt_emails,
row_number() over (partition by account_domain order by cnt_emails desc) as rk_most_populated
FROM cte
;
; -- For each email, get the accountid and account domain if exist, start by salesforce contacts 
DROP TABLE IF EXISTS all_email_to_domain;
CREATE TEMP TABLE all_email_to_domain AS
-- When salesforce is the CRM
-- Salesforce contacts
SELECT
c.email as email,
SPLIT_PART(c.email, '@', 2) as email_domain,
a.id as accountid,
CASE WHEN a.source_key_value IS NOT NULL THEN account_domain ELSE SPLIT_PART(c.email, '@', 2) END as account_domain,
ROW_NUMBER() over (PARTITION BY c.email ORDER BY rk_most_populated) as rk -- to remove duplicates
FROM crm_contacts c
LEFT JOIN account_domain a
ON COALESCE(c.a_convertedaccountid, c.a_accountid) = a.source_key_value
AND a.source_system = 'salesforce'
WHERE c.source_system = 'salesforce'
AND c.source_system_object IN ('Lead', 'Contact');
INSERT INTO all_email_to_domain
-- Other contacts
SELECT
c.email as email,
SPLIT_PART(c.email, '@', 2) as email_domain,
NULL as accountid,
SPLIT_PART(c.email, '@', 2) as account_domain,
ROW_NUMBER() over (PARTITION BY c.email) as rk -- to remove duplicates
FROM contacts c
LEFT JOIN crm_contacts cc
ON cc.email=c.email
AND cc.source_system='salesforce'
AND cc.source_system_object IN ('Lead', 'Contact')
WHERE c.source_system != 'madkudu'
AND cc.email IS NULL -- contacts not already added from Salesforce
;
; -- Only keep business domains 
DROP TABLE IF EXISTS email_to_domain;
CREATE TEMP TABLE email_to_domain AS
WITH cte_biz_domain AS (
SELECT DISTINCT account_domain as domain
FROM all_email_to_domain
EXCEPT
SELECT DISTINCT domain
FROM contacts_computations
WHERE is_personal = 1
AND is_spam = 1
)
SELECT DISTINCT etd.*
FROM all_email_to_domain etd
INNER JOIN cte_biz_domain cc
ON etd.account_domain=cc.domain
WHERE
rk<=1
;
; -- Create the mk_accountid_domain which is either from the explicit relationship or from the domain 
DROP TABLE IF EXISTS email_to_account_stage;
CREATE TABLE email_to_account_stage AS
-- to attach orphan leads to an account with the same domain and there are multiple accounts possible, attach it to the most populat one SELECT etd.email,
etd.accountid,
etd.account_domain,
CASE
WHEN etd.accountid IS NOT NULL THEN etd.accountid || '_' || etd.account_domain -- when the email has an explicit link, the mk_accountid_domain is the id and domain of the account its attached to
ELSE 'madkudu_map_domain_' || etd.email_domain -- if orphan cannot be attach, create an account
END as mk_accountid_domain,
mpa.rk_most_populated
FROM email_to_domain etd LEFT JOIN account_domain mpa ON mpa.account_domain=etd.account_domain AND mpa.rk_most_populated=1 ;
; -- swap stage table and live table 
DROP TABLE IF EXISTS email_to_account;
ALTER TABLE email_to_account_stage RENAME TO email_to_account;
;

Event Mapping

Example:

template_version: 2
description: Event mapping
global_variables:
has_hubspot: false
has_segment: true
has_amplitude: false
has_kissmetrics: false
has_mixpanel: false
has_marketo: false
has_salesforce_campaigns: false
has_salesforce_tasks: true
segment_event_exclusion_list: []
amplitude_event_exclusion_list: []
kissmetrics_event_exclusion_list: []
mixpanel_event_exclusion_list: []
hubspot_has_formsubmissions: false
hubspot_has_click_statusChange: false
##### DO NOT MODIFY PAST THIS POINT #############

sql_chain:
# Hubspot events
- description: insert in Events stage the HUBSPOT events
sql: |
{{#has_hubspot}}
{{#hubspot_has_formsubmissions}}
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE -- Event name for signals
false
-- Catch-All
ELSE 'Submitted other HubSpot form'
END AS event_text,
e.event_timestamp,
CASE -- Event name
false
-- Catch-All
ELSE 'Submitted other HubSpot form'
END AS meta_event,
NULL AS contact_id,
e.contact_id AS contactid,
CASE -- non user activity check box
false
-- Catch-All
ELSE TRUE
END AS is_positive_user_activity,
CASE -- activity type
false
-- Catch-All
ELSE 'Web Activity'
END AS activity_type
FROM
events_raw as e
WHERE
e.source_system = 'hubspot'
AND e.event NOT IN ('statuschange', 'click')
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
;
{{/hubspot_has_formsubmissions}}
{{#hubspot_has_click_statusChange}}
-- insert the meta-events from the events with a "email like contact id (i.e the Hubspot click and status change) (standard for all hubspot, you should'nt have to change anything)
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE -- Event name for signals
false -- doesn't exist on the UI for now
-- Catch-All
ELSE 'Other HubSpot event'
END AS event_text,
e.event_timestamp,
CASE -- Event name
false
-- Catch-All
ELSE 'Other HubSpot event'
END AS meta_event,
NULL AS contact_id,
c.id AS contactid,
CASE -- non user activity check box
false
-- Catch-All
ELSE TRUE
END AS is_positive_user_activity,
CASE -- activity type
false
-- Catch-All
ELSE 'Email Activity'
END AS activity_type
FROM
events_raw as e
INNER JOIN
contacts AS c
ON e.contact_source_key_value = c.email
AND c.source_system='hubspot'
WHERE
c.source_system = 'hubspot'
AND e.event IN ('statuschange', 'click')
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
;
{{/hubspot_has_click_statusChange}}
{{/has_hubspot}}
# Segment events
- description: insert in Events stage the SEGMENT events
sql: |
{{#has_segment}}
-- get the list of the least frequent events in the last 9 months
DROP TABLE IF EXISTS least_frequent_events;
CREATE TEMP TABLE least_frequent_events AS
WITH cte AS (
SELECT
event,
COUNT(DISTINCT id) AS count_event
FROM
events_raw
WHERE
event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND source_system = 'segment'
GROUP BY 1
)
SELECT *
FROM cte
WHERE
count_event < 100 -- the limited number of events can be adjusted here
;
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE -- MK Event Name for Signals
WHEN event = 'viewed_unknown_page' THEN 'Viewed Unknown Page'
WHEN event = 'viewed_home_page' THEN 'Viewed Home Page'
WHEN event = 'enrichment_provider' THEN 'Enrichment Provider'
WHEN event = 'viewed_predictions_page' THEN 'Viewed Predictions Page'
WHEN event = 'viewed_integrations_page' THEN 'Viewed Integrations Page'
WHEN event = 'viewed_pricing_page' THEN 'Viewed Pricing Page'
WHEN event = 'viewed_post' THEN 'Viewed Post'
WHEN event = 'viewed_mapping_overview_page' THEN 'Viewed Mapping Overview Page'
WHEN event = 'viewed_processes_page' THEN 'Viewed Processes Page'
WHEN event = 'viewed_performances_page' THEN 'Viewed Performances Page'
WHEN event = 'viewed_login_page' THEN 'Viewed Login Page'
WHEN event = 'viewed_profile_page' THEN 'Viewed Profile Page'
WHEN event = 'viewed_salesforce_page' THEN 'Viewed Salesforce Page'
WHEN event = 'viewed_contact_page' THEN 'Viewed Contact Page'
WHEN event = 'viewed_homepage_page' THEN 'Viewed Homepage Page'
WHEN event = 'viewed_conversion_mapping_page' THEN 'Viewed Conversion Mapping Page'
WHEN event = 'viewed_careers_page' THEN 'Viewed Careers Page'
WHEN event = 'viewed_data_overview_page' THEN 'Viewed Data Overview Page'
WHEN event = 'viewed_platform_page' THEN 'Viewed Platform Page'
WHEN event = 'viewed_admin_page' THEN 'Viewed Admin Page'
WHEN event = 'viewed_platform_how_it_works_page' THEN 'Viewed Platform How It Works Page'
WHEN event = 'viewed_profiles_page' THEN 'Viewed Profiles Page'
WHEN event = 'viewed_diagnostics_page' THEN 'Viewed Diagnostics Page'
WHEN event = 'viewed_team_page' THEN 'Viewed Team Page'
WHEN event = 'viewed_onboarding_hubspot_page' THEN 'Viewed Onboarding Hubspot Page'
WHEN event = 'viewed_lead_scoring_page' THEN 'Viewed Lead Scoring Page'
WHEN event = 'viewed_insights_page' THEN 'Viewed Insights Page'
WHEN event = 'logged_in' THEN 'Logged In'
WHEN event = 'clicked_cta' THEN 'Clicked Cta'
WHEN event = 'viewed_hubspot_page' THEN 'Viewed Hubspot Page'
WHEN event = 'qualified_company_visited_the_website' THEN 'Qualified Company Visited The Website'
WHEN event = 'viewed_yaml_to_sql_page' THEN 'Viewed Yaml To Sql Page'
WHEN event = 'viewed_madkudu_facebook_page' THEN 'Viewed Madkudu Facebook Page'
WHEN event = 'viewed_fastlane_landing_page_page' THEN 'Viewed Fastlane Landing Page Page'
WHEN event = 'viewed_evr_page' THEN 'Viewed Evr Page'
WHEN event = 'viewed_404_page' THEN 'Viewed 404 Page'
WHEN event = 'viewed_blog_page' THEN 'Viewed Blog Page'
WHEN event = 'viewed_event_mapping_page' THEN 'Viewed Event Mapping Page'
WHEN event = 'viewed_attribute_mapping_page' THEN 'Viewed Attribute Mapping Page'
WHEN event = 'viewed_salesforce_data_discovery_page' THEN 'Viewed Salesforce Data Discovery Page'
WHEN event = 'viewed_customers_page' THEN 'Viewed Customers Page'
WHEN event = 'viewed_hubspot_velocity_page' THEN 'Viewed Hubspot Velocity Page'
WHEN event = 'viewed_settings_page' THEN 'Viewed Settings Page'
WHEN event = 'fast_lane' THEN 'Fast Lane'
WHEN event = 'viewed_rocketreach_alternatives_and_competitors_or_g2_page' THEN 'Viewed Rocketreach Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_best_lead_intelligence_software_in_2020_or_g2_page' THEN 'Viewed Best Lead Intelligence Software In 2020 Or G2 Page'
WHEN event = 'requested_demo' THEN 'Requested Demo'
WHEN event = 'viewed_hubspot_data_discovery_page' THEN 'Viewed Hubspot Data Discovery Page'
WHEN event = 'viewed_evr_insights_page' THEN 'Viewed Evr Insights Page'
WHEN event = 'viewed_users_page' THEN 'Viewed Users Page'
WHEN event = 'viewed_intercom_page' THEN 'Viewed Intercom Page'
WHEN event = 'viewed_flags_page' THEN 'Viewed Flags Page'
WHEN event = 'viewed_behavioral_activity_configuration_page' THEN 'Viewed Behavioral Activity Configuration Page'
WHEN event = 'email_captured' THEN 'Email Captured'
WHEN event = 'viewed_lusha_alternatives_and_competitors_or_g2_page' THEN 'Viewed Lusha Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_privacy_page' THEN 'Viewed Privacy Page'
WHEN event = 'viewed_audience_mapping_page' THEN 'Viewed Audience Mapping Page'
WHEN event = 'viewed_saastr_madkudu_breakfast_page' THEN 'Viewed Saastr Madkudu Breakfast Page'
WHEN event = 'viewed_poc_page' THEN 'Viewed Poc Page'
WHEN event = 'viewed_account_page' THEN 'Viewed Account Page'
WHEN event = 'viewed_push_page' THEN 'Viewed Push Page'
WHEN event = 'viewed_simulator_page' THEN 'Viewed Simulator Page'
WHEN event = 'viewed_dandb_hoovers_alternatives_and_competitors_or_g2_page' THEN 'Viewed Dandb Hoovers Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_edit_audience_page' THEN 'Viewed Edit Audience Page'
WHEN event = 'viewed_madkudu_sheets_page' THEN 'Viewed Madkudu Sheets Page'
WHEN event = 'clicked_changelog' THEN 'Clicked Changelog'
WHEN event = 'viewed_model_page' THEN 'Viewed Model Page'
WHEN event = 'viewed_segment_page' THEN 'Viewed Segment Page'
WHEN event = 'viewed_clearbit_alternatives_and_competitors_or_g2_page' THEN 'Viewed Clearbit Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_api_usage_page' THEN 'Viewed Api Usage Page'
WHEN event = 'viewed_use_case_segment_page' THEN 'Viewed Use Case Segment Page'
WHEN event = 'viewed_data_discovery_page' THEN 'Viewed Data Discovery Page'
WHEN event = 'viewed_madkudu_for_zapier_page' THEN 'Viewed Madkudu For Zapier Page'
WHEN event = 'started_oauth' THEN 'Started Oauth'
WHEN event = 'viewed_signup_page' THEN 'Viewed Signup Page'
WHEN event = 'activated_integration' THEN 'Activated Integration'
WHEN event = 'viewed_madkudu_academy_page' THEN 'Viewed Madkudu Academy Page'
WHEN event = 'viewed_marketo_page' THEN 'Viewed Marketo Page'
WHEN event = 'viewed_api_page' THEN 'Viewed Api Page'
WHEN event = 'fast_lane_modal_clicked' THEN 'Fast Lane Modal Clicked'
WHEN event = 'fast_lane_modal_dismissed' THEN 'Fast Lane Modal Dismissed'
WHEN event = 'viewed_madfiles_episode_2_page' THEN 'Viewed Madfiles Episode 2 Page'
WHEN event = 'viewed_segment_mapping_page' THEN 'Viewed Segment Mapping Page'
WHEN event = 'viewed_authentication_page' THEN 'Viewed Authentication Page'
WHEN event = 'viewed_use_case_drift_page' THEN 'Viewed Use Case Drift Page'
WHEN event = 'viewed_madfiles_episode_1_page' THEN 'Viewed Madfiles Episode 1 Page'
WHEN event = 'viewed_apolloio_alternatives_and_competitors_or_g2_page' THEN 'Viewed Apolloio Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_hubspot_mapping_page' THEN 'Viewed Hubspot Mapping Page'
WHEN event = 'viewed_amplitude_page' THEN 'Viewed Amplitude Page'
WHEN event = 'viewed_madfiles_episode_3_page' THEN 'Viewed Madfiles Episode 3 Page'
WHEN event = 'viewed_blog_demystifying_data_science_for_marketers_page' THEN 'Viewed Blog Demystifying Data Science For Marketers Page'
WHEN event = 'viewed_all_posts_page' THEN 'Viewed All Posts Page'
WHEN event = 'completed_oauth' THEN 'Completed Oauth'
WHEN event = 'viewed_billing_page' THEN 'Viewed Billing Page'
WHEN event = 'viewed_uplead_alternatives_and_competitors_or_g2_page' THEN 'Viewed Uplead Alternatives And Competitors Or G2 Page'
WHEN event = 'signed_up' THEN 'Signed Up'
WHEN event = 'viewed_marketo_mapping_page' THEN 'Viewed Marketo Mapping Page'
WHEN event = 'viewed_use_case_outreach_page' THEN 'Viewed Use Case Outreach Page'
WHEN event = 'viewed_use_case_invision_page' THEN 'Viewed Use Case Invision Page'
WHEN event = 'viewed_leadiq_alternatives_and_competitors_or_g2_page' THEN 'Viewed Leadiq Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_reset_password_page' THEN 'Viewed Reset Password Page'
ELSE event_text -- Keep the granularity of the events for the signals
END AS event_text,
e.event_timestamp,
CASE -- MK Event name
WHEN event = 'viewed_unknown_page' THEN 'Viewed Unknown Page'
WHEN event = 'viewed_home_page' THEN 'Viewed Home Page'
WHEN event = 'enrichment_provider' THEN 'Enrichment Provider'
WHEN event = 'viewed_predictions_page' THEN 'Viewed Predictions Page'
WHEN event = 'viewed_integrations_page' THEN 'Viewed Integrations Page'
WHEN event = 'viewed_pricing_page' THEN 'Viewed Pricing Page'
WHEN event = 'viewed_post' THEN 'Viewed Post'
WHEN event = 'viewed_mapping_overview_page' THEN 'Viewed Mapping Overview Page'
WHEN event = 'viewed_processes_page' THEN 'Viewed Processes Page'
WHEN event = 'viewed_performances_page' THEN 'Viewed Performances Page'
WHEN event = 'viewed_login_page' THEN 'Viewed Login Page'
WHEN event = 'viewed_profile_page' THEN 'Viewed Profile Page'
WHEN event = 'viewed_salesforce_page' THEN 'Viewed Salesforce Page'
WHEN event = 'viewed_contact_page' THEN 'Viewed Contact Page'
WHEN event = 'viewed_homepage_page' THEN 'Viewed Homepage Page'
WHEN event = 'viewed_conversion_mapping_page' THEN 'Viewed Conversion Mapping Page'
WHEN event = 'viewed_careers_page' THEN 'Viewed Careers Page'
WHEN event = 'viewed_data_overview_page' THEN 'Viewed Data Overview Page'
WHEN event = 'viewed_platform_page' THEN 'Viewed Platform Page'
WHEN event = 'viewed_admin_page' THEN 'Viewed Admin Page'
WHEN event = 'viewed_platform_how_it_works_page' THEN 'Viewed Platform How It Works Page'
WHEN event = 'viewed_profiles_page' THEN 'Viewed Profiles Page'
WHEN event = 'viewed_diagnostics_page' THEN 'Viewed Diagnostics Page'
WHEN event = 'viewed_team_page' THEN 'Viewed Team Page'
WHEN event = 'viewed_onboarding_hubspot_page' THEN 'Viewed Onboarding Hubspot Page'
WHEN event = 'viewed_lead_scoring_page' THEN 'Viewed Lead Scoring Page'
WHEN event = 'viewed_insights_page' THEN 'Viewed Insights Page'
WHEN event = 'logged_in' THEN 'Logged In'
WHEN event = 'clicked_cta' THEN 'Clicked Cta'
WHEN event = 'viewed_hubspot_page' THEN 'Viewed Hubspot Page'
WHEN event = 'qualified_company_visited_the_website' THEN 'Qualified Company Visited The Website'
WHEN event = 'viewed_yaml_to_sql_page' THEN 'Viewed Yaml To Sql Page'
WHEN event = 'viewed_madkudu_facebook_page' THEN 'Viewed Madkudu Facebook Page'
WHEN event = 'viewed_fastlane_landing_page_page' THEN 'Viewed Fastlane Landing Page Page'
WHEN event = 'viewed_evr_page' THEN 'Viewed Evr Page'
WHEN event = 'viewed_404_page' THEN 'Viewed 404 Page'
WHEN event = 'viewed_blog_page' THEN 'Viewed Blog Page'
WHEN event = 'viewed_event_mapping_page' THEN 'Viewed Event Mapping Page'
WHEN event = 'viewed_attribute_mapping_page' THEN 'Viewed Attribute Mapping Page'
WHEN event = 'viewed_salesforce_data_discovery_page' THEN 'Viewed Salesforce Data Discovery Page'
WHEN event = 'viewed_customers_page' THEN 'Viewed Customers Page'
WHEN event = 'viewed_hubspot_velocity_page' THEN 'Viewed Hubspot Velocity Page'
WHEN event = 'viewed_settings_page' THEN 'Viewed Settings Page'
WHEN event = 'fast_lane' THEN 'Fast Lane'
WHEN event = 'viewed_rocketreach_alternatives_and_competitors_or_g2_page' THEN 'Viewed Rocketreach Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_best_lead_intelligence_software_in_2020_or_g2_page' THEN 'Viewed Best Lead Intelligence Software In 2020 Or G2 Page'
WHEN event = 'requested_demo' THEN 'Requested Demo'
WHEN event = 'viewed_hubspot_data_discovery_page' THEN 'Viewed Hubspot Data Discovery Page'
WHEN event = 'viewed_evr_insights_page' THEN 'Viewed Evr Insights Page'
WHEN event = 'viewed_users_page' THEN 'Viewed Users Page'
WHEN event = 'viewed_intercom_page' THEN 'Viewed Intercom Page'
WHEN event = 'viewed_flags_page' THEN 'Viewed Flags Page'
WHEN event = 'viewed_behavioral_activity_configuration_page' THEN 'Viewed Behavioral Activity Configuration Page'
WHEN event = 'email_captured' THEN 'Email Captured'
WHEN event = 'viewed_lusha_alternatives_and_competitors_or_g2_page' THEN 'Viewed Lusha Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_privacy_page' THEN 'Viewed Privacy Page'
WHEN event = 'viewed_audience_mapping_page' THEN 'Viewed Audience Mapping Page'
WHEN event = 'viewed_saastr_madkudu_breakfast_page' THEN 'Viewed Saastr Madkudu Breakfast Page'
WHEN event = 'viewed_poc_page' THEN 'Viewed Poc Page'
WHEN event = 'viewed_account_page' THEN 'Viewed Account Page'
WHEN event = 'viewed_push_page' THEN 'Viewed Push Page'
WHEN event = 'viewed_simulator_page' THEN 'Viewed Simulator Page'
WHEN event = 'viewed_dandb_hoovers_alternatives_and_competitors_or_g2_page' THEN 'Viewed Dandb Hoovers Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_edit_audience_page' THEN 'Viewed Edit Audience Page'
WHEN event = 'viewed_madkudu_sheets_page' THEN 'Viewed Madkudu Sheets Page'
WHEN event = 'clicked_changelog' THEN 'Clicked Changelog'
WHEN event = 'viewed_model_page' THEN 'Viewed Model Page'
WHEN event = 'viewed_segment_page' THEN 'Viewed Segment Page'
WHEN event = 'viewed_clearbit_alternatives_and_competitors_or_g2_page' THEN 'Viewed Clearbit Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_api_usage_page' THEN 'Viewed Api Usage Page'
WHEN event = 'viewed_use_case_segment_page' THEN 'Viewed Use Case Segment Page'
WHEN event = 'viewed_data_discovery_page' THEN 'Viewed Data Discovery Page'
WHEN event = 'viewed_madkudu_for_zapier_page' THEN 'Viewed Madkudu For Zapier Page'
WHEN event = 'started_oauth' THEN 'Started Oauth'
WHEN event = 'viewed_signup_page' THEN 'Viewed Signup Page'
WHEN event = 'activated_integration' THEN 'Activated Integration'
WHEN event = 'viewed_madkudu_academy_page' THEN 'Viewed Madkudu Academy Page'
WHEN event = 'viewed_marketo_page' THEN 'Viewed Marketo Page'
WHEN event = 'viewed_api_page' THEN 'Viewed Api Page'
WHEN event = 'fast_lane_modal_clicked' THEN 'Fast Lane Modal Clicked'
WHEN event = 'fast_lane_modal_dismissed' THEN 'Fast Lane Modal Dismissed'
WHEN event = 'viewed_madfiles_episode_2_page' THEN 'Viewed Madfiles Episode 2 Page'
WHEN event = 'viewed_segment_mapping_page' THEN 'Viewed Segment Mapping Page'
WHEN event = 'viewed_authentication_page' THEN 'Viewed Authentication Page'
WHEN event = 'viewed_use_case_drift_page' THEN 'Viewed Use Case Drift Page'
WHEN event = 'viewed_madfiles_episode_1_page' THEN 'Viewed Madfiles Episode 1 Page'
WHEN event = 'viewed_apolloio_alternatives_and_competitors_or_g2_page' THEN 'Viewed Apolloio Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_hubspot_mapping_page' THEN 'Viewed Hubspot Mapping Page'
WHEN event = 'viewed_amplitude_page' THEN 'Viewed Amplitude Page'
WHEN event = 'viewed_madfiles_episode_3_page' THEN 'Viewed Madfiles Episode 3 Page'
WHEN event = 'viewed_blog_demystifying_data_science_for_marketers_page' THEN 'Viewed Blog Demystifying Data Science For Marketers Page'
WHEN event = 'viewed_all_posts_page' THEN 'Viewed All Posts Page'
WHEN event = 'completed_oauth' THEN 'Completed Oauth'
WHEN event = 'viewed_billing_page' THEN 'Viewed Billing Page'
WHEN event = 'viewed_uplead_alternatives_and_competitors_or_g2_page' THEN 'Viewed Uplead Alternatives And Competitors Or G2 Page'
WHEN event = 'signed_up' THEN 'Signed Up'
WHEN event = 'viewed_marketo_mapping_page' THEN 'Viewed Marketo Mapping Page'
WHEN event = 'viewed_use_case_outreach_page' THEN 'Viewed Use Case Outreach Page'
WHEN event = 'viewed_use_case_invision_page' THEN 'Viewed Use Case Invision Page'
WHEN event = 'viewed_leadiq_alternatives_and_competitors_or_g2_page' THEN 'Viewed Leadiq Alternatives And Competitors Or G2 Page'
WHEN event = 'viewed_reset_password_page' THEN 'Viewed Reset Password Page'
ELSE 'Other Segment Activity' -- Catch all the meta_event for the feature evaluation
END AS meta_event,
NULL AS contact_id,
e.contact_id AS contactid,
CASE -- positive user activity
WHEN event = 'viewed_unknown_page' THEN TRUE
WHEN event = 'viewed_home_page' THEN TRUE
WHEN event = 'enrichment_provider' THEN TRUE
WHEN event = 'viewed_predictions_page' THEN TRUE
WHEN event = 'viewed_integrations_page' THEN TRUE
WHEN event = 'viewed_pricing_page' THEN TRUE
WHEN event = 'viewed_post' THEN TRUE
WHEN event = 'viewed_mapping_overview_page' THEN TRUE
WHEN event = 'viewed_processes_page' THEN TRUE
WHEN event = 'viewed_performances_page' THEN TRUE
WHEN event = 'viewed_login_page' THEN TRUE
WHEN event = 'viewed_profile_page' THEN TRUE
WHEN event = 'viewed_salesforce_page' THEN TRUE
WHEN event = 'viewed_contact_page' THEN TRUE
WHEN event = 'viewed_homepage_page' THEN TRUE
WHEN event = 'viewed_conversion_mapping_page' THEN TRUE
WHEN event = 'viewed_careers_page' THEN TRUE
WHEN event = 'viewed_data_overview_page' THEN TRUE
WHEN event = 'viewed_platform_page' THEN TRUE
WHEN event = 'viewed_admin_page' THEN TRUE
WHEN event = 'viewed_platform_how_it_works_page' THEN TRUE
WHEN event = 'viewed_profiles_page' THEN TRUE
WHEN event = 'viewed_diagnostics_page' THEN TRUE
WHEN event = 'viewed_team_page' THEN TRUE
WHEN event = 'viewed_onboarding_hubspot_page' THEN TRUE
WHEN event = 'viewed_lead_scoring_page' THEN TRUE
WHEN event = 'viewed_insights_page' THEN TRUE
WHEN event = 'logged_in' THEN TRUE
WHEN event = 'clicked_cta' THEN TRUE
WHEN event = 'viewed_hubspot_page' THEN TRUE
WHEN event = 'qualified_company_visited_the_website' THEN TRUE
WHEN event = 'viewed_yaml_to_sql_page' THEN TRUE
WHEN event = 'viewed_madkudu_facebook_page' THEN TRUE
WHEN event = 'viewed_fastlane_landing_page_page' THEN TRUE
WHEN event = 'viewed_evr_page' THEN TRUE
WHEN event = 'viewed_404_page' THEN TRUE
WHEN event = 'viewed_blog_page' THEN TRUE
WHEN event = 'viewed_event_mapping_page' THEN TRUE
WHEN event = 'viewed_attribute_mapping_page' THEN TRUE
WHEN event = 'viewed_salesforce_data_discovery_page' THEN TRUE
WHEN event = 'viewed_customers_page' THEN TRUE
WHEN event = 'viewed_hubspot_velocity_page' THEN TRUE
WHEN event = 'viewed_settings_page' THEN TRUE
WHEN event = 'fast_lane' THEN TRUE
WHEN event = 'viewed_rocketreach_alternatives_and_competitors_or_g2_page' THEN TRUE
WHEN event = 'viewed_best_lead_intelligence_software_in_2020_or_g2_page' THEN TRUE
WHEN event = 'requested_demo' THEN TRUE
WHEN event = 'viewed_hubspot_data_discovery_page' THEN TRUE
WHEN event = 'viewed_evr_insights_page' THEN TRUE
WHEN event = 'viewed_users_page' THEN TRUE
WHEN event = 'viewed_intercom_page' THEN TRUE
WHEN event = 'viewed_flags_page' THEN TRUE
WHEN event = 'viewed_behavioral_activity_configuration_page' THEN TRUE
WHEN event = 'email_captured' THEN TRUE
WHEN event = 'viewed_lusha_alternatives_and_competitors_or_g2_page' THEN TRUE
WHEN event = 'viewed_privacy_page' THEN TRUE
WHEN event = 'viewed_audience_mapping_page' THEN TRUE
WHEN event = 'viewed_saastr_madkudu_breakfast_page' THEN TRUE
WHEN event = 'viewed_poc_page' THEN TRUE
WHEN event = 'viewed_account_page' THEN TRUE
WHEN event = 'viewed_push_page' THEN TRUE
WHEN event = 'viewed_simulator_page' THEN TRUE
WHEN event = 'viewed_dandb_hoovers_alternatives_and_competitors_or_g2_page' THEN TRUE
WHEN event = 'viewed_edit_audience_page' THEN TRUE
WHEN event = 'viewed_madkudu_sheets_page' THEN TRUE
WHEN event = 'clicked_changelog' THEN TRUE
WHEN event = 'viewed_model_page' THEN TRUE
WHEN event = 'viewed_segment_page' THEN TRUE
WHEN event = 'viewed_clearbit_alternatives_and_competitors_or_g2_page' THEN TRUE
WHEN event = 'viewed_api_usage_page' THEN TRUE
WHEN event = 'viewed_use_case_segment_page' THEN TRUE
WHEN event = 'viewed_data_discovery_page' THEN TRUE
WHEN event = 'viewed_madkudu_for_zapier_page' THEN TRUE
WHEN event = 'started_oauth' THEN TRUE
WHEN event = 'viewed_signup_page' THEN TRUE
WHEN event = 'activated_integration' THEN TRUE
WHEN event = 'viewed_madkudu_academy_page' THEN TRUE
WHEN event = 'viewed_marketo_page' THEN TRUE
WHEN event = 'viewed_api_page' THEN TRUE
WHEN event = 'fast_lane_modal_clicked' THEN TRUE
WHEN event = 'fast_lane_modal_dismissed' THEN TRUE
WHEN event = 'viewed_madfiles_episode_2_page' THEN TRUE
WHEN event = 'viewed_segment_mapping_page' THEN TRUE
WHEN event = 'viewed_authentication_page' THEN TRUE
WHEN event = 'viewed_use_case_drift_page' THEN TRUE
WHEN event = 'viewed_madfiles_episode_1_page' THEN TRUE
WHEN event = 'viewed_apolloio_alternatives_and_competitors_or_g2_page' THEN TRUE
WHEN event = 'viewed_hubspot_mapping_page' THEN TRUE
WHEN event = 'viewed_amplitude_page' THEN TRUE
WHEN event = 'viewed_madfiles_episode_3_page' THEN TRUE
WHEN event = 'viewed_blog_demystifying_data_science_for_marketers_page' THEN TRUE
WHEN event = 'viewed_all_posts_page' THEN TRUE
WHEN event = 'completed_oauth' THEN TRUE
WHEN event = 'viewed_billing_page' THEN TRUE
WHEN event = 'viewed_uplead_alternatives_and_competitors_or_g2_page' THEN TRUE
WHEN event = 'signed_up' THEN TRUE
WHEN event = 'viewed_marketo_mapping_page' THEN TRUE
WHEN event = 'viewed_use_case_outreach_page' THEN TRUE
WHEN event = 'viewed_use_case_invision_page' THEN TRUE
WHEN event = 'viewed_leadiq_alternatives_and_competitors_or_g2_page' THEN TRUE
WHEN event = 'viewed_reset_password_page' THEN TRUE
ELSE TRUE
END AS is_positive_user_activity,
CASE -- Activity type
WHEN event = 'viewed_unknown_page' THEN 'App Usage'
WHEN event = 'viewed_home_page' THEN 'App Usage'
WHEN event = 'enrichment_provider' THEN 'App Usage'
WHEN event = 'viewed_predictions_page' THEN 'App Usage'
WHEN event = 'viewed_integrations_page' THEN 'App Usage'
WHEN event = 'viewed_pricing_page' THEN 'App Usage'
WHEN event = 'viewed_post' THEN 'App Usage'
WHEN event = 'viewed_mapping_overview_page' THEN 'App Usage'
WHEN event = 'viewed_processes_page' THEN 'App Usage'
WHEN event = 'viewed_performances_page' THEN 'App Usage'
WHEN event = 'viewed_login_page' THEN 'App Usage'
WHEN event = 'viewed_profile_page' THEN 'App Usage'
WHEN event = 'viewed_salesforce_page' THEN 'App Usage'
WHEN event = 'viewed_contact_page' THEN 'App Usage'
WHEN event = 'viewed_homepage_page' THEN 'App Usage'
WHEN event = 'viewed_conversion_mapping_page' THEN 'App Usage'
WHEN event = 'viewed_careers_page' THEN 'App Usage'
WHEN event = 'viewed_data_overview_page' THEN 'App Usage'
WHEN event = 'viewed_platform_page' THEN 'App Usage'
WHEN event = 'viewed_admin_page' THEN 'App Usage'
WHEN event = 'viewed_platform_how_it_works_page' THEN 'App Usage'
WHEN event = 'viewed_profiles_page' THEN 'App Usage'
WHEN event = 'viewed_diagnostics_page' THEN 'App Usage'
WHEN event = 'viewed_team_page' THEN 'App Usage'
WHEN event = 'viewed_onboarding_hubspot_page' THEN 'App Usage'
WHEN event = 'viewed_lead_scoring_page' THEN 'App Usage'
WHEN event = 'viewed_insights_page' THEN 'App Usage'
WHEN event = 'logged_in' THEN 'App Usage'
WHEN event = 'clicked_cta' THEN 'App Usage'
WHEN event = 'viewed_hubspot_page' THEN 'App Usage'
WHEN event = 'qualified_company_visited_the_website' THEN 'App Usage'
WHEN event = 'viewed_yaml_to_sql_page' THEN 'App Usage'
WHEN event = 'viewed_madkudu_facebook_page' THEN 'App Usage'
WHEN event = 'viewed_fastlane_landing_page_page' THEN 'App Usage'
WHEN event = 'viewed_evr_page' THEN 'App Usage'
WHEN event = 'viewed_404_page' THEN 'App Usage'
WHEN event = 'viewed_blog_page' THEN 'App Usage'
WHEN event = 'viewed_event_mapping_page' THEN 'App Usage'
WHEN event = 'viewed_attribute_mapping_page' THEN 'App Usage'
WHEN event = 'viewed_salesforce_data_discovery_page' THEN 'App Usage'
WHEN event = 'viewed_customers_page' THEN 'App Usage'
WHEN event = 'viewed_hubspot_velocity_page' THEN 'App Usage'
WHEN event = 'viewed_settings_page' THEN 'App Usage'
WHEN event = 'fast_lane' THEN 'App Usage'
WHEN event = 'viewed_rocketreach_alternatives_and_competitors_or_g2_page' THEN 'App Usage'
WHEN event = 'viewed_best_lead_intelligence_software_in_2020_or_g2_page' THEN 'App Usage'
WHEN event = 'requested_demo' THEN 'App Usage'
WHEN event = 'viewed_hubspot_data_discovery_page' THEN 'App Usage'
WHEN event = 'viewed_evr_insights_page' THEN 'App Usage'
WHEN event = 'viewed_users_page' THEN 'App Usage'
WHEN event = 'viewed_intercom_page' THEN 'App Usage'
WHEN event = 'viewed_flags_page' THEN 'App Usage'
WHEN event = 'viewed_behavioral_activity_configuration_page' THEN 'App Usage'
WHEN event = 'email_captured' THEN 'App Usage'
WHEN event = 'viewed_lusha_alternatives_and_competitors_or_g2_page' THEN 'App Usage'
WHEN event = 'viewed_privacy_page' THEN 'App Usage'
WHEN event = 'viewed_audience_mapping_page' THEN 'App Usage'
WHEN event = 'viewed_saastr_madkudu_breakfast_page' THEN 'App Usage'
WHEN event = 'viewed_poc_page' THEN 'App Usage'
WHEN event = 'viewed_account_page' THEN 'App Usage'
WHEN event = 'viewed_push_page' THEN 'App Usage'
WHEN event = 'viewed_simulator_page' THEN 'App Usage'
WHEN event = 'viewed_dandb_hoovers_alternatives_and_competitors_or_g2_page' THEN 'App Usage'
WHEN event = 'viewed_edit_audience_page' THEN 'App Usage'
WHEN event = 'viewed_madkudu_sheets_page' THEN 'App Usage'
WHEN event = 'clicked_changelog' THEN 'App Usage'
WHEN event = 'viewed_model_page' THEN 'App Usage'
WHEN event = 'viewed_segment_page' THEN 'App Usage'
WHEN event = 'viewed_clearbit_alternatives_and_competitors_or_g2_page' THEN 'App Usage'
WHEN event = 'viewed_api_usage_page' THEN 'App Usage'
WHEN event = 'viewed_use_case_segment_page' THEN 'App Usage'
WHEN event = 'viewed_data_discovery_page' THEN 'App Usage'
WHEN event = 'viewed_madkudu_for_zapier_page' THEN 'App Usage'
WHEN event = 'started_oauth' THEN 'App Usage'
WHEN event = 'viewed_signup_page' THEN 'App Usage'
WHEN event = 'activated_integration' THEN 'App Usage'
WHEN event = 'viewed_madkudu_academy_page' THEN 'App Usage'
WHEN event = 'viewed_marketo_page' THEN 'App Usage'
WHEN event = 'viewed_api_page' THEN 'App Usage'
WHEN event = 'fast_lane_modal_clicked' THEN 'App Usage'
WHEN event = 'fast_lane_modal_dismissed' THEN 'App Usage'
WHEN event = 'viewed_madfiles_episode_2_page' THEN 'App Usage'
WHEN event = 'viewed_segment_mapping_page' THEN 'App Usage'
WHEN event = 'viewed_authentication_page' THEN 'App Usage'
WHEN event = 'viewed_use_case_drift_page' THEN 'App Usage'
WHEN event = 'viewed_madfiles_episode_1_page' THEN 'App Usage'
WHEN event = 'viewed_apolloio_alternatives_and_competitors_or_g2_page' THEN 'App Usage'
WHEN event = 'viewed_hubspot_mapping_page' THEN 'App Usage'
WHEN event = 'viewed_amplitude_page' THEN 'App Usage'
WHEN event = 'viewed_madfiles_episode_3_page' THEN 'App Usage'
WHEN event = 'viewed_blog_demystifying_data_science_for_marketers_page' THEN 'App Usage'
WHEN event = 'viewed_all_posts_page' THEN 'App Usage'
WHEN event = 'completed_oauth' THEN 'App Usage'
WHEN event = 'viewed_billing_page' THEN 'App Usage'
WHEN event = 'viewed_uplead_alternatives_and_competitors_or_g2_page' THEN 'App Usage'
WHEN event = 'signed_up' THEN 'App Usage'
WHEN event = 'viewed_marketo_mapping_page' THEN 'App Usage'
WHEN event = 'viewed_use_case_outreach_page' THEN 'App Usage'
WHEN event = 'viewed_use_case_invision_page' THEN 'App Usage'
WHEN event = 'viewed_leadiq_alternatives_and_competitors_or_g2_page' THEN 'App Usage'
WHEN event = 'viewed_reset_password_page' THEN 'App Usage'
ELSE 'Web Activity'
END AS activity_type
FROM
events_raw AS e
WHERE
e.source_system = 'segment'
AND e.event NOT IN (SELECT event FROM least_frequent_events) -- do not map least frequent events
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND e.event !='enrichment_provider'
AND e.event NOT IN (
{{#segment_event_exclusion_list}}'{{.}}',{{/segment_event_exclusion_list}} 'MadKuduDummyLastItem'
)
{{/has_segment}}
# Amplitude events
- description: insert in Events stage the AMPLITUDE events
sql: |
{{#has_amplitude}}
-- get the list of the least frequent events in the last 9 months
DROP TABLE IF EXISTS least_frequent_events;
CREATE TEMP TABLE least_frequent_events AS
WITH cte AS (
SELECT
event,
COUNT(DISTINCT id) AS count_event
FROM
events_raw
WHERE
event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND source_system = 'amplitude'
GROUP BY 1
)
SELECT *
FROM cte
WHERE
count_event < 100 -- the limited number of events can be adjusted here
;
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE -- MK Event Name for Signals
false
ELSE event_text -- Keep the granularity of the events for the signals
END AS event_text,
e.event_timestamp,
CASE -- MK Event name
false
ELSE 'Other Amplitude Activity' -- Catch all the meta_event for the feature evaluation
END AS meta_event,
NULL AS contact_id,
e.contact_id AS contactid,
CASE -- positive user activity
false
ELSE TRUE
END AS is_positive_user_activity,
CASE -- Activity type
false
ELSE 'Web Activity'
END AS activity_type
FROM
events_raw AS e
WHERE
e.source_system = 'amplitude'
AND e.event NOT IN (SELECT event FROM least_frequent_events) -- do not map least frequent events
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND e.event NOT IN (
{{#amplitude_event_exclusion_list}}'{{.}}',{{/amplitude_event_exclusion_list}} 'MadKuduDummyLastItem'
)
{{/has_amplitude}}
# Mixpanel events
- description: insert in Events stage the MIXPANEL events
sql: |
{{#has_mixpanel}}
-- get the list of the least frequent events in the last 9 months
DROP TABLE IF EXISTS least_frequent_events;
CREATE TEMP TABLE least_frequent_events AS
WITH cte AS (
SELECT
event,
COUNT(DISTINCT id) AS count_event
FROM
events_raw
WHERE
event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND source_system = 'mixpanel'
GROUP BY 1
)
SELECT *
FROM cte
WHERE
count_event < 100 -- the limited number of events can be adjusted here
;
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE -- MK Event Name for Signals
false
ELSE event_text -- Keep the granularity of the events for the signals
END AS event_text,
e.event_timestamp,
CASE -- MK Event name
false
ELSE 'Other Mixpanel Activity' -- Catch all the meta_event for the feature evaluation
END AS meta_event,
NULL AS contact_id,
e.contact_id AS contactid,
CASE -- positive user activity
false
ELSE TRUE
END AS is_positive_user_activity,
CASE -- Activity type
false
ELSE 'Web Activity'
END AS activity_type
FROM
events_raw AS e
WHERE
e.source_system = 'mixpanel'
AND e.event NOT IN (SELECT event FROM least_frequent_events) -- do not map least frequent events
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND e.event NOT IN (
{{#mixpanel_event_exclusion_list}}'{{.}}',{{/mixpanel_event_exclusion_list}} 'MadKuduDummyLastItem'
)
{{/has_mixpanel}}
# Kissmetrics events
- description: insert in Events stage the KISSMETRICS events
sql: |
{{#has_kissmetrics}}
-- get the list of the least frequent events in the last 9 months
DROP TABLE IF EXISTS least_frequent_events;
CREATE TEMP TABLE least_frequent_events AS
WITH cte AS (
SELECT
event,
COUNT(DISTINCT id) AS count_event
FROM
events_raw
WHERE
event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND source_system = 'kissmetrics'
GROUP BY 1
)
SELECT *
FROM cte
WHERE
count_event < 100 -- the limited number of events can be adjusted here
;
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE -- MK Event Name for Signals
false
ELSE event_text -- Keep the granularity of the events for the signals
END AS event_text,
e.event_timestamp,
CASE -- MK Event name
false
ELSE 'Other Kissmetrics Activity' -- Catch all the meta_event for the feature evaluation
END AS meta_event,
NULL AS contact_id,
e.contact_id AS contactid,
CASE -- positive user activity
false
ELSE TRUE
END AS is_positive_user_activity,
CASE -- Activity type
false
ELSE 'Web Activity'
END AS activity_type
FROM
events_raw AS e
WHERE
e.source_system = 'kissmetrics'
AND e.event NOT IN (SELECT event FROM least_frequent_events) -- do not map least frequent events
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
AND e.event NOT IN (
{{#kissmetrics_event_exclusion_list}}'{{.}}',{{/kissmetrics_event_exclusion_list}} 'MadKuduDummyLastItem'
)
{{/has_kissmetrics}}
# Marketo events
- description: insert in Events stage the Marketo events
sql: |
{{#has_marketo}}
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE
-- placeholder Analytics events - Display (signals)
false
ELSE event_text
END AS event_text,
e.event_timestamp,
CASE
-- placeholder Analytics events - Name (insights)
false
ELSE 'Other Marketo ' || event
END AS meta_event,
NULL AS contact_id,
e.contact_id AS contactid,
CASE
-- placeholder Analytics events - Positive activity
false
ELSE TRUE
END AS is_positive_user_activity,
CASE
-- placeholder Analytics events - Activity Type
false
ELSE 'Other'
END AS activity_type
FROM
events_raw AS e
WHERE
e.source_system = 'marketo'
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
{{/has_marketo}}
# Salesforce Campaign events
- description: insert in Events stage the Salesforce Campaign events
sql: |
{{#has_salesforce_campaigns}}
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
ce.id AS id,
c.a_type AS event,
CASE
false
ELSE 'Other Salesforce Campaign Activity'
END AS event_text,
ce.a_createddate::TIMESTAMP AS event_timestamp,
CASE
false
ELSE 'Other Salesforce Campaign Activity'
END AS meta_event,
NULL AS contact_id,
ce.contact_id AS contactid,
CASE
false
ELSE TRUE
END AS is_positive_user_activity,
CASE
false
ELSE 'Web Activity'
END AS activity_type
FROM
campaigns AS c
INNER JOIN
campaign_events AS ce
ON ce.campaign_id = c.id
WHERE
c.source_system = 'salesforce'
AND ce.source_system_object = 'CampaignMember'
AND c.a_status!= 'Aborted'
AND ce.a_status!= 'Sent'
AND ce.created_date >= CURRENT_DATE - INTERVAL '9 MONTH'
{{/has_salesforce_campaigns}}
# Salesforce Tasks events
- description: insert in Events stage the Salesforce tasks events
sql: |
{{#has_salesforce_tasks}}
INSERT INTO events_stage
(
id,
event,
event_text,
event_timestamp,
meta_event,
contact_id,
contactid,
is_positive_user_activity,
activity_type
)
SELECT
e.id,
e.event,
CASE -- MK Event Name for Signals
WHEN a_type = 'Email' AND a_id = 'Invalid/Duplicate' THEN 'Email'
ELSE 'Other Salesforce task'
END AS event_text,
e.event_timestamp,
CASE -- MK Event name
WHEN a_type = 'Email' AND a_id = 'Invalid/Duplicate' THEN 'Email'
ELSE 'Other Salesforce task'
END AS meta_event,
NULL AS contact_id,
e.contact_id AS contactid,
CASE -- positive user activity
WHEN a_type = 'Email' AND a_id = 'Invalid/Duplicate' THEN TRUE
ELSE TRUE
END AS is_positive_user_activity,
CASE -- Activity type
WHEN a_type = 'Email' AND a_id = 'Invalid/Duplicate' THEN 'Sales Activity'
ELSE 'Sales Activity'
END AS activity_type
FROM
events_raw AS e
WHERE
e.source_system = 'salesforce'
AND e.event='task'
AND e.a_status='Completed'
AND e.event_timestamp >= CURRENT_DATE - INTERVAL '9 MONTH'
{{/has_salesforce_tasks}}