Business Snippets, Formulas & AI Prompts

=QUERY(
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID_HERE", "Orders!A:F"),
  "SELECT Col1, Col2, Col4, Col6 
   WHERE Col4 = 'Completed' 
     AND Col6 >= 25 
     AND Col1 >= date '"&TEXT(TODAY()-30, "yyyy-mm-dd")&"' 
   ORDER BY Col6 DESC 
   LABEL Col1 'Order Date', Col2 'Customer Name', Col4 'Status', Col6 'Revenue ($)'",
  1
)

=IFS(
  B2 <= 0, 
  "🚨 CRITICAL: Out of Stock",
  B2 <= (C2 * D2), 
  "⚠️ REORDER NOW (" & ((C2 * D2 * 2) - B2) & " units needed)",
  B2 <= (C2 * D2 * 1.5), 
  "⚡ Low Buffer (" & ROUND(B2/C2, 0) & " days remaining)",
  TRUE, 
  "✅ Healthy Stock (" & ROUND(B2/C2, 0) & " days)"
)

=LET(
  salePrice, A2,
  costOfGoods, B2,
  adSpendPerUnit, C2,
  gatewayRate, 0.029,
  gatewayFlat, 0.30,
  marketplaceFeeRate, 0.05,
  grossRevenue, salePrice,
  gatewayFee, (grossRevenue * gatewayRate) + gatewayFlat,
  platformCommission, grossRevenue * marketplaceFeeRate,
  totalDeductions, costOfGoods + adSpendPerUnit + gatewayFee + platformCommission,
  netProfit, grossRevenue - totalDeductions,
  netMargin, netProfit / grossRevenue,
  "Net Profit: $" & TEXT(netProfit, "0.00") & " (" & TEXT(netMargin, "0.0%") & " Margin)"
)

=IF(ISBLANK(A2), "", 
  LET(
    rawDigits, REGEXREPLACE(TO_TEXT(A2), "[^0-9]", ""),
    IF(LEFT(rawDigits, 1) = "0", 
      "+62" & MID(rawDigits, 2, 20),
      IF(LEFT(rawDigits, 1) = "8",
        "+62" & rawDigits,
        "+" & rawDigits
      )
    )
  )
)

=LET(
  cohortSignups, COUNTIFS(Subscriptions!$B:$B, B$1, Subscriptions!$C:$C, "<="&EOMONTH($A2, -1)),
  retainedActive, COUNTIFS(Subscriptions!$B:$B, B$1, Subscriptions!$D:$D, ">="&$A2, Subscriptions!$C:$C, "<="&$A2),
  IF(cohortSignups = 0, "-", TEXT(retainedActive / cohortSignups, "0.0%"))
)

You are an empathetic, highly efficient Customer Support Operations Lead.

OBJECTIVE:
Evaluate the incoming customer support message, assess sentiment urgency, and generate an immediate resolution draft.

RULES:
1. Detect churn or chargeback risk (HIGH, MEDIUM, LOW).
2. Never apologize defensively; offer proactive accountability and clear next steps.
3. Keep the drafted response under 150 words.

OUTPUT FORMAT (Strict JSON):
{
  "urgency_level": "CRITICAL" | "HIGH" | "NORMAL",
  "churn_risk": "HIGH" | "MEDIUM" | "LOW",
  "detected_pain_point": "Short summary of the core friction",
  "recommended_action": "e.g. Issue immediate refund / Reset API key / Escalate to billing",
  "empathetic_draft_reply": "Hi [Customer Name],\n\n[Empathetic opening acknowledging their exact frustration].\n\n[Clear actionable resolution or timeline].\n\nBest regards,\nSupport Team"
}

TASK:
Write a high-converting, non-spammy 3-sentence cold email tailored to the prospect data below.

PROSPECT INPUT:
Name: {{Prospect_Name}}
Company: {{Company_Name}}
Website / Bio: {{Company_Summary_Or_Bio}}
Our Solution: {{Our_Product_Or_Service}}

STRICT EDITORIAL CONSTRAINTS:
1. Sentence 1: Specific observation proving you actually researched their business (NO generic flattery like "I love your work!").
2. Sentence 2: Connect their probable operational bottleneck to our tangible result (include numbers or time saved).
3. Sentence 3: Low-friction soft Call to Action (e.g. "Worth exploring for 5 minutes next Tuesday, or are you covered?").
4. Under 65 words total. Zero corporate buzzwords ("synergy", "game-changer", "revolutionary").

OUTPUT:
Subject Line: {{Subject}}
Body:
{{Body}}

You are an elite Direct-Response E-Commerce Copywriter.

TASK:
Transform dry manufacturer specifications into 5 high-converting, benefit-driven product bullets for an e-commerce product page.

FRAMEWORK TO APPLY:
Feature + Practical Benefit + Emotional Relief.
(Example: "Made of 304 Stainless Steel" -> "Rust-Proof 304 Stainless Steel: Withstands daily dishwasher cycles without tarnishing, so you never have to scrub stubborn water stains by hand.")

INPUT PRODUCT DATA:
Product: {{Product_Title}}
Raw Specs: {{Raw_Manufacturer_Specs}}
Target Audience: {{Target_Shopper}}

OUTPUT GUIDELINE:
- 5 concise bullet points.
- Begin each bullet with a bold 2-3 word anchor phrase.
- Focus on how the product simplifies the buyer's daily routine.

You are an automated accounting parser. 

Extract all transactional details from the raw invoice/receipt text below. Output ONLY a valid JSON object matching the schema. Do not write markdown code fences or conversational text.

RAW INVOICE TEXT:
{{Raw_Invoice_Text}}

STRICT JSON SCHEMA:
{
  "vendor_name": string,
  "invoice_number": string | null,
  "transaction_date": "YYYY-MM-DD",
  "currency": "USD" | "IDR" | "EUR" | "GBP",
  "subtotal": number,
  "tax_amount": number,
  "total_amount": number,
  "line_items": [
    {
      "description": string,
      "quantity": number,
      "unit_price": number,
      "total": number
    }
  ],
  "payment_method": string | null
}

You are a Ghostwriter for high-credibility founders and technical operators.

TASK:
Repurpose the core insights of this long-form article into a 5-part LinkedIn / X micro-guide.

CONSTRAINTS:
1. Post 1 (Hook): State the counter-intuitive finding or operational lesson in under 25 words. No clickbait or question spam.
2. Posts 2-4: Concrete playbooks or frameworks explaining HOW to solve the problem (use bullet points and exact metrics).
3. Post 5 (Takeaway): 1 actionable rule the reader can execute in under 15 minutes today.
4. Total tone: Pragmatic, humble, and evidence-based. No rocketship emojis (🚀) or fake motivational drama.

ARTICLE INPUT:
{{Article_Text}}

function doPost(e) {
  var lock = LockService.getScriptLock();
  lock.tryLock(10000); // Prevent concurrent write race conditions

  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var payload = JSON.parse(e.postData.contents);

    var timestamp = new Date().toISOString();
    var eventType = payload.event || payload.type || 'order_created';
    var customerEmail = payload.email || (payload.data && payload.data.email) || 'N/A';
    var customerName = payload.name || (payload.data && payload.data.name) || 'N/A';
    var amount = payload.amount || (payload.data && payload.data.amount) || 0;
    var rawDetails = JSON.stringify(payload);

    sheet.appendRow([timestamp, eventType, customerName, customerEmail, amount, rawDetails]);

    return ContentService
      .createTextOutput(JSON.stringify({ status: 'success', recorded_at: timestamp }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

function sendTelegramLeadAlert(leadName, leadEmail, source, note) {
  var botToken = "YOUR_TELEGRAM_BOT_TOKEN_HERE"; // From @BotFather
  var chatId = "YOUR_TELEGRAM_CHAT_ID_HERE";     // Your personal or group chat ID

  var message = "🔔 *New Inbound Lead Received!*\n\n"
    + "👤 *Name:* " + leadName + "\n"
    + "📧 *Email:* " + leadEmail + "\n"
    + "🎯 *Source:* " + source + "\n"
    + "📝 *Note:* " + (note || "None") + "\n\n"
    + "⏱️ _Time: " + Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd HH:mm:ss") + " WIB_";

  var url = "https://api.telegram.org/bot" + botToken + "/sendMessage";
  var payload = {
    chat_id: chatId,
    text: message,
    parse_mode: "Markdown"
  };

  UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });
}

function generateAndEmailInvoice(customerEmail, customerName, invoiceNumber, amountUsd) {
  var templateDocId = "YOUR_GOOGLE_DOC_TEMPLATE_ID";
  var outputFolderId = "YOUR_GOOGLE_DRIVE_FOLDER_ID";

  // Duplicate template and substitute variables
  var copy = DriveApp.getFileById(templateDocId).makeCopy("Invoice_" + invoiceNumber, DriveApp.getFolderById(outputFolderId));
  var doc = DocumentApp.openById(copy.getId());
  var body = doc.getBody();

  body.replaceText("{{CUSTOMER_NAME}}", customerName);
  body.replaceText("{{INVOICE_NO}}", invoiceNumber);
  body.replaceText("{{AMOUNT}}", "$" + amountUsd.toFixed(2));
  body.replaceText("{{DATE}}", Utilities.formatDate(new Date(), "UTC", "yyyy-MM-dd"));
  doc.saveAndClose();

  // Export to PDF and send via Gmail
  var pdfBlob = copy.getAs("application/pdf").setName("Invoice_" + invoiceNumber + ".pdf");
  MailApp.sendEmail({
    to: customerEmail,
    subject: "Official Invoice #" + invoiceNumber + " | ScriptCuan",
    body: "Hi " + customerName + ",\n\nThank you for your business. Please find your official invoice attached.\n\nBest regards,\nFinance Desk",
    attachments: [pdfBlob]
  });
}

-- Aggregate monthly active recurring revenue and churn metrics
SELECT 
  strftime('%Y-%m', date(created_at, 'unixepoch')) AS billing_month,
  COUNT(DISTINCT user_id) AS total_active_accounts,
  SUM(CASE WHEN status = 'active' THEN amount_cents ELSE 0 END) / 100.0 AS mrr_usd,
  SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) AS canceled_subscribers,
  ROUND(
    (SUM(CASE WHEN status = 'canceled' THEN 1.0 ELSE 0.0 END) / COUNT(DISTINCT user_id)) * 100.0, 
    2
  ) AS churn_percentage
FROM subscriptions
WHERE created_at >= strftime('%s', 'now', '-6 months')
GROUP BY billing_month
ORDER BY billing_month DESC;

-- High-performance sales and Average Order Value breakdown
SELECT 
  product_id,
  product_title,
  COUNT(id) AS total_orders_count,
  SUM(quantity) AS units_sold,
  ROUND(SUM(total_price), 2) AS total_gross_revenue,
  ROUND(AVG(total_price), 2) AS average_order_value_aov,
  ROUND(SUM(discount_amount), 2) AS total_discounts_absorbed
FROM order_items
WHERE status = 'paid'
GROUP BY product_id, product_title
ORDER BY total_gross_revenue DESC
LIMIT 20;