Analyzing Loan Performance with Autonmis: Unlocking Insights from Multi-Source Data

In today’s competitive financial landscape, understanding the performance of loans influenced by various marketing campaigns is critical. With Autonmis, you can combine insights from Google Ads, Salesforce CRM, and HubSpot to create a detailed analysis of your loan portfolios. This guide will walk you through how Autonmis empowers businesses to analyze marketing influence on loans, helping decision-makers allocate resources effectively and maximize return on investment.

What is Autonmis?

Autonmis is an AI-enhanced data platform built for businesses to make data-driven decisions. The starter edition in Autonmis integrates multiple data sources into a unified data warehouse, equipped with features like:

• SQL Editor for custom queries,
• Dashboards for real-time insights,
• Notebooks to save and share analysis, and
• Interactive Charts to visualize metrics dynamically.

With these capabilities, Autonmis allows users to explore data connections across marketing, sales, and finance effortlessly.

Step 1: Setting Up the Analysis with Key Questions

Our goal is to uncover valuable insights about loans influenced by different marketing channels. Key questions to guide our analysis include:

• How much loan disbursement does each Google Ads campaign generate?
• Which Salesforce campaigns lead to the most significant returns?
• How does lead source impact the loan’s interest rate?

Step 2: Exploring the Data Schema

This analysis uses tables from Google Ads, Salesforce CRM, HubSpot, and public loan data. Here’s a breakdown:

• Google Ads Tables (google_ads_analytics.google_ads_loan_account, google_ads_analytics.Campaigns): Stores campaign data, allowing us to trace loans back to specific ad campaigns.
• Salesforce CRM Tables (salesforce_crm.salesforce_opportunity_loan_account, salesforce_crm.Opportunities): Links Salesforce opportunities to loans, enabling campaign performance analysis.
• HubSpot Marketing Tables (hubspot_marketing.hubspot_campaign_loan_account, hubspot_marketing.Contacts): Provides insights into customer lifecycle stages and campaign impact on loans.
• Loan Data Tables (loan_account, loan_txns): The foundational loan data tables containing loan amounts, transaction history, and repayment details.

Each dataset plays a specific role in answering our business questions, ensuring a 360-degree view of campaign effectiveness and financial impact.

Step 3: Running Key Queries for Comprehensive Insights

This section contains several SQL queries executed in Autonmis that reveal insights into loan disbursements, campaign influence, customer acquisition channels, and loan repayment patterns.

Query 1: Total Loan Amount by Google Ad Group

In addition to analyzing campaigns, we can break down loan performance by ad groups within Google Ads campaigns. This query calculates the total loan amount influenced by each ad group

1  SELECT
2      gag.AdGroupName,
3      SUM(la.loan_amount) AS TotalLoanAmountByAdGroup
4  FROM 
5      public.loan_account la
6  JOIN 
7      google_ads_analytics.google_ads_loan_account gala ON la.loan_account_number = gala.loan_account_number
8  JOIN 
9      google_ads_analytics.AdGroups gag ON gala.ad_group_id = gag.AdGroupID
10  GROUP BY 
11      gag.AdGroupID, gag.AdGroupName
12  ORDER BY 
13      TotalLoanAmountByAdGroup DESC;

Output Interpretation: This query provides insights into which ad groups within campaigns are most effective. Marketing teams can use this data to refine ad targeting strategies, directing resources toward top-performing ad groups to maximize loan generation.

Query 2: Average Loan Amount by Salesforce Campaign

To understand the loan impact of Salesforce campaigns, we calculate the average loan amount associated with each campaign. This provides a metric for campaign effectiveness in terms of loan quality and average disbursement.

1  SELECT
2      sf.CampaignName,
3      AVG(la.loan_amount) AS AvgLoanAmountByCampaign
4  FROM
5      public.loan_account la
6  JOIN 
7      salesforce_crm.salesforce_opportunity_loan_account sola ON la.loan_account_number = sola.loan_account_number
8  JOIN 
9      salesforce_crm.Campaigns sf ON sola.campaign_id = sf.CampaignID
10  GROUP BY 
11      sf.CampaignID, sf.CampaignName
12  ORDER BY 
13      AvgLoanAmountByCampaign DESC;

Output Interpretation: By focusing on the average loan amount per campaign, this query helps identify campaigns that bring in high-value loans, allowing marketing and sales teams to focus on campaigns that align with high-value disbursements.

Query 3: Total Overdue Charges by Google Ad Campaign

This query focuses on the financial quality of loans by calculating total overdue charges per Google Ad campaign. Campaigns leading to higher overdue charges may indicate a riskier customer segment or the need for improved customer support.

1  SELECT
2      gac.CampaignName,
3      SUM(lt.overdue_charges) AS TotalOverdueCharges
4  FROM 
5      public.loan_txn_ledger lt
6  JOIN 
7      google_ads_analytics.google_ads_loan_account gala ON lt.loan_account_number = gala.loan_account_number
8  JOIN 
9      google_ads_analytics.Campaigns gac ON gala.campaign_id = gac.CampaignID
10  GROUP BY 
11      gac.CampaignID, gac.CampaignName
12  ORDER BY 
13      TotalOverdueCharges DESC;

Output Interpretation: Campaigns with high overdue charges may signal a need for targeted follow-up or additional credit assessments. This insight helps financial teams manage risk by refining customer acquisition criteria within Google Ads.

Query 4: Customer Acquisition by Lead Source with HubSpot Contacts

This query calculates the number of new loans per lead source, distinguishing between Salesforce and HubSpot. Understanding customer acquisition by lead source can help teams focus efforts on channels with better conversion rates or customer retention.

1  SELECT
2      hc.lead_source,
3      COUNT(DISTINCT la.loan_account_number) AS TotalLoansByLeadSource
4  FROM 
5      public.loan_account la
6  JOIN 
7      hubspot_marketing.Contacts hc ON la.contact_id = hc.contact_id
8  GROUP BY 
9      hc.lead_source
10  ORDER BY 
11      TotalLoansByLeadSource DESC;

Output Interpretation: This query output reveals which lead sources (e.g., HubSpot campaigns or Salesforce leads) bring in the most customers, enabling more precise marketing strategies that align resources with top-performing lead channels.

Query 5: Total Principal Paid by Loan Type

This query calculates the total principal repaid by loan type, providing insights into loan repayment trends. By examining loan repayment by type, financial teams can monitor repayment patterns and assess the effectiveness of different loan programs.

1  SELECT
2      la.loan_type,
3      SUM(rs.principal_paid) AS TotalPrincipalPaid
4  FROM 
5      public.loan_account la
6  JOIN 
7      public.repayment_schedule rs ON la.loan_account_number = rs.loan_account_number
8  GROUP BY 
9      la.loan_type
10  ORDER BY 
11      TotalPrincipalPaid DESC;

Output Interpretation: This output reveals which loan types are being repaid the fastest or have the highest principal repayments, giving finance teams actionable data to adjust loan offerings or repayment terms to optimize cash flow.

Query 6: Average Interest Rate by Customer Lifecycle Stage in HubSpot

Lifecycle stages in HubSpot can offer insights into customer engagement and potential for loan upsell. This query calculates the average interest rate by customer lifecycle stage, allowing segmentation analysis based on the relationship phase (e.g., new customer, returning customer)

1  SELECT
2      hc.lifecycle_stage,
3      AVG(la.interest_rate) AS AvgInterestRateByStage
4  FROM 
5      public.loan_account la
6  JOIN 
7      hubspot_marketing.Contacts hc ON la.contact_id = hc.contact_id
8  GROUP BY 
9      hc.lifecycle_stage
10  ORDER BY 
11      AvgInterestRateByStage;

Output Interpretation: By identifying which lifecycle stages correspond with favorable interest rates, finance and marketing teams can adjust their efforts to target stages that align with low-interest, high-value loans, leading to a more favourable loan portfolio.

By running these queries, Autonmis allows teams to gain actionable insights into various aspects of loan performance:

• Campaign and Ad Group Performance: Google Ads data provides clarity on which campaigns and ad groups drive the highest loan disbursements and overdue charges, allowing better resource allocation.
• Loan Value by Salesforce Campaign: Salesforce CRM data helps teams focus on high-value campaigns.
• Customer Acquisition and Lifecycle: HubSpot and Salesforce data show customer acquisition effectiveness by lead source and help pinpoint the most valuable stages in the customer lifecycle.

Conclusion

With Autonmis, businesses can go beyond traditional reporting to perform in-depth analyses across multiple data sources. This example highlights how Autonmis can unlock insights that inform strategies for loan performance optimisation, empowering businesses to make smarter, data-driven decisions.