Queries with overlapping date periods

[MightyMicrobe]

Hi all, 

I am exporting data out of Adobe Analytics and need to build a view showing metrics over a 45-day period, shifted weekly. Essentially, I need to see week on week change of the overlapping/preceding 45-day periods. 

Example:

Weeks Back	Date Start	Date End	Metric 1	Metric 2
3	2019-12-18	2020-01-31	2,226,341	5,173,205
2	2019-12-25	2020-02-07	2,254,015	5,259,445
1	2020-01-01	2020-02-14	2,299,498	5,428,996
-	2020-01-08	2020-02-21	2,319,646	5,492,041

I can manually build individual queries for each period and append them to each other, but this is an extremely slow process and I will have to build about 400 queries individually.

Is there a way to automate it? 

[v-diye-msft · Accepted solution]

Hi @MightyMicrobe 

Your data is too scarce to repro the full issue. for the "need to build a view showing metrics over a 45-day period, shifted weekly" you can probably create a calculated table first then add the measure:

date = var k = ADDCOLUMNS(FILTER(CALENDAR(DATE(2019,01,01),DATE(2020,01,08)),WEEKDAY([Date],2)=3),"date end",[Date]+44)
return
k

Weeks back = CALCULATE(COUNTROWS('date'),FILTER('date',[Date]>=EARLIER([Date])))-1

I can't figure out where the metrics info from, but you can manage the calendar table with your data to calculate them automatically. 

Pbix attached.

[Anonymous]

As you know, Web Analytics solutions does not provide you data at the lowest granularity and therefore you can't sum non-summable metrics (like users) over periods. There isn't an easy solution to this. A few options are

- Use metrics that are summable (page views) instead of non-summable (users) for your analysis. PV *might* be a good proxy for your users but depends how much you can change your requirements

- Statistically calculate the overlapping on average and reduce it accordingly. So for if on a day you have 100 users but on a 45 days you have 4000 users (instead of 4500), download the data on a daily level, sum and reduce by 1-(4000/4500)=11.1%. It's an approximation of course.

- Use direct query connectors: there exists some connectors that allows to work on a direct query model for GA (and therefore they re-query every period for non-summable) but not sure for Adobe Analytics (try CDATA).

- And of course run every single query...probably it can be automated through Power Query, but I never tried. Still, however, this will be fairly slow as you will have to make 400 requests to AA.

[amitchandak]

I think we should allocate the data at day level work on it.

Refer, how to allocate at Day level, Look for Table Daily Allocation: https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

Refer, how to work with rolling weeks

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[MightyMicrobe]

No, this will not work, unfortunately. The nature of the data I'm working with is such that 45 day period != sum total of 45 individual days, I need to ingest the data as one "block" of 45 days, not as 45 individual blocks of 1 day each. 

Not sure if it makes sense but that's about the best way I can explain it 🙂