HubSpot Email Marketing SpotApp

SpotApps are ThoughtSpot’s out-of-the-box solution templates built for specific use cases and data sources. They are built on ThoughtSpot Modeling Language (TML) Blocks, which are pre-built pieces of code that are easy to download and implement directly from the product.

The HubSpot Email Marketing SpotApp mimics the HubSpot data model. When you deploy it, ThoughtSpot creates several worksheets, answers, and Liveboards, based on your HubSpot data in your cloud data warehouse.

This is a sample Liveboard, created after you deploy the HubSpot Email Marketing SpotApp:

Hubspot SpotApp Liveboard

Use the HubSpot Email Marketing SpotApp to identify the success of your email campaigns. Determine which emails are working, and use that information to make data-driven decisions about where to focus your marketing budget.

Prerequisites

Before you can deploy the HubSpot Email Marketing SpotApp, you must complete the following prerequisites:

Run SQL commands

The following SQL commands help standardize data types and column names. Replace the database name and schema name with your specific information.

The following SQL example is for the Snowflake cloud data warehouse. You may need to modify the code for the SQL requirements of your specific cloud data warehouse.

SQL commands:

Click on the dropdown to view the SQL commands.
CREATE VIEW "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_EMAIL_EVENTS" AS
SELECT E."APP_ID",E."APP_NAME", DATEADD('MS',E."CREATED",'1970-01-01') AS "CREATED", E."EMAIL_CAMPAIGN_ID", E."ID", E."PORTAL_ID",
E."RECIPIENT", E."TYPE", DATEADD('MS',E."SENT_BY_CREATED",'1970-01-01') AS "SENT_BY_CREATED", E."SENT_BY_ID", E."BROWSER_NAME", E."LOCATION_CITY",
E."LOCATION_COUNTRY", E."LOCATION_STATE", E."USER_AGENT", E."DURATION", E."SOURCE", E."PORTAL_SUBSCRIPTION_STATUS",
E."ATTEMPT", E."RESPONSE",E."URL", E."SUBJECT", E."FROM" AS "FROM_EMAIL",
E."DROP_MESSAGE", E."CAUSED_BY_ID",
IFF(CON."TOTAL_REVENUE" >0 AND E.TYPE='SENT' AND CON."FIRST_DEAL_CREATED_DATE">DATEADD('MS',E."CREATED",'1970-01-01'),1,0)  AS "PRE_CUSTOMER_MARKETING_EMAIL",
TO_DATE(DATEADD('MS',E."CREATED",'1970-01-01')) AS "CREATED_AT_DATE", TO_TIME(DATEADD('MS',E."CREATED",'1970-01-01')) AS "CREATED_AT_TIME"
FROM "HS_HUBSPOT"."PUBLIC"."HS_EMAIL_EVENTS" E
LEFT JOIN "HS_HUBSPOT"."PUBLIC"."HS_CONTACTS" CON
ON E."RECIPIENT"=CON."EMAIL"

CREATE VIEW "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_EMAIL_TEMP" AS
select to_varchar(t0.value:"absoluteUrl") AS "EMAIL_URL", DATEADD('MS',to_varchar(t0.value:"created"),'1970-01-01')  AS "DATE_CREATED",
to_varchar(t0.value:"fromName") AS "EMAIL_FROM_NAME", to_varchar(t0.value:"name") AS "CAMPAIGN_NAME",
to_varchar(t0.value:"replyTo") AS "REPLY_TO_EMAIL", to_number(t0.value:"stats"."counters"."bounce") AS "BOUNCE",
to_number(t0.value:"stats"."counters"."click") AS "CLICK", to_number(t0.value:"stats"."counters"."contactslost") AS "CONTACTS_LOST",
to_number(t0.value:"stats"."counters"."delivered") AS "DELIVERED", to_number(t0.value:"stats"."counters"."open") AS "OPEN",
to_number(t0.value:"stats"."counters"."reply") AS "REPLY", to_number(t0.value:"stats"."counters"."sent") AS "SENT",
to_number(t0.value:"stats"."counters"."unsubscribed") AS "UNSUBSCRIBED", to_number(t0.value:"stats"."ratios"."bounceratio") AS "BOUNCE_RATIO",
to_double(t0.value:"stats"."ratios"."clickratio") AS "CLICK_RATIO", to_double(t0.value:"stats"."ratios"."clickthroughratio") AS "CLICK_THROUGH_RATIO",
to_double(t0.value:"stats"."ratios"."openratio") AS "OPEN_RATIO",to_varchar(t0.value:"subject") AS "EMAIL_SUBJECT", to_number(t1.value) AS "CAMPAIGN_ID"
from "HS_HUBSPOT"."PUBLIC"."HS_MARKETING_EMAILS", lateral flatten (input => "OBJECTS") as t0,
lateral flatten (input => t0.value:"allEmailCampaignIds") as t1

CREATE VIEW "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_CAMPAIGN_SENT_DATE" AS
SELECT "CAMPAIGN_ID",min ("CREATED") AS "DATE_CAMPAIGN_SENT" FROM "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_EMAIL_TEMP" t LEFT JOIN "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_EMAIL_EVENTS" ev
ON t."CAMPAIGN_ID"=ev."EMAIL_CAMPAIGN_ID" GROUP BY "CAMPAIGN_ID"

CREATE VIEW "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_EMAIL" AS
SELECT t.*,s."DATE_CAMPAIGN_SENT",TO_DATE(s."DATE_CAMPAIGN_SENT") AS "CAMPAIGN_DATE",TO_TIME(s."DATE_CAMPAIGN_SENT") AS "CAMPAIGN_TIME"
FROM "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_EMAIL_TEMP" t LEFT JOIN "HS_HUBSPOT"."PUBLIC"."HUBSPOT_MARKETING_CAMPAIGN_SENT_DATE" s
ON t."CAMPAIGN_ID"=s."CAMPAIGN_ID"

Deploy the HubSpot Email Marketing SpotApp

After you complete the prerequisites, you are ready to deploy the HubSpot Email Marketing SpotApp and begin leveraging its pre-built content.

To deploy the HubSpot Email Marketing SpotApp, refer to Deploying SpotApps.

HubSpot Email Marketing SpotApp schema

The following table describes the schema for the HubSpot Email Marketing SpotApp.

Table/View Column Column type Required column

HUBSPOT_MARKETING_EMAIL

EMAIL_URL

VARCHAR

Y

HUBSPOT_MARKETING_EMAIL

DATE_CREATED

TIMESTAMP_NTZ(9)

Y

HUBSPOT_MARKETING_EMAIL

CAMPAIGN_NAME

VARCHAR

Y

HUBSPOT_MARKETING_EMAIL

BOUNCE

NUMBER(38,0)

N

HUBSPOT_MARKETING_EMAIL

CLICK

NUMBER(38,0)

Y

HUBSPOT_MARKETING_EMAIL

DELIVERED

NUMBER(38,0)

Y

HUBSPOT_MARKETING_EMAIL

OPEN

NUMBER(38,0)

Y

HUBSPOT_MARKETING_EMAIL

REPLY

NUMBER(38,0)

Y

HUBSPOT_MARKETING_EMAIL

SENT

NUMBER(38,0)

Y

HUBSPOT_MARKETING_EMAIL

UNSUBSCRIBED

NUMBER(38,0)

Y

HUBSPOT_MARKETING_EMAIL

BOUNCE_RATIO

NUMBER(38,0)

N

HUBSPOT_MARKETING_EMAIL

CLICK_RATIO

FLOAT

Y

HUBSPOT_MARKETING_EMAIL

CLICK_THROUGH_RATIO

FLOAT

Y

HUBSPOT_MARKETING_EMAIL

OPEN_RATIO

FLOAT

Y

HUBSPOT_MARKETING_EMAIL

EMAIL_SUBJECT

VARCHAR

Y

HUBSPOT_MARKETING_EMAIL

CAMPAIGN_ID

NUMBER(38,0)

Y

HUBSPOT_MARKETING_EMAIL

DATE_CAMPAIGN_SENT

DATE

N

HUBSPOT_MARKETING_EMAIL

CAMPAIGN_DATE

DATE

Y

HUBSPOT_MARKETING_EMAIL_EVENTS

APP_ID

NUMBER(38,0)

N

HUBSPOT_MARKETING_EMAIL_EVENTS

EMAIL_CAMPAIGN_ID

NUMBER(38,0)

N

HUBSPOT_MARKETING_EMAIL_EVENTS

ID

VARCHAR

Y

HUBSPOT_MARKETING_EMAIL_EVENTS

RECIPIENT

VARCHAR

N

HUBSPOT_MARKETING_EMAIL_EVENTS

TYPE

VARCHAR

Y

HUBSPOT_MARKETING_EMAIL_EVENTS

BROWSER_NAME

VARCHAR

N

HUBSPOT_MARKETING_EMAIL_EVENTS

LOCATION_CITY

VARCHAR

N

HUBSPOT_MARKETING_EMAIL_EVENTS

LOCATION_COUNTRY

VARCHAR

N

HUBSPOT_MARKETING_EMAIL_EVENTS

LOCATION_STATE

VARCHAR

N