Hey all,

I've tried writing this post several times, but this is the final draft of the post with the most clarity. I will start out the post explaining what the scenario is here and what my goal is.

There are several departments at the company I work for, and each department sends out various emails to customers. The time at which these emails are sent is based upon the number of days that have passed since the customer made a purchase (i.e. "Thank You" email might go out 1 day after purchase, "Check Out Our Blog!" might go out after 10 days, etc.). I want an easy way to view a customizable timeline of the different emails we send out in each department as well as the ability to use checkboxes to select / deselect any given department and add / remove it from the timeline (which I envision happening automatically whenever a box is checked / unchecked). In terms of presentation, I envision there being a series of horizontally-stacked rows where each row is a different department. Along the x-axis would be the timeline (which starts at 0 and is based on the number of days since a customer has made a purchase) and within the timeline would be each email that is sent as well as a status code that is associated with each email.

For the purposes of this question, I've taken some company data and replaced it with dummy data. Here is a link to the spreadsheet: Dummy Data.xlsx

As you will see in the attached Excel file, there are four columns (see image for example data): days.png
  • Name
  • Department
  • Number of Days Post-Purchase That Email Is Sent
  • Status Code

The "Name" column is the name of an email that is sent out, the "Department" is the company department who sends the email, the "Number of Days Post-Purchase That Email Is Sent" column represents the number of days that will pass after a purchase is made until the email is sent (i.e. if they purchased on July 1st and the number in the column was "4", the user would receive the email on July 6th (4 days had passed)) and the "Status Code" is a number which correlates to an internal method of identifying customer status.

Now, as to what I'm trying to accomplish

The big-picture vision would allow me to use Checkmarks to select different Departments and all email that are within the checked departments are then plotted out in a timeline within Visio (or somehow, to the same effect, in Excel) based on the "Number of Days Post-Purchase That Email Is Sent" number (see below image)

stats.png

In a perfect world, I would be able to assign some properties or something to the boxes created in Visio (or in the excel equivalent) so I could link those boxes to a screenshot of the email in question when the email is clicked on (and the links would be automatically assigned to the boxes that are populated in the timeline based on the email title)

I want to be able to toggle on / off the different departments (again, via checkmarks) and have new vertical lanes created or omitted on the timeline based on which boxes are checked (see the below image for an example)

check example.png

Do any of you have any ideas if / how this is possible? It would be a great tool for my company and one that I would get a lot of mileage with in terms of getting a quick, customization snapshot of what emails are being sent to customers and when they are being sent out.

Thanks!!