Repo for code to generate and store example Freshdesk ticket activity data

• python >= 3.7
• sqlite3 >= 3.28

These scripts have been tested using Ubuntu, results may vary in Windows or MacOS.

• numpy
• random
• sqlite3

The following state machine has been implemented to generate freshdesk data.

Transitions between states are randomly chosen until a state is reached where there are no more transitions.

Data lands in a raw format into the stg_activity table. It is then normalized into the freshdesk_ tables for reporting.

This codebase contains scripts to

1. Generate example Freshdesk data
2. Create an sqlite schema to store data
3. Insert freshdesk data into the sqlite database
4. Normalize freshdesk data into a relational structure
5. Generate a ticket summary report

To run all of the steps end to end, run the Makefile in this directory.

git clone https://github.com/mantmic/freshdesk-data
cd freshdesk-data
make

To generate data use the ticken_gen.py file. This can be executed in the bash terminal. Run the following to see the parameters

python3 ticket_gen.py -h

Running the ticket_gen.py will by default generate data for 1000 tickets and output the data to activities.json

python3 ticket_gen.py

To set up the database run the following using test.db

sqlite3 test.db
.read sql/schema.sql

To insert data generated by the ticket_gen.py script run the data_insert.py script. Run the following to see the parameters.

python3 data_insert.py -h

Running data_insert.py will by default insert data from the activities.json file into the sqlite database, test.db

python3 data_insert.py

To normalize raw Freshdeck data in the sqlite schema run the following

sqlite3 test.db
.read sql/update_data.sql

To output the ticket data summary report run the following

sqlite3 test.db -header
.read sql/ticket_summary.sql