Discussions

Expand all | Collapse all

Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

  • 1.  Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 16 days ago
    I have a project management application that has a CRM component built into it.  My sales team enters their annual sales goal on a table called Sales Goals

    The sales team enters their data on the table and I can view it as follows.  Please note that we have two types of sales we track (design revenue and build revenue).  We are a design and construction firm.

    Now I have another table called Invoices.  I want to build a meter chart on the home page of each user showing their percentage towards goal. I also want to be able to view the bottom report with a comparison to show what has currently been invoiced and a percentage towards goal.  Again per quarter and annual separating the two types.

    I am struggling with how to relate this data....  My first instinct was I need to summarize the data out of the Invoices table.  But I need that sum sorted by type and sales person.  I would need to pull each out quarterly and could sum it up for the annual via formula.

    My other thought was if I need a snapshot table of some sort, but honestly I am not sure if I would need that as the data is always live.  It is not like it is a cumulative number like a pipeline tracker or something like that.

    Any advice on how to structure something like this?  I have to imagine someone has built this type of functionality into a CRM tool.

    Thanks!



    ------------------------------
    Ivan Weiss
    ------------------------------


  • 2.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 16 days ago
    Hi Ivan!

    Without having access to the data myself, this is my initial thinking...

    Relate Invoices to these Sales goals (or this could be done with a sync table if you don't want to relate it directly). I assume the salesperson is already on (or could be added to the invoice). The Sales goal would be the parent with Invoices as the child. I assume there is only one salesperson per invoice.

    You can then create your summary fields by quarter, type, and annual... I assume the information you'd need to filter these summaries is already included on the invoice (ie quarter & type). Separating it by salesperson will happen automatically as part of the relationship.

    Then it's just a matter of adding formula fields to the Sales goal table to give you the %.

    In my head, I believe this will do the trick for you. Let me know if it helps 😀👍

    ------------------------------
    Sharon Faust (QuickBaseJunkie.com)
    Founder, Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------



  • 3.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 15 days ago
    The good news is after some tinkering I came to a very similar thought as to what you had and implemented it.  The only thing I am sure of is for some reason I created a snapshot table as a table import....  To run daily and move the invoices over to it.  Honestly, I am not sure why I would need that after thinking about it some more.  So now I am just giving some thought on if I should eliminate that and pull the data directly from invoices.  I would think I want to as for some reason if something changes with an invoice, it affects the report to goals so I dont think the snapshot table is necessary.  Which means rebuilding that :( but oh well!

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 4.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 13 days ago
    Sharon, so quick question....  On the association I understand I make the relationship in Quick Base.  However, do my users have to associate an invoice with a sales goal record?  Otherwise are they not linked?  I am not quite sure how we get that association to match.  I basically need to auto match the sales goal with the sales person and year.  Otherwise they are not going to link....

    I dont think manually associating makes sense as the users wouldnt really understand the relationship and why they need to match them up.  Unless I just need to explain it and they need to make it habit.

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 5.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 13 days ago
    You could use Case or If logic in a formula field to auto populate the sales goal.

    Something along the lines of If([Person]="Jane" and [Year]="2020", "Sales Goal 1", null)

    ------------------------------
    Adam Keever
    ------------------------------



  • 6.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 13 days ago
    What I meant was dont I need to populate the "Related Sales Goal" field on the invoices?  If so, how do I auto associate them?  It is going to be a record number that doesnt really align with anything.

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 7.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 13 days ago
    I am sure there are other ways to achieve this, but here is one method that I know of that works. You will need five tables and six relationships.
    Here is the result using one user and one quarter for demonstration purposes:

    Each goal and invoice must select the team member, sales type, and quarter from the relationships and the one to many relationships allow goals and sales to be summarized by sales type and quarter on the team member table and formula numeric fields allow you to calculate the percentage of completion:









    ------------------------------
    Adam Keever
    ------------------------------



  • 8.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 13 days ago
    Ivan, what I saw in the screenshot you provided was 6 goal records (one for each salesperson) where the individual goals appeared to be fields on those records (not separate tables). 

    If you relate Invoices > Goals all you're users would need to do is select the salesperson associated with that invoice (assuming you set it as the proxy field).

    If you want it to automatically 'link up' I would suggest making the salesperson name the key field and making a 'user' field or other dropdown the related reference field on the Invoice. In this scenario, you would have to have the salesperson on the Invoice (or some other key identifier).

    ------------------------------
    Sharon Faust (QuickBaseJunkie.com)
    Founder, Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------



  • 9.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 13 days ago
    One - Sale Goals to Many- - Invoices is the relationship and make the Sale Person the proxy lookup field in invoices. Change the default record picker in Sales Goals to Team Member as the only field showing so that way people are only selecting names. I would suggest having a formula in the Invoices table to assign  the quarter based on invoice date if you don't want people to pick the quarter themselves. For example:
    If(Month([Invoice Date])>0 and Month([Invoice Date])<4,1,
    If(Month([Invoice Date])>3 and Month([Invoice Date])<7,2,
    If(Month([Invoice Date])>6 and Month([Invoice Date])<10,3,
    4)))
    Now with the relationship setup you can create the summary fields. Yes you could use the "is during" Criteria when making the summary instead. Make sure when you design this that you are thinking of what it will look like at the end of the year going into the next year with new sales goals.  Don't forget the field to select in Invoices if the project is design or build.


    ------------------------------
    jason johnson
    ------------------------------



  • 10.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 9 days ago
    Edited by Ivan Weiss 9 days ago
    So I think I followed all of this and here is a screenshot of my relationship.  I also got rid of the snapshot and now have sales goals going directly to Invoices.  But on my report I have nothing being summarized up from Invoices.  For some reason I dont think those records or automatically linking.  I guess I am not sure how I automatically get the related sales goal to link to the appropriate invoices

    I added a report to the invoices table to show my quarterly invoices so we can see the data.  Obviously I see the issue is the related sales goal column is empty.  When I am not understanding is how to auto populate that to create the link.  I dont want end users to have to link to sales goals manually for every single invoice.



    ------------------------------
    Ivan Weiss
    ------------------------------



  • 11.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 7 days ago
    Where is project solutions coming from?

    ------------------------------
    jason johnson
    ------------------------------



  • 12.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 7 days ago
    It is a lookup field on invoices being passed from several levels up.

    The root table is a Project.  It contained Project Solutions (which is a relation to team members).

    Projects<Opportunities
    Opportunities<Order
    Order<Invoices

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 13.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 4 days ago
    Just wanted to give this a nudge and see if anyone had any more input on it?  What I am stuck on is I have the association of Sales Goal< Invoices.  So I can summarize the invoices to the sales goals.  

    Except I cannot physically connect them.  How do I populate the related sales goal field on invoices?

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 14.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 4 days ago
    Hi Ivan,

    I know you're anxious to get this solved, I've been busy with clients who get first priority 😉, community is just in bits of free time.

    In my recommended solution (above) I stated"
    If you want it to automatically 'link up' I would suggest making the salesperson name the key field and making a 'user' field or other dropdown the related reference field on the Invoice. In this scenario, you would have to have the salesperson on the Invoice (or some other key identifier).


    Basically, before you create the relationship you need to have a field on the Invoices table for salesperson (and it needs to be populated, basic User field is fine), then on the Goal table make the salesperson field the key field. Now when you create the relationship instead of allowing it to generate the "related sales goal" field, you choose the Salesperson User field.

    Once done, all of your records will be linked. 🙌

    If there isn't already a populated salesperson field on the invoices table, someone will have to go through the work to designate which applies to who.



    ------------------------------
    Sharon Faust (QuickBaseJunkie.com)
    Founder, Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------



  • 15.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 4 days ago

    Thanks for the response Sharon. I completely understand the response time and of course respect the client piece first.  (I do own three of your courses just for a kudos on those) but I also know when it sits in the forums a bit it gets lost too so that is why the bump. 


    I understand the linking on key field to user except the problem is that wont be a unique identifier. For my sales goals I will have goals for 2020, 2021, 2022 etc. so each of those rows would have Ivan Weiss as the key and it wouldnt work. That is what I am stuck on. The key has to be an auto generated number, I think. 



    ------------------------------
    Ivan Weiss
    ------------------------------



  • 16.  RE: Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

    Posted 4 days ago
    I see...

    From the first screenshot you posted it looked like the records were by salesperson and the years were fields on the records. Looking again, I see they are grouped under 2020 sales goals.

    That does complicate things.

    It would still be possible to create a custom key field that is a combo of the user name & year (making it unique) that is on both tables. The process is more than I can get into now, but maybe this will point you in the right direction.

    ------------------------------
    Sharon Faust (QuickBaseJunkie.com)
    Founder, Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------