Sample SQL Queries

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.