Leave a comment
Get the GH Bookmarklet

Ask GH

I'm trying to create a report that compares our sales MOM and compare that to last year (e.g: Jan 15 vs 16, Feb 15 vs 16, Mar 15 vs 16, etc). Now the hard part is creating a visual report that does this for 1,000 clients. Doing this for let's say 10 clients is easy. We just create 10 graphs. But how can we do this for 1,000 clients? The goal of this report is so we can see our clients spending habits and take action on that. For e.g: Bob's Plumbing Ltd spends a lot in Jul 2015 and 2016, let's prepare for Jul 2017. Any ideas?

  • RE

    Rezki El Mokaddam

    about 3 years ago #

    Pivot Table on Excel, my friend :-) This tool is a lifesaver.

    • ST

      Stanley Tan

      about 3 years ago #

      How would you create a Pivot Table with a MOM graph compared YOY for 1000 clients?

      As far as I know, Pivot Table easily creates a table data that we can view but we need something visual for each client.

  • MD

    Matthew Duckworth

    about 3 years ago #

    For 1000 reports, you have to realize (as I'm sure you already have) that you'll be looking at 1,000 reports! WOW! You're lucky!

    That's higher than Dunbar's number, so you won't learn much by putting them side by side like you have been. As you've figured out, you'll have look at them a new way.

    If you know someone who is experienced in R or Python for Data Science (check Upwork or R Bloggers if you don't have anyone in mind), you could probably do some analysis pretty quickly to group people into cohorts by the amount of spend they've had per month... so, it would look like a chart that had segments like:

    50k-25k... etc.

    I'd have to know more about what would be the trigger for you to say "Bob is about to spend a lot. Lets get prepared." Like, is that based on % change, $ change or absolute spend? You know? The analysis would depend on that.

    I think you could also segment the companies into cohorts and then create separate line graphs for all the companies in the cohort... that way you could have maybe 20 graphs. The data would make a lot more sense that way. If you're in a business like I am, you'll have to produce the report each month, so that's a whole other problem... if you can get a solution in R, there are people out there who can create a simple dashboard app for you where you just upload your data and refresh the graphs. Once the code is written, it doesn't take long to update the report. If you have them write it in Plotly (an R package), you can easily hone in on date ranges and specific customers to get more granular.

    Microsoft Power BI is another tool you could use, but I actually think its faster and ultimately cheaper to have someone build something custom in R/Python that you can update yourself with a CSV file.

    If you have all the data, it might be a fun project to look at. Sounds like you might be able to get something "good enough" by breaking things into cohorts though.