Build Database Architecture and Write Complex MySQL Queries

This project was awarded to SovDyn for $32 USD / hour.

Get free quotes for a project like this
Project Budget
$25 - $50 USD / hour
Total Bids
Project Description

I want to basically make an app that takes most of the following data and stores it in a database that can then be assembled on queries similar to what you wrote so that it can be analyzed for trends. The purpose of the software is to provide a high-level statistical overview of a user's online sales and transactions. The software enables a user's site to accept webhook POST requests and record them to a local database.

Once the data is recorded, the software will present the data in such a way that it "answers questions" that are posed in the user interface. Here is a general overview of the types of questions that I, as the seller, think should be answered by analyzing the dataset.

- How much money did I make today/this week/month/year? How does this compare (numbers or percentage-wise) to a week ago today/last week/month/year?

- Which of my products are selling the best? How many of each did I sell today/this week/month/year?

- How's my soccer ball product selling? How many soccer balls have I sold today/this week/month/year? Which is the most popular variation - large or small? How much money has the soccer ball made me?

- Which countries am I selling the most in? Which products are most popular in each country? What areas of a particular country am I selling the most in? How much money have I earned from transactions in the UK this week?

- How have my sales been affected by the Site Maintenance event? Could this explain why sales were down last Friday?

- How many refunds have I had to process? How much have I had to spend on shipping these products? How does this affect the amount of money I've made over the past month?

These are just examples. I'll expect you to be opinionated and know how the database should best be structured without getting too out of control and focusing only on the parts that are relevant to making assumptions about product, location, and date/timing trends. Prove to me that you are able to answer these types of questions. I have a start on some of the queries that I can share as a base, but you'll need to write most of the queries and present to me the dataset so that I can use it to populate my templates.

Here is an example of the request data available to be recorded to the database: [url removed, login to view]

A full list of variables can be found at [url removed, login to view]

Note: we will not capture any data from the transaction that can personally identify the customer. (do not capture the customer's name, email address)

I did my best to describe what it is that I am looking for, but I may not be completely on point with the best technical solution to this project. I'm leaving that up to you, because you're smarter than I am and can take the reigns and make decisions for the best software architecture and user experience.

You should feel comfortable and have experience with the problems of performance, EAV and architecture, how to write efficient queries, and how to organize the results into comprehensive datasets. I have taken time to write this post, so to be sure that you've read and understand the requirements and are willing to take it on, please do not leave a generic message simply saying this will get done. Make me feel confident that you understand what I'm trying to achieve and try to provide evidence of being capable of my project, perhaps by sharing a past experience that you may have had with a similar project. This will only help us both work best together and give you a better chance at getting the job.

Awarded to:

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online