Best way to automatically pull data for analytics.

[rf-devin]

Does anyone have a good way they have found to automatically pull data from Service Titan that I could then connect to a BI dashboard like Power BI, Tableau, Looker Studio, etc? For example, performance reports that I can have updated automatically without having to pull the report manually. 

[rich_qua]

As Sheena said,

1. Create report and schedule it to be sent to email address.  I have a dedicated email address for all my automations triggered by emails (automations@yourdomain.com)

2. Create automation in make.com or Zapier to save spreadsheet to folder.  Use unique subject lines for reports to filter then download to one drive / any cloud drive.  I prefer to use OneDrive as the native integration inside of PowerBI with SharePoint folder makes it very easy.  

3. Set folder (GET DATA > SharePoint Folder) as data source within Power-bi.  Google how to make sure that the folder is referenced in the cloud and not on your local machine.

4. Schedule refresh in PowerBI service after automation runs to download spreadsheet from email. 

Note - Trying to do direct API queries in Powerbi for large amounts of information will take FOREVER.  I had it setup that way and it took over 45 minutes to refresh the report every time.  With a lot of tweaking, I got my report refresh times down < 5 minutes.  I have 10 data sources (folders) with some that have 50k+ rows of data.  I am not a developer.  I have zero coding experience and just learned how to do this over the past year so the skill ceiling for this kind of stuff is not high.  

[Sheena_Palacios · Accepted solution]

Hi @rf-devin you could always opt to build custom reports on specific metrics and automate them to send to your email on any cadence you'd like. From there you could setup an auto-forward rule in your email to send to whatever data aggregator you're using. That or gain access to the API on the backend and connect it that way, if your third party software gives that option.