I'm trying to take a, large, deeply nested JSON object that contains all contacts and their properties in our marketing CRM, and transform it to backfill a daily table.
The object is coming from this api endpoint with the parameter "Property Mode" = value_and_history.
The JSON object contains a list of objects for each contact record in the CRM, which contains another nested object with all the properties associated with a respective contact record (each contact has between 20-80 unique properties that I need to backfill for).
Within the object containing properties, there is a nested list of objects ("versions" in example below) which holds the property value and a timestamp from when the property was updated.
Example JSON object:
{
"contacts": [
{
"addedAt": 1390574181854,
"uniqueContactId": 204727,
"canonical-vid": 204727,
"portal-id": 62515,
"properties": {
"leadScore": {
"value": "50",
"versions": [
{
"value": "50",
"timestamp": 1493910688065
},
{
"value": "25",
"timestamp": 1494022165157
},
{
"value": "30",
"timestamp": 1493011165157
}
]
},
"lifecycleStage": {
"value": "salesQualifiedLead",
"versions": [
{
"value": "salesQualifiedLead",
"timestamp": 1493911260146
},
{
"value": "marketingQualifiedLead",
"timestamp": 1493911177118
},
{
"value": "lead",
"timestamp": 1493011165157
}
]
}
}
}
]
}
I've flattened the object into a dataframe in the structure below;
| contactId | timestamp | propertyName | propertyValue | |------------|---------------|----------------|------------------------| | 204727 | 1493910688065 | leadScore | 50 | | 204727 | 1494022165157 | leadScore | 25 | | 204727 | 1494012165567 | leadScore | 30 | | 204727 | 1493911260146 | lifecycleStage | salesQualifiedLead | | 204727 | 1493911177118 | lifecycleStage | marketingQualifiedLead | | 204727 | 1493910832532 | lifecycleStage | lead |
I'm not sure if I'm headed down the right path with this approach. I think I could pivot this dataframe and join it to a date dimension table then group and ffill the dataframe, but again, I'm not confident this is the best approach.
The end result I'm looking for is a dataframe with a record for each contact for each day from the addedAt day to current day, with columns for each property, containing the value of that property on the given day.
e.x.
|dim_date | contactId | added_at | leadScore | lifecycleStage | |----------|------------|---------------|-----------|-------------------| |2019-10-16| 204727 | 2014-01-24 | 50 |salesQualifiedLead | | [...] | |2017-05-04| 204727 | 2014-01-24 | 50 |salesQualifiedLead | | [...] | |2017-04-24| 204727 | 2014-01-24 | 25 |lead |