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
Ad stats by ad group monthly
Generates monthly ad statistics by ad group, including spend, clicks, cost per click, cost per conversion, click-through rate, conversions, conversion value, impressions, and interactions. It uses data from the Google Ads integration and groups the results by month and ad group name.
Ad stats by ad monthly
Generates monthly ad statistics for Google Ads, including spend, clicks, cost per click, cost per conversion, click-through rate, conversions, conversion value, impressions, and interactions. It groups the data by month and ad name.
Ad stats by campaign monthly
Generates monthly ad statistics by campaign, including spend, clicks, cost per click, cost per conversion, click-through rate, conversions, conversion value, impressions, and interactions, using data from Google Ads.
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
Ad stats by ad monthly
Generates monthly ad statistics for Facebook Ads, including spend, impressions, clicks, click-through rate (CTR), cost per click (CPC), cost per thousand impressions (CPM), reach, and frequency. It groups the data by month and ad name.
Ad stats by campaign monthly
Retrieves monthly ad statistics by campaign from Facebook Ads, including spend, impressions, clicks, CTR, CPC, CPM, reach, and frequency. It groups the data by month and campaign name.
Ad stats by ad_set monthly
Retrieves monthly ad statistics for a specific Facebook ad set, including spend, impressions, clicks, click-through rate (CTR), cost per click (CPC), cost per thousand impressions (CPM), reach, and frequency. It groups the data by month and ad set name.
Tiktok ads
Ad stats by ad monthly
Generates monthly ad statistics for TikTok ads, including clicks, conversions, impressions, conversion rate, CTR, CPM, likes, follows, reach, profile visits, shares, cost, cost per conversion, and CPC. It pulls data from the ad daily report and ad tables and groups the results by month and ad name.
Ad stats by campaign monthly
Generates monthly ad statistics by campaign for TikTok Ads, including metrics such as clicks, conversions, impressions, and cost per conversion. It pulls data from the campaign daily report and campaign tables and groups the results by month and campaign name.
Ad stats by ad_group monthly
Generates monthly ad statistics by ad group for TikTok Ads integration, including clicks, conversions, conversion rate, impressions, CTR, CPM, likes, follows, reach, profile visits, shares, cost, cost per conversion, and CPC. It uses a left join to combine data from the ad group daily report and ad group tables.
Linkedin ads
Ad stats by campaign monthly
Generates monthly ad statistics by campaign for LinkedIn Ads, including cost, clicks, impressions, engagements, shares, CTR, CPM, CPC, leads, and actions. It uses data from the ad_analytics_by_campaign and campaign tables and groups the results by month and campaign name.
Ad stats by campaign_group monthly
Generates monthly ad statistics by campaign group for LinkedIn Ads, including cost, clicks, impressions, engagements, shares, CTR, CPM, CPC, leads, and actions. It pulls data from ad analytics, campaign, and campaign group tables and groups the results by month and campaign group name.
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
Daily Ad Report
Extracts a detailed overview of daily advertising performance on Snapchat, aggregated by account. This combines data from various ad-related tables to provide metrics like swipes, impressions, and spend.
Aggregated Daily Campaign Report
Extracts a day-by-day breakdown of advertising performance on Snapchat, grouped by campaigns and accounts. It integrates data from the daily campaign report with the campaign and account tables to deliver metrics like swipes, impressions, and spend for each campaign.
Aggregated Daily Ad Report
Extracts a daily breakdown of advertising performance on Snapchat, specifically focusing on individual ads. This integrates data from the daily ad report with the ad, creative, and account tables to deliver metrics like swipes, impressions, and spend for each ad.
Microsoft ads
Aggregated Daily Account Report
Extracts a daily breakdown of advertising performance on Bing, grouped by accounts. It integrates data from the daily account performance report with the account table to deliver metrics like clicks, impressions, and spend for each account.
Aggregated Daily Campaign Performance Report
Extracts a daily breakdown of advertising performance on Bing, grouped by campaigns. It integrates data from the daily campaign performance report with the campaign table to deliver metrics like clicks, impressions, and spend for each campaign.
Aggregated Daily Ad Group Performance Report
Extracts a daily breakdown of advertising performance on Bing, grouped by ad groups. It integrates data from the daily ad group performance report with the ad group table to deliver metrics like clicks, impressions, and spend for each ad group.
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.
