Warehouses

Example SQL queries to analyze your web, mobile and cloud app data

Facebook Ads / Google Adwords + Google Adwords + Segment - Cross Platform Ad Level Comparisons
Question How can I compare the success of my ads at the individual ad level? Sources Facebook Ads Google Adwords Query with fb_perf as ( select a.id as ad_id, a.name as ad_name, ad.…
- 3 yrs ago
- 17,817
- 5
- Warehouses
Facebook Ads / Google Adwords + Salesforce + Stripe + Segment - Returns on Ad Campaigns
Question What’s the lifetime ROI and ROAS of my ad campaigns? What is the value that my ad campaigns are bringing in? Sources Facebook Ads Google Adwords Salesforce Stripe Query with page_hits as (…
- 3 yrs ago
- 16,173
- Warehouses
Facebook Ads / Google Adwords + Stripe + Segment - Cross Platform Ad Comparison
Question Where are my ads overlapping? Do I need to start thinking about an attribution model? Sources Facebook Ads Google Adwords Stripe Query with signups as ( select date_trunc('month', s.…
- 3 yrs ago
- 16,783
- Warehouses
Facebook Ads / Google Adwords + Segment - First or Last Touch Attribution
Question How do I start using some basic attribution to de-duplicate my conversions across platforms? Sources Facebook Ads Google Adwords Query with page_views as ( select row_number() over…
- 3 yrs ago
- 18,666
- Warehouses
Facebook Ads / Google Adwords + Zendesk + Segment - Ticket Load
Question How are my ads affecting my support ticket load? Sources Facebook Ads Google Adwords Zendesk Query -- page view from traffic from Facebook Ads with page_hits as ( select pages.…
- 3 yrs ago
- 15,626
- 1
- Warehouses
Mandrill – emails over customer lifecycle
Question View how many emails a customer is receiving over time Sources Mandrill (see full visual schema here ) Results Query select date_trunc('week', mandrill.…
- 3 yrs ago
- 16,909
- 1
- Warehouses
Hubspot – campaign performance
Question Measure your campaigns performance with email events Sources Hubspot (see full visual schema here ) Results Query select campaigns.name campaign, email_events.…
- 3 yrs ago
- 17,471
- 1
- Warehouses
Hubspot + Segment – conversion rate of email campaigns
Question Build a custom funnel of email events to in-app conversions Sources Hubspot (see full visual schema here ) Results Query with email_events as ( select type event, recipient email,…
- 3 yrs ago
- 17,575
- 1
- Warehouses
Mandrill + Stripe – revenue per campaign
Question Analyze the conversion rate and revenue from an email campaign Sources Mandrill (see full visual schema here ) Stripe (see full visual schema here ) Results Query with email_events as (…
- 3 yrs ago
- 17,179
- 2
- Warehouses
Hubspot + Stripe – revenue per campaign
Question Analyze the paid conversion rate and revenue from an email campaign Sources Hubspot (full schema here ) Stripe (full schema here ) Results Query with email_events as ( select type event,…
- 3 yrs ago
- 17,207
- 1
- Warehouses
Mandrill + Segment – conversion of email campaign to signup
Question Build a custom funnel of email events to in-app conversions Sources Mandrill (see full visual schema here ) Analytics.js (docs here ) Results Query with email_events as ( select event,…
- 3 yrs ago
- 17,854
- 1
- Warehouses
Intercom + Stripe – revenue impact of support
Question Do customers that you talk to pay you more, on average? Sources Intercom (see full visual schema here ) Results Query with intercom_stripe as ( select intercom_users.…
- 3 yrs ago
- 18,540
- 1
- Warehouses
Intercom – conversations by customer
Question Measure which customers have interacted most with your team Sources Intercom (see full visual schema here ) Results Query select users.email, count(distinct conversations.…
- 3 yrs ago
- 16,558
- 1
- Warehouses
Intercom + Segment – conversion rate of customers you talk to
Question How are customer conversations impacting conversion? Sources Intercom (see full visual schema here ) Results Query select count(distinct segment_users.…
- 3 yrs ago
- 19,380
- 1
- Warehouses
Zendesk + Segment – trending ticket tags
Question What are the most common tickets in a free trial, and how are those tags trending over time? Sources Zendesk (see full visual schema here ) Analytics.…
- 3 yrs ago
- 17,087
- 1
- Warehouses
Zendesk – weekly average first response time
Question How fast is your support team responding to customer support tickets? Sources Zendesk (full schema here ) Results Query select date_trunc('week', created_at) week,…
- 3 yrs ago
- 17,356
- 1
- Warehouses
Zendesk + Stripe – ticket load by plan type
Question What is the support load by subscription plan? Sources Zendesk (full schema here ) Stripe (full schema here ) Results Query select stripe_plans.…
- 3 yrs ago
- 17,640
- 1
- Warehouses
Zendesk – customer ticket creation
Question Who created a ticket? Who did not create a ticket? These sub queries are best applied to other tables if you want to join in from your user database or from the `identifies` table.…
- 3 yrs ago
- 16,896
- 1
- Warehouses
Zendesk – how many tickets are created by month?
Question How many Zendesk tickets are created by month? Sources Zendesk (full schema here ) Query select date_trunc('month', t.created_at) as month, count(t.id) from zendesk.…
- 3 yrs ago
- 16,345
- 1
- Warehouses
Zendesk – tickets by topic
Question How many tickets are created by topic? This is an important topic if you use the `topic` field in Zendesk. For our support team, we use that field to categorize each ticket by the product…
- 3 yrs ago
- 18,645
- 1
- Warehouses
Stripe + Segment – average revenue by first page viewed
Question What is the average revenue according to the first page viewed? Sources Stripe (full schema here ) Analytics.js (docs here ) Results Query with -- Get the revenue by customer stripe_revenue…
- 3 yrs ago
- 17,280
- 1
- Warehouses
Stripe – average revenue by plan
Question What is the annual revenue you are generating by plan? Sources Stripe (full schema here ) Results Query select date_trunc('month', charges.created), plans.name plan, count(distinct charges.…
- 3 yrs ago
- 16,551
- 1
- Warehouses
Stripe + Zendesk – ticket load by plan type
Question What is the support load by subscription plan? Sources Zendesk (full schema here ) Stripe (full schema here ) Results Query select stripe_plans.…
- 3 yrs ago
- 19,311
- 2
- Warehouses
Salesforce + Zendesk – popular tags by stage
Question What types of types of tickets are your prospects sending to your success team? Sources Salesforce (full schema here ) Zendesk (full schema here ) Results Query with tickets as ( select *…
- 3 yrs ago
- 16,401
- 1
- Warehouses
Salesforce + Segment – popular content leading to closed deals
Question What blog content is viewed by successful closed deals? Sources Salesforce (see full visual schema here ) Analytics.js (docs here ) Results Query select p.url, count(distinct p.…
- 3 yrs ago
- 17,406
- 1
- Warehouses

1 2 Next

Warehouses

Facebook Ads / Google Adwords + Google Adwords + Segment - Cross Platform Ad Level Comparisons

Facebook Ads / Google Adwords + Salesforce + Stripe + Segment - Returns on Ad Campaigns

Facebook Ads / Google Adwords + Stripe + Segment - Cross Platform Ad Comparison

Facebook Ads / Google Adwords + Segment - First or Last Touch Attribution

Facebook Ads / Google Adwords + Zendesk + Segment - Ticket Load

Mandrill – emails over customer lifecycle

Hubspot – campaign performance

Hubspot + Segment – conversion rate of email campaigns

Mandrill + Stripe – revenue per campaign

Hubspot + Stripe – revenue per campaign

Mandrill + Segment – conversion of email campaign to signup

Intercom + Stripe – revenue impact of support

Intercom – conversations by customer

Intercom + Segment – conversion rate of customers you talk to

Zendesk + Segment – trending ticket tags

Zendesk – weekly average first response time

Zendesk + Stripe – ticket load by plan type

Zendesk – customer ticket creation

Zendesk – how many tickets are created by month?

Zendesk – tickets by topic

Stripe + Segment – average revenue by first page viewed

Stripe – average revenue by plan

Stripe + Zendesk – ticket load by plan type

Salesforce + Zendesk – popular tags by stage

Salesforce + Segment – popular content leading to closed deals

Home