This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
If you need more geographical information on a coordinate, try converting it into a spatial object and using the Find Nearest Tool to find coinciding Experian geographical data from an Allocate Input Tool.
Have you ever used the ConsumerView Analytical File in US Core Data and stared wide-eyed at the codes returned? There is now an alternative to looking in the documentation for the coded values! The ConsumerView Renaming Macro allows you to rename the codes into readable data.
If you are building a predictive model, inevitably you will want to analyze the effect that your independent variables have on your dependent variable. This article is meant to shed some light on the Alteryx-specific options for this type of analysis!
The ConsumerView Matching macro enables users to match their customer file to the Experian ConsumerView data. Starting with customer information such as name and address you can leverage the ConsumerView macro in Alteryx to append a variety of information about your customers such as household segmentation, home purchase price, presence of children in a home, estimated education and income levels, length of residence, and many more!
Have you ever used the Allocate tools and received back some strange looking variable names? You're not alone! The Allocate Rename Fields Macro will allow you to rename your fields into readable variables.
The macro can be downloaded here. Note: This will navigate you to the Alteryx Gallery. Select "Download & Install the Allocate Rename Fields Macro" and follow the prompts to install.
USING THE TOOL
The Allocate tools allow users to enrich their workflows with third party data provided from Experian and the US Census. This data contains demographic and household information by geography. Allocate tools can be found under the “Demographic Analysis” tab in the Alteryx toolbar; they include the Allocate Input, Allocate Append, Allocate Report, and Allocate Metainfo.
Allocate Input and Allocate Append tools allow users to select variables to display by geography. Once configured, the fields returned look something like this:
Add the Allocate Rename macro after the Allocate Input/Append. In the Configuration window, select the Dataset that you are pulling from. Press Run for the magic!
Voila! Your field names are now human-readable.
What if my company blocks access to downloading new tools/macros from the Gallery?
In the case that you cannot download this macro, you can use Alteryx to dynamically rename the field names. See is it possible to get the variable name I see in the Allocate tool?
Mosaic BG Dominant and Mosaic BG Household Distribution counts are balanced to Experian’s census estimates. ConsumerView is a marketing file and therefore doesn’t need to be balanced to the census estimates.
Question How can you calculate the standard deviation of the population (SDpop) rather than standard deviation on the sample data (SDsample)?
Answer Please find the attached v9.5 macro, special thanks to one of our Community users!
Standard deviation of the population (SDpop) and standard deviation on the sample (SDsample) are very similar - the only difference is that in SDpop the sum of the squared variances is divided by n instead of n-1 as it is for SDsample (where n is the number of data points in the vector). This macro takes a vector of doubles which needs to be named 'dataVals' and it returns the standard deviation of the population. Accurate out to the 9th or 10th place.
It is now easy to update your variable names using the Dynamic Rename tool and the MetaInfo. First, on the Allocate MetaInfo tool, you will want to select the data you are using and leave the Variables selected: Next, bring in a Dynamic Rename tool, select the Take a Field Names from Right Input Rows option (have your MetaInfo come in on the Right). For Old Field Name from Column choose Name, and for New Field Name from Column choose Description. I would recommend also choosing Ignore for if number of Field Names do not match. What the tool is doing is matching the data in the Name column of the MetaInfo and if it matches any of the names from the left Input data, it will replace it with the data in the Description column of our MetaInfo. I choose ignore because the tool will try to match all variables in the MetaInfo data set to the left input data, and throw a warning or error if it doesn't match. Below is an example of what the process would look like. It's also been configured into a macro that can be downloaded below: The configuration of the macro is simple, just pick the data set you are using (this should automatically populate with what you have loaded on your computer): Below is an example of how your module would look using the macro, it would need to be place somewhere below your Allocate tool to work: After using the macro (or process) the variables will appear as the description (which is what is shown in the Allocate tools):
Real Estate Model Site Analysis Module Business Problem: Modeling trade areas for potential sites can be time consuming and is analytically intensive, yet accurate sales forecasting of prospective locations depends on these types of analyses. In order to comprehend the effect store placement will have on a retai l network, consumer demographics must be examined and incorporated with data from existing and competitor locations. Actionable Results:
Forecast potential sales for a desired location by modeling trade area analytics
Consider existing and competitor locations to find optimum markets for expansion
Calculate trade area values for potential and competitor sites using weighted logic that considers the distance of potential customers to the site and their penetration indices
Overview: T rade area analysis of a prospective location can help assure it will return profits without cannibalizing existing sales from other sites. Current store and competitor locations must be considered in order to identify ideal markets for expansion. Vertical: Retail, Real Estate Key Tools Used: Trade Area, Street Geocoder, Allocate Append Application Process: This app forecasts potential sales for a desired location by modeling associated trade area analytics, demographics and psychographic consumer profiling. The process also looks at current store and competitor locations, and uses these to identify optimum markets for expansion. Trade area values are then calculated for both the site and competition by weighing the distance of potential customers to the site and their penetration index.
Household Level Analytics Module
Business Problem: Businesses investing in new customer acquisition will be more successful in reaching prospects if they know which consumer profiles best describe their current customers. Compiling customer databases through marketing or loyalty card programs allows businesses to know who their customers are, as well as where they are located. When correctly leveraged, this type of information enables strategic and focused spending of marketing funds. Actionable Results :
Understand the demographic attributes of your customer base
Target new customers that fit the profile of your current customers
Ensure that your advertising and marketing funds are spent in the most effective way possible
Overview: Would you like to identify key demographic traits of your target customers? By appending household-level characteristics to a customer file, you can achieve the most accurate Consumer Profiling of both existing and prospective cstomers. This analysis allows business owners to target households that are not in their customer database, but are in their trade area and match the demographics of current customers. Customer acquisition using targeted households is a more efficient way to direct spending on advertising and marketing programs. Vertical: Retail Data Utilized: Customer file containing the following fields:
Customer Address containing street number, street name, city, state
Customer ZIP Code
Alteryx Data: Experian Household File Application Process:
The selected customer file is run through the Calgary Join tool using Experian household data to isolate the Experian records that match the customer records.
Fuzzy Matching is then performed to eliminate all duplicate records.
Finally, the wizard outputs the customer file with appended household-level data.
Issue: In the Age by Sex Comparison Report, the Age 55-64 range was incomplete and the % did not match the range on other reports. The fix: The Age range formula in the report template is corrected and the file is available for download. What to do with the file: Copy the Age By Sex Comparison Report.rptx file to your locally installed Allocate directory overwriting the existing copy. The default directory is: C:Program Files (x86)AlteryxDataProductsPortfolioALTERYX_US_Experian_12A_12BReports.
If you have a set of points and need a location geography you can create a small trade area around each point and then run use the physically within option in the Allocate Append. The tool needs polygon in order to do the spatial match.
Rendering to pptx: -Set the height and width for each component on the report. When automatic settings are used, they will yield less desirable results. Layout Tool: -Use Layout tools in abundance when organizing snippets in your reports. The flexibility and capability of this tool lends itself to concatenation, especially when creating borders and using the grouping options. -Use the Separator option in the Layout Tool. To add some spacing between snippets with the Layout Tool, check the Separator checkbox. By choosing the color white, and 5-10 pixels of separation, your report’s snippets will have a pleasing “buffered” appearance. Custom Allocate Repots: -To create a custom report to be used in the Alteryx Allocate Report tool, open Allocate.exe. C:Program Files (x86)Alteryx2011DataProductsPortfolio -Select the variables you want in your report on the Variables tab, then click File - Export .RPTX File. Select whether you want a Comparison/Summary or Rank report. If you want to include a specific logo, check Pick a logo for the report. Click OK. Give your custom report a name, and make sure that it is in the Reports folder. In order for it to be an option in the Allocate tool in Alteryx, you need to save the .rptx file in the appropriate Reports folder for the data you are working. Click Save. Your custom report will now be available through the Allocate Report tool inside of Alteryx. Best Practice: When creating an Analytical App that creates a PDF report, it is often best to have the app Render to a .pcxml (Composer) format. When the app is loaded to the Gallery or AlteryxWeb, the end user will have more flexibility by having the ability export the report as a PDF, PPTX, DOCX, XLSX.
Question Why am I getting zeros from the Allocate Tool?
Answer In the Allocate tools, you must specify a Spatial Field for the geography to calculate demographics for that area. Block Centroid Retrieval Methodology is used and pulls data for the centroids that fall inside of your polygon. Demographics can only be extracted for polygons, not points. If you have both a point and a polygon in your data stream (a point location and its 3 mile trade area) select the polygon (radii), not the point.
By default, Alteryx will select "SpatialObj" which is a point. If “SpatialObj” is selected you will end up with zeros for your selected data variables. Be sure to select the polygon instead, for example, “SpatialObject_Trade_Area".
Best practice: Add a Select tool prior to the Allocate tool and rename your spatial objects. For example, "My Location" and "3-mile trade area".
Update Allocate Append tool using XML
Works in 9.5 and 10.0
You’re running a process to select certain variables to be used within a model. You’ve built your process, but you’re getting tired of having to run it twice. Once to pull thousands of variables to check for relevance, and a second time with just the variables you want to include in the final model based on the tests you’ve run.
There’s good news! You can use the Action tool within the Interface toolset to update the Raw XML of the Allocate Append tool to dynamically select the variables you want to use, and it’s not as hard as you might think.
The first thing we need to do is find out what the XML code is for the variables we want to use, and the format it needs to be in for the Allocate Append tool to recognize it. You can enable the XML view from the User Settings menu (Options è User SettingsèEdit User Settings). On the Advanced tab, there is a check box to “Display XML in Properties Window”:
Once you’ve checked the box, return to your Allocate Append tool, or any tool on your canvas, and you’ll see a new option on the right hand side that will allow you to see the XML code the tool is creating.
From here you can get the format you need for the XML code that we’ll pass into the macro to be created later.
Once you know the variables you want to use, you can use the variable name (code, not description) to build out the XML string as show above. If you select multiple variables, what you’ll notice is that they are each on their own line under the “<Variables>” tag in the XML code. The list you make must follow the same format:
In the sample workflow attached, you’ll see that I am using a Text Input tool to simulate the data stream that contains the fully compiled XML strings needed. As you will most likely see in your data, I have one variable per record. The problem is I need all of the variables in the same cell, on their own line. So how do we combine the records into one, and add a new line?
The answer is we use the Summarize tool. Within the Summarize tool we can use the Concat function to combine the XML strings into a single cell, and in the concatenate Properties section, we can indicate that we want to use a new line as the separator by typing in \n.
Now that the prep work is done, all we need to do is pass this new variable list into the Allocate Append tool through XML. This can be achieved with a simple Batch Macro. For the Control Parameter you want to use the Variable list that we just created. The Control Parameter gets connected to the Allocate Append tool which adds the Action tool as shown below.
In your Action tool, select the option to “Update Raw XML with Formula”, expand the options under Allocate Append until you see “Variables” and highlight that section. You’ll want to update the Inner XML, and the formula to use is the connection from the Control Parameter as shown below.
Once you have this set up, simply add your Macro Input (for your incoming data stream) and Macro Output (to feed back into your workflow) to complete the macro set up.
Return to your original workflow, insert your newly created Batch Macro and connect your inputs. Your variable list stream will feed into the ¿ input, and your main data stream to the other.
You’re now set to dynamically change the variables you are pulling! Simply run your process for selecting relevant variables, build your XML strings through the Formula tool and pass them into your macro.