Forum Discussion
Helper IIQueries with overlapping date periods
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?
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 kWeeks back = CALCULATE(COUNTROWS('date'),FILTER('date',[Date]>=EARLIER([Date])))-1I 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.
4 Replies
- amitchandak
Super User
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 🙂
- v-diye-msft
Community Support
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 kWeeks back = CALCULATE(COUNTROWS('date'),FILTER('date',[Date]>=EARLIER([Date])))-1I 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.
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.
