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:
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:
-
Review the required tables and columns for the SpotApp.
-
Ensure that your columns match the required column type listed in the schema for your SpotApp.
-
Sync all tables and columns from HubSpot to your cloud data warehouse. You can sync only the required tables and columns, but ThoughtSpot recommends syncing all tables and columns from HubSpot to your CDW. The columns can be HubSpot’s out-of-the-box columns, or any custom columns that you are using instead of the out-of-the-box columns.
If you are using an ETL/ELT tool or working with another team in your organization to move data, our recommendation is that you sync all columns from the tables listed in the SpotApp. -
Obtain credentials and SYSADMIN privileges to connect to your cloud data warehouse. The cloud data warehouse must contain the data you would like ThoughtSpot to use to create Answers, Liveboards, and Worksheets. Refer to the connection reference for your cloud data warehouse for information about required credentials:
-
The connection name for each new SpotApp must be unique.
-
Super Admin access to HubSpot
-
Access to the following HubSpot tables in your cloud data warehouse. Refer to HubSpot Email Marketing SpotApp schema for more details.
-
HUBSPOT_MARKETING_EMAIL
-
HUBSPOT_MARKETING_EMAIL_EVENTS
-
-
Run the required SQL commands in your cloud data warehouse. Refer to Run SQL commands.
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:
Open the dropdown menu 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 |
