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
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
New deals by month
Retrieves the number of new deals created each month from Hubspot and organizes them by month.
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.
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.
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.
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.
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.
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.
Google ads
Account Report
Generates an account report for the Google Ads integration. It retrieves data from the `google_ads.account_stats` and `google_ads.account` tables, and calculates various metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions. The results are grouped by date, account ID, account name, currency code, auto tagging status, and time zone.
Ad Report
Generates an ad report by joining data from various tables in the Google Ads integration. It retrieves information such as date, account ID and name, campaign name and ID, ad group name and ID, ad ID and name, ad status and type, display URL, final URLs, spend, impressions, clicks, CPC, CTR, CPM, and conversions. Additional columns can be manually specified if needed.
Ad-group Report
Generates an ad-group report by retrieving data from the Google Ads integration. It combines information from various tables such as ad_group_stats, account, campaign, and ad_group to provide metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions. The report is grouped by date, account, campaign, and ad group.
Campaign Report
Generates a campaign report by joining data from the Google Ads integration. It retrieves various metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions for each campaign, along with account information. The template uses common aggregation functions and grouping to summarize the data at the desired level of granularity.
URL Report
Generates a report for URL performance in Google Ads. It retrieves data from various tables, including ad stats, accounts, campaigns, ad groups, and ads. The template calculates metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions. Additional columns can be specified manually if needed.
Ad stats by country monthly
Generates monthly ad statistics by country, including spend, clicks, CPC, cost per conversion, CTR, conversions, conversion value, impressions, and interactions, using data from Google Ads. It groups the data by month and country criterion ID.
Facebook ads
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.
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.
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.
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.
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.
Tiktok ads
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.
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.
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.
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.
Linkedin ads
Account Report
Generates an account report for the LinkedIn Ads integration. It retrieves data from the linkedin_ads.account, linkedin_ads.campaign, and linkedin_ads.ad_analytics_by_campaign tables. The report includes various metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions, grouped by date, account, and other specified columns.
Campaign Group Report
Generates a campaign group report by joining multiple tables from the LinkedIn Ads integration. It retrieves data such as date, account information, campaign group details, status, currency, run schedule, and various performance metrics like spend, impressions, clicks, CPC, CTR, CPM, and conversions. The data is grouped by specific columns for aggregation.
Campaign Report
Generates a campaign report by joining data from the LinkedIn Ads integration. It retrieves information such as the date, account details, campaign name, campaign status, impressions, clicks, spend, and various performance metrics like CPC, CTR, and CPM. The data is grouped by different columns and can be adjusted based on the selected columns.
Creative Report
Generate a report on LinkedIn Ads performance. It combines data from various tables such as creative, campaign, campaign_group, account, and report to provide insights on metrics like spend, impressions, clicks, CPC, CTR, and conversions. The template uses left joins to connect the tables based on their respective IDs and groups the data by specific columns for aggregation.
Economic
Invoice line items
Retrieves invoice line items from the economic integration. It combines data from the "booked_invoices" table and the "products" and "customers" tables to provide information such as the customer name, invoice details, product details, and order by date in descending order.
Order items
Retrieves order items from the "economic" integration. It combines data from the "orders" table and the "customers" table, joining them based on the customer ID. The result is sorted in descending order by date.
Stripe
Get all subscriptions
Extracts a comprehensive overview of Stripe subscriptions. This helps in understanding the status and duration of various subscriptions tied to different customers.
Get subscription plans
Pulls an exhaustive view of Stripe subscriptions, diving into associated items, the specifics of plans, discounts applied through coupons, and additional metrics like the amount discounted and subscription intervals.
Normalize to monthly revenue
This SQL model fetches the monthly subscription amounts, converts them to EUR using average monthly forex rates, and provides insights into revenue streams in a standardized currency.
Identify active subscriptions and sum revenue
This SQL model fetches the monthly revenue per subscription and applies discounts to provide a net monthly amount. It then identifies if a subscription is active or canceled based on the status.
Create time series for each customer
This SQL model captures the monthly recurring revenue (MRR) trend for each customer, including the change in MRR, and provides a rank based on the MRR. It also considers the customer's subscription status and cancellation date.
Summarize Monthly Recurring Revenue (MRR)
This SQL model provides a comprehensive view of the Monthly Recurring Revenue (MRR) by categorizing the revenue into new MRR, contraction MRR, churn MRR, and expansion MRR. It also computes the net new MRR and total MRR over time.
Snapchat ads
Account Report
Generates an account report for the Snapchat Ads integration. It retrieves data from various tables such as ad_daily, account, ads, ad_squads, and campaigns. The report includes metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions, grouped by date, account ID, account name, and currency.
Ad Group Report
Generates an ad group report for Snapchat Ads. It retrieves data from various tables such as ad_squad_daily_report, ad_account, ad_squad, and campaign. The resulting report includes metrics such as date, account ID and name, campaign ID and name, ad group ID and name, currency, spend, impressions, clicks, CPC, CTR, CPM, and conversions.
Campaign Report
Generates a campaign report for Snapchat Ads. It retrieves data from three tables - campaign_daily_report, ad_account, and campaign. The report includes information such as date, account ID and name, campaign ID and name, currency, spend, impressions, clicks, CPC, CTR, CPM, and conversions. The data is grouped by various columns for analysis.
Ad Report
Generates an ad report by integrating with Snapchat Ads. It retrieves data from various tables such as ad_daily_report, campaign, ad_account, and ad. The report includes information such as date, account ID and name, campaign ID and name, ad ID and name, currency, spend, impressions, clicks, CPC, CTR, CPM, and conversions. The data is filtered based on the date and grouped by different columns.
Microsoft ads
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.
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.
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.
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
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.
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.
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.
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.
Amazon ads
Campaign Report
Extracts a comprehensive report of advertising performance for Amazon's Sponsored Products, grouped by campaigns and accounts. This report integrates data from the campaign report with the account and campaign tables to provide metrics such as cost, clicks, and impressions for each campaign.
Ad Group Report
This template extracts a comprehensive breakdown of advertising performance on Amazon, grouped by ad groups for sponsored products. It integrates data from the sponsored product ad group report with the profile and ad group tables to deliver metrics like cost, clicks, and impressions for each ad group.
Keyword Report
This template extracts a comprehensive breakdown of advertising performance on Amazon, grouped by keywords for sponsored products. It integrates data from the sponsored product keyword report with the profile and keyword tables to deliver metrics like cost, clicks, and impressions for each keyword.
Ad Report
This template extracts a comprehensive breakdown of advertising performance on Amazon, grouped by product ads for sponsored products. It integrates data from the sponsored product product ad report with the profile and ad tables to deliver metrics like cost, clicks, and impressions for each product ad.
Xero
Journal Entries Report
This template generates a report on journal entries from Xero, breaking down the data by year and month. It gives you a count of all journal entries and a count of individual journal lines for each journal entry within the specified periods.
Monthly Invoice Summary Report
This template generates a monthly invoice summary report from Xero, organizing the data by year and month. It details various invoice metrics such as the total number and amount of invoices, total tax, total amount due and paid, amount credited, and the number of unique contacts involved.
Invoice Entries Report
This template generates a report on invoice entries from Xero, breaking down the data by year and month. It gives detailed information on each invoice entry including various attributes such as invoice ID, description, quantity, and so on.
Tracking Details Report
This template generates a report that brings together tracking details from journal and invoice records, outlining essential details including source type, source ID, and financial particulars such as net and gross amounts, tax details, and tracking categories.
Monthly Sales Report
This template creates a monthly sales report by analyzing invoice data. It breaks down sales numbers and revenue per product/service and item code on a monthly basis, offering a clear view of how different offerings perform over time.
Intercom
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.
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.
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.
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.
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.
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 Selling Partner API
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
