Web Application for Analyzing Sales Data

Problem:

Sales teams need a quick way to review and analyze customer part sales. The ERP system used does not have a report that fits the need. Previously, sales teams would export data into spreadsheets and manually arrange data for each customer. Using a spreadsheet was time consuming, error prone, and lacked all data needed for a thorough analysis.

Summary:

• Web application designed using PHP and MySql database.
• Sales inputs customer number or part number into appplication and data grid returned.
• When customer number entered, grid displays yearly columns, part number rows, and data cells that show total sales, total qty sold, and average margin.
• When part number entered, grid shows each customer that uses this part as a row with same data in cells.
• Spot trends like new business, lost business, margin changes.
• Input form has yearly dropboxes to limit date ranges.
• Large data sets with 1000s of rows still load in a few seconds.

Screenshots:

Results:

• Reviews of key customer groups (20-30 key customers per sales team) take about 1 day instead of weeks.
• Time saved allows sales team reviews to be done regularly.
• Margin issues spotted easily.
• Salespeople print reports for sales calls resulting in more productive discussions.

Possible Future Improvements:

• Direct export from ERP database to MariaDB database. Currently data is exported from ERP to .csv file, then imported to MariaDB.
• Add data for customer returns or credits, which can change the yearly totals.
• Add customer cross references to part numbers.

Technology and Code:

• PHP multi-dimensional array used as storage for grid data.

// Part Data Array

static public function getPartDataArrayDateRange($part_numbers, $sales_data, $startyear, $endyear) {
        //take the part numbers array and create a 3 dimensional array with part number
        // as first dimension and year ($startyear to $endyear) as 2nd dimension.  3rd
        // dimension is a type of sales data.  Below 'total_sales', 'total_costs', 'total_quantity'.
        // initialize all values to zero.

        $year_start = $startyear;
        $year_end = $endyear;
        date_default_timezone_set('UTC');

        $a = array();

        //initialize all data to zero
        while ($row = mysql_fetch_array($part_numbers)) {
            for ($year=$year_start; $year <= $year_end; $year++) {
                $a[$row['No_']][$year]['total_sales'] = 0;
                $a[$row['No_']][$year]['total_costs'] = 0;
                $a[$row['No_']][$year]['total_quantity'] = 0;
            }
        }

        //reset pointer to first record, just in case
        mysql_data_seek($sales_data, 0);
        //fill in data based on sales history data
        while ($row_sales = mysql_fetch_array($sales_data)) {

            $a[$row_sales['No_']][$row_sales['Year']]['total_sales'] += $row_sales['Amount'];
            $a[$row_sales['No_']][$row_sales['Year']]['total_costs'] += ($row_sales['Quantity'] * $row_sales['Unit Cost (LCY)']);
            $a[$row_sales['No_']][$row_sales['Year']]['total_quantity'] += $row_sales['Quantity'];

        }

        return $a;
    }

• Database helper class simplifies calls to data source.

    // Call to Database class

    $db = new Database();

    $distinct_parts = $db->getDistintPartNumbersForCustomerDateRange($customer_number, $start_year, $end_year);
    $sales_history = $db->getShipmentsByCustomerDateRange($customer_number, $start_year, $end_year);

    $arr_sales_data = PartDataArray::getPartDataArrayDateRange($distinct_parts, $sales_history, $start_year, $end_year);

• Portion of the database.php class

    // database.php snippet

    class Database {

    private $_connection;

    public function __construct() {
        $this->_connection = $this->get_connection();
    }

    /* returns mysql connection */
    public function get_connection() {

        //sensitive data removed...
        $server = "removed";
        $username = "removed";
        $password = "removed";
        $database = "removed";

        $connection = mysql_connect($server, $username, $password) or die(mysql_error());
        mysql_select_db($database) or die(mysql_error());

        return $connection;
    }

    public function getHistoryPartCustomer($partnumber, $customernumber) {

        $query = sprintf("SELECT * FROM shipments_by_location WHERE No_ = '%s' AND `Sell-to Customer No_` = '%s' ORDER BY `Shipment Date`;",
                mysql_real_escape_string($partnumber),
                mysql_real_escape_string($customernumber) );

        $result = mysql_query($query, $this->_connection);

        return $result;

    }

    // ... database methods for other scenarios ...

    }