Merchandising board for products people view but never buy
Rank your catalog by view to cart and cart to purchase rate, so the products getting real traffic and losing the sale sit at the top of the screen.
Google Analytics
Shopify
Google SheetsMarketingOperationsAI ReportsResearch & MonitoringI run an online store and I want a merchandising board that shows me which products people look at but do not buy. Build me an app I open when I want to find and fix conversion problems in my catalog.
The main screen is a single ranked table of my catalog. Pull the catalog from Shopify with List Products and show each product's featured image, title, price, status, vendor and product type. Next to each product show the real on-site funnel from Google Analytics using Run Report (GA4) over a 28 day window, querying the item dimensions and metrics: itemId and itemName as dimensions, and itemsViewed, itemsAddedToCart, itemsPurchased and itemRevenue as metrics. Resolve the GA4 property with List Account Summaries rather than asking me to paste a property ID or hardcoding one. From those numbers compute a view to cart rate (add to carts divided by views) and a cart to purchase rate (purchases divided by add to carts), and show both as percentages beside the raw counts and the revenue.
The default sort is the one I actually care about: most viewed with the worst purchase rate first, so the top of the screen is a ranked list of merchandising problems rather than a catalog dump. Let me re-sort by any column, and let me filter to a single product type or vendor. Two controls sit at the top of the board and both must be adjustable by me: the date window (default 28 days, with shorter and longer options) and a minimum view threshold. Products below the view threshold are filtered out by default, because tiny numbers produce nonsense conversion rates, and I want to be able to raise or lower that threshold and see the board update. Always show which date window the numbers cover, and how many products are currently hidden by the threshold.
Match the GA4 item ID to the Shopify product ID or to a variant SKU. Matching will not be perfect, and that is important information rather than something to hide: put every GA4 item that failed to match a product, and every product that got no GA4 rows at all, into a visible "Not matched" tab with its item ID, name and view count, so I can see when my product feed IDs are drifting. Show the matched and unmatched counts on the tab itself.
Clicking any row opens a detail panel for that product. It shows where the traffic to that product is coming from (run a second GA4 report scoped to that item, broken out by traffic source, channel group and campaign), the landing pages people arrive on, and a week over week trend of views, add to carts and purchases across the selected window so I can see whether it is getting worse. Below that, list the product's variants pulled with Shopify List Variants, showing SKU, price and inventory. I can change a variant price right there using Update Variant, but only behind an explicit confirmation step that shows the old price and the new price side by side and makes me confirm before anything is written to the store.
Every row also has a "Diagnose this product" button that kicks off a background agent for that single product. The agent should: compare the product's funnel rates against the median rates for other products of the same product type on the board; check whether the traffic reaching it is coming from a mismatched source or campaign (for example paid traffic from a campaign whose intent does not match the product, or a landing page sending the wrong audience); and read the product's own description body and images by fetching the full product from Shopify. It then picks one primary verdict from four options, price problem, copy problem, imagery problem or traffic quality problem, and writes a short plain-language recommendation explaining why, with the numbers it relied on.
The agent's output has to land in two places. First, store it in the app and show it on the product's row and in its detail panel with the date it was written, so the board gradually fills up with verdicts and I can see which products have already been diagnosed. Second, append it to a Google Sheets decision log with Append Values, one row per diagnosis, including the date, product title, SKU, product type, views, view to cart rate, cart to purchase rate, revenue, the verdict and the recommendation text. That dated log is the point: next month I want to look back and see whether the fix actually worked. Show the diagnosis running in the background so I can keep working the board while it thinks, and let me re-run a diagnosis later to get a fresh dated entry.
A note on writes: Google Analytics is read only here, so the only two things this app ever writes are the Shopify variant price change and the row appended to the Google Sheets log. Keep the price change behind its confirmation step, and make the log append visible in the app so I can tell it succeeded.
What does this prompt do?
- Lists your whole store catalog with photo, price, status, vendor and product type, and puts the real shopping funnel beside each one: views, add to carts, purchases and revenue over the last 28 days.
- Sorts by the thing you actually care about, most viewed with the worst purchase rate first, so the top of the screen is a ranked list of merchandising problems rather than a catalog dump.
- Hides products below a view threshold you control, and puts any product whose analytics ID no longer lines up with your store into a visible not matched tab so you catch a drifting product feed.
- Gives every row a Diagnose button that sends a background assistant to compare the product against others of its type, read its own description and photos, and write back a plain verdict, then log it with the date so you can check next month whether the fix worked.
What do I need to use this?
- An online store you can connect, with permission to read your catalog and update variant prices
- A Google Analytics account with ecommerce tracking running on that store, so product views, add to carts and purchases are being recorded
- A Google account and a spreadsheet you want to use as the dated decision log
- Product IDs in your analytics that line up with your store's products or SKUs. The board tells you when they stop lining up
How can I customize it?
- Change the date window and the minimum view threshold at the top of the board, so tiny numbers stop producing nonsense rates
- Change the default sort, or filter the board down to one product type or one vendor when you are working a specific category
- Point the decision log at a different spreadsheet or tab, and adjust what the diagnosis is asked to weigh up before it picks a verdict
FAQs
Do I need to build a report in Google Analytics first?
Why is a product missing from the main list?
Can it change my prices without asking?
What does the Diagnose button actually do?
Does this work for a small store with modest traffic?
Related templates
Stop buying stale lists. Reps run a saved search, work the results like an inbox, and only the accounts they approve ever reach your CRM.
Drag creators through Sourced to Wrapped on a board grouped by campaign, with audience stats on every card and a one-click brief for each creator.
Pick a repricing rule, send an assistant out to check competitor pages, then approve the new prices that clear your margin floor.
See the projected bounce rate for any outbound send before it goes out, and keep the launch button locked until the list is clean enough to be safe.
Pick any date range and see what each device in your home actually cost to run, not just how many kilowatt-hours it used.
Search three months of global coverage on your topic, rank the outlets actually writing about it, and draft a tailored pitch for each one.
Stop guessing which products are quietly losing you sales.
Open one ranked board and see exactly where shoppers drop off, product by product, with a recommendation attached.