Has anyone been able access salesforce historical data?

Canioagain
Canioagain Contributor
edited March 2023 in Datasets

Fellow Domosapiens,

I know I have to append my data and immediately start snapshoting Salesforce data (or any data for that matter) but, I'm wondering (hoping) someone has been able to figure out how to bring in Salesforce Opportunities with Historical Trending data without having to do this?

Tagged:

Best Answer

  • mhouston
    mhouston Contributor
    Answer ✓

    @Canioagain this was something I tackled about a year ago in our domo instance. As far as I could find, there was no easy way to query it out of salesforce - it doesn't seem that there is an opportunity history object you can query.

    I created a new salesforce dataset (salesforce_daily_append) that snapshots the salesforce opportunities daily.

    I was able to leverage the opportunity field history and stage history items (https://help.salesforce.com/s/articleView?id=sf.opp_history.htm&type=5) to build a historic dataset via a python script, which I then loaded to Domo and used an ETL to append to the salesforce_daily_append to create a full opportunity history dataset.

    The script to build the historic dataset from the opportunity field history and stage history items basically just fills in the gaps between the records (so if an opportunity was created as a lead jan 1 and changed to cultivation on feb 1, it creates records from jan 2 - jan 31 for that opportunity as lead, etc). You could probably also do this in magic ETL.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @Canioagain

    Im not a Salesforce user but on other systems sometimes I can export a report from the system to get the historical data and then combine it with the data from the connector using an ETL or view. It depends on the Salesforce platform if they even keep historical records of opportunities. Based on what I’ve read I don’t think the historical trending is kept so you won’t be able to get it historically

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • mhouston
    mhouston Contributor
    Answer ✓

    @Canioagain this was something I tackled about a year ago in our domo instance. As far as I could find, there was no easy way to query it out of salesforce - it doesn't seem that there is an opportunity history object you can query.

    I created a new salesforce dataset (salesforce_daily_append) that snapshots the salesforce opportunities daily.

    I was able to leverage the opportunity field history and stage history items (https://help.salesforce.com/s/articleView?id=sf.opp_history.htm&type=5) to build a historic dataset via a python script, which I then loaded to Domo and used an ETL to append to the salesforce_daily_append to create a full opportunity history dataset.

    The script to build the historic dataset from the opportunity field history and stage history items basically just fills in the gaps between the records (so if an opportunity was created as a lead jan 1 and changed to cultivation on feb 1, it creates records from jan 2 - jan 31 for that opportunity as lead, etc). You could probably also do this in magic ETL.

  • Canioagain
    Canioagain Contributor

    @mhouston That's what I've already done. I have some dev resources I'm going to see if they can crack the nut and get to the data some way and pull it in. I'll follow up here and let you know what I do (eventually)

    Thank you

  • mhouston
    mhouston Contributor

    @Canioagain you can get the opportunity history and field history using the domo salesforce connector and using the "Browse objects and field" option and selecting OpportunityHistory and OpportunityFieldHistory. I did a basic writeup when I built this that outlined the main logical steps of processing - let me know if you would like to see that, I'd be happy to share.

Categories