Track website traffic by source

SQL query for HubSpot

Get website traffic by source from HubSpot data.

SELECT source, SUM(sessions) AS total_sessions FROM website_traffic_table GROUP BY source;

HubSpot data model

Table Contacts

Vid: Integer
AddedAt: Integer
Portal-id: Integer
Is-contact: Boolean
Canonical-vid: Integer
Properties_company_value: String
Properties_lastname_value: String
Properties_firstname_value: String
Properties_lastmodifieddate_value: String
Properties_associatedcompanyid_value: String

Table Companies

PortalId: Integer
CompanyId: Integer
IsDeleted: Boolean
Properties_city_value: String
Properties_name_value: String
Properties_city_source: String
Properties_name_source: String
Properties_city_sourceId: String
Properties_name_sourceId: String
Properties_website_value: String
Properties_city_timestamp: Integer
Properties_name_timestamp: Integer
Properties_website_source: String
Properties_website_sourceId: String
Properties_website_timestamp: Integer
Properties_city_updatedByUserId: Integer
Properties_city_versions_0_name: String
Properties_name_updatedByUserId: Integer
Properties_name_versions_0_name: String
Properties_city_versions_0_value: String
Properties_name_versions_0_value: String
Properties_city_versions_0_source: String
Properties_name_versions_0_source: String
Properties_website_updatedByUserId: Integer
Properties_website_versions_0_name: String
Properties_city_versions_0_sourceId: String
Properties_name_versions_0_sourceId: String
Properties_website_versions_0_value: String
Properties_city_versions_0_requestId: String
Properties_city_versions_0_timestamp: Integer
Properties_name_versions_0_requestId: String
Properties_name_versions_0_timestamp: Integer
Properties_website_versions_0_source: String
Properties_website_versions_0_sourceId: String
Properties_website_versions_0_requestId: String
Properties_website_versions_0_timestamp: Integer
Properties_city_versions_0_updatedByUserId: Integer
Properties_name_versions_0_updatedByUserId: Integer
Properties_website_versions_0_updatedByUserId: Integer

Table Deals

Table Contact Lists

Magical SQL for HubSpot with Peliqan.io

Explore data in a rich spreadsheet UI. Use Magical SQL to combine and transform data. SQL queries become new tables that can be shared with business users and used in any BI tool such as Microsoft Power BI, Tableau or Metabase.

Let AI do the work for you

Peliqan’s AI assistant helps you to write SQL queries to get to insights fast. Ask your question in plain English and immediately see the result in Peliqan’s rich spreadsheet viewer.