Weld logo

Weld SQL templates

Get inspiration on how to model your data, with our collection of foundational SQL templates we have battle-tested at Weld. All the templates will work with data from the Weld Data Integrations.

shopify logo

Shopify

shopify logo
Customer growth by month
Analyzes the growth of customers on a monthly basis using data from the Shopify integration. It selects the month and counts the number of new customers, grouping the results by month. The output is ordered by month.
shopify logo
Monthly sales
Retrieves monthly sales data from a Shopify integration by joining order and order line tables and grouping the results by month. The template uses the date_trunc function to extract the month from the created_at date field and calculates the total sales by multiplying the quantity and price fields.
shopify logo
Top ten products
Retrieves the top ten products based on the quantity sold. It uses an inner join to combine order lines and orders data and groups the results by product title. The template is limited to only display the top ten products based on the count of orders.
shopify logo
Yealy revenue
Retrieve the total sales revenue for each year by joining the order and order line tables and grouping the results by year. The template uses the date_trunc function to extract the year from the created_at column.
shopify logo
Orders with tax, shipping , refunds and discounts
Retrieves detailed information about orders, including tax, shipping, refunds, and discounts, from a Shopify integration. It uses several subqueries to calculate various metrics such as total order line discounts, order level discounts, and line return.
shopify logo
Customer cohort
Generates a customer cohort report for a Shopify integration. The report includes information on customer revenue, cohort, and customer retention over time.
hubspot logo

Hubspot

hubspot logo
New companies by month
Retrieves a list of new companies created each month from the HubSpot integration and groups them by month. The results are ordered by month.
hubspot logo
Closed won deals by owner
Retrieves the number of closed won deals by owner from HubSpot and orders them in descending order based on the number of deals.
hubspot logo
Closed lost deals by owner
Retrieves the number of closed lost deals by owner from HubSpot and orders them by the highest number of deals. It uses a left join to match the owner ID from the deals table to the owners table and concatenates the first and last name of the owner for readability.
hubspot logo
Closed won deals amount by owner
Retrieves the total amount of closed won deals for each owner in HubSpot and orders them in descending order.
hubspot logo
Emails by owner
Retrieves the number of emails sent by each owner in HubSpot by joining the engagements and owners tables and grouping the results by owner.
hubspot logo
New deals by month
Retrieves the number of new deals created each month from HubSpot and organizes them by month.
hubspot logo
Calls by owner
Retrieves the number of calls made by each owner in HubSpot and displays it alongside their first and last name. It uses a left join to connect the engagements and owners tables and filters the results to only include calls. The output is grouped by owner.
hubspot logo
Meetings by owner
Retrieves the number of meetings organized by each owner in HubSpot and displays it alongside their first and last name. It uses a left join to connect the engagements and owners tables and filters the results to only include meetings. The output is grouped by owner.
hubspot logo
Tasks completed by owner
Retrieves the number of completed tasks for each owner in HubSpot, using a left join between the engagements and owners tables. The result includes the owner's first and last name, as well as the count of completed tasks.
hubspot logo
Deals by deal stage
Retrieves the number of deals in each stage of the sales pipeline from HubSpot and groups them by their respective stage labels.
hubspot logo
Conversion from contacts to deals by owner
Converts contacts to deals by owner in HubSpot, displaying the owner's name, the number of contacts they have, the number of deals they have, and the conversion rate between the two. It uses left joins to connect the necessary tables and groups the results by owner.
hubspot logo
New contacts by month
Retrieves the number of new contacts added to HubSpot each month and groups them by month. It uses the date_trunc function to extract the month from the createdAt field and orders the results by month.
facebook-ads logo

Facebook ads

facebook-ads logo
Account Report
Generates an account report for the Facebook Ads integration. It retrieves data from the "ad_insight" and "account" tables and calculates various metrics such as spend, impressions, clicks, CPC, and CTR. The report is grouped by date, account ID, account name, account status, business country code, created time, and currency.
facebook-ads logo
Ad Report
Integrates with Facebook Ads and retrieves data from various tables such as ad_insight, account, campaign, ad_set, and ad. It selects specific columns from these tables and performs calculations to generate aggregated metrics such as spend, reach, impressions, clicks, CPC, CTR, and CPM. The template then joins these tables based on specific conditions and groups the results by certain columns.
facebook-ads logo
Ad-set Report
Generates an ad-set report by integrating with Facebook Ads. It retrieves data from various tables such as ad_set_insight, account, campaign, ad_set, and ad. The template then selects specific columns and performs calculations to provide insights on metrics like spend, reach, impressions, clicks, CPC, CTR, and CPM. The final result is grouped by different dimensions such as date, account, campaign, and ad set.
facebook-ads logo
Campaign Report
Creates a campaign report by integrating with Facebook Ads. It retrieves data from three tables: campaign_insight, account, and campaign. The template calculates various metrics such as clicks, impressions, spend, CPC, and CTR, and groups the results by date, account, and campaign.
facebook-ads logo
URL Report
Generates a URL report for Facebook Ads integration. It retrieves data from various tables such as ad_insight, creative, account, ad, ad_set, and campaign. The template selects specific columns like date, account ID and name, campaign ID and name, ad set ID and name, ad ID and name, creative ID and name, and extracts UTM parameters from the URL. It also calculates the sum of clicks, impressions, and spend, and groups the data accordingly.
facebook-ads logo
Conversion Insights
This SQL template retrieves conversion insights data from Facebook Ads, including various metrics such as spend, reach, impressions, clicks, and more. It joins data from different tables like ad_insight, ad, and ad_roas_insight_conversion_insights to provide a comprehensive analysis of ad performance.
tiktok-ads logo

Tiktok ads

tiktok-ads logo
Account Report
Generates an account report for the TikTok Ads integration. It retrieves data from the `ad_daily_report`, `advertiser`, and `ad` tables, and joins them based on specific conditions. The resulting report includes aggregated metrics such as impressions, clicks, spend, reach, conversion, likes, comments, shares, profile visits, follows, video watched durations, and video view percentages.
tiktok-ads logo
Ad Group Report
Generates an ad group report for the TikTok Ads integration. It retrieves data from various tables such as ad_group_daily_report, ad_group, advertiser, and campaign. The report includes metrics like impressions, clicks, spend, reach, conversion, and engagement metrics such as likes, comments, and shares. The data is grouped by date, account, campaign, and ad group.
tiktok-ads logo
Campaign Report
Generates a campaign report for the TikTok Ads integration. It retrieves data from the `campaign_daily_report`, `campaign`, and `advertiser` tables. The report includes metrics such as clicks, impressions, spend, reach, conversions, likes, comments, shares, profile visits, follows, video views, and various calculated metrics like CPC, CTR, and CPM. The data is grouped by date, account ID, account name, campaign ID, and campaign name.
tiktok-ads logo
Ad Report
Generates an ad report by joining multiple tables from the TikTok Ads integration. It retrieves data such as impressions, clicks, spend, reach, conversions, likes, comments, shares, and various video metrics for each ad group. The report also includes information about the advertiser, campaign, account, currency, gender, audience type, and budget.
microsoft-ads logo

Microsoft ads

microsoft-ads logo
Account Report
Generates an account report by integrating with Microsoft Ads. It retrieves data from the ad_performance_daily_report and account tables, joins them based on the account_id, and calculates metrics such as clicks, impressions, and spend. The resulting report includes information about the date, account name, account ID, time zone, device OS, device type, network, and currency code.
microsoft-ads logo
Ad Group Report
Generates a report that combines data from the "ad_performance_daily_report," "ad_group," "campaign," and "account" tables in the Microsoft Ads integration. It joins these tables based on specific columns and calculates aggregated metrics such as clicks, impressions, and spend. The resulting report includes information on the date, account, campaign, ad group, device, network, and currency code.
microsoft-ads logo
Campaign Report
Generates a campaign report by integrating with Microsoft Ads. It combines data from the campaign performance daily report, campaign, and account tables. The joined table includes information such as date, account name and ID, campaign name and ID, campaign type and status, device OS and type, network, currency code, clicks, impressions, and spend.
microsoft-ads logo
Ad Report
Generates an ad report by joining data from multiple tables related to Microsoft Ads. It retrieves information such as date, account name and ID, campaign name and ID, ad group name and ID, ad name and ID, device OS, device type, network, currency code, clicks, impressions, and spend. The template performs a left join on the specified tables and groups the data based on selected columns. The final result includes all columns from the joined data.
pinterest-ads logo

Pinterest ads

pinterest-ads logo
Account Report
Generates an account report for Pinterest Ads integration. It retrieves data from the `pinterest_ads.ad_account_report` table and joins it with the `pinterest_ads.ad_account` table. The report includes information such as date, account name, account ID, currency code, country, spend, impressions, clicks, CPC, CTR, CPM, and total conversions. The data is grouped by various columns specified in the query.
pinterest-ads logo
Ad Group Report
Generates an ad group report for the Pinterest Ads integration. It retrieves data from various tables such as ad groups, campaigns, and advertisers. The report includes information such as the date, account name and ID, campaign name and status, ad group name and status, spend, impressions, clicks, CPC, CTR, CPM, and total conversions.
pinterest-ads logo
Campaign Report
Generates a campaign report for Pinterest Ads integration. It retrieves data from the `pinterest_ads.campaign_report`, `pinterest_ads.campaign`, and `pinterest_ads.ad_account` tables. The report includes information such as date, account name and ID, campaign name and status, spend, impressions, clicks, CPC, CTR, CPM, and total conversions.
pinterest-ads logo
Ad Report
Generates an ad report for the Pinterest Ads integration. It retrieves data from multiple tables, including ad reports, ad accounts, campaigns, and ads. The report includes information such as date, account name and ID, campaign name and ID, ad name, status, type, created time, spend, impressions, clicks, CPC, CTR, CPM, and total conversions. The data is grouped by various columns for analysis.
intercom logo

Intercom

intercom logo
Monthly Conversations By Team
Extracts monthly conversation data by team from the Intercom integration. It selects the month, team ID, team name, and ticket count from the conversation table. It then joins the conversation table with the team table and groups the results by month, team ID, and team name, ordering them by month and ticket count in descending order.
intercom logo
Monthly Tickets By Team
Extracts monthly ticket data for each team, including the team ID, team name, and the count of tickets. It uses the Intercom integration and joins the ticket and team tables based on the team assignee ID. The results are grouped by month, team ID, and team name, and ordered by month and ticket count in descending order.
intercom logo
Monthly Ticket Resolution Time
Extracts the monthly ticket resolution time for a specific integration (intercom). It selects the month, team ID, team name, and average resolution time in days. It joins the ticket and team tables, filters for resolved tickets, groups the results by month and team, and sorts them in descending order by month and ascending order by average resolution time.
intercom logo
Monthly Tags By Conversation
This SQL template extracts monthly tags by conversation from the Intercom integration. It uses a common table expression (CTE) to join the conversation and conversation_tag tables, and then retrieves the month, tag name, and tag count for each conversation. The results are grouped by month and tag name, and ordered by month in descending order.
intercom logo
Monthly Average Conversations Rating
Extracts the monthly average rating for conversations from the Intercom integration. It selects the truncated month and calculates the average rating for conversations that have a non-null rating value. The results are grouped by month and ordered in descending order.
intercom logo
Count of Tickets By State
Extracts the count of tickets grouped by month and ticket state from the Intercom integration. It uses the `date_trunc` function to truncate the `created_at` column to the month level. The result is then ordered by month in descending order, followed by the ticket count in descending order.
amazon-mws logo

Amazon Selling Partner API

amazon-mws logo
Monthly Sales Performance Reports
Extracts monthly sales performance reports for Amazon integration. It calculates the total sales amount, number of orders, and total items sold for each month. Additionally, it calculates the average order value by dividing the total sales amount by the number of orders. The results are ordered in descending order by month.
amazon-mws logo
Monthly Inventory Reports
Extracts monthly inventory reports for Amazon integration. It retrieves data from the Amazon Selling API to calculate various metrics such as total items listed, total fulfillable quantity, total damaged items, expired items, total unfulfillable quantity, items in transit, and items receiving. The results are ordered by month in descending order.
amazon-mws logo
Monthly Financial Analysis
Generates a monthly financial analysis for Amazon integration. It calculates the gross revenue, refunds, net revenue, average item price, average promotion discount, average shipping price, and average shipping tax. The analysis is based on data from the settlement report and orders by last updated date report. The results are sorted in descending order by month.
amazon-mws logo
Monthly Shipping and Delivery Reports
Generates a monthly shipping and delivery report for Amazon integration. It calculates various metrics such as the number of orders, total items shipped and unshipped, average days to ship, and the count of premium and regular orders. It also includes information on different shipping service levels and the number of orders for each level. The final result is sorted by month and shipping service level.
amazon-mws logo
Monthly Returns Analysis
Extracts a monthly returns analysis for Amazon integration. It calculates various metrics such as total returns, total refunded amount, in-policy returns, out-of-policy returns, prime returns, and non-prime returns. Additionally, it provides information on return reasons and their respective counts. The results are sorted by month and reason count in descending order.
amazon-mws logo
Monthly Order Update Tracking Report
Extracts a monthly order update tracking report for Amazon integration. It retrieves data from the "orders_by_last_updated_date_report" table and calculates various metrics such as total orders updated, total updates, average price updated, status breakdown, and channel breakdown. The results are sorted by month, status count, and channel count in descending order.
klaviyo logo

Klaviyo

klaviyo logo
Monthly User Engagement per Campaign
Calculates the monthly user engagement per campaign using data from Klaviyo integration. It extracts the year, month, campaign ID, event name, and counts the number of unique persons for specific events. It then aggregates the counts for each event type and campaign, grouping them by year, month, campaign ID, and campaign name. The final result is ordered by year, month, and campaign name.
klaviyo logo
Monthly Campaign Status Breakdown by List
Generates a monthly breakdown of campaign status for a specific integration with Klaviyo. It retrieves the number of campaigns, the number of campaigns sent, the number of campaigns in draft and scheduled status, as well as other campaign statuses if needed. Additionally, it includes the list name, the number of lists used, and the last sent date. The results are grouped by campaign month, list name, and template name, and ordered in descending order of the campaign month.
klaviyo logo
Monthly Flow Events
Generates a report of monthly flow events from the Klaviyo integration. It calculates the total number of events triggered and the number of distinct persons triggered for each flow in a given month. The result includes the month, flow ID, flow name, flow status, trigger type, trigger filter, customer filter, total events triggered, and distinct persons triggered, ordered by month and flow name.
klaviyo logo
Email Events
Retrieves various event details from the Klaviyo integration, including the event ID, timestamp, event name, custom properties, Klaviyo properties, UUID, person ID, metric ID, campaign ID, flow ID, flow, attributed event ID, message ID, send cohort, send timestamp, and order ID (if the event name is "Placed Order"). The results are ordered by the event timestamp in descending order.
klaviyo logo
Events Flow Campaign Report
Retrieves data from the Klaviyo integration to generate an Events Flow Campaign Report. It combines information from the events, campaign, and flow tables to provide details such as event timestamps, event names, custom properties, Klaviyo properties, metric IDs, campaign IDs, person IDs, flow IDs, attributed event IDs, message IDs, send cohorts, send timestamps, order IDs, flow names, flow statuses, campaign subjects, and campaign sent timestamps. The final result is sorted in descending order based on the event timestamps.
instagram-business logo

Instagram business

instagram-business logo
Profile Overview Report
Profile overview report for Instagram Business integration. It combines data from different tables to calculate average follows count, average followers count, average profile views, and average website clicks for each user on a monthly basis. The results are then ordered by month.
instagram-business logo
User Engagement and Outreach Report
Generates a report on user engagement and outreach for an Instagram Business integration. It combines data from various tables to calculate average follower count, profile views, website clicks, like count, comments count, feed impressions, and feed reach on a monthly basis. The result is sorted by month and user ID.
instagram-business logo
Media Performance Report
Generates a media performance report for Instagram Business integration. It combines metrics from the media_history and media_insights tables to provide a comprehensive view of monthly media performance. The report includes metrics such as total media posts, likes, comments, carousel engagement, impressions, reach, reel likes, plays, feed engagement, impressions, and reach.
instagram-business logo
Media Insights Deep Dive
Retrieves various metrics for different types of media posts (carousel, reel, story, feed). It also calculates engagement rates for each type of media. The template joins the media data with the media history table to include additional information such as caption, timestamp, media type, likes count, and comments count. The final result is sorted by month and media posted date in descending order.
instagram-business logo
Growth Analysis
Calculates the growth rates of various metrics for an Instagram Business account. It retrieves monthly data on average followers, average follows, total feed engagement, total carousel engagement, and total reel likes. It then calculates the growth rate percentages for each metric compared to the previous month. The results are sorted by month in descending order.
instagram-business logo
Engagment Metric Analysis
Calculates various engagement metrics for Instagram Business accounts on a monthly basis. It retrieves data such as the total number of media posts, likes, comments, feed engagement, carousel engagement, reel likes, reel comments, story replies, and story exits. Additionally, it calculates average engagement per post metrics, including average likes, comments, feed engagement, carousel engagement, reel likes, and reel comments per post. The results are ordered by month in descending order.
clickup logo

Clickup

clickup logo
Team Overview Report
Generates a team overview report by retrieving data from the ClickUp integration. It calculates the total number of tasks, total time spent, task status, and task count for each team. The report includes team ID, name, color, total tasks, total time spent, task status, and tasks in each status. The results are ordered by team ID.
clickup logo
Task Dependency Analysis
Performs a task dependency analysis in ClickUp. It retrieves information about task dependencies, including task names, statuses, and dependent task details. It also calculates the total number of dependencies for each task and determines if a task is a potential blocker based on its status and the status of its dependent tasks. The results are sorted by task ID and dependent task ID.
clickup logo
User Activity and Performance Report
Generates a user activity and performance report by integrating with ClickUp. It retrieves information such as the total number of tasks, tasks completed, tasks overdue, total time spent, task priority, task status, and task count by priority and status for each user. The results are ordered by user ID.
clickup logo
Monthly Time Tracking and Billing Report
Generates a monthly time tracking and billing report using data from ClickUp. It calculates the billable and non-billable time for each user, as well as the total time spent. The report also includes the billable amount, calculated based on a hypothetical rate of $50 per hour.
clickup logo
Task Checklist Progress
Generates a report on the progress of task checklists in ClickUp. It retrieves the total number of checklist items, resolved items, and unresolved items for each task. It calculates the completion percentage based on the resolved items and total checklist items. The results are sorted by completion percentage and task ID.
google-analytics logo

Google analytics

google-analytics logo
Account Report
This SQL template generates a comprehensive account report by aggregating user statistics, campaign performance, and event data from Google Analytics. It combines these metrics to provide insights on total users, new users, sessions, user engagement, campaign details, and event revenue for each day. The results are ordered by date in descending order.
google-analytics logo
User Activity Overview
This SQL template aggregates monthly user activity data from Google Analytics, including total users, new users, sessions, average sessions per user, screen page views, and user engagement duration.
google-analytics logo
Campaign Report
This SQL template generates a monthly summary report of campaign performance metrics from Google Analytics data. It aggregates data such as total users, sessions, engaged sessions, events per session, engagement rate, event count, conversions, total revenue, and user engagement duration. The results are ordered by period and total users in descending order.
google-analytics logo
Channel Traffic Overview
This SQL template provides a monthly overview of channel traffic from Google Analytics, summarizing metrics such as total users, sessions, engaged sessions, events per session, engagement rate, event count, conversions, total revenue, and user engagement duration, grouped by channel.
google-analytics logo
Event Performance
This SQL template aggregates Google Analytics event data by month, summarizing event counts, user counts, average events per user, and total revenue for each event. It groups the data by period and event name, then orders the results by event count in descending order.
google-analytics logo
Page Path Analysis
This SQL template aggregates Google Analytics data to provide a monthly overview of page paths, including metrics such as screen page views, total users, new users, event count, conversions, total revenue, and user engagement duration, grouped by traffic source.
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.