Refreshing Appended Queries with Adobe Analytics Segments

[ramoher]

Hi everyone!

I've built a PBI report utilizing the Adobe Analytics Conntector, pulling in data on 4 segments into 4 separate tables (one segment per table). I then add a couple of columns to each table, then append the 4 queries together into a new table. All tables have the same columns, so no issues there. Initially this worked well for a week. I've gone on to incorporate other data sources and transformations, but haven't touched these queries.

However, when I try to refresh the data, it won't populate any rows of data OR only pull in data from one segment. I've replicated this same setup with another set of data, and I've not had any issues with it refreshing. Why is this happening? Anyone else experiencing this issue?

[Anonymous]

Hi @ramoher ,

Can you please provide more details about your scenario?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[ramoher]

I've created a data source to pull in data from Adobe Analytics for one specific segment. Here are the specs for the pull:

Date Range = 1/1/2018 to 12/31/2023

Segment = Segment A

Top 100 by Dimension Month

Metrics/Dimensions:

• Date Granularity
  • Year
  • Month
  • Day
• Mobile Visits
• Page Views
• Return Visits
• Custom Metric 1
• Custom Metric 2
• Custom Metric 3
• Custom Metric 4
• Custom Metric 5
• Custom Metric 6
• Custom Metric 7
• Unique Visitors
• Visits
• Bounces
• Entries

After that, I added 3 calculated columns and 1 text column. I repeated this for Segments B, C, and D. Once all the queries were built, I used Append Queries as New to combine the 4 data tables into one new combined table.

I've replicated this same process using the same segments (but different dimensions/metrics) in another portion of the same report. That hasn't had the same refresh issues.

[Anonymous]

HI @ramoher ,

Is there anything particular in your calculation formulas? (e.g. reference other query, invoke custom function..)

Regards,

Xiaoxin Sheng

[ramoher]

Here's the specific query for each of the tables. I've changed some values for privacy.

let
    Source = AdobeAnalytics.Cubes([HierarchicalNavigation=true]),
    AccountName = Source{[Name="AccountName"]}[Data],
    #"production" = AccountName{[Id="production"]}[Data],
    #"Added Items" = Cube.Transform(#"production",
        {
            {Cube.ApplyParameter, "DateRange", {#date(2018, 1, 1), #date(2025, 12, 31)}},
            {Cube.ApplyParameter, "Segment", {{"SegmentID"}}},
            {Cube.ApplyParameter, "Top", {100, "month"}},
            {Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
            {Cube.AddMeasureColumn, "Mobile Visits", "mobilevisits"},
            {Cube.AddMeasureColumn, "Page Views", "pageviews"},
            {Cube.AddMeasureColumn, "Return Visits", "returnvisits"},
            {Cube.AddMeasureColumn, "Unique Visitors", "uniquevisitors"},
            {Cube.AddMeasureColumn, "Visits", "visits"},
            {Cube.AddMeasureColumn, "Bounces", "bounces"},
            {Cube.AddMeasureColumn, "Entries", "entries"}
        }),
    #"Added Desktop Visits" = Table.AddColumn(#"Added Items", "Desktop Visits", each [Visits]-[Mobile Visits]),
    #"Added Average Page Views per Visit" = Table.AddColumn(#"Added Desktop Visits", "Average Page Views per Visit", each if [Visits] <> 0 then [Page Views]/[Visits] else 0),
    #"Rounded Off" = Table.TransformColumns(#"Added Average Page Views per Visit",{{"Average Page Views per Visit", each Number.Round(_, 2), type number}}),
    #"Added New Visits" = Table.AddColumn(#"Rounded Off", "New Visits", each [Visits]-[Return Visits]),
    #"Added Division" = Table.AddColumn(#"Added New Visits", "Division", each "Division A"),
    #"Replaced Infinity w/ Blank" = Table.ReplaceValue(#"Added Division",#infinity,"",Replacer.ReplaceValue,{"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day", "Mobile Visits", "Page Views", "Return Visits", "Unique Visitors", "Visits", "Desktop Visits", "Average Page Views per Visit", "New Visits", "Division"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Infinity w/ Blank",{{"Date Granularity.Level 1: Year", Int64.Type}, {"Date Granularity.Level 2: Month", Int64.Type}, {"Date Granularity.Level 3: Day", Int64.Type}, {"Mobile Visits", Int64.Type}, {"Page Views", Int64.Type}, {"Return Visits", Int64.Type}, {"Unique Visitors", Int64.Type}, {"Visits", Int64.Type}, {"Desktop Visits", Int64.Type}, {"New Visits", Int64.Type}})
in
    #"Changed Type"

[Anonymous]

Hi @ramoher ,

I haven't found anything operations may cause the issue, I think it may related to append query operations.
If you remove append query table from your report, did refresh works?

Regards,

Xiaoxin Sheng

[ramoher]

I've tried and the queries are not working consistently for the individual tables either. I know that it isn't the segment portion, as those are working with the other queries mentioned.