report on historic opportunity status changes

Question

HI, I think I'm having one of those days where i tend to make thing more complex than they are, and i'm missing some easy solution. I need to create a report that shows the progression of opportunities at the end of each quarter. Something like:

at the end of Q1 we had 10 opps in Submitted status, at the end of Q2 3 of these had progressed to completed, 3 to accepted, and 4 were declined. At the end of Q3 etc etc,,,

How can I actually do this? I can use Excel and PowerBi, but I just don't seem to be able to download the data I need

Dennis Ladnier · Answer

@Alex Wong is correct (as is usual!).

What I do is, via the skyapi, extract a list of all current opportunity status info and place them in a data warehouse noting changes from 'yesterday' - this provides a history of status changes without having to individually call the prospect api per constituent.

Probably more complex than you want but it works.

Alex Wong · Answer

Unfortunately, I don't think there is a "query/list" way to get all the status changes for you to create this report.

The only option I can think of is via the prospect API that gives you status PER constituent:

* Query API to get list of constituents with prospect status that you are interested in reporting
* For each constituent prospect record, call the prospect API mentioned above to get the information and store into an excel file
* Directly uses the excel file to create report or create power bi that consume this excel file to create report