How to Analyze 1000s of Tables Using AI

As always, I'm presenting from within Displayr, both because it's designed for presentation and because our other lovely product, Q, doesn't really do much AI work because it's not on the cloud.

I've done many versions of this webinar before, but this one's been massively revised due to time saves created by AI.

As we go on, type any questions into the GoToWebinar app, and I'll get through them at the end. Just to check to see if audio is working, if someone could give me a thumbs up or something, that would be really helpful.

We will, of course, send you a recording of the webinar.

Thank you, Sheila. You are the fastest.

Last time I presented this topic, which was a bit over a year ago, I didn't even have the first three things on the agenda. So the world's changed a lot. The first four topics are really about setting yourself up so you can work quickly and efficiently.

The last two are about what needs to happen in terms of how the data is analyzed. We're going to race through the first five and spend most of our time on data reduction.

A bit of preparation goes a long way.

Let's create a chart. We're gonna try and understand a little bit about templates and why we're talking about them.

We're gonna tell chat to create a table showing orders in the last month by date. Then turn it into a column chart. Now at the very beginning, I'm gonna do a few things via chat just because for those of you who are new to Displayr and don't know the user interface, chat is definitely the easiest way to get started. And then I'm going to migrate more to doing things in the user interface by clicking and dragging, simply because I'm still a bit faster than AI. We don't have to watch it think as it tries to figure out what to do.

Now, creating a template, you might think, what has this got to do with finding the story in your data? Well, it's a natural thing when you've collected data to jump in and play with the data. But as a pretty general rule, you end up getting a bit lost. And so one of the things you want to do is you want to set up things to maximize your chance of finding the story as quickly as you can.

And oh, I didn't quite get to the end. Oh, it did. It's put on a new page for me. Alright. It's got the table. It's got the chart. I'm gonna get it to tidy up the chart a little bit better.

And I don't like that. It's a bit ugly for me. Make the colors go from oops.

Light purple to dark purple.

Remove the x axis and y axis titles and make the x axis labels horizontal.

So customizing charts takes your time. And so often when people explore the data, they end up using ugly charts or ugly tables, which make it less likely to find the right story. And so I'm just emphasizing here that one of the useful things to do is to actually create templates that you can then use later on automatically. And at the same time, I'm gonna show you some new template functionality that just came live yesterday.

Now you can see here that my decision to make the x axis horizontal wasn't that clever because everything's overlapping.

I'm not going to spend time trying to optimize this visualization.

This is an example of interesting visualization. I'm just going to click here and go Save As Template.

And this is the new functionality. I'm just going to tell it to save it as a default. And I could alternatively place this in a different folder. But by saving this a default, what's going to happen is next time I create a bar chart, which I'll quickly do now by drag and drop, right awareness, we'll cross tab that by date.

We'll look at that as a column chart. Notice here it's telling me a template. It's previewing the chart you just saw.

And thus, we saved a bit of time.

A template is one approach to automation. It's for when you want to make things look the same.

When you want to automate how things are done, the equivalent in much more modern technology is the skill.

It's a set of instructions that tells AI what steps to do in what order. And it's a way that you can encode your own processes in.

Going back to this example here.

Here, I've created a whole lot of tables, and I'll talk more about them later.

I've also, before this webinar, written up something called a skill of my own called our pages of conclusions, which is my general methodology for how to read through large numbers of tables and figure out what's interesting. Now we're going to release this in the app soon, next week or so. It's gonna replace the research agent. Those who use it, it's much better. But you write up your skills, and then you get the AI to use them. And so I'm just gonna tell it to run it now.

And I can add a bit more context if I want to.

Now this is gonna take a bit of time because it is gonna read through a whole lot of tables. Talk about how many tables short.

But it needs to extract some context as we're going along. So it's going to start to ask me some questions to make sure it can work out how to actually answer the questions correctly.

So first thing it wants to know is what's the population that we're researching. So I'm gonna answer them. So number one is it's people aged fifteen plus.

Second question, what are my analysis goals? Well, it's a tracker. So what's changed over the last quarter compared to the previous quarter?

And what implications does it have for my brand, Burger Chef, disguised data.

And the last thing to ask me, what do I wanna do with unused evidence? And I'm just gonna delete it.

Now before I do it, I just wanna show you how much I've got here. I've got if I hold down all of these, each of these groups has got tables in, and I've got a total of three hundred and thirty pages worth of tables underlying all of this.

So delete them.

And off it goes. And this is going to take a little bit of time. So we're going to go and do something else while we wait.

The third preparation is to make sure you give the AI the right context. In this case, the AI was smart, and it asked me for the relevant context, which was where was the sample from, that is the underlying population assumptions, and what were my goals. And I could give it much more context if I wanted.

Bit four is create and follow an analysis plan.

This is an example, and don't worry if the jargon is new to you. We've covered this kind of other introductory webinars. If you don't create analysis plans, I encourage you to start doing it.

All of the kind of stuff at the top of the data checking and data cleaning is stuff that we just do automatically with data preparation agent and AI these days, but I've written it up for those of you that like to see what's happening.

As a promise, we're racing through these first sections. We're going to spend a lot more time on data reduction. But I just want to talk about casting a wide net. It used to be the case that a key difference between skilled and poor researchers was the number of crosstabs they created.

The great researcher created a very small number, just the ones they directly needed to answer the client's key strategic questions.

The novice instead created lots and lots of crosstabs, read through them all hoping to find something to share.

Today, I think the smart play is to do both. You still want to cleverly think through what you need to achieve, write it up in your analysis plan, and just do those specific analyses. But I'd always now create a large number of tables and see if AI can find it.

Now as I started talking about before, we have three hundred and thirty pages here. It's just reading through them all.

The reason I've got so many is I've got tables of everything by date for the total market, and then I've drilled into submarkets. So I'm particularly interested because it's fast food in men under the twenty five. And so I've just got them as separate tables. As a researcher, would I have been bothered to read through everything for that?

Maybe not. When the AI can do it for a couple of dollars, I'm definitely going to let the AI do it. So you want to cast a wide net. AI can help you go through it all.

And so we move on to data reduction.

Once you've got all of the data, so you've created heaps of tables or other analyses, you need to go ahead and reduce it all.

Data reduction.

The pope of the day, five hundred odd years ago, saw the Statue of David done by Michelangelo and was pretty impressed. It's the reason that we today think it's the greatest statue in history, perhaps.

And he said to David, he said, well, how did you do it? David I'm sorry, not David. Michelangelo said, it's simple. I just remove everything that's not David. And this is how we find the story in the data, except that we remove everything that's not interesting to stakeholders.

A second analogy.

Returning to the fish motif that you'll see later through this presentation.

When we reduce the quantity of data, we distill and we concentrate it so that we end up with a much stronger flavor, much like a fish sauce for those of you who are Vietnamese and Thai food. And in practice, we do this at two levels.

We reduce the number of tables, and we reduce the numbers in the tables.

Let me show you.

It's eight basic techniques to master, and all of them lead to a reduction in the amount of information that we need to look at or a distillation.

The first of these is to delete uninteresting analysis.

And this is indeed what I've asked AI to do for me automatically. And so it's going through. I gave it three hundred and thirty tables, and by the time it's finished, save one from before, we're gonna come back, and these are the same tables.

It's all the way down to twenty three. So it massively reduced the number of tables for us, less things to read.

The second technique is to remove clutter, and really deletion is a form of doing this. So if we look at this example here, I've got a report of tables, very short one. The first one is interview length by age. Who cares?

Delete. That's the best way to reduce clutter. Now we've got a table, and we're gonna go through this reasonably quickly. It's got you know, it's quite a big table if you think about it.

A lot of it's eighteen rows.

There's six columns, or eight columns, sorry. Should have spelled it right. So there's one hundred and forty four columns, or one hundred and forty four numbers. We want to reduce that number so it's easier to see the pattern. Now, all experienced researchers know that the column percent is the one that we really always wanna look at. So in the modern world, we shouldn't show them all on the page.

Look for the others if we need them. Oh, this column's clearly got nothing interesting.

Alright.

What else can we do here? Well, the percentage signs are kind of redundant, aren't they? They're all percentages.

So turn them off now. You'll see I'm gonna click on each of the things rather than use chat. It it's because it's a little faster, but I also wanna show you what's happening. You everything I'm showing you here that you could do via chat.

Let's even take the caption off at the bottom. It's not that necessary at this stage.

So what's the next data reduction technique? The next technique is to merge similar things.

What can we merge here?

First thing we might wanna merge is let's merge together these two age categories.

There's this. And so each time we combine these into smaller categories, we are simplifying the amount of information. We're reducing it, concentrating it. And when I merged them up, I went a bit too fast. So let me just show you the thing I was doing in my head, which I didn't say out loud. I noticed that the patterns in each columns were basically the same. That's what gave me permission to combine them together.

I really should have clicked redo there anyway.

So we started with a hundred and forty four numbers. We're now down to about twenty four, so we've reduced the information quite a lot.

The next technique sounds a bit more academic. It's called changing the scale.

To appreciate this, we're going to put an average on the table.

Put it at the base of the table.

Now if you look at this data, it shows us that younger people are more likely to buy. If you look at the average, it's a lower score for younger people, so it's kind of back to front. This is one of the things you've to be very careful of when you compute averages in market research surveys. You need to look at how the values are stored. So we have the data here looking at priced intent. So let's find that data.

And what we see is I would definitely buy this gold score of one. So high numbers mean lower intent purchase. So we should reverse the scales. And, we could do this automatically. It's just as fast to do it manually here.

And now the averages make more sense. But if you think about it a bit longer, why even one to five? Why not probability? So if someone is telling the truth and they know the truth, definitely a bite means they're definitely going to bite.

One hundred percent, probably, maybe that's seventy five percent. Not sure, maybe twenty percent. Some of you might be quite uncomfortable with this kind of guessing of numbers. People have been doing it for seventy years, and it seems to always work pretty well.

There's a great paper by a psychometrician by the board on this topic if you want to read it up from the 1960s. So now we've got our estimated probabilities, and we could have done even better if we had some historical benchmark data. But now we're saying, okay, we think there's twenty four percent of people are going to purchase this concept that we're doing a test for, and it's only six percent amongst the oldest group. It's forty two percent amongst the younger group.

So that is scaling the data. Now there's a little example scale. Most market researchers don't even think of scaling. And what it is is you could give the top two categories here a score of a hundred and everything else a score of zero.

And when we do that, that's those numbers, it's exactly the same as if we just combine the first two categories together.

Which is a top two box score.

And so top two box score itself is just a scale. So let's simplify this table even more because there's more we can do with it.

Take the average off because it's got no more interesting information. Now if you think about it, if you've got a high number here, these other numbers must be lower because they're forced to add up to a hundred percent. These hundred percents tell us nothing.

These numbers are just the complement of the opposite of the number stop, so they tell us nothing.

And we've now gone down to four numbers. Arguably let's make this a bit simpler, call it total.

Arguably, we could go down to three. We can make it even a little bit more simple again by just getting rid of the significance testing because we don't really need at this stage the whole answer. It's pretty clear.

And so we've reduced the data very heavily now.

There are lots of other ways of changing the scale of data. The most famous is the Net Promoter Score, which is a kind of rescaling, but also the advanced analysis, things like factor analysis, doing the same thing.

The next technique is to summarize. Now earlier, I kicked off that analysis of all three hundred and thirty pages.

When we do that, we end up with twenty three, and it's written a summary describing it. And so we can read at the top. The key conclusion that my little skill has created is that we should win back the young males because the young males are a source of slippage with their share having slipped. Well, I want to drill into this to find out more about it.

And so we see that it's telling us that the brand is still well known, but we've lost momentum. And we're seeing our share went from twenty five percent to five percent. So the challenge with AI is it's great at finding patterns, but sometimes it gets wrong. It makes mistakes.

So we always want to check it by looking at the underlying data. So this data isn't computed by AI at all, it's just computed by a standard Displayr in the background. The AI just uses Displayr’s computations, which allows us to always check. And we can see that indeed it's true.

Our share went from twenty one percent to five percent, which is pretty catastrophic. We very quickly found something pretty interesting.

Another example. Now, previously, over the last few slides, I was showing you how we as market researchers might distill a whole of information to simplify it for the client. But we can take another approach with AI, which is we can give them all the information and let them just ask what they want to know.

So here, I've got a big report. It's been made kinda pretty. It's got lots of data in it.

Just let the client chat. What are the biggest concerns people have about traveling to Mexico?

Now we got a lot of feedback that this wasn't fast enough, so it's now about six times faster than it was a few weeks ago. And you'll see we've very quickly got an answer. And, again, just like us, our clients can click the hyperlink and check the underlying data. And so the AI can make mistakes, but we don't have to.

Now when we summarize information, there are two broad strategies. Summarize numbers as text, which is the newest kid on the block and what we're doing with AI today, and the more traditional approach of summary statistics, where we place a large amount of data with a smaller amount of data, such as a top two box score, which we've already looked at.

Let's do a couple more examples, because this is definitely an area where you can achieve a lot of time savings. Now, again, I'm not going to use AI to run these, but you can get AI to run these today. The reason I'm not is I really wanted to explain how they're done, but using the skill for what I'm about to show you, definitely the way I'd be doing it going forward.

Alright. So I have here a big table which is looking at how likely people would be to buy a product by income. Some idiot, let's call it me, created way too many income categories. So it's really hard to get a feel for this. The standard way of solving this has been forever in market research to midpoint target them. So what we do is we find the underlying data, And we're going to change its structure from categories to be numeric.

And so now we've got averages. But what do these mean? What's a score of seventeen point one? Well, let's go in, edit the values, and look.

And we can see that we've got that same issue we had before, which is common to market research data, where the under thousand score is one. We need to replace it with midpoint, say, five hundred. The one thousand to three thousand, we should be replacing it by fifteen hundred. We could type them in.

We could ask AI to do it. Or if we know how to do it, we just click the shortcut button, and it will automatically do that for us.

Here's another example.

Here's some TV data. Which shows are being watched most often? I'll give you a chance to work it out while I drink some coffee.

Now it didn't take you that long to probably work out the two most popular shows here with Stranger Things and The Mandalorian.

This table's only got seventy numbers. And because you're probably pretty good with data, you read it pretty quickly. But what if you understand how watching one program influenced watching another? You might cross tab this by itself.

And that gets us this table, which has fourteen forty numbers in it and is just too big, too hard to possibly process.

So let's start by simplifying this guy.

As we've seen before, with categorical data, particularly for ordered categories, the smart thing to do is often to make them numeric.

And as we've seen before, when we do that, we need to check that the underlying values we've got make sense. In this case here, can see somebody's already gone in and fixed the data up to make these midpoints or the values appropriate.

And so now what we're looking at here, the data is interpreted as a proportion of episodes watched. But a standard way I like to tidy this kind of data up is I wanna change it from showing average to instead showing share.

And let's sort it.

And now we can very quickly see that the Mandalorian and Stranger Things are the top two alternatives. Now the thing that's much cooler, though, is when we've done this, the way that both Displayr and our other product Q work is they remember these changes to the underlying data. If we wish to, we can change that if we want. But what it means is this fourteen hundred and forty cells before have now been reduced to a table with only hundred and twenty one cells in it. I'm gonna get rid of the sum, which is not that interesting.

Oops.

Need to hide it. Click the wrong option.

And so now we're down to a hundred numbers. Now each of these numbers is another summary statistic. Because we've got numeric data in the rows, which is the frequency of watching, things of watching the columns, numeric variables are best analysed with correlations, and so Displayr is showing us correlations. And this is indeed a correlation matrix.

And this is going to move us to our next technique.

Now with the midpoint recording of income, for those of you who haven't seen it before, I just kinda walk through the math of what's going on here. We move on to reordering. Now we've already reordered by sorting data. It's the most common usage of reordering.

But there's a much cooler form of reordering called diagonalisation. And it sounds weird at first, but it's super magical. And this is something that I have, despite more than thirty years of attempts, been unable to automate very effectively. And I still do this by hand.

I don't think AI can do this yet. But it'll come soon. I'm working hard at it. But the basic idea, when you rearrange the rows and the columns so that they form kind of diagonal lines, or equivalently, we put all the low numbers in a corner, we tend to get interesting and much easier to read tables.

And you might not believe me, but you're about to see it for yourself.

So here we're looking at all of the correlations. What we're gonna do is we're gonna move around the rows and columns so that all of the small numbers go into one corner. And because they're already comping in the bottom left corner, I'm gonna make that the corner. What do we need to change?

Well, the first thing we can see here is we've got Doctor Who at the very bottom, but its numbers aren't the smallest numbers. So I'm gonna drag it up, and I'm gonna release it to just under Star Trek Discovery. It's got similar numbers. Notice that because I've got the same data in the rows and the columns, the column also got reordered at the same time.

I can see here that the undoing is also in the wrong spot. It's got pretty low numbers, so I need to drag it down.

Put up, like, the handmade's tail, which is a bit similar.

Figo's also in the wrong spot. It's got the lowest numbers. It's not correlated with anything else. I'm gonna drag it to the bottom.

And so we've moved most of our numbers into the bottom of tranquil, and I think it's easy to read. But this is one that when we show it as a heat map, you're going to be staggered by what we see. Staggered. It's pretty cool. So what we can see here is it's actually formed two big squares.

So we've identified two clusters by reordering or segments, to use the language of diagonalization, of shows. So the first one are all your classic science fiction shows. They're all clumped together. And then we have the premium adult TV viewing also clumped together.

We can see The Handmaid's Tale, which is both premium adult viewing and science fiction, is closest to the science fiction cluster. And we can see a little smaller block within, a small segment, within the science fiction programs, which is the super nerdy science fiction programs. And then within them, a subgroup, which is Picard and Discovery. And if you're a nerd like me, you'll know they're both from the Star Trek universe. So we've done a great job of finding the story in that data using diagonalisation.

The next technique is to decompose data, to pull it apart. Now you might think, why do I want to pull something apart? Well, you pull it apart because and pulling apart feels like the opposite of data reduction, but you pull it apart because often the patterns are easier to see in a part of the data.

So take, for example, profit.

If you're really trying to understand and compare two companies, profit is rarely the best way to compare them. Depends what you're trying to do, but often you get a much more interesting story if you compare them based on revenue. And that's the most well known decomposition, perhaps, which is profit is revenue minus cost.

Revenue, you can break up into number of customers times units per customer times price per unit. And this TV viewing data we're looking at before, here, the interpretation was that people on average watched zero point seven one or seventy one percent of The Mandalorian's program. We can break that up into two separate tables, the percentage of people that watched more than one episode or the penetration, to use the jargon, and amongst the people that watch more than one, how often did they watch, what proportion of watch is seventy two percent. And if you multiply these two numbers together, you get back to the first number.

Why would we do it? Well, look here. On this table here, we saw that was a long way behind.

But we see when we look at the penetration, it's not such a long way behind. The real difference is that Picard had a much lower frequency amongst the people that watched it more than once. It was very poor at keeping people loyal to the program than with Mandalore and Strange Things, and that was really its problem.

Another kind of decomposition is to use an algorithm to pull it apart. Things like factor analysis, correspondence analysis, R and D doing that. The most well known one is one that many of you who went to business school will have seen, which is seasonal decompositions, you've got data like this and you know the wiggle is, know, the high peak might be summer or winter or something. And you can, using some principle math, pull it apart to the seasonal element and the underlying trend, another form of decomposition.

The last of the techniques is to use common sense. As many of Witters noted, the problem with common sense is that it's not so common. But in market research, it really reduces to three things.

The first is we need to look for consistency. If a result is not consistent with existing data and theories, it is probably wrong.

The second thing is we need to smell it. If it smells fishy, it probably is.

There's a rule called Twyman's rule, which says that any result that looks interesting is probably wrong, and this depressing rule, sadly, is usually right.

But the third secret and the one which I'm gonna share with you today is to look for apes.

No. Not those kind of apes. Ape stands for an alternative plausible explanation.

If you find an alternative plausible explanation for the result, it's the job of a researcher, and this is something that AI can help with, but he's nowhere near as good as humans yet, thankfully. It's the job of the researcher to check what other plausible explanations there are other than the most obvious one. And those of you who've got a great eye for numbers probably already did this on table eye previewed before.

So Burger Chef, right, twenty one percent to five percent seems catastrophic. You've got your top line prepared. The audience is gonna be very excited.

But should they be? Because if we look historically, actually, it's been at five percent, only a little over a year. That's a massive change in market share.

And clearly, the last result of the twenty one percent may actually be the most problematic result.

So what's going on here?

Explanation one, it's a very volatile market. People are competing heavily for these people, the under 25s men. Possible. Explanation two, we did something wrong with pronouns.

Maybe. Explanation three, and the one which I'd want to investigate, is we have a underlying data collection issue because anybody who's ever tried to recruit for a survey, males under twenty five, knows they're kind of hard to get. And that means sometimes you use some slightly dodgy sample to get your numbers up, and maybe that was really what happened. And actually, our market share hasn't moved at all.

And this comes back, remember, to Reiman's rule. If a result looks interesting, it's probably wrong. Could be what's happened here.

So this is what we've covered. These are the data reduction techniques. What questions have you got? Please type them into the questions field in GoToWebinar, and I'll get onto them now. Sheila says, what do the arrows mean? Cool. That is a great question.

Let's have a little look.

When we collect market research data, we're trying to show changes in a market. And the most obvious explanation is that when we see something's changed, the market in fact changed. So in this case here, the proportion of people or young men to consume Burger Chef dropped. That's explanation one.

Alternative plausible explanation two is that the drop is just due to random noise.

It's just randomness in the data, sampling error, to use the jargon. Now, the way that Displayr shows a table like this is because it's time series data, it's compared this number with this, and the statistical test has been conducted, telling us that the p value, which I ain't going to define in this webinar, is zero point zero two, which tells us that it's very unlikely that it's sampling error, that randomness has just caused such a big fluctuation. It's likely to be a real movement of some kind of the data, but that doesn't mean a real movement in the market. It could just be we stuffed up some aspect of data collection, as I was talking about before.

The other way you can show significance, it's kind of old away. I don't find it very useful myself, but a lot of our clients love it, is you can show significance using little letters comparing each column. But I find it very hard to quickly spot interesting results this way.

Sheila also asks, are the numbers compared to the net column? Well, it depends on the table we're looking at. So if we go back to this column, we don't even have a net. Because it's time series here, it compares to the previous period. If I was to say now let's just create a new table. We'll go like before, go aided awareness by age.

Here, what it's doing, you can think it is comparing to the net. At a technical level, it's not really valid to compare a number to the net because the number here, the net includes this subgroup. So really what you want to do when you compare a number at sixty seven percent is you want to compare it to all of these other categories combined together.

So if you're trying to see if the fifty five if the forty five to forty nine had a higher had a different score, you would do this comparison.

And you then might select those cells and do the statistical test which compares those columns.

But the way that Displayr works for free, Q as well, is it's automatically done that in the background. So if I click that, I'll get exactly the same answer just for that.

Says, hi. I think I missed a few first few seconds.

Can I show how Tim created the recommendation slide with AI? Was it recommendation for the whole report or one chart? It was the recommendation for the whole report. I did it using a skill that I've created for myself down here called add pages of conclusions. And that skill asked me which pages I wanted to use, and I said everything in the report.

It also asked me a few other questions. We're gonna make that skill available to everybody next week.

How do we decide which technique to use? Is there any priority that we can use? There is not. This is you can think of the the these are your potential tools.

Right? Thinking of the kitchen. Here's your food processor. Here's your stove. Here's your knife. They're all techniques that you do need to apply.

Unlike with cooking, though, where you need to get the order right, usually, you look at something and you find which of these can be best and you apply it. Look at it. Then you keep reapplying until it's become simple. It's a habit thing, a practise thing.

How about if our client wants a dashboard of key metrics? Can we do this? Sure. I mean, any report that you've created, you can just share, publish to the web, and then becomes a report like what I showed you here.

So if you want it to be a very small report with just a number of key metrics, you can do that. If you want it be detailed, you can do it. The smart play these days, though, is to actually and this is a big change. When I started, there was a derogative term used for market research to use data duct.

They would give a client a pile of five hundred tables and leave it to the client. The good researcher had the job of distilling that down to one page of insights. But today, I think you can data dump as long and you should, hide heaps of dubs and then just give them the tool to chat with it directly like this.

Now for those of you I'm always shocked that people turn up to my webinars at all. But to just pursue this, I've added in a newsletter to my LinkedIn. So if you find me, Tim Bock on LinkedIn, I've got news newsletter called Understanding People's Scale, which I'm doing once a week.

If you're one of our customers, thank you so much for being our customer. If you're not, become a customer. You can purchase straight on the website or book a demo and get somebody to take you through it all. Anyway, thank you all for watching this webinar. Those are our customers. Thank you for being our customers. Have a great day, everybody.

Bye now.