Question

Can I use reports to create a spreadsheet of data to export for a mail merge?

  • 18 February 2022
  • 8 replies
  • 66 views

Userlevel 1
Badge
  • Inspirational User
  • 25 replies

The new reporting feature seems awesome, but way too advanced for me. I can’t figure it out...but what I’m trying to do, is use the date from Copper to merge into our proposal letter...is this something I can do? Where can I find really good information on how to use reporting...everything I’ve seen doesn’t address what I want to do and I’m having trouble interpreting what the videos show to my own needs….any help would be soooo appreciated.

 

 


This topic has been closed for comments

8 replies

Userlevel 7
Badge +7

Hi @ARCO, thanks for posting!

First, I totally get what you’re saying about interpreting videos/articles have applying it to your own use case. Reports are so specific to each company’s specific needs and setup, it’s hard to translate those generalized videos/articles into action items. This is an area I’ve been thinking about so, so I’d love to hear your thoughts on how we can improve our resources.

That being said, if you wanna talk live to someone about reports, we host some weekly sessions :slight_smile:

  1. Reporting Q&A - Wednesdays
  2. Ask-an-expert general Q&A - Thursdays
  3. Full list of all recurring webinars here

Now onto your question:

Can I use reports to create a spreadsheet of data to export for a mail merge? [...] [how to] use the date from Copper to merge into our proposal letter

So, it sounds like you’re storing a date in Copper that you want to pipe into some proposal letters. Can you tell me more about that date? Is it a custom date field in Copper?

If it’s a custom date field, you don’t need to set up a report- you can just export the records.

  1. Apply a filter (maybe based on the date or any other field)
  2. Select the records you’d like to export (see gif below)

This will give you an export file for those records, which you can then use for your mail merge.

Let me know if that addresses your question. :slight_smile:

Userlevel 1
Badge

Hi, Thanks for getting back to me so quickly. I will check out the Q&A sessions for reports, that sounds helpful.

I made a big typo in my original post. I meant to type the word data, not date. We gather some data in Copper and then send proposals that use some of that data - for instance, the project number, contact name, company name and address, the value of the proposal etc. I was hoping to create a report that we can export as an Excel file to use on the actual proposal (as a mail merge) word document. Most of the fields are custom, but some are not.

 

Thanks again for your help.

ARCO

Userlevel 7
Badge +7

@ARCO Ah, data not date! Gotcha.

So, you can use the same export method I mentioned above. The downside to that is it’s going to give you every single field that the system has for the record type. So you’ll end up with quite a wide spreadsheet that you’ll need to comb through.

If you only want to export certain information, you can use our reporting system to build a table and then export it.

Here’s a video on building and exporting a table that contains:

  • Opportunity Name
  • Opportunity ID (optional, recommended for when you have different opps with the same name)
  • Contact Name
  • Company Name
  • Address
  • Project Number
  • Value

 

Let me know if that’s helpful :slight_smile:

Userlevel 1
Badge

Hi Michelle,

 

Yes!! This was really helpful, thank you so much for taking the time to make it. After posting my question, I did a little more poking around and somehow figured most of this out, but your confirmation is very helpful. Now that I have this data that I can export, I’m wondering if I can create any calculation fields on the value field….for instance, our Value = a “full” study, but we also offer 3 other levels of service, which are manually calculated based on the cost of the “full”….is this something copper could calculate for me, and then add those values to the table and export too?

Userlevel 7
Badge +7

@ARCO glad you found it helpful!

our Value = a “full” study, but we also offer 3 other levels of service, which are manually calculated based on the cost of the “full”….is this something copper could calculate for me, and then add those values to the table and export too?

So that answer is yes, it’s possible, but the best way to do it depends on your setup and pricing model. Can you let me know:

  1. It sounds like a customer only buys one service level at a time - is that correct?
  2. How to do you calculate the cost of those other service levels? Is it based on a % of the full service cost? E.g. Service Level 1 = 50% of the cost of a full study?
  3. Do you have custom fields in Copper to store the cost of each service level for each deal?

If your team needs to see what the actual cost of each service level it, I might recommend having workflow automation do the calculation for you each time someone fills out the Value of a full study. Basically we tell the system: workflow automation, please take 50% of the Value and pop it into the Service Level 1 box. Then we include this information in the reporting system where you can also export it.

There’s also a way to have the reporting system do this but you’d have to navigate into the reporting system every time you want to see the cost for a service level. That’s fine for exporting/mail merging, but it doesn’t give your regular users the ability to see the service level costs if they’re looking at their opportunities.

Userlevel 1
Badge

Hi Michelle,

Thank you so much for your response. Wow, there are many options in Copper that I wasn’t aware of. To answer your questions:

  1. It sounds like a customer only buys one service level at a time - is that correct? Yes, that’s correct.
  2. How to do you calculate the cost of those other service levels? Is it based on a % of the full service cost? E.g. Service Level 1 = 50% of the cost of a full study? Something like that...our lesser service = $250 less than the full, except for one which is 10% less. The more expensive options are = 1.5 times the “full” option. 
  3. Do you have custom fields in Copper to store the cost of each service level for each deal? I do not currently have custom fields for these other values, but could add them if that’s the easier way to make this happen. Our team does not need to see the values as all really, they just start to work on the project after the level of service has been agreed to.

Thanks again for all of your time with this, I really appreciate your help.

Ruth

Userlevel 7
Badge +7

Thanks for the additional details @ARCO !

I’m going to give you an example for demonstration purposes. This is the pricing model we’ll use in our example:

  1. Basic, which is priced at the Value minus $250
  2. Full/Standard, which is priced at 1x the Value
  3. Premium, which is priced at 1.5x the Value

What we can do is get the system to calculate the Value of Chosen Service Level based on the Value and the Service Level.

For example, if your Value is $1000 and your Service Level is Basic, the system will automatically put $750 into the Value of Chosen Service. Then you can include Service Level and Value of Chosen Service into your reports to analyze things like:

  • How much revenue did we generate from all service levels?
  • How much revenue did we generate from Basic service levels?
  • How much extra revenue did we generate by selling Premium packages instead of Full/Standard packages?

Ok, so how do we set this up?

 

1. Create the fields you’ll need

First, you’ll need to make sure there’s a place to record which Service Level you are selling to a potential client. In your case, that is two fields:

  1. A dropdown field for Service Level, and
  2. A currency field for Value of Chosen Service Level

You can create a new field by going to Settings > Customization > Manage Fields on Records. In the example screenshot above, I also added a collapsible section to organize all the pricing fields into one section.

 

2. Set up the the workflow automations

Workflow automation is a tool within Copper that lets you say: if X happens in Copper, do Y. In this case, we’re saying:

  • If someone selects the Basic Service Level, subtract $250 from the Value and store that in Value of Chosen Service Level
  • If someone selects the Full/Standard Service Level, take the Value and copy it into Value of Chosen Service Level
  • If someone selects Premium Service Level, calculate 150% of the Value and store that in Value of Chosen Service Level

So, we’ll need three workflow automation rules for our three service level packages.

To set up a workflow automation, go to Settings > Automation > Workflow Automation > Add Workflow.

Let’s walk through the first workflow automation for Basic tier.

First, add a name for the workflow automation. Maybe something like “Calculate value of Basic service level”

This is what the Trigger setup will look like:

Make sure you select Opportunity and When a specific field is update. You’ll see we have two trigger conditions below, which mean:

  1. The Value on that Opportunity must be above 0, and
  2. The Service Level must be equal to Basic

 

This is what the Action will look like:

Make sure you select Update and Opportunity.

Under our Action, we’re saying: Take the Value of Chosen Service Level, and use this formula to update it: monetary_value-250 (i.e. take the Value and subtract $250)

Then click Save and remember to turn the Workflow Automation on.

 

Then, you can set up Workflow Automation rules for each of your service levels.

For Full/Standard it would look like this:

 

For Premium it would look like this:

 

 

For there, you can pull those fields into your reports. Let me know if you’d like further guidance on these automations or on including those items in your reports.

If you prefer to get live help, we also offer some weekly sessions you can attend:

Thanks ARCO :slight_smile:

Userlevel 1
Badge

Hi Michelle,

Sorry I didn’t reply yesterday, I was out of the office most of the day. 1st of all THANKYOU!! This is really amazing, and I really appreciate all of the time this must have taken.

 

As you’ll recall this question started because I want to create a spreadsheet to use with a mail merge doc to send out bids - I need all of my pricing to show up in the spreadsheet for each job, not just after the client has selected an option. Although this will be used because I do enter the option they selected after they select it.

In your scenario, I would only see each one after the product was selected by the client. I think perhaps using the option of having this information calculated only for the report might be better. And truthfully, it’s a little more detailed than I originally told you. I was hoping to extrapolate and expand on the information you supplied. However, our pricing goes in our bid packets as a small spreadsheet that looks like the document attached here. 

Right now, I have a word document where the spreadsheet uses an F9 function to calculate each of the amounts after I enter the 8 week values. I’m hoping to utilize the report from Copper to enter this information into the word document (as well as other mail merge information from Copper) to run our bids in batches twice a week.

What are your thoughts on that? If I were to create 2 more custom fields where I could enter the 8 week ‘With Site Visit’ and the 8 week ‘No Site Visit’ values, and use Copper’s “value” field for the 8 week ‘Full’ option can Copper create a report that calculates the rest of the bid information into a spreadsheet that I can then download and mail merge with my word doc? 

 

Again, I truly appreciate your help with this. If I can get this all figured out, my boss is going to think we’re geniuses (will I know you already are)

ARCO