How do I automate data from multiple sources into my sheet?

[Sid Deswal]

I have to do business reporting for a startup I'm helping, and I'm thinking of connecting Excel to Google Analytics, Hubspot (marketing), Salesforce and Xero so that I can pull data from across the user journey (website visitor to lead to customer to revenue) from these different systems, and build trend reports as well as chart them.

What would be the best way to automate this process so that they don't have to manually copy-paste data every couple weeks, and the data they see in their reporting Excel is up to date?

 

[Chihiro]

There are few methods.
But will depend on who need access to the data and from where.

Try and look up PowerBI, to share data it will cost $9.99 USD/month per user. But the cost is well worth it, for small companies. For large corporation, there's Premium, which is usage based model. Unfortunately, there isn't good pricing option for mid-size company that I'm aware of.

PowerBI is specifically designed to bring in data from various sources and then to create data model(s) to report and analyze.

While it isn't still a mature product, it has great potential and allows for various customization. To make it easier to connect to data from internal and external sources, I'd recommend setting up Gateway server (on premises)
EDIT: Gateway Server will allow you to query Database/files without directly exposing your database/server to external IP (which is security concern).

Other than that, you may want to ensure those that prepare reporting and handle data get Office 365 Pro Plus subscription (or standalone Excel). This will give you more connectors and will allow you to connect to various sources without resorting to custom coding.