Can You Analyze Customer Data From Multiple Tools in a Spreadsheet? When It Works and When It Doesn’t.
TL;DR: A shared key is a field such as email that appears in every export. A small team can analyze customer data from multiple tools in a spreadsheet when the question is numeric and a shared key links the rows. It stops working when customers can’t be matched, the answer sits in free text, or you repeat the job monthly.
How do you combine exports from several tools in a spreadsheet?
Choose the question, then the key, then join it all together. Take a question that spans billing and support, such as how many of last month’s cancelled customers contacted support first. Five steps are needed to answer it:
Write the question as one sentence that names both groups of customers.
Export one file per tool and keep every column.
Pick the shared key, usually email or customer ID, and clean it in both files by trimming spaces, lowercasing it and storing it as text.
Join the files on the key.
Count or pivot the result, then list the rows that found no match.
Microsoft’s VLOOKUP page says the lookup value must sit in the first column of the range, that approximate match is the default unless you ask for an exact one, and that stray leading or trailing spaces break the match. The same page recommends XLOOKUP, which returns exact matches by default and works in any direction.
For bigger files, Power Query records the join as repeatable steps. Microsoft’s documentation lists seven kinds of join. The left anti join returns only the rows in the first table that have no match in the second, so it lists the customers who appear in billing but never in support. Excel can show you exactly what your match missed. If the question includes tickets or survey comments, or you ask it every month, a Scry run reads the same exports, as the Scry section below explains.
How do you match customers across exports from different tools?
Match on a field that every tool has. Email is the typical choice, but a customer can use one address for billing and another for support, so a match on email finds fewer people than you have. Customer ID works only if each tool stores it, and order numbers rarely appear in every tool.
When I read exports from different tools side by side, the match is where most of the time goes. Treat the list of rows without a match as a finding. It shows which tools identify customers differently, and every count you report is a minimum until that list is empty.
Match exactly first, then check the near misses by hand, such as one name that appears under two addresses. Write the matched and unmatched counts next to every figure, and write the key rule down so the next person repeats it. The approach carries over when you reconcile ad platform revenue with store orders, where the key is the order.
What does a spreadsheet job across three tools look like?
Take a made-up company with 20 people. Its billing export lists 1,200 customers, and 96 cancelled last month. Its support export holds 410 tickets, and its survey export holds 230 responses with no customer ID. The question is which cancellations followed a support contact.
Matching billing to support on email finds 41 of the 96 cancelled customers with a ticket in the prior 30 days. Checking the other 55 by name finds 9 more whose tickets came from a different address, so the answer is 50 of 96, a little over half. The 230 survey responses can’t join to anyone, so they stay out of the count. The 50 tickets still have to be read to learn what those customers said. Each step needs a written rule if someone else repeats the job. All figures in this example are made up for illustration.
When does a spreadsheet stop working for customer questions?
A spreadsheet stops working when customers can’t be matched, the answer sits in free text, the job repeats, or a broken lookup goes unnoticed. Size is rarely the reason for a small team. Microsoft’s limits page puts a worksheet at 1,048,576 rows by 16,384 columns, and a 1,200-customer billing file uses a tiny fraction of that.
No shared key. If you can't match customers on a single key, it can be difficult to be confident that each count is unique.
Free text. Tickets, reviews and other comments have to be read, and reading doesn’t scale the way a formula does.
Repetition. A job done once is a project. A job repeated every month depends on whoever remembers the steps.
Silent errors. A broken lookup returns a plausible number instead of an error.
In a 2000 paper for EuSpRIG, the European Spreadsheet Risks Interest Group reviewed fifteen years of research and found it unanimous that spreadsheet errors are both common and non-trivial. Only cell-by-cell code inspection had been shown to reduce them. Check every formula in a report you repeat.
Can a spreadsheet analyze support tickets and survey comments?
Yes, by tagging them yourself. Read a sample of 50 tickets, write five to eight tags that cover what you read, add a tag column, and apply it to every row. A pivot by tag then counts the reasons. A sample of 50 is manageable, but tagging thousands is a project, and two people will tag one ticket differently, so write each tag’s rule down. For a worked example of what tickets can reveal about cancellations, see what support data shows about churn.
Where does Scry fit if you already use a spreadsheet?
Scry works with your spreadsheet. You send the exports you already have, such as the billing file, the support file and the survey file, together with the question. Scry lines the exports up, reads the free text, and returns a report that shows where the sources agree and disagree, lists what the data couldn’t determine under Gaps & Unknowns, and includes a human review of the findings. If the question repeats, you set it as a standing objective and later runs build on earlier ones. A customer intelligence layer is that standing job done for you, and how a Scry engagement works covers the three steps.
The choice comes down to the question. A cross-source metric is one that cannot be computed inside any single system of record, and a spreadsheet can compute one when a clean key links the files and you need the answer once. Send it to Scry when any of those four causes applies. Pasting the files into a chat tool is a third route, and a run adds checks that a chat window skips. Dashboards are a fourth, and they answer a different question.
The five steps above also prepare an export for either route. To see what that looks like on your own numbers, send one export and one question to hello@monadux.com, and get back what a run finds.
What are the limits of this advice?
This covers a numeric question across two or three exports for a company with identifiable customers. It doesn’t cover marketplace or B2B account hierarchies, or data that needs legal or privacy review before it leaves a tool. The ticket and survey counts in the example are made up.
-- Steven Rencher, Founder of Monadux
FAQ
Can I use a spreadsheet to combine data from several business tools?
Yes, when the question is numeric and a shared key such as email links the exports. Export one file per tool, clean the key, join the files with XLOOKUP or Power Query, then count the result and list the rows without a match. The unmatched list shows how much of the answer is missing.
How do I match customers across exports from different tools?
Match on the field every tool stores in one format, usually email or customer ID, after trimming spaces and lowercasing it. Match exactly first, then check the unmatched rows by hand for different addresses or name variants. Report the matched and unmatched counts beside every figure, since each count is a minimum.
When does a spreadsheet stop working for customer questions?
A spreadsheet stops working when customers can’t be matched across tools, when the answer sits in free text, when you repeat the job every month, or when a wrong lookup returns a plausible number. Size is rarely the cause, since Excel holds 1,048,576 rows per worksheet.
Can a spreadsheet analyze support tickets and survey comments?
Yes, by tagging. Read a sample, write five to eight tags, add a tag column, apply it to every row and pivot by tag. Reading becomes the bottleneck once you reach thousands of rows, and two people will tag one ticket differently unless each tag has a written rule.
Should I use a spreadsheet or Scry for a question that spans several tools?
Use a spreadsheet when the question is numeric, one shared key links the files and you need the answer once. Send it to Scry when customers can’t be matched cleanly, when the answer sits in tickets or survey comments, or when you need it every month. Scry reads the exports your spreadsheet uses.