windsor.ai API / date question

[Pikachu-Power]

Hello all,

i dont know if someone has experience with windsor.ai but I have one date question concerning the windsor.ai API:

I use windsor.ai to import Bing Ads data into Power BI via the created weblink like:

https://connectors.windsor.ai/bing?api_key=[your API key]&date_preset=last_7d&fields=campaign,clicks,spend,impressions,date,source&_renderer=csv

I would like to load all dates from 01.01.2020 until today in a dynamic way. Or last 3 years would be also ok. 

date_preset=last_2years is a possible value in the URL but there is no date_preset=last_3years. I could say "&date_from=2020-01-01" but that is not dynamic. After 10 years i would import all dates from 10 years.

How could I do that in the URL? Is it maybe possible to say TODAY - last_3years for the date_present field?

Many thanks.

Best Regards.

[Adel]

this has nothing to do with the connector, it's limited from bing ads, they don't allow pulling data older than 2 years, 

The solution here would be to use a data warehouse to get your historical data and then connect it to power bi.

details from bing here 
https://docs.microsoft.com/en-us/advertising/guides/release-notes?view=bingads-13

[Pikachu-Power]

@Adel but when I say "&date_from=2020-01-01" i get data older than two years. but it is not dynamic. you mean something like TODAY - 3 YEARS is not allowed from the bing ads webside? 

[Adel]

exactly yes, but i know that with windsor.ai you can pull historical data older than 2 years with almost all marketing platform you would need a data warehouse, and windsor.ai has it's own cloud so maybe that could help.

[Pikachu-Power]

Okay thats strange... windsor accepts

&date_from=2020-01-01

We acually just have to set the date dynamic. 

@artemus you didnt use "&date_from=". maybe there is the mistake?

@Adel thanks for yout alternative idea 

[artemus]

You can do this like:

"https://somewebsite.example/a/b/c?start=" & DateTimeZone.ToText(DateTimeZone.FixedUtcNow() - #duration(365 * 3, 0, 0, 0), [Format = "yyyy-MM-dd", Culture = ""]) & "&foo=bar"

[Pikachu-Power]

Hi Artemus,

thanks for your reply. Something with the Format seems to be not ok:

Expected Token: Comma:

My whole query looks like:

= Csv.Document(Web.Contents("https://connectors.windsor.ai/all?api_key=[your API key] & DateTimeZone.ToText(DateTimeZone.FixedUtcNow() - #duration(365 * 3, 0, 0, 0), [Format = "yyyy-MM-dd", Culture = ""]) & fields=account_name,campaign,clicks,spend,impressions,date,adnetwork_conversions,&_renderer=csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None])