It's a difficult question for sure. For many of the projects I work on, the data that you need to do this is stored in different places, and you have to bring it together first and transform it into a common format.

I use a few tools to help me with this.

I use python as a Swiss army knife to explore data. There are many python packages available to access common APIs (Google, your ESP, whatever) that makes putting these pieces together easier.

RapidMiner [rapidminer.com] is open source and provides a nice GUI to build a data pipeline and do transformation, and also to run statistical analysis. You can script python (and R) inside of RapidMiner, or they have drag and drop controls you can use for analysis. They have a healthy community that is very friendly and commercial support if you need it. You can also do basic visualization in RapidMiner too and they have a server component you can use to keep data up to date and deploy dashboards.

The first step in the project is to define all of your data sources - so where are your leads coming from, and how can you access that data. You mentioned a CRM, and obviously you're using analytics of some sort. What else? ESP? Advertising?

Next, you need to figure out which of those systems have APIs, and roughly define what you want to get out of them. For example, if we're looking at our ESP, you may want to aggregate on a campaign level, or maybe on an individual email address level, or something else.

Then, I like to define my output - essentially, in Excel, mock up what I want the report to look like. Then, I go through the steps of using python and/or rapidminer to bring my data together, normalize it, analyze it, and format it however I like.

Thanks Jake that's interesting. How much time do you think you spend on this every month for each client? Or is it completely automated?

Depending on the client we work with different CRMs, advertising platforms, ESPs and mostly Google Analytics. Some clients work with tools that offer all in solutions, i.e. Hubspot and Infusionsoft, but their marketing reports are not perfect by far. Salesforce has some good plugins but still not perfect.

In the end we end up doing a lot of work and collecting all data in excel, not ideal especially when working with many advertising platforms, phone tracking, lead forms.

It really does depends on the client and what phase of "data enlightenment" they are in. :)

Of course we try to automate what we can, but always on a client by client basis, because the output of the reporting efforts need to match the business model. Interestingly enough, I spend more time on clients that are "data-savvy" because they are often asking for ad-hoc reports that can't be automated or they are investing in systems and tools that can give them even more data. That's why I use RapidMiner so much - it's good for developing ad-hoc queries and it also lets you save/reuse components, and it documents your workflow so that a developer can take it and reimplement it in code.

CRMs are generally easy. If they're using anything mainstream there will be a "best practice" way of getting data out. RapidMiner can make web calls and extract JSON, so if whatever you're using has any sort of normal looking REST API, you can just do it all there. If your budget supports it, you can buy no-code-needed plugins for RM that will natively connect to GA and Salesforce.

For phone tracking you just need to make sure you're using a carrier with a sane API that can report call data - lots of people use Twilio but I also use and like Anveo and Flowroute. Anveo is neat because you can build your call flows in a GUI and have web events happen at certain points.

It sounds like you're working mostly with web based CRMs and ad platforms. I think you could probably bring everything together fairly easily with python and RapidMiner.

Once you build what you'd like to see for a report, then you would need to build some way to keep it up to date. Usually we build a database just for reporting purposes, and we have code that regularly ingests data from the APIs we need to call. Then, we hook up a visualization tool to that database to produce the reports we want (or we automate sending of the reports in PDF/email form, or whatever).

I've been meaning to play with AWS QuickSight [aws.amazon.com] as a new visualization tool. I haven't been able to get to it it yet, but I saw a demo on stage, and it looks pretty good for applications like this.

Jake and I recently had a similar discussion and I so far have only managed to get a bunch of the relevant Python libraries installed and get the project set up (which is pretty easy). I just haven't had time to get into the nuts and bolts yet.

I have very simple needs and reporting (and abilities!) compared to Jake. Also, we have a lot holes in our data - when someone picks up the phone in response to a print ad, that's pretty much untrackable - we don't have enough numbers to set up a number for each ad. Also since the delay between initial form submission and an actual sale with defined revenue could be a year (or more), we don't cycle back every month like you do.

So my reporting is really modest, but for what it's worth...

For putting together decent looking reports with minimal effort, Google Data Studio is easy once you make the data available

- Google Analytics and AdWords are available by default

- Anything you can pull into Google Sheets can be connected. You mentioned that your process with Excel was tedious, but I wasn't sure if that was a data entry problem or a formatting for presentation problem.

Getting stuff into Google Sheets is anywhere from easy to difficult. In the easiest cases, if there's a Zapier of IFTTT integration, you're all set. If you're looking to pull social media data, etc, then you can get that easily with Supermetrics (which works with Excel and can query a lot of databases too). If you set up your Sheets to run reports regularly, the data should refresh and be ready for the weekly/monthly pull into Data Studio

And then there are the proprietary sites that are industry-specific that have no integrations with Zapier or Supermetrics, which is where python and old fashioned scraping comes into play and that's the stage I'm at now.

For me it depends on the size of the business. For bigger businesses that are not tech savvy I'd recommend funneling all your data to the Google Cloud Platform. You can dump pretty much anything, form Eloqua to Salesforce, to custom CRMs, GA, DoubleClick, Kissmetrics, Demandbase, Adobe Marketing Clout, whatever you can think of. Then with some work, you can slice and dice the information anyway you like and create 4d charts and in-depth tables, custom client funnels and ROI gains and losses for each step and compare customer journeys from your personalization or A/B efforts. Works like magic. But that is for medium and above sized businesses and needs some work to create the correct business logic.

For SMB's and Mom and Dad shops, excel is all you would ever need to get decent level of statistical accuracy and measure ROI, ROAS, Customer Life Time Value (if you have the historical data) and paid vs. non paid customer journey compared to the client type grid. Yes, this requires a bit more daily manual work, but it's not anything too time consuming if you set up the tables early.

tag for future read

@smilie,

you can just click the "Follow Thread" button at the bottom to tag for future reading. Then you can see it in your followed threads instead of waiting for a new post which may never happen (maybe that's what you meant when you said "tag", but for us old-timers, the "Follow Thread" button is new and I sometimes don't think to use it... old habits die hard).