Weekly Algolia search dashboard in Google Sheets

PromptCreate

Every Monday at 08:00, roll up the last seven days of Algolia search analytics into a trended Google Sheets dashboard so leadership can open one link instead of logging in to Algolia. Trigger this on a cron: every Monday 08:00 in the org's local timezone.

For the configured Algolia index, pull two matched windows so the workflow can compute week-over-week deltas: the trailing 7 days (last Monday 00:00 through yesterday 23:59) and the prior 7 days (the week before that). For each window, call Algolia "Analytics: Searches Count" for the total search volume, "Analytics: Top Searches" for the top 10 most popular queries, "Analytics: Top Searches with No Results" for the top 10 zero-result queries, "Analytics: Top Hits" for the top 10 most frequently returned records, and "Analytics: Top Filter Attributes" for the top 10 facets shoppers filtered by.

Then write the results into the configured Google Sheets file across four tabs. On the KPI history tab, use Google Sheets "Batch Update Values" to append one new dated row for this week with columns for: report date, total searches, week-over-week change in total searches, distinct top-search count, top-1 search term and its share of searches, zero-result query count, week-over-week change in zero-result count, top filter attribute, and a compact JSON blob containing the full top-10 lists (searches, no-results, hits, filters) so nothing is lost. On the Top Searches tab, the Top No-Results tab, and the Top Filters tab, use "Batch Update Values" to overwrite the named range on each tab with this week's top-10 list so any charts or named ranges the sheet owner has built stay pointed at fresh data. Never overwrite historical KPI rows: only append.

Finally, write a short natural-language commentary cell next to this week's KPI row. Compare the two windows and call out (1) the biggest movers in total volume and in individual top queries, (2) any notable new zero-result queries that were not zero-result last week and any that dropped off, and (3) meaningful shifts in category concentration based on the top filter attributes. Two to four sentences, plain English, written for a non-technical leadership reader who is skimming on a phone. When a category is empty (for example a week with no zero-result queries), say so explicitly instead of leaving the commentary silent.

Configuration the user should be able to edit without touching the workflow logic: the Algolia index name, the Google Sheets file id, the tab names for KPI history / top searches / top no-results / top filters, the top-N cutoff (default 10), and the cron time and timezone.

Additional information

What does this prompt do?

Pulls last week's Algolia search performance every Monday morning so leadership never has to log in.
Appends a new dated row to a KPI tab in Google Sheets and refreshes tabs for top searches, top no-results, and top filters.
Writes a short plain-English commentary on the biggest movers, notable new zero-result queries, and shifts in what people are filtering by.
Keeps a week-over-week trend line growing on its own so anyone opening the sheet reads a summary, not raw numbers.

What do I need to use this?

An Algolia account with analytics enabled on the index you want to report on
A Google Sheets file you want the weekly numbers written into
The tab names in that sheet the workflow should update (KPI history, top searches, top no-results, top filters)

How can I customize it?

Change the day and time the roll-up runs (default is every Monday at 8am).
Point it at a different Algolia index or a different Google Sheets file.
Adjust which headline numbers land in the KPI row or how long the top-10 lists should be.

Frequently asked questions

Do people opening the sheet need Algolia access?

No. That is the whole point. Every number leadership sees is already written into Google Sheets, so anyone with access to the sheet can open it on Monday morning and read the summary without an Algolia login.

How far back does the trend go?

The dashboard grows one row per week. The first Monday it runs you get one data point. After a month you have a month of trend, after a quarter you have a quarter, and so on. Older rows are never overwritten.

What is the commentary cell for?

It is a short plain-English write-up sitting next to each week's numbers. It calls out the biggest movers, any new searches that returned nothing, and any shifts in what shoppers are filtering by, so busy readers get the story without reading the raw data.

Can I use this on any Algolia index?

Yes. Point the workflow at whichever index you want reported on. If you run more than one storefront or search surface, you can duplicate the workflow and give each one its own Google Sheets tab.

What if a week has no searches or no zero-result queries?

The workflow still runs and still writes a row. Empty categories are noted in the commentary so the trend line stays continuous and you can see gaps at a glance.

Related templates

Auto-refresh a Google Sheet from Databricks every night

Every night we run your saved Databricks query and refresh a Google Sheet with the results, so your team reads the latest numbers without a login.

5d ago

Deterministic Code

Prompt

Log daily football results and league standings to Sheets

Every morning, automatically record yesterday's match results and each league's latest table into a spreadsheet you can chart or pivot.

5d ago

Deterministic Code

Prompt

Daily IP reputation screening with Slack threat alerts

Every morning we check your list of IP addresses for abusive activity, update your spreadsheet, and alert your team only about the risky ones.

5d ago

Agentic Task

Prompt

Log every new Azure DevOps work item to Google Sheets

Automatically add every new Azure DevOps work item to a Google Sheets tracker so your team can filter, sort, and report without opening Azure DevOps.

5d ago

Deterministic Code

Prompt

Daily data breach monitoring for key email accounts

Check key email accounts against known data breaches every weekday, log every exposure to a spreadsheet, and get a Slack alert when a new breach shows up.

5d ago

Deterministic Code

Prompt

Morning team travel briefing with live flight alerts

Every weekday morning, your team gets one Slack briefing on who's flying today, their live flight status, and any delays or cancellations to act on.

5d ago

Agentic Task

Prompt

Stop copy-pasting Algolia numbers into a slide every Monday.

Let the dashboard update itself and give leadership one link they can open all quarter.

Explore more prompts Create