cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MightyMicrobe
Helper I
Helper I

Queries 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? 

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

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

 

08.PNG

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

4 REPLIES 4
v-diye-msft
Community Support
Community Support

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

 

08.PNG

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors