Sample SQL Queries
Finished collecting data? Here are some sample SQL queries to get you started. You can copy these queries and run them in the Workbench in the Panoply platform and also save them as views there. These queries can also be used with an External workbench or in your BI tool.
Here is a list of sample SQL queries for our most popular sources.
Data Sources:
Facebook Ads
Last 7 VS Previous 7
This query compares the sessions of the last 7 days to the previous 7 days aggregated by campaign_name. This query demonstrates the use of a concept that can be used with any dimension and metric\s, comparing different values of the metric based on different values of time or a given dimension.
Monthly Breakdown
This query sums the total cost, clicks and impressions by month and year from facebook-ads.
Adwords API
Monthly Breakdown
This query sums the total cost and conversions by month and year from the adgroup_performance_report.
Google Analytics
Last 7 VS Previous 7
This query compares the sessions of the last 7 days to the previous 7 days aggregated by day. This query demonstrates the use of a concept that can be used with any dimension and metric\s, comparing different values of the metric based on different values of the dimension.
Medium Breakdown
This displays a sessions and bounce rate from the past 30 days aggregated by medium.
Hubspot
Time elapsed histogram: From contact create date to MQL conversion
This query quantifies how long it takes for Hubspot contacts to become Marketing Qualified Leads. Contacts are grouped into bins based on the elapsed time from contact to MQL. (‘Day 1’, ‘Day 2-7’, ‘Day 8-28’ and ‘Days 28+’). This data is pulled from the Hubspot API into Panoply.
Form Submission Analysis
This query calculates metrics about form submissions and the context of the form submissions. This data is pulled from the Hubspot API into Panoply.
Companies View
Denormalized view of Hubspot’s default values in hubspot_companies_properties key-value pair table.
Contacts View
Denormalized view of Hubspot’s default values in hubspot_contacts_properties key-value pair table.
Salesforce
New Opportunities Summary
This query shows different details about opportunities derived from Salesforce data.
Account to Email View
View for getting the connection between Salesforce accountid to the relevant user email.