Update Designated Market Area (DMA) ID in Experience Event Field Group to String, from Integer

[#1]

Hi All.  I hope things are going well.  I had an idea I was hoping you could consider.

It seems that the OOTB functionality of the AEP data processing from a Web SDK implementation includes converting the user's IP address into the Designated Marketing Area Identifier (DMA ID).  This identifier is part of the Web SDK Experience Event Field Group and again is provided by Adobe automatically, meaning this is NOT something we set or configure within the Web SDK itself (if you exclude "turning the feature on" within the Data stream and Web SDK extension).

The values within this field look like numbers - for the US based visitors

505, 506, 507, etc

The problem with this field is it's data type.  It's set up as an "integer" field, not a text string

For example, ID = 505 is actually the Detroit MI area.  but there is no 'DMA Name' field in AEP, just the DMA ID.  So running a report in AEP Query Services or CJA and getting a 505 number is not helpful to our analysts.  We need to translate the ID 505 into Detroit.

The problem is since the field is set up as an integer we are not able to classify it.  We've tried setting up an AEP Lookup Table, but you can not set up a Relationship between two dataset when you're dealing with 'integer' fields, the keys that connect tables together must be 'string' values.

We tried converting the DMA ID integer into a String using the Data Prep Mapping Services, but it seems the DMA ID doesn't yet exist or isn't accessible by the Data Prep tools so those efforts also failed.

We tired using CJA Lookup capability within Derived Fields and we after 4 business days can't seem to get our DMA Dataset connected to our CJA Connection... the tool just won't process the dataset because we can't set the DMA ID field as an 'identity field' because it's an integer data type.

So our idea is:

- to have Adobe add the DMA Name field to the field group and then update the DMA processing to also populate the DMA Name field automatically without the need for us to 'classify' the DMA ID

- if that's not possible, we'd like Adobe to update the Web SDK Experience Event Field Group so that the DMA ID field data type is a 'string' instead of an 'integer'. 

- If that is not possible without impacting already existing customer who are using that Field Group, then we ask that you expose the DMA ID field to the Data Prep services so we can manually create a rule that will convert the integer field into a string field. 

- If that's not possible due to the way AEP processes data, then we ask that Adobe provide a solution to calculate the DMA within the Web SDK Extension or Adobe Data Collection Data Element so we can set our own custom field in our Schema to collect the DMA ID as a string value (so we can classify it within AEP / CJA)

I hope that all made sense

[#2]

We're running into this, too! Plus one to this idea!

[#3]

Hey, we figured it out! The DMA ID field doesn't need to be a string to do a lookup. You can do lookups with integers.

• Create a schema for DMA lookups using the Other base class
  • Create a new custom class with the behavior of Record
  • This schema should have two fields: DMA ID and DMA Name
• Create a dataset using this schema
  • Use the local file upload source to populate this dataset from a CSV
• Add this DMA lookup data set to your CJA connection
  • Configure this lookup dataset to select the DMA ID as the key, and select the DMA ID from your event dataset as the matching key

[#4]

@NikitaFe 

How did you get CJA to load a lookup table without an identifier in the schema?

Meaning, the schema you create that includes the two fields (DMA ID and DMA Name), one of those fields has to be identified as an identifier.  You can't use DMA ID cause 'integer' fields can't be set as identifiers.  Did you use the DMA Name as your identifier when you created that Schema?

Can you please elaborate on your solution?   Thanks

[#5]

Our DMA schema doesn't have an identity. As far as AEP knows the DMA dataset isn't connected to any other dataset. In the CJA connection we have our event data set and DMA lookup data set. In the settings for the DMA lookup data set we specify the primary key in the DMA lookup dataset and the matching foreign key in the event dataset.

[#6]

@NikitaFe 

I tried that approach already.  I was never able to get my DMA dataset backfilled into my CJA Connection.  After 10 business days, my DMA dataset (which only had 200 rows) still wasn't "backfilled complete", indicating that CJA won't ingest datasets that don't have 'identities'.  To your knowledge, has that changed?

and to confirm, when you matched you're two datasets in the CJA Connection together, you joined two 'integer' fields across your two datasets?

[#7]

Yes, we joined two integer fields across our two datasets. Our DMA dataset also has ~200 rows. We did ours in the opposite order. We created the schema, then the dataset, then configured the connection (with import new data turned on and dataset backfill turned off), and then finally uploaded the csv. Oddly, in the connection settings it does say that the number of records is 0, but it does give us a preview of the first 10 rows. We were getting No Value for the lookup fields on the day we set it up but it was populated and working in Workspace on the next day.