Q: Can Gemini really write complex formulas like these from a natural-language prompt?

Yes, as long as your prompt is specific and detailed. The Formula Assistant works best when you clearly describe what you want (e.g. “Group customers into Low, Medium and High based on Net revenue” rather than just “Create segments”). The more context you give, the more accurate the generated formula will be.

Q: Why do some functions like RANK() or RUNNING_SUM() not appear here?

Those functions simply aren’t supported in Looker Studio. If you try to use them, you’ll get an error, and Gemini won’t suggest them either. In most cases, you can work around these limitations using functions like PERCENTILE() or by pre-calculating rankings in your data source (e.g. BigQuery or Sheets) before connecting it to Looker Studio.

Q: What’s the difference between row-level and aggregated formulas?

Row-level calculations happen for each individual record before visualisation (e.g. checking if Discount > 0.10 per order), while aggregated formulas happen after data is grouped (e.g. SUM(Net revenue) per category). For KPIs, comparisons, and segmentation, you’ll usually want aggregated calculations.

Q: How can I validate that a formula is doing what I expect?

The simplest way is to test it in a table before using it in a scorecard or chart. Create a basic table with the relevant dimension (e.g. Category) and add the new field. This lets you see the result for each row and spot any unexpected behaviour before you use it in a final dashboard.

20 advanced Looker Studio formula prompts for analysts

If you’ve been using the Looker Studio Formula Assistant for a while, you already know how powerful it is. With a simple natural-language prompt, Gemini can generate everything from simple KPIs to grouped dimensions. But once you’ve mastered the basics, it’s time to go further and use advanced Looker Studio formula prompts.

Advanced users can push Gemini much harder, and get it to write complex formulas for segmentation, data cleaning, conditional logic, text manipulation, time logic, and comparisons. The key is knowing the right prompts to use and staying within supported functions.

In this guide, we’ll explore 20 advanced Looker Studio formula prompts that every analyst should try. They range from intermediate-level optimisations to more technical expressions that can save you hours of manual work.

Advanced Looker Studio Formula Prompts - Day of the week clarifycation

🧠 Segmentation & Classification

Prompt (be specific!):
“Create revenue bands: Low (< 5,000), Medium (5,000–15,000), High (> 15,000) using Net revenue aggregated.”

CASE
  WHEN SUM(Net revenue) < 5000 THEN "Low"
  WHEN SUM(Net revenue) >= 5000 AND SUM(Net revenue) <= 15000 THEN "Medium"
  ELSE "High"
END

Prompt:
“Classify by revenue & profit together (both aggregated thresholds).”

CASE
  WHEN SUM(Net revenue) > 10000 AND SUM(Profit) > 2000 THEN "High revenue & profit"
  WHEN SUM(Net revenue) > 10000 THEN "High revenue, low profit"
  ELSE "Low revenue"
END

Prompt:
“Discount buckets from the row-level Discount field: None, Light (1–10%), Heavy (>10%).”

CASE
  WHEN Discount = 0 THEN "No Discount"
  WHEN Discount >= 0.01 AND Discount <= 0.10 THEN "Light Discount"
  ELSE "Heavy Discount"
END

Prompt:
“Flag orders in Q4 (Oct–Dec).”

CASE
  WHEN EXTRACT(MONTH FROM Order Date) = 10
    OR EXTRACT(MONTH FROM Order Date) = 11
    OR EXTRACT(MONTH FROM Order Date) = 12
  THEN "Q4"
  ELSE "Other Quarters"
END

Prompt:
“Label orders by delivery speed using DATE_DIFF(Delivery Date, Order Date): Fast (≤2), Standard (3–5), Slow (>5).”

CASE
  WHEN DATE_DIFF(Delivery Date, Order Date) <= 2 THEN "Fast"
  WHEN DATE_DIFF(Delivery Date, Order Date) <= 5 THEN "Standard"
  ELSE "Slow"
END

🔢 Numerical Calculations

Prompt:
“Compute YoY revenue growth: current calendar year vs previous calendar year.”

(
  SUM(CASE WHEN EXTRACT(YEAR FROM Order Date) = EXTRACT(YEAR FROM CURRENT_DATE()) THEN Net revenue END)
  -
  SUM(CASE WHEN EXTRACT(YEAR FROM Order Date) = EXTRACT(YEAR FROM CURRENT_DATE()) - 1 THEN Net revenue END)
)
/ NULLIF(
  SUM(CASE WHEN EXTRACT(YEAR FROM Order Date) = EXTRACT(YEAR FROM CURRENT_DATE()) - 1 THEN Net revenue END),
  0
)

Prompt:
“Revenue per day (unique order dates).”

SUM(Net revenue) / COUNT_DISTINCT(Order Date)

Prompt:
“% Gross→Net reduction (discount impact).”

(SUM(Gross revenue) - SUM(Net revenue)) / NULLIF(SUM(Gross revenue), 0) * 100

📅 Time Intelligence

Prompt:
“Return Net revenue for the last 30 days relative to today.”

CASE
  WHEN DATE_DIFF(CURRENT_DATE(), Order Date) >= 0
   AND DATE_DIFF(CURRENT_DATE(), Order Date) <= 30
  THEN Net revenue
END

Prompt:
“Fiscal quarter label from Order Date (Jan–Mar = Q1, etc.).”

CASE
  WHEN EXTRACT(MONTH FROM Order Date) >= 1 AND EXTRACT(MONTH FROM Order Date) <= 3 THEN "Q1"
  WHEN EXTRACT(MONTH FROM Order Date) >= 4 AND EXTRACT(MONTH FROM Order Date) <= 6 THEN "Q2"
  WHEN EXTRACT(MONTH FROM Order Date) >= 7 AND EXTRACT(MONTH FROM Order Date) <= 9 THEN "Q3"
  ELSE "Q4"
END

Prompt:
“Create ‘Weekend’ when WEEKDAY(Order Date) = 1 (Sunday) or 7 (Saturday), else ‘Weekday’.”
(Note: WEEKDAY returns 1=Sunday … 7=Saturday)

CASE
  WHEN WEEKDAY(Order Date) = 1 OR WEEKDAY(Order Date) = 7 THEN "Weekend"
  ELSE "Weekday"
END

🧪 Text Manipulation & Regex (RE2, no Python-style prefixes)

Prompt:
“Extract the first number sequence from Product Name.”

REGEXP_EXTRACT(Product Name, '[0-9]+')

Prompt:
“Get the first word from Category.”

REGEXP_EXTRACT(Category, '^[^ ]+')

Prompt:
“Uppercase product names.”

UPPER(Product Name)

Prompt:
“Prefix code: first three letters of Product Name.”

LEFT_TEXT(Product Name, 3)

Prompt:
“Trim spaces, collapse multiple spaces to one, and uppercase Product Name.”

UPPER(
  TRIM(
    REGEXP_REPLACE(Product Name, ' +', ' ')
  )
)

Prompt:
“Get the brand/code prefix from Product Name before first space or ‘-’.”

REGEXP_EXTRACT(Product Name, '^[^ -]+')

Prompt:
“Create a hyperlink to Google search for Product Name.”

HYPERLINK(
  CONCAT('https://www.google.com/search?q=', Product Name),
  CONCAT('Search ', Product Name)
)

📊 Ranking & Comparison (No `RANK()`)

Prompt (Top-decile using supported PERCENTILE)
“Flag items in the top 10% of Net revenue at the chart’s grain.”

CASE
  WHEN SUM(Net revenue) >= PERCENTILE(Net revenue, 90) THEN "Top 10%"
  ELSE "Other"
END

Prompt (Top-N via threshold):
“Flag high-performers with an absolute cut-off (e.g., ≥ 25k).”

CASE
  WHEN SUM(Net revenue) >= 25000 THEN "High Performer"
  ELSE "Other"
END

❌ As RANK()isn’t a supported function in Looker Studio, the Formula Assistant won’t suggest it. For true ranks, pre-compute in your data source or use BigQuery.

Best practices for advanced Looker Studio formula prompts

🧠 Be precise with conditions: The more specific your prompt, the better Gemini’s output.
🔁 Combine techniques: Use CASE statements, and functions together for more powerful fields.
📚 Learn from the formulas: Even if you’re an advanced user, reading Gemini’s output is a great way to discover new syntax.
🧪 Validate everything: Always test your formulas on a sample dataset before applying them broadly.

FAQs – Advanced Looker Studio formula prompts

Q: Can Gemini really write complex formulas like these from a natural-language prompt?

Yes, as long as your prompt is specific and detailed. The Formula Assistant works best when you clearly describe what you want (e.g. “Group customers into Low, Medium and High based on Net revenue” rather than just “Create segments”). The more context you give, the more accurate the generated formula will be.
Q: Why do some functions like RANK() or RUNNING_SUM() not appear here?

Those functions simply aren’t supported in Looker Studio. If you try to use them, you’ll get an error, and Gemini won’t suggest them either. In most cases, you can work around these limitations using functions like PERCENTILE() or by pre-calculating rankings in your data source (e.g. BigQuery or Sheets) before connecting it to Looker Studio.
Q: What’s the difference between row-level and aggregated formulas?

Row-level calculations happen for each individual record before visualisation (e.g. checking if Discount > 0.10 per order), while aggregated formulas happen after data is grouped (e.g. SUM(Net revenue) per category). For KPIs, comparisons, and segmentation, you’ll usually want aggregated calculations.
Q: How can I validate that a formula is doing what I expect?

The simplest way is to test it in a table before using it in a scorecard or chart. Create a basic table with the relevant dimension (e.g. Category) and add the new field. This lets you see the result for each row and spot any unexpected behaviour before you use it in a final dashboard.

Final thoughts – Advanced Looker Studio formula prompts

These advanced Looker Studio formula prompts unlock the full potential of Gemini. Instead of writing long, error-prone formulas manually, you can describe your goal in natural language, and let AI do the hard work for you.

From advanced segmentation and regex extractions to parameter-driven calculations and ranking logic, these prompts can save analysts hours of work and drastically speed up dashboard development. The more you experiment, the better Gemini becomes at understanding your intent, and the faster you’ll go from idea to insight. 🚀