In this tutorial I'll show you how to build out automated sales reporting for your Shopify Store.

What you'll need.

• 1 x Shopify Store
• 1 x Integromat Account
• 1 x Google Sheet
• 1 x Twillio Account

First up, open Google Sheets and add the the following headers onto your sheet.

• Order Number
• Email
• Shipping Address
• City Phone
• Country
• Code
• Zip / Postcode
• Month
• Week
• Daily Total
• Weekly Total
• Monthly Total
• Todays Date
• This Week
• This Month

Next add some dummy data into the first batch of columns which will simulate orders that will come in from Shopify.

Once done move on to the next part.

‍

For this part we're going to add in some formulas to total up order values by day, week and month.

In the cell R2 enter '=TODAY()' to get todays date.

Next in cell N2, enter '=SUMIF(A:A,R2,B:B), this will give us the daily order value total.

Now in cell S2, enter '=WEEKNUM(R2)', this will give us the week number.

In cell T2 enter, '=MONTH(R2)' for the month of the year.

Next let's total the weekly amount by entering '=SUMIF(L:L,S2,B:B)'.

Now enter '=SUMIF(K:K,T2,B:B) to get the total for the month.

Once you've done this move onto the next step.

‍

In this part let's add some formulas that will let us get values in the weekly and months totals.

In cell L2, enter '=ARRAYFORMULA(IF(ISBLANK(A2), " ", WEEKNUM(A2)))'. This should now give you a number which relates to the week of the year currently.

Next in cell K2, enter '=ARRAYFORMULA(IF(ISBLANK(A2), " ", MONTH(A2)))'. Now we have our month.

Note this will only display if there is order data containing a date in the A column.

When you're done you're ready to move onto the next step.

‍

Open Integromat.

Create a Scenario and type Shopify and Google Sheets into the search bar. Press continue.

Now on your canvas click the ? and add Shopify. Connect your Shopify account and configure.

Now  add another module which is going to be Google Sheets. Connect your account and find the right worksheet.

Next map the Order Value, Order ID, Order Number, Email, Shipping Address, City, Phone, Country Code and Zip into the relevant fields.

Press ok.

Now in the Date field, let's format the date by clicking the Date and Time module in the blue pop up menu. Click the formatDate element.

Enter the Created At field in the first half of the brackets and then 'DD/MM/YYYY" in the second.

Press ok and test.

Now onto the next step.

‍

In Integromat, go back to the dashboard and create a new Scenario.

Add the Google Sheet module 'Get a Cell' and connect your sheet + cell N2 that related to our Daily Total.

Next add Twilio as the second module and link them together. If you are adding Twilio for the first time you will need to add your credentials.

Now inside of the Twilio module select Send a Message.

It should pull in your Twilio phone number in the Sender Number field. In the Recipient Number enter your own mobile number for testing.

In the message enter your report information containing the daily order value making sure you contain the value from the Get a Cell step.

Test it and check the SMS arrives.

Next click the Schedule Setting in the bottom left. Set it to a time of your choosing.

Set it live.

Add additional notification channels like Gmail or Slack now you know how to add modules.

That's it!

‍

If you would like to send the weekly and monthly values in your Daily Report then add two more Get a Cell modules in.

Configure the modules to look at the corresponding cells inside your Google Sheet.

Amend your report to reflect the new values.

All done.

I hope you enjoyed this tutorial and let me know if you have any issues.

Tom Osman

@tomosman on Twitter