Adobe Analytics

MaheshKo1 · 11/26/24

Hi Adobe Community,

I'm working on integrating Adobe Analytics data into my data warehouse and I'm having some trouble mapping column names from my desired output to the appropriate API 2.0 request body/query parameters.

Specifically, I'm trying to figure out how to:

Identify the relevant API endpoints that will provide the necessary data for my desired columns.
Understand the structure of the request body/query parameters to ensure I'm providing the correct information.
Map the API response fields to the specific column names I need in my data warehouse.

Attached the related report screenshot and the first-level breakdown query.

{
    "rsid": "xxx",
    "globalFilters": [
        {
            "type": "segment",
            "segmentId": "s300009243_61f08dc2a20a477af936feef"
        },
        {
            "type": "segment",
            "segmentId": "s300009243_626c56495aaaea0597b65785"
        },
        {
            "type": "dateRange",
            "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
            "dateRangeId": "last7FullDays"
        }
    ],
    "metricContainer": {
        "metrics": [
            {
                "columnId": "2",
                "id": "metrics/productinstances",
                "sort": "desc",
                "filters": [
                    "0",
                    "1"
                ]
            },
            {
                "columnId": "3",
                "id": "metrics/productinstances",
                "filters": [
                    "2",
                    "3"
                ]
            },
            {
                "columnId": "4",
                "id": "metrics/productinstances",
                "filters": [
                    "4",
                    "5"
                ]
            },
            {
                "columnId": "5",
                "id": "metrics/productinstances",
                "filters": [
                    "6",
                    "7"
                ]
            },
            {
                "columnId": "6",
                "id": "metrics/productinstances",
                "filters": [
                    "8",
                    "9"
                ]
            },
            {
                "columnId": "7",
                "id": "metrics/productinstances",
                "filters": [
                    "10"
                ]
            },
            {
                "columnId": "8",
                "id": "metrics/orders",
                "filters": [
                    "11"
                ]
            },
            {
                "columnId": "9",
                "id": "metrics/units",
                "filters": [
                    "12"
                ]
            },
            {
                "columnId": "10",
                "id": "cm_visits_orders_defaultmetric",
                "filters": [
                    "13"
                ]
            }
        ],
        "metricFilters": [
            {
                "id": "0",
                "type": "breakdown",
                "dimension": "variables/averagepagetime",
                "itemId": "0"
            },
            {
                "id": "1",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "2",
                "type": "breakdown",
                "dimension": "variables/averagepagetime",
                "itemId": "15"
            },
            {
                "id": "3",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "4",
                "type": "breakdown",
                "dimension": "variables/averagepagetime",
                "itemId": "30"
            },
            {
                "id": "5",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "6",
                "type": "breakdown",
                "dimension": "variables/averagepagetime",
                "itemId": "60"
            },
            {
                "id": "7",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "8",
                "type": "breakdown",
                "dimension": "variables/averagepagetime",
                "itemId": "180"
            },
            {
                "id": "9",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "10",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "11",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "12",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            },
            {
                "id": "13",
                "type": "dateRange",
                "dateRange": "2024-10-31T00:00:00.000/2024-11-07T00:00:00.000",
                "dateRangeId": "last7FullDays"
            }
        ]
    },
    "dimension": "variables/evar30",
    "settings": {
        "countRepeatInstances": true,
        "includeAnnotations": true,
        "limit": 5,
        "page": 0,
        "nonesBehavior": "return-nones"
    },
    "statistics": {
        "functions": [
            "col-max",
            "col-min"
        ]
    },
    "capacityMetadata": {
        "associations": [
            {
                "name": "applicationName",
                "value": "Analysis Workspace UI"
            },
            {
                "name": "projectId",
                "value": "66985795e520aa370805292a"
            },
            {
                "name": "projectName",
                "value": "Signal Acceleration: Art Olvera - 44559"
            },
            {
                "name": "panelName",
                "value": "Brands and Products"
            }
        ]
    }
}

what is the role of "filters": ["0", "1"] here? why it's increasing every time?

How do I link the columnId with the specific section?

The response is -

{
    "totalPages": 324,
    "firstPage": true,
    "lastPage": false,
    "numberOfElements": 5,
    "number": 0,
    "totalElements": 1618,
    "columns": {
        "dimensions": [
            {
                "id": "variables/evar30",
                "dimensionColumnId": "77d569fe-9b2a-43b0-a276-f740111eea0b",
                "type": "string"
            }
        ],
        "columnIds": [
            "2",
            "3",
            "4",
            "5",
            "6",
            "7",
            "8",
            "9",
            "10"
        ],
        "dimension": {
            "id": "variables/evar30",
            "dimensionColumnId": "77d569fe-9b2a-43b0-a276-f740111eea0b",
            "type": "string"
        }
    },
    "rows": [
        {
            "data": [
                8785.0,
                540.0,
                365.0,
                311.0,
                73.0,
                12277.0,
                648.0,
                2877.0,
                0.08307692307692308
            ],
            "annotations": [],
            "itemId": "1663810891",
            "value": "HP INC"
        },
        {
            "data": [
                5830.0,
                262.0,
                159.0,
                176.0,
                48.0,
                7579.0,
                744.0,
                3297.0,
                0.16294349540078842
            ],
            "annotations": [],
            "itemId": "106967146",
            "value": "LENOVO"
        },
        {
            "data": [
                3740.0,
                236.0,
                146.0,
                148.0,
                38.0,
                5670.0,
                82.0,
                467.0,
                0.022361603490591766
            ],
            "annotations": [],
            "itemId": "2863956527",
            "value": "CISCO SYSTEMS"
        },
        {
            "data": [
                3463.0,
                147.0,
                96.0,
                100.0,
                29.0,
                5449.0,
                94.0,
                235.0,
                0.023552994237033324
            ],
            "annotations": [],
            "itemId": "2088319439",
            "value": "APC"
        },
        {
            "data": [
                2891.0,
                158.0,
                97.0,
                201.0,
                60.0,
                3993.0,
                212.0,
                1190.0,
                0.09985869053226566
            ],
            "annotations": [],
            "itemId": "161397080",
            "value": "APPLE"
        }
    ],
    "summaryData": {
        "filteredTotals": [
            65632.0,
            4448.0,
            2716.0,
            2811.0,
            785.0,
            97428.0,
            4201.0,
            622829.0,
            0.0296959714986534
        ],
        "annotations": [
            {
                "id": "6737dcb0495aae11457e05c6"
            }
        ],
        "totals": [
            65632.0,
            4448.0,
            2716.0,
            2811.0,
            785.0,
            97428.0,
            4201.0,
            622829.0,
            0.0296959714986534
        ],
        "annotationExceptions": [],
        "col-max": [
            8785.0,
            540.0,
            365.0,
            311.0,
            73.0,
            12277.0,
            744.0,
            580752.0,
            2.2857142857142856
        ],
        "col-min": [
            1.0,
            1.0,
            1.0,
            1.0,
            1.0,
            1.0,
            1.0,
            1.0,
            5.617977528089888E-4
        ]
    }
}

How columnIds linked with the request body?

I'm particularly interested in understanding how to handle complex data structures and filtering options within API requests. Any guidance or best practices would be greatly appreciated.

Thanks,

Mahesh

SSampsa · 11/28/24

To answer at least a part of your question: The filters listed refer to the "metricFilters" array and the item ids you've defined in the request.

Since you have a dimension with breakdowns, it looks like the first two (0 and 1, brand and product) index of columns are used by those and the rest listed (between 2 and 9) are metrics, as shown. When creating the request this information is already known, so the same can be applied to the response and data mapped accordingly.

MaheshKo1 · 11/28/24

I have no issues to compare the request with the response.

The issue is the request JSON handles the CPI team and they merge all levels of breakdown API responses to a single JSON file where the columnID's fields are available.

The issue is the request JSON handles the CPI team and they merge all levels of breakdown API responses to a single JSON file where the columnID's fields are available and then share only the response to the data bricks team and the data bricks team is not aware of the request body JSON and columnID field at all["2", "3"...].

How they are linked/related to these string number column names with the proper meaningful name?

If a user can see the request and response body/JSON at the same time then they can co-relate the columnID, but in our case, one team is only dependent on the response.

SSampsa · 11/28/24

I see, that is a bit of a pickle.

As I see it the request and response are a pair, and the request defines what these columns and the filters applied to them are, response will not return them again as from the design point of view this is not required. To make it work these should be passed along with the response if the data will go to another environment instead of only the response, otherwise the end situation is like the one you're in now.

Alternatively definitions for reports could be predefined and shared for both platforms, so that Data Bricks has the definitions ready and does not expect them to come with the data.