Excel guru needed: import data from a text file and generate nice visual reports
$10-500 USD
In Progress
Posted over 9 years ago
$10-500 USD
Paid on delivery
Hello,
I am searching for someone very skilled in Excel, who can generate useful and visually appealing reports.
I) YOUR JOB
Create an XLSX file that will:
1. allow easy import of data from a tab-delimited text file
2. based on the imported data the XLSX file will generate various reports.
II) DATA FILE
You will not generate any data files, you will use files provided by me.
A sample text file is attached. It contains several columns delimited by a tab character. Columns are:
1. Submitted - date and time when a support ticket was submitted
2. Submitted by - indicates how the support ticket was submitted. Possible values are "Email piping", "POP3 fetching", "Customer" when tickets are submitted from customer interface. If the value is anything else, it was submitted by a staff member (value is staff member name)
3. First staff reply - date and time when a staff member first responded to the ticket. Can be empty (no staff reply yet)
4. First staff reply by - name of staff who responded in column #3
5. Resolved at - date and time when support ticket was marked as resolved
6. Resolved by - "Customer" if customer marked ticket resolved, "Automatically for inactivity", or staff name if resolved by a staff member.
7. Category - ticket category
8. Priority - ticket priority
9. Status - ticket status
10. Owner - ticket owner (name of the staff member who the ticket is assigned to)
11. Time worked - time a staff member spent working on the ticket
11. # of staff replies - total number of staff replies to the support ticket
III) REPORTS
When data is imported, the Excel file needs to generate and display various reports.
Reports should include figures and charts. They should be logically organized, nicely formatted and printable.
Important questions reports need to answer:
1. Ticket summary
How many tickets are submitted, tickets by status/category/priority, time trends (for example graphs showing this data by months, days of weeks, which hours of day have most tickets submitted)
2. Time to first response
How long it takes for staff to respond after ticket is submitted? I need shortest, longest, average. A chart showing how many tickets were replied to within first 1 hour, 2 hours, ... 1 day ... 2 days ... 1 week ... more than 1 week after submitted.
3. Time to resolution
How long does it take for the ticket to be marked resolved after submitted? Similar as above.
4. Number of staff replies to resolution
Tickets with status "Resolved" - how many staff replies were needed? How many were resolved after only 1 staff reply?
5. Individual agent performance
For each staff member under "Owner" - how many tickets did they handle, how many are resolved, what was the average time to first reply/to resolution, average number of responses, total time worked, average time worked, time trends...
6. Category performance
For each "Category" - how many tickets were inside, how many are resolved, what was the average time to first reply/to resolution, average number of responses, total time worked, average time worked, time trends ...
The perfect solution will have a select box in all the above reports allowing to select each report by all or only by a selected category, priority or agent.
IV) OTHER REQUIREMENTS
- reports should work in Excel 2010+, preferably tested on several Excel versions,
- please indicate how long it will take you to finish,
- when posting a bid please add text "my new reports" so I will know you have read all this. Bids without the "my new reports" keyword will be ignored,
- this will be considered as "Work under hire" according to US law (copyrights belong to project holder)
- please show me any previous Excel reports you generated (bidders with samples of past Excel work will have priority)
Please feel free to ask if you have any questions.
Happy bidding!
Klemen