基于Stripe、Postgres和Gmail的订阅收入优化器 - 预测性留存

• •Google 账号和 Gmail API 凭证
• •PostgreSQL 数据库连接信息
• •可能需要目标 API 的认证凭证

{
  "nodes": [
    {
      "id": "1",
      "name": "每日收入分析",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        240,
        300
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "value": "0 6 * * *"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "2",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        140,
        180
      ],
      "parameters": {
        "width": 240,
        "height": 160,
        "content": "## 收入优化"
      },
      "typeVersion": 1
    },
    {
      "id": "3",
      "name": "收入设置",
      "type": "n8n-nodes-base.set",
      "position": [
        440,
        300
      ],
      "parameters": {
        "values": {
          "number": [
            {
              "name": "churnRiskThreshold",
              "value": 0.7
            },
            {
              "name": "upsellThreshold",
              "value": 0.8
            },
            {
              "name": "retentionDiscount",
              "value": 25
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "4",
      "name": "获取客户分析",
      "type": "n8n-nodes-base.postgres",
      "position": [
        640,
        300
      ],
      "parameters": {
        "query": "SELECT customer_id, usage_score, engagement_score, support_tickets, payment_history, plan_type, mrr FROM customer_analytics WHERE active = true"
      },
      "typeVersion": 1
    },
    {
      "id": "5",
      "name": "分析收入机会",
      "type": "n8n-nodes-base.code",
      "position": [
        840,
        300
      ],
      "parameters": {
        "jsCode": "// Advanced churn prediction and revenue optimization\nconst customers = $input.all();\nconst settings = $node['Revenue Settings'].json;\nconst processedCustomers = [];\n\nfor (const customer of customers) {\n  const data = customer.json;\n  \n  // Calculate churn risk score\n  let churnRisk = 0;\n  \n  // Usage decline factor (40% weight)\n  const usageScore = data.usage_score || 0;\n  if (usageScore < 0.3) churnRisk += 0.4;\n  else if (usageScore < 0.6) churnRisk += 0.2;\n  \n  // Engagement decline (30% weight)\n  const engagementScore = data.engagement_score || 0;\n  if (engagementScore < 0.4) churnRisk += 0.3;\n  else if (engagementScore < 0.7) churnRisk += 0.15;\n  \n  // Support ticket volume (20% weight)\n  const supportTickets = data.support_tickets || 0;\n  if (supportTickets > 5) churnRisk += 0.2;\n  else if (supportTickets > 2) churnRisk += 0.1;\n  \n  // Payment issues (10% weight)\n  const paymentIssues = data.payment_history?.failed_payments || 0;\n  if (paymentIssues > 0) churnRisk += 0.1;\n  \n  // Calculate upsell potential\n  let upsellPotential = 0;\n  if (usageScore > 0.8 && engagementScore > 0.7) {\n    upsellPotential = 0.9;\n  } else if (usageScore > 0.6 && engagementScore > 0.5) {\n    upsellPotential = 0.6;\n  } else if (usageScore > 0.4) {\n    upsellPotential = 0.3;\n  }\n  \n  // Determine actions\n  let recommendedAction = 'monitor';\n  let priority = 'low';\n  \n  if (churnRisk >= settings.churnRiskThreshold) {\n    recommendedAction = 'retention_campaign';\n    priority = 'high';\n  } else if (upsellPotential >= settings.upsellThreshold) {\n    recommendedAction = 'upsell_campaign';\n    priority = 'medium';\n  } else if (churnRisk >= 0.5) {\n    recommendedAction = 'engagement_campaign';\n    priority = 'medium';\n  }\n  \n  // Calculate potential revenue impact\n  const currentMRR = data.mrr || 0;\n  let potentialRevenue = currentMRR;\n  \n  if (recommendedAction === 'upsell_campaign') {\n    potentialRevenue = currentMRR * 1.5; // 50% increase\n  } else if (recommendedAction === 'retention_campaign') {\n    potentialRevenue = currentMRR * 0.8; // 80% retention with discount\n  }\n  \n  processedCustomers.push({\n    customer_id: data.customer_id,\n    current_mrr: currentMRR,\n    churn_risk: Math.round(churnRisk * 100) / 100,\n    upsell_potential: Math.round(upsellPotential * 100) / 100,\n    recommended_action: recommendedAction,\n    priority: priority,\n    potential_revenue: Math.round(potentialRevenue * 100) / 100,\n    usage_score: usageScore,\n    engagement_score: engagementScore,\n    plan_type: data.plan_type,\n    analyzed_at: new Date().toISOString()\n  });\n}\n\nreturn processedCustomers;"
      },
      "typeVersion": 1
    },
    {
      "id": "6",
      "name": "筛选高风险客户",
      "type": "n8n-nodes-base.if",
      "position": [
        1040,
        200
      ],
      "parameters": {
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.recommended_action }}",
              "rightValue": "retention_campaign"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "7",
      "name": "获取客户详情",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1240,
        200
      ],
      "parameters": {
        "url": "https://api.stripe.com/v1/customers/{{ $json.customer_id }}",
        "method": "GET",
        "headers": {
          "Authorization": "Bearer {{ $credentials.stripe.secretKey }}"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "8",
      "name": "发送留存活动",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1440,
        200
      ],
      "parameters": {
        "sendTo": "={{ $json.email }}",
        "message": "=<!DOCTYPE html>\n<html>\n<head>\n  <style>\n    body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }\n    .container { max-width: 600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; }\n    .retention-offer { background: linear-gradient(45deg, #ff6b6b, #ffa500); color: white; padding: 20px; text-align: center; margin: 20px 0; border-radius: 10px; }\n    .value-prop { background: #e8f4f8; padding: 20px; margin: 20px 0; border-radius: 8px; }\n    .discount { background: #28a745; color: white; padding: 15px; text-align: center; margin: 15px 0; border-radius: 5px; }\n    .cta { background: #007bff; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 20px 0; }\n  </style>\n</head>\n<body>\n  <div class=\"container\">\n    <div class=\"retention-offer\">\n      <h2>💎 Exclusive Offer Just for You</h2>\n      <p>We noticed you're an important customer and want to show our appreciation</p>\n    </div>\n    \n    <p>Dear {{ $json.name }},</p>\n    \n    <p>Your success is our priority, and we want to ensure you're getting maximum value from our platform.</p>\n    \n    <div class=\"value-prop\">\n      <h3>🎯 We're Here to Help</h3>\n      <p>Let us provide you with:</p>\n      <ul>\n        <li>Personalized onboarding session</li>\n        <li>Advanced features training</li>\n        <li>Priority customer support</li>\n        <li>Custom integration assistance</li>\n      </ul>\n    </div>\n    \n    <div class=\"discount\">\n      <h3>🎁 Special Appreciation Discount</h3>\n      <p><strong>{{ $node['Revenue Settings'].json.retentionDiscount }}% OFF</strong> your next 3 months</p>\n      <p>Valid until {{ new Date(Date.now() + 7*24*60*60*1000).toLocaleDateString() }}</p>\n    </div>\n    \n    <div style=\"text-align: center;\">\n      <a href=\"https://support.company.com/retention/{{ $node['Analyze Revenue Opportunities'].json.customer_id }}\" class=\"cta\">\n        💬 Schedule Success Call\n      </a>\n    </div>\n    \n    <p style=\"color: #666; font-size: 14px; margin-top: 30px;\">\n      This offer is exclusively for you. Questions? Reply to this email.\n    </p>\n  </div>\n</body>\n</html>",
        "options": {
          "contentType": "html"
        },
        "subject": "Special Offer - We Value Your Business 💎"
      },
      "typeVersion": 1
    },
    {
      "id": "9",
      "name": "筛选向上销售机会",
      "type": "n8n-nodes-base.if",
      "position": [
        1040,
        300
      ],
      "parameters": {
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.recommended_action }}",
              "rightValue": "upsell_campaign"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "10",
      "name": "获取客户详情1",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1240,
        300
      ],
      "parameters": {
        "url": "https://api.stripe.com/v1/customers/{{ $json.customer_id }}",
        "method": "GET",
        "headers": {
          "Authorization": "Bearer {{ $credentials.stripe.secretKey }}"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "11",
      "name": "发送向上销售活动",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1440,
        300
      ],
      "parameters": {
        "sendTo": "={{ $json.email }}",
        "message": "=<!DOCTYPE html>\n<html>\n<head>\n  <style>\n    body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }\n    .container { max-width: 600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; }\n    .upsell-header { background: linear-gradient(45deg, #007bff, #0056b3); color: white; padding: 20px; text-align: center; margin: 20px 0; border-radius: 10px; }\n    .usage-stats { background: #d4edda; padding: 20px; margin: 20px 0; border-radius: 8px; }\n    .upgrade-benefits { background: #fff3cd; padding: 15px; margin: 15px 0; border-radius: 5px; }\n    .cta { background: #28a745; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 20px 0; }\n  </style>\n</head>\n<body>\n  <div class=\"container\">\n    <div class=\"upsell-header\">\n      <h2>🚀 You're Ready for the Next Level</h2>\n      <p>Based on your usage, we have exciting recommendations for you</p>\n    </div>\n    \n    <p>Hi {{ $json.name }},</p>\n    \n    <p>We've noticed you're making great use of our platform! Your engagement shows you're ready for advanced features.</p>\n    \n    <div class=\"usage-stats\">\n      <h3>📊 Your Usage Insights</h3>\n      <p><strong>Usage Score:</strong> {{ Math.round($node['Analyze Revenue Opportunities'].json.usage_score * 100) }}%</p>\n      <p><strong>Engagement Level:</strong> {{ Math.round($node['Analyze Revenue Opportunities'].json.engagement_score * 100) }}%</p>\n      <p><strong>Current Plan:</strong> {{ $node['Analyze Revenue Opportunities'].json.plan_type }}</p>\n    </div>\n    \n    <div class=\"upgrade-benefits\">\n      <h3>🎯 Unlock These Benefits</h3>\n      <ul>\n        <li>Advanced analytics and reporting</li>\n        <li>Priority customer support</li>\n        <li>Custom integrations</li>\n        <li>Increased usage limits</li>\n        <li>Advanced collaboration tools</li>\n      </ul>\n    </div>\n    \n    <div style=\"text-align: center;\">\n      <a href=\"https://billing.company.com/upgrade/{{ $node['Analyze Revenue Opportunities'].json.customer_id }}\" class=\"cta\">\n        ⬆️ Upgrade Now\n      </a>\n    </div>\n    \n    <p style=\"color: #666; font-size: 14px; margin-top: 30px;\">\n      Questions about upgrading? Our success team is here to help.\n    </p>\n  </div>\n</body>\n</html>",
        "options": {
          "contentType": "html"
        },
        "subject": "Ready to Unlock More Value? 🚀"
      },
      "typeVersion": 1
    },
    {
      "id": "12",
      "name": "筛选需要重新激活的客户",
      "type": "n8n-nodes-base.if",
      "position": [
        1040,
        400
      ],
      "parameters": {
        "conditions": {
          "options": {
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "conditions": [
            {
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.recommended_action }}",
              "rightValue": "engagement_campaign"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "13",
      "name": "获取客户详情2",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1240,
        400
      ],
      "parameters": {
        "url": "https://api.stripe.com/v1/customers/{{ $json.customer_id }}",
        "method": "GET",
        "headers": {
          "Authorization": "Bearer {{ $credentials.stripe.secretKey }}"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "14",
      "name": "发送重新激活活动",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1440,
        400
      ],
      "parameters": {
        "sendTo": "={{ $json.email }}",
        "message": "=<!DOCTYPE html>\n<html>\n<head>\n  <style>\n    body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }\n    .container { max-width: 600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; }\n    .re-engagement { background: #6f42c1; color: white; padding: 20px; text-align: center; margin: 20px 0; border-radius: 10px; }\n    .help-offer { background: #e8f4f8; padding: 20px; margin: 20px 0; border-radius: 8px; }\n    .resources { background: #f8f9fa; padding: 15px; margin: 15px 0; border-radius: 5px; }\n    .cta { background: #007bff; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; display: inline-block; margin: 20px 0; }\n  </style>\n</head>\n<body>\n  <div class=\"container\">\n    <div class=\"re-engagement\">\n      <h2>🎯 Let's Maximize Your Success</h2>\n      <p>We want to help you get the most out of our platform</p>\n    </div>\n    \n    <p>Hi {{ $json.name }},</p>\n    \n    <p>We've noticed you haven't been as active lately. We're here to help you succeed!</p>\n    \n    <div class=\"help-offer\">\n      <h3>🤝 Personal Success Session</h3>\n      <p>Let our experts help you:</p>\n      <ul>\n        <li>Optimize your current workflow</li>\n        <li>Discover features you might have missed</li>\n        <li>Set up automation to save time</li>\n        <li>Answer any questions you have</li>\n      </ul>\n    </div>\n    \n    <div class=\"resources\">\n      <h3>📚 Helpful Resources</h3>\n      <ul>\n        <li><a href=\"#\">Quick Start Guide</a></li>\n        <li><a href=\"#\">Video Tutorials</a></li>\n        <li><a href=\"#\">Best Practices</a></li>\n        <li><a href=\"#\">Community Forum</a></li>\n      </ul>\n    </div>\n    \n    <div style=\"text-align: center;\">\n      <a href=\"https://calendly.com/success-call/{{ $node['Analyze Revenue Opportunities'].json.customer_id }}\" class=\"cta\">\n        📅 Book Free Success Call\n      </a>\n    </div>\n    \n    <p style=\"color: #666; font-size: 14px; margin-top: 30px;\">\n      No obligation - just here to help you succeed!\n    </p>\n  </div>\n</body>\n</html>",
        "options": {
          "contentType": "html"
        },
        "subject": "We Miss You! Let's Get You Back on Track 🎯"
      },
      "typeVersion": 1
    },
    {
      "id": "15",
      "name": "便签1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1340,
        60
      ],
      "parameters": {
        "width": 240,
        "height": 160,
        "content": "## 收入智能"
      },
      "typeVersion": 1
    }
  ],
  "connections": {
    "Revenue Settings": {
      "main": [
        [
          {
            "node": "Get Customer Analytics",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Customer Details": {
      "main": [
        [
          {
            "node": "Send Retention Campaign",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Customer Details1": {
      "main": [
        [
          {
            "node": "Send Upsell Campaign",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Customer Details2": {
      "main": [
        [
          {
            "node": "Send Re-engagement Campaign",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Daily Revenue Analysis": {
      "main": [
        [
          {
            "node": "Revenue Settings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Customer Analytics": {
      "main": [
        [
          {
            "node": "Analyze Revenue Opportunities",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter High Risk Customers": {
      "main": [
        [
          {
            "node": "Get Customer Details",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter Re-engagement Needed": {
      "main": [
        [
          {
            "node": "Get Customer Details2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter Upsell Opportunities": {
      "main": [
        [
          {
            "node": "Get Customer Details1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Analyze Revenue Opportunities": {
      "main": [
        [
          {
            "node": "Filter High Risk Customers",
            "type": "main",
            "index": 0
          },
          {
            "node": "Filter Upsell Opportunities",
            "type": "main",
            "index": 0
          },
          {
            "node": "Filter Re-engagement Needed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}