API query app for advanced users

[cbsteven]

I made a little app that uses a Google Docs Spreadsheet to help people query their own store using the 3DCart API and have the results download directly into your spreadsheet.


This is a fairly rough beta form but it should work well enough and be relatively easy to use, but it does require some understanding of SQL syntax. It also requires caution because you can use it to over-write some data in your store. (Any query that begins with the word 'SELECT' is safe)

You can view the app here: https://github.com/Nelluk/3dcartSheetScript

If people actually use it I would be happy to continue to develop it and add features or make it a little more user-friendly.

Let me know what you think!

[Brtp4]

Thanks!!

BP

[jleclair]

Great concept sir. I'll be watching the repo!

[elightbox]

cbsteven, Thank you. I have been reluctant to tackle the API, but this makes it so easy to query.

Here is the link to download the API reference with the tables & table fields. I had to find it to modify a query, so I thought I would make it easy to find for others.
https://support.3dcart.com/Knowledge...ical-reference

[cbsteven]

You're welcome, and let me know if there is anything you think can be improved. I use a version of this for my own store and did a quick conversion to making it fit for general use.

Things I use it for:

-look for 'new' orders that fail credit card AVS check, or have mis-matched billing addresses, and email me the order number to give a second look.

-export contact info to MailChimp (more granular control than the built in mailchimp plugin)

You could also use this to build your own Google Products export file, with a few simple modifications. It might not work for more than a few hundred products though, but I might try to fix that.

[elightbox]

Do you know where to find the Rewards data. I need a query for the rewards system with customer name, e-mail, and rewards balance.

Our mailers reminding customers with their rewards balance are amongst our most successful and they have an incredibly high open and conversion rate. Currently it is a laborious process because the rewards report does not have e-mail addresses, so I need to do an Access update query to update the rewards sheet with the customer e-mail.

[cbsteven]

Good question.. I didn't see anything in their published database schema.

Can you give more information on how you do it with your Access query?

[elightbox]

1-Export the reward balances
Reports > Business > Rewards-overall
2.Export customers
3.add a column in customers sheet for "name". create a formula and fill the column in "last, First" format (to match the rewards sheet)
4.add two columns in Rewards sheet for "first-name" & "e-mail"
5. import both sheets into Access
6. create an update query in Access to update the e-mail and first name in the rewards table from the customers table (link name from customer's table to Name in Rewards table)

After the update the Rewards sheet will have first name, e-mail address and rewards points. You can copy and paste into Mailchimp and send a mailer.
Don't forget to include a link to instructions on how to use the reward points.

It would be nice to just be able to pull the name,e-mail,& reward-points info in one shot from 3dcart

[cbsteven]

App Update

I made some improvements today:
- The results processing is MUCH more efficient. It can now handle queries that give several thousand results, instead of just a few hundred.
- 'Results' sheet is now cleared of data when a new query is run
- There is a new sheet called 'History'. Every time a query is run it will log the query you used, the date/time, and the number of results returned. If you remove the 'History' sheet then the logging will stop.


It'd be easiest to make a new copy of the spreadsheet from the original, or you can copy over the updated script source from the github files.


@elightbox: I emailed Jimmy to get some info on how to look up rewards, hopefully he can give me some guidance.

[cbsteven]

1-Export the reward balances

Got it. Jimmy pointed me at the customer_rewards table which isn't listed in the PDF I have.

SELECT c.billing_firstname, c.billing_lastname, c.email, SUM(r.points) AS total_points FROM customers c LEFT JOIN customer_rewards r ON (r.contactid = c.contactid) GROUP BY c.billing_firstname, c.billing_lastname, c.email

I added this to the spreadsheet's Examples list, and plan on using it myself!

[elightbox]

Thanks for looking this up, but it keeps timing out.

We close to 4K rewards customers, and 38K in the customer's table.

[cbsteven]

Are you using the new version of the sheet that I posted this afternoon?

[elightbox]

Yes, the new sheet.
This SQL

SELECT c.billing_firstname, c.billing_lastname, c.email, SUM(r.points) AS total_points FROM customers c LEFT JOIN customer_rewards r ON (r.contactid = c.contactid) GROUP BY c.billing_firstname, c.billing_lastname, c.email

[elightbox]

One more thing.
Customers who check the Box on Checkout_1 for product updates are automatically added to MailChimp now. I have verified this with certainty.
I don't know if they are added to the 3dcart mailing list as I do not utilize it.

[cbsteven]

Yes, the new sheet.
This SQL

Try this one. It will limit results to only those who have at least 100 points:

SELECT c.billing_firstname, c.billing_lastname, c.email, SUM(r.points) AS total_points FROM customers c LEFT JOIN customer_rewards r ON (r.contactid = c.contactid) GROUP BY c.billing_firstname, c.billing_lastname, c.email HAVING SUM(r.points) > 100

Barring that, you may have to split it up into more than one query, which should still be easier than your current method. Replace SELECT with SELECT TOP 2000 for example, and see if that works. Play with that until you find the limit where it times out, and then do the math to see how many queries you'll need to get the full set. Once you know that I can help you figure out how to split it up. For example you could do one query for people whose last name starts with A-M.