Alteryx Designer Desktop Discussions

mstern · ‎03-06-2017

Hello,

I've only been using Alteryx for less than 2 months, so it's very possible I'm using these tools incorrectly. In simplest form I'm trying to create a workflow that defines a trade area to determine how many people and households are within it.... Pretty basic stuff. To make it only slightly more complicated I'm selecting all of the zip codes that touch or intersect the TA so I can determine what % of each zip code falls within the TA.

I'm using the Allocate Input to pull all the zip codes and then using the spacial match to determine which zips touch or intersect the TA.

I'm then using the Calgary Join to determine which people & Households fall with the TA.

For both tools I've selected the same Experian DB (more recent Vintage).

The workflow itself works as far as selecting and breaking out the zips, but while validating the data I can't get the total population or households from the Allocate Tool to match the Calgary Join even though they are pointing to the same set. Allocate makes it easy to pull the sum HH and Pop, but it's not quite as clear with Calgary join. In the Calgary Join I'm summing the "Number of People in Living Unit". For Households I've tried counting the unique number of AddressID's as well as the HH_ZeroBasedRecordID, both of which give me different values, and neither match the Allocate Tool results.

Is there a best practice or recommended way of pulling HH and Population data from Calgary Join? I've attached a visual of my workflow, but let me know if I should provide anything else. The workflow gets a little ugly towards the end, because I was trying to summarize the different combinations of fields to see if I can match the Allocate results.

Thanks in advance for your help.

LonnieY · ‎03-06-2017

Hi. I think you are trying to compare population estimates at an aggregated geographic level with Experian households aggregated by their address. Two very different methodologies. The Allocate demographics for ZIP Codes will seldom match the Experian HH summarized counts using the Calgary tool. While the ConsumerView file may be included in developing the Allocate CAPE estimates, CAPE is not a simple rollup of the ConsumerView records.

Lonnie

mstern · ‎03-07-2017

Thank you Lonnie.

So if I want to aggregate households and population in and around a Trade Area is using the Experian household CV data the best way?

Is counting unique ZeroBasedRecordID's the best way to estimate the number of households in a TA? It doesn't look like AddressID on it's own takes into account Suite or Apt numbers.

As for population, is "Number of Persons in living" unit the most accurate way to estimate population within a trade area radius or drive time?

Is there a better tool or strategy for calculating these?

Thank again for your help!

LonnieY · ‎03-07-2017

What works best for you depends on the end use of the information.

If a robust set of demographics for analytics is the goal, then the Allocate Input or Append tools with CAPE data are a good choice. The data methodology is explained in the quarterly release documents also on the Community. This is my first preference and the data generation is pretty fast using Allocate tools.

If numbers of households/individuals based on ConsumerView attributes (not found in Allocate) are required or selecting households by a criteria and aggregating for marketing purposes (but not mailing using a ConsumerView list), then the ConsumerView Calgary file might be used. A few things to consider....

When summarizing addresses to a geography, records not geocoded precisely will be included and may inflate the numbers. Records without geocodes would not be included.
The ConsumerView file reflects the first 6 household members which may under represent the total
Calgary joins with ConsumerView (spatial joins) might be slower than using Allocate
Not all households may be captured. Households can opt out of lists.