BI Spatial - From Customer List To Retail Expansion Strategy - Inspire 2017

No matter what industry you're in, understanding who are your best customers and where to find more of them is the brass ring. And with Alteryx, it's a simple process. In this session, we'll demonstrate a few easy steps that will help you move from Excel spreadsheets to the foundation of a fact-based expansion plan. You'll leave understanding how to utilize ConsumerView data, Calgary join functionality, spatial processing, and reporting tools to produce a final report that's fit to share with every executive in your organization.



Video Transcription


Andy Moncla:
Did everyone intend to come to this presentation, so like a flight, once we start it's... I apologize for the long title. We're going to try and get through everything that is included in the title. We'll see what happens.

By a show of hands, who uses segmentation now? Who uses Mosaic within... Very good. Who uses some other segmentation? Which other segmentations? PRIZM?

Crowd question:
Personicx.

Andy Moncla:
Personicx?

Crowd question:
Yeah.

Andy Moncla:
Okay.

I don't want to debate which segmentation method is better, I just want to show you how I use it within Alteryx. My clients and I have had some pretty good success with this, so we're going to talk about segmentation very briefly, show you how to bring...

How many of you are new to Alteryx? You all know how to do a file input, so we can cut those slides out. I just wanted to get level set as to what your expertise level was, and you'll do the same for me when I'm done.

We'll go through the ConsumerView matching, how you do a segmentation study, prioritize your next markets, and then once you get to that market using Mosaic in ConsumerView, how do you pick the best areas. I'm not going to get into competition or drive times or anything like that, I'm going to focus on Mosaic. I encourage you to stop me, interrupt me with questions whenever you want to.

Getting back to Mosaic, this is Experian's definition. You can all read, probably better than I can. Anyway, what we've had success with using Mosaic, we do a lot of work with grocery clients, and they are primarily higher-end grocery clients, so age, income matters. Mosaic fits very well into what we do, and you can see the axes, low-income to high-income, young to old.

We're going to go through this as if this was a real study. We're going to use a grocery store in my hometown of Greensboro, North Carolina. Some of you may be familiar with that, probably not. If you have questions as to where we got the data, we are not using a client's data. You can be at ease that we're not divulging anything that a client gave us.

You all know how to import data so we can bypass this, but all you really need is the address fields to do Mosaic appending. Ideally, you want your customer ID, if you have the name, that's great, but you don't have to. I talked to Experian about when the name does not match in ConsumerView and they said it's still valid to use that address match. If you disagree with that, I think there's some Experian folks maybe in the room, but...

This is a quick tip. How many of you copy from Excel and just paste right into Alteryx? Did the rest of you know that you could do that? All you do is you bring down a text input tool, and as long as you click in the top right corner, right-click or hit the paste button, it'll just populate in the same structure as Excel. You don't want to do that with tens of thousands of records, and if you have zip codes with leading zeroes, they disappear, but for other jobs, it's a very quick way to get data into Alteryx.

ConsumerView matching. How many of you use that currently? How many of you have the data, have ConsumerView? Okay. If you have it and you're not using it, you're missing out on a great tool that's readily available. When you connect your data input to the ConsumerView matching tool... I can't see, do we have a... Is that a laser? Well, that doesn't work either.

For the next few slides, the tool is in the top right-hand corner that I'm talking about. When you attach the customer data to ConsumerView with matching, you get the option of matching by address. You can see at the top right name and phone fields. We're not using those for this example. The option of running it through CAS is something you should always do, it's a best practice, so please do that. CAS is a postal system that standardizes addresses, so it does increase your match rate.

In the example, the workflow up there, you see the ConsumerView with matching tool and you have two outputs. One is a report and one is the actual data. The report gives you an idea as to how well you matched. It doesn't match well with PO boxes or businesses. You need to keep in mind that ConsumerView with matching is only looking at residential addresses. If you are mapping out customers or appending customers with lat-longs that are businesses, use the address match tools that are available. We're focusing on residential right now.

Is this too basic? Okay. You see that the address only... Can you read that, because I can't see it myself. It tells you your match rates. These match rates are a little bit higher because I used a better data source, but typically, we see about an 80% match.

We're going to go through the next set of tools in the top of the workflow. The select fields... We're really only interested in this example in the household ID.

Excuse me.

The household ID changes with every data iteration. Another best practice is do not use that from one data iteration to the next. It's simply a record ID and has no association over time, so do not use that. I found out the hard way.

In the data that we're looking at, we're interested in Mosaic household, Mosaic zip plus four, the spacial object, and GeoLevel. By the way, when you do the ConsumerView with matching, it appends 125 variables to your file. I use... How many use more than ten? Well, there's some smarter people in here. I use a couple that help me use this job.

One of the things that happens when you append your customer file with Mosaic is Experian sometimes does not have enough information to actually attach a Mosaic code to that specific address. When that happens, they also give you the most dominant Mosaic code for the zip plus four. What we do is, as a standard practice, we automatically change that if it's a u00, we put the zip plus four Mosaic code on there. You can choose not to.

There's some perfectionists out there that say, "Only want to match on name, address," but if you think of who lives on a zip plus four, basically a block [inaudible 00:09:19], typically there's some uniformity to who's on that block, whether it's the home price or home size or age of home, so you can choose to use it or not. I do use it. We use the formula tool to do this. Everyone here probably uses these as well.

Then, the GeoLevel is a... How many of you do spacial modeling at the household level? You have to be careful of the GeoLevel, so if you're doing drive times between your customers that you're appending, if you use a GeoLevel of zip5, you're basically taking the centroid of the zip code. What I have here, the top ten problematic points that are included in ConsumerView. You can imagine how bad a model would be if you misallocate 67,000 households if you're doing drive times. You really need to make sure you back out the GeoLevel, that is zip5.

Is everybody already doing that? If you need to excuse yourselves and go change your models, I would understand. It's primarily in urban areas, because you have more people there. Anyway, that's what I go through when I'm doing the ConsumerView with matching. I apologize, I started with a cold this morning.

You do the appending so you can do a segmentation study. I'm sure everyone does their study differently. What I'm looking for is a group of primary segments, secondary segments, and I'm typically trying to capture 70%-80% of the customers with that primary and secondary group.

Can you even read that? The top is the primary... Those are the primary segments, and the bottom half are the secondary segments. This is a report that I spit out of Alteryx. We're going to walk through how we get to that primary and secondary, and then also, how do you build that chart or table.

By the way, if you come see me after the presentation, not immediately but maybe in the Solutions Center, if you're interested in any of these workflows and you have a USB or you give me your email, I'll mail them to you and you're welcome to use them.

This is my segmentation workflow. I'm going to break it into three different sections: the Mosaic summation, when you identify your target segments, and then the table creation.

On the Mosaic summation, we take advantage of several tools. I'm not going to talk about every tool. If we look at the top line or top half of the workflow, we're bringing in the appended data, excuse me, and then we're doing a spacial match for the DMA that the store's actually in. I want to limit it to those customers. I use Allocate to select the Greensboro DMA and do the spacial match, and then what comes out of it, I do a summary by Mosaic code of the number of households, so how many A1s, A02, A03. The second summarization tool is for the total for the customers in that market. I append those back and then I use my formula tool to calculate the share that each Mosaic segment represents.

I think it's important when you're doing a Mosaic segmentation analysis to understand what the appropriate base geography is for establishing indices. How many of you use DMAs? National? Both? Okay. It's important to understand how those segments stack up relative to in that store versus the rest of the market.

On the bottom half, that's what we're doing, using that same DMA boundary file or spacial object coming out, we're matching it up to Calgary Join so I get the ConsumerView households, and I just remembered that I needed to spell out one very important point. I'm only using ConsumerView. I don't mix ConsumerView with households with anything coming out of Allocate from the Experian data. It's two different methodologies. It's okay if you want to use Allocate to get some general direction, bit if you're going to do something that's very precise, I think it's important that you stick with one or the other. I think Wendy Chow has spoken about that in previous presentations. This is only using ConsumerView. If you want to talk more about that afterwards, I'm happy to do that.

Anyway, what happened at the top half we doing on the bottom half just for that DMA so we can establish our baseline, and then we bring them back together so we can calculate those indices. I think you can see the formulas. The template that Alteryx gave me used a very interesting font, so on the second tool here, that's supposed to be a zero. Anyway, we're dividing the Mosaic share for the store by the Mosaic share from the DMA to calculate the index.

As a safety for myself because my if/then statements aren't as precise as I'd like them to be, so I found that filter tools do a better job of taking those that meet this criteria. I treat them one way and then I use a filter to do the others. It's a personal preference. I'm sure some of you could write very precise if/then statements.

In this example, for the filters for primary, what I'm looking for are those segments that contribute at least two percent of the total customers and also index at least at 150. The secondary, I'm looking for those that are at least one percent and 100. It's pretty general. This is for this presentation. It takes more work than that to actually get to a good set, but as a starting point, that's what I do. The reason I have the browse tables there is I want to see what the results are as I'm building this.

I'm curious, does anyone want to share what your minimum threshold is for a primary? Does that seem reasonable to you? You seem to be using a lot of segmentation systems so I'm picking on you, so.

The point is, with Alteryx you can set your criterias however you'd like to. The table creation, we're really just summarizing, at the top half, all the primary segments and the secondary segments, and then the primary and secondary as a target. What we do is we attach a record ID to each of the primary, secondary, and total, and we always give it like 100 because it's always going to be more than the number of Mosaic segments, so when we do a sort later on, it's going to put them in the right order. We also output the actual target segments so we can use that for the mapping portion coming later.

Any questions so far? If I finish this and we have no questions, which it seems like there's not going to be any, we can open up some workflows and we can actually dig into it if you'd like to.

The table creation. This is... I'm borrowing the behavior metainfo tool to get some information about the Mosaic segments that I can bring into the table. You see I have a text input here. I found it easier just to have a text input there with all of my Mosaic information that I can use over and over again and I don't have to worry about, "Where's that Mosaic file?". Then, it's really just bringing all that data together, sorting it based on the order field that I'm using, which is the record ID field, and going to the table creation.

Are you building tables within Alteryx? Anybody? Do most of you just spit the data out to Excel and finish it there? Yeah.

You can do a pretty decent report in Alteryx, and I was excited when I heard that there's new reporting coming with Alteryx. I've heard it for years and I'm looking forward to it. I've been looking forward to it, I think I've had three kids graduate from high school while I've been waiting.

It is usable as is. What I find is I'm outputting directly to PDF and I'm using a tabloid, and from there I can actually bring it into PowerPoint or something else that I want to use. I set my width at sixteen inches. You can go in and you can rename fields so if you've got that Mosaic household field and you don't want that, you just want to call it Mosaic. You can set the width for each variable and so on.

I want to talk about... At the bottom you see I've circled the column rules. How many of you are using column or row rules?

Hello, Keith.

In this tool, we're setting the colors fore every group of Mosaic. If it's in the A group, we're going to use the purple that is being suggested. Again, it's a pain to do this the first time, so if you want this, see me, I'll give it to you because I don't want you to have to do it.

We have A through S, we set the colors. I chose to leave the text white, but I've gone back and fourth: should that be a black text or white? That's preference and I'm not going to bother you with that.

Using that workflow that I showed you earlier where you bring in your customer data and you append it and you use those primary and secondary rules in your indexing, you can create a report and it runs in just a couple of seconds. That's not true, it's more like a minute.

Crowd question:
[inaudible 00:22:36]

Andy Moncla:
Yeah. Yep.

I'm going to jump out real quick before I get into something else. You can do some pretty cool reporting. Everything that you see here is going to be produced in Alteryx. This is not a sales pitch, I'm sorry, but this is an example where we were using the... What's the name of the layer with the satellite view? It's not the TomTom, I know that.

Anyway, you can see the satellite imagery. This is... the data that we're using for the grocery study is actually based on mobile phones where we capture that data and we analyze it, and then we attach Mosaic to it. All these maps that we do are produced, all the trade areas, everything is done within Alteryx using the reporting tools, using the mapping. It's kind of a ugly map, but you can actually bring in images.

Is anyone from Experian here? Oh, hello. Mandy likes us to go to her website and use the segmentation portal. If you go on to... Raise your hand again, please. If you see Mandy after the presentation, she can tell you how you can get credentials to go into the segmentation protal and have access to this tremendous up-to-date data. Mandy doesn't like using those images because they may change, but for doing a report, this is a great way to bring in images, and for every report, you can bring in the corresponding Mosaic code. Let me get out of that and go back to the PowerPoint.

For market prioritization... I'll drink some water before I try and say that again. This is the workflow that I put together for this demonstration. Again, I'm starting with Allocate to identify which markets I want to use. Allocate's a great tool for the base geographies. The store that we were looking at was in Greensboro so I decided to look at all the other DMAs in North Carolina to see what would be the next logical step if that was a single store and they wanted to expand to the next markets.

Again, using ConsumerView with matching... How many of you are aware of the spacial indexing within ConsumerView? Yeah, it's... You can pull back these households in seconds, so it's really a great tool to use. Using the same methodology of... You're not using the zip5 GeoLevels, changing the Mosaic of u00 to the zip plus four, you can get that data very quickly for every DMA in north Carolina. The text input that I have here is actually not a... Oops, sorry. It's not a text input but the file input that I created from the previous job which is the Mosaic segments. It's simply the segment code and is it primary or secondary. I do the match this way rather than going into ConsumerView and clicking on all these different segments and saying, "Okay, these are primary, these are secondary." It just seems to go faster this way and you can do it at one time.

The union tool that you see after that, there are going to be segments that weren't primary or secondary. I want to bring them back in so I can calculate my totals, and then after that I've got a formula tool where I'm just saying, "if no, then,"... I think we use, "other". Is that what I said? Yes, other. Then it's a simple summarize tool, cross-tab, and then I'm doing some calculations on there, and with that, it creates that table there. That's actually... I copied and pasted... I heard Dean say that over and over again this morning, but that actually went to an Excel report, so that formatting and so on. Now that I know that Raleigh-Durham is the next best market for those same types of Mosaic segments...

This is ugly, I'm sorry. I really don't like the default colors for the ramping within the mapping tool, but let me walk you through this and then I'll show you what you can do after that. Again, using the Allocate tool to get my base geography, and I'm using the grid overlay, or the grid tool. Anybody using that? If you want to get... I'm glad a couple people... If you're doing any kind of store planning and you want that precision of where your target segments or you want to do some spacial modeling but you don't want to do every household, doing the grid overlay is a great method of getting to a smaller geography.

On your left is a... Using a one-mile grid cell, and then a half-mile to the right. Again, using ConsumerView with matching, I'm getting all the households. This is getting to be like a broken record, but you're using the same methodology over and over again; In fact, this is the exact same job as the previous workflow but I simply added the grid cells and did the summarizations at that level. This ran I think in just about a minute and a half to get to a pretty precise map even though we don't like the colors.

You can do a better heat map if you take the time to go into the mapping tool and choose colors, change the opacity. There are all sorts... How many of you use GIS? All right, that's good. If you know how to operate a GIS, you understand the layer mindset, and that's the same within the mapping tool where you just have to change layers and so on.

I think... I was told to make it a half an hour and stop for questions. It's 31 minutes. Again, if you want to dig into any of these in depth, we can go to the... What is it, Solutions Center in the community area and I'm happy to show you these, share them with you. Are there any questions? Would you please go to the mic? I was instructed to say that.

Crowd question:
[inaudible 00:30:53]

Andy Moncla:
Thank you. Me, either.

Crowd question:
I just started to use the data bundle and one of the problems I have was the ConsumerView, as you alluded to, you have an excess of fields, of [inaudible 00:31:07]. Are you aware of any macro or anything that can simplify that output that maybe will-

Andy Moncla:
The select tool. No, I say that very quickly. Early on, I would see these messages that I had a temp file of gigabytes or something like that and I would panic, but I didn't have that much RAM or space on my computer, so I called Ned Harding and I said, "I can't have these gigantic temp files." He said, "Well, they're not really there," so even though... If you go directly from ConsumerView... Let's go back up a couple. If you go directly from ConsumerView to that select tool and deselect everything that you don't want, all those gigantic spacial objects, they're gone, so you're not really bogging down your system.

Are you copying and pasting tools from one workflow to another?

Crowd question:
Occasionally.

Andy Moncla:
I do it all the time, because if I've got a select tool or a bit of workflow that I know I'm going to use over and over again, I'll just copy and paste it or I'll create a macro and just use that time and time again.

Crowd question:
There's two problems. There's one about the excessive number of fields, but two is the report out. It's these codes that are meaningless until you go in and you have to write your own formula to...

Andy Moncla:
I'm embarrassed for you all to see my desktop, so maybe I don't have to show that.

What you can do is... I'll share this with you, too if I... Let me go to my desktop. I've created files like this so that as a pass-through, if it gets that code, I assign it this income. Come talk to-

Crowd question:
I'm trying to shortcut this step.

Andy Moncla:
What's that?

Crowd question:
I'm trying to shortcut this step.

Andy Moncla:
Come see me. That's probably the best shortcut I can offer.

Crowd question:
Yeah. It's a very good one.

Andy Moncla:
It's a pain.

Everything with Alteryx, there's that upfront cost of, you have to invest in building your workflows, then you get... The payout is at the back half, but yeah, I'm willing to share all this stuff. Yes, sir.

Crowd question:
Can I say something really quickly and you can review it?

Andy Moncla:
I will.

Crowd question:
[inaudible 00:33:54], wouldn't that also be the Allocate renamer macro that does what he was asking?

Andy Moncla:
Say that again?

Crowd question:
The Allocate renamer macro, doesn't that do exactly what he would need?

Andy Moncla:
Here's the truth. I've been using this for twelve years and there is some legacy code stuck in my mind. If you say it can do it, I believe that it can. I've reused so many of my workflows that... Does anyone else know about that renamer? Would you go to the mic and tell everybody what you're talking about?

Crowd question:
[inaudible 00:34:41]

Andy Moncla:
Allocate renamer, if I heard you right.

Crowd question:
Yeah, it's not there, you have to... It's one of the... I don't know if it's a crew macro or what it is, but we use it a lot because we do a lot with Allocate like you're doing. All you have to do is, it's a macro that's doing exactly what he said, it's a lookup and it renames all of those codes so that you don't have to do the manual-

Andy Moncla:
Well, since this is ConsumerView, does it work with ConsumerView with data because it's not an Allocate...

Crowd question:
I know it not Allocate... It may not. Yeah.

Andy Moncla:
Since I don't use as much of the Experian data from Allocate... I'll use it very quickly, but for-

Crowd question:
You're back-filling to get your summary stuff.

Andy Moncla:
Yeah.

Crowd question:
Right. I wasn't sure. If the codes are the name the same, then that might work.

Andy Moncla:
Yeah, I think that might be a question for Mandy, but I think they are different for things like education, occupation.

Crowd question:
[inaudible 00:35:42]

Andy Moncla:
Yeah. That's a... I promise you I'm going to look into it when we finish. Are there any other questions?

How many of you are more interested in ConsumerView now that you see what's in it. Zero. Awesome. How many of you have customer lists? Why did the rest of you come? Now, I'm joking. There's a lot of data that's available to you that you maybe should investigate. Yes, sir. Yes.

Crowd question:
There's ConsumerView match [inaudible 00:36:34] database, how's that different?

Andy Moncla:
That's a great question.

Let's see here. ConsumerView with matching is when you want to actually append to a list. Have you looked at the macro? I'm going to open it because it's... Let me hide this. It is gigantic.

You know how sometimes you think you can do a better job than Alteryx at doing something? Well, this is one of the times that you don't want to try and do a better job. This has fuzzy matching. If you're going to use name in the matching, it does a great job of doing matching to name. It is a... I think they call it a waterfall kind of method where they're going to try it this way. If it doesn't make it this way, it automatically gets rerouted to another method and another method, and so I always take the first candidate that it brings back because that's going to have the highest matching score.

ConsumerView with matching is really this macro which houses other macros in it. The Calgary Join is simply an index database of all the households in the United States. It has information like mortgage, how much did you pay for the house, when did you buy it. It's more about the household. Then there's the individual database that has... Mandy, how old do you have to be, 19? Yeah. For individuals who are 19 and older, if they have data on those folks, they're in the database, and you can use that household ID with the same iteration of data or release of data, you can match that household to the individual and get that kind of information.

Every field in ConsumerView is indexed so you can pull data incredibly fast, so if I... Let me grab this real quick. We'll go with individual and I'm going to put in my last name which is probably not fair because there aren't a lot of us. Surname is Moncla...

It just went through 140-something million households and pulled back 135 in... whatever seconds, and if we look at that data, does not have the spacial components, so you can do a Calgary Join to that.

Household... That was individual, right? Yeah, and then you match on the ID... Well, that's kind of hard to see there. There it is. There. That's how quickly it took the results from that household data, individual database to see the households, and... Sorry, I was doing business elsewhere.

You can see that I've got a Cajun lineage, everybody's from Louisiana, but that's how quickly it pulls it back. The spacial indexing, that didn't demonstrate that, that's just looking on name, but if you, instead of... Do the Allocate demo real quick.

So, Experian... We want a DMA for... I guess it didn't matter which one I chose, but since we were doing Greensboro... You take the spacial object and you match it to the spacial object that's within the ConsumerView. I didn't do anything like GeoLevel of exclude zip5, or anything like that, but that's how quickly the spacial indexing within ConsumerView works. It's actually taken longer to map it than to pull it back. That pooled almost 800,000 points back that quickly.

By the way, those are all the household points in Greensboro DMA, so it's... The field that we used for the indexing, we use a spacial object coming out of Allocate and we matched it up to the spacial object-

Crowd question:
The one before.

Andy Moncla:
The one before? Oh, I'm sorry. Got to get rid of that. That was the household ID. Let me grab that again. Where is it? Did I miss it, or is it under "HH"? There are you. Zero-based record ID is what it is here, but I think when you're joining it up, it's HH, you just look for that household ID.

Anyway.

Crowd question:
[inaudible 00:43:14]

Andy Moncla:
What's that?

Crowd question:
[inaudible 00:43:16]

Andy Moncla:
Okay. Yes, sir.

Crowd question:
How efficient do your targeting get?

Andy Moncla:
When I used this methodology when I worked for the parent company of North Face and we appended the segments with Mosaic and then we mapped out... We did direct mail in support of the new store opening. We also chose billboards that were in the appropriate places. It was more than double what the previous openings were because we're getting to the people that we knew... but when I worked... The parent company of North Face is VF Corporation, and so we also had wholesale data, so we knew what was selling in what markets, so there was more to it than just the appending.

Anything else? Come by and see me if you want any of this. Thank you.

^Top

TESTEZ LA
PUISSANCE
D’ALTERYX.

Pour Démarrer