This query calculates metrics about form submissions and the context of the form submissions. This data is pulled from the Hubspot API into Panoply.
WITH form_submissions
AS (
SELECT s."conversion-id" submission_id,
TIMESTAMP 'epoch' + s.TIMESTAMP / 1000 * INTERVAL '1 second' submission_timestamp,
row_number() OVER (
PARTITION BY s.hubspot_contacts_id ORDER BY s.TIMESTAMP
) submission_order,
s."page-id" page_id,
s."page-url" page_url,
s."page-title" page_title,
s.title form_title,
s."form-id" form_id,
s.hubspot_contacts_id
FROM "hubspot_contacts_form-submissions" s
)
SELECT f.guid form_id,
f.name form_current_name,
fs.page_url,
fs.page_title,
fs.form_title,
count(DISTINCT fs.submission_id) total_submissions,
count(DISTINCT fs.hubspot_contacts_id) total_contacts,
count(DISTINCT CASE WHEN submission_order = 1 THEN fs.submission_id END) first_submissions,
round(total_submissions::FLOAT / total_contacts, 2) submission_per_contact
FROM hubspot_forms f
JOIN form_submissions fs ON fs.form_id = f.guid
WHERE submission_timestamp > CURRENT_DATE - 28
GROUP BY 1,2,3,4,5;
Column | Description | |
---|---|---|
form_id |
Hubspot’s internal canonical form identifier, sometimes referred to as the guid |
|
form_current_name |
Most recent name of the form in the Hubspot app (form names are mutable) | |
page_url |
The full URL the form was submitted on excluding protocol | |
page_title |
The title of the page where the form was submitted at the time of submission | |
form_title |
The name of the form at the time of submission | |
total_submissions |
Count of unique form submissions for each form, also referred to as “conversions” | |
total_contacts |
Count of unique contacts who submitted each form | |
first_submissions |
Count of times that this was a contacts’ first form submission (often point of | acquisition) |
submission_per_contact |
Average number of submission per contact |